实验环境
- 操作系统 Redhat5.4 x86
- 数据库版本 oracle 11gR2 (11.2.0.1.0)
- 实验前已经做了RMAN全量备份包括controlfile、spfile
实验模拟
部分controlfile文件损坏或丢失
案例模拟
查询数据库中控制文件信息
SQL> col name for a60
SQL> set line 180
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------------ --- ---------- --------------
/u01/app/oracle/oradata/PROD/disk1/control1 NO 16384 580
/u01/app/oracle/oradata/PROD/disk2/control2 NO 16384 580
/u01/app/oracle/oradata/PROD/disk3/control3 NO 16384 580
SQL>
SQL>
SQL>
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/PROD/d
isk1/control1, /u01/app/oracle
/oradata/PROD/disk2/control2,
/u01/app/oracle/oradata/PROD/d
isk3/control3
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
删除部分controlfile文件
至少保留一份controlfile文件没有被损坏或丢失。
本例删除control1 和control3 保留control2文件。
SQL> !rm /u01/app/oracle/oradata/PROD/disk1/control1
SQL> !rm /u01/app/oracle/oradata/PROD/disk3/control3
SQL> !ls /u01/app/oracle/oradata/PROD/disk1/control1
ls: /u01/app/oracle/oradata/PROD/disk1/control1: No such file or directory
SQL>
SQL> !ls /u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control2
SQL> !ls /u01/app/oracle/oradata/PROD/disk3/control3
ls: /u01/app/oracle/oradata/PROD/disk3/control3: No such file or directory
重启数据库
重启数据库提示以下报错:
SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk1/control1'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 658509476 bytes
Database Buffers 171966464 bytes
Redo Buffers 5160960 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
检查alert日志发现以下报错信息:
Fri May 05 22:03:43 2017
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk3/control3'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk1/control1'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri May 05 22:03:43 2017
Checker run found 2 new persistent data failures
alert 日志文件提示 control1 control3 丢失文件不存在。
案例恢复
由于三份文件中的内容是完全相同的,因此我们可以通过操作系统命令从已存在的control2,分别复制出control1,control3文件,要保持文件名以及绝对路径与损坏前相同,如果因为存储故障短时间内无法保证文件路径以及文件名一致,则需要修改spfile文件中control_files参数的值。
恢复前与恢复后文件名以及路径不变情况:
[oracle@node1 ~]$ cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk1/control1
[oracle@node1 ~]$ cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk3/control3
[oracle@node1 ~]$ ls /u01/app/oracle/oradata/PROD/disk1/control1
/u01/app/oracle/oradata/PROD/disk1/control1
[oracle@node1 ~]$ ls /u01/app/oracle/oradata/PROD/disk3/control3
/u01/app/oracle/oradata/PROD/disk3/control3
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 22:18:43 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
RMAN命令恢复法:
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 5 22:31:14 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (not mounted)
RMAN>
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
828 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
822 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
828 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
822 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Use a multiplexed copy to restore control file /u01/app/oracle/oradata/PROD/disk2/control3
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_89851306.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_89851306.hm
contents of repair script:
# restore control file using multiplexed copy
restore controlfile from '/u01/app/oracle/oradata/PROD/disk2/control2';
sql 'alter database mount';
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting restore at 05-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/PROD/disk2/control1
output file name=/u01/app/oracle/oradata/PROD/disk2/control2
output file name=/u01/app/oracle/oradata/PROD/disk2/control3
Finished restore at 05-MAY-17
sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete
Do you want to open the database (enter YES or NO)? YES
database opened
RMAN> exit
Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 22:36:23 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk2/control1
/u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ ls -la /u01/app/oracle/oradata/PROD/disk2/control*
-rw-r----- 1 oracle oinstall 9519104 May 5 22:37 /u01/app/oracle/oradata/PROD/disk2/control1
-rw-r----- 1 oracle oinstall 9519104 May 5 22:37 /u01/app/oracle/oradata/PROD/disk2/control2
-rw-r----- 1 oracle oinstall 9519104 May 5 22:37 /u01/app/oracle/oradata/PROD/disk2/control3
[oracle@node1 ~]$
恢复前与恢复后文件名以及路径发生变化的情况:
模拟disk1、disk3两块存放controlfile文件的磁盘都损坏了段时间内无法恢复,临时将control1、control3文件存放在disk2目录下,与control2保持一致,步骤如下:
SQL> alter system set control_files='/u01/app/oracle/oradata/PROD/disk2/control1','/u01/app/oracle/oradata/PROD/disk2/control2','/u01/app/oracle/oradata/PROD/disk2/control3' scope=spfile;
System altered.
SQL> !cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk2/control1
SQL> !cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk2/control3
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 658509476 bytes
Database Buffers 171966464 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk2/control1
/u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control3
SQL>
全部controlfile文件都损坏或丢失
案例模拟
手动删除所有控制文件:
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 22:40:30 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk2/control1
/u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control3
SQL> !rm /u01/app/oracle/oradata/PROD/disk2/control*
SQL> !ls /u01/app/oracle/oradata/PROD/disk2/control*
ls: /u01/app/oracle/oradata/PROD/disk2/control*: No such file or directory
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk2/control1'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 658509476 bytes
Database Buffers 171966464 bytes
Redo Buffers 5160960 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
案例恢复
利用RMAN从之前的备份中进行恢复:
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 5 22:42:09 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (not mounted)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1071 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
1068 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control2 is missing
1065 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1071 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
1068 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control2 is missing
1065 CRITICAL OPEN 05-MAY-17 Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk2/control3 was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD/disk2/control2 was unintentionally renamed or moved, restore it
3. If file /u01/app/oracle/oradata/PROD/disk2/control1 was unintentionally renamed or moved, restore it
4. If a standby database is available, then perform a Data Guard failover initiated from the standby
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore a backup control file
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2346970307.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2346970307.hm
contents of repair script:
# restore control file
restore controlfile from autobackup;
sql 'alter database mount';
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting restore at 05-MAY-17
using channel ORA_DISK_1
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PROD
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/PROD/autobackup/2017_05_05/o1_mf_s_943223985_djs3olf9_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/PROD/autobackup/2017_05_05/o1_mf_s_943223985_djs3olf9_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/PROD/disk2/control1
output file name=/u01/app/oracle/oradata/PROD/disk2/control2
output file name=/u01/app/oracle/oradata/PROD/disk2/control3
Finished restore at 05-MAY-17
sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete
RMAN>
至此,数据库控制文件恢复已经完成,注意每次进行玩以后及时做一次rman的全量备份。