实验环境

操作系统 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的全量备份。