合 如果控制文件损坏那么如何恢复？恢复控制文件的方式有哪几种？

如果控制文件损坏那么如何恢复？恢复控制文件的方式有哪几种？

答案：如果控制文件有多个，而只损坏了单个控制文件，那么只需要关闭数据库，拷贝其它好的控制文件覆盖掉坏的控制文件即可。也可以修改参数文件，只保留1个控制文件。如果损坏了全部控制文件，那么需要重新创建控制文件或从备份恢复。在有控制文件备份的情况下，restore controlfile命令可以用来还原控制文件。在还原控制文件后需要对数据库执行完全介质恢复并以resetlogs选项来打开数据库。

RMAN可以将控制文件还原到它的默认存储位置，也可以使用restore controlfile ... to destination来指定控制文件的恢复位置。当还原控制文件时，控制文件的默认位置是由参数control_files控制的。如果没有设置control_files参数，那么数据库判断还原控制文件存储位置的规则将会与没有设置control_files参数时创建控制文件时使用的规则一样。

如下命令可以从备份集中恢复控制文件：

restore controlfile from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak';

restore controlfile to '/home/oracle/a.ctl' from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak' ;

在将控制文件还原到默认位置时，数据库必须处于nomount状态。如果从自动备份中还原控制文件，那么必须首先设置数据库DBID，然后执行restore controlfile from autobackup命令。

最后，可以考虑使用控制文件快照进行恢复。如果没有任何备份的控制文件，那么需要重建控制文件。重建控制文件的脚本可以通过命令“ALTER DATABASE BACKUP CONTROLFILE TO TRACE;”获取。

在启动数据库的时候，如果报控制文件的版本不一致（ORA-00214），那么只需要将高版本的数据库的控制文件覆盖低版本的数据库的控制文件即可，如下所示：

SYS@OCPLHR1> startup

ORACLE instance started.

Total System Global Area 521936896 bytes

Fixed Size 2229944 bytes

Variable Size 301992264 bytes

Database Buffers 209715200 bytes

Redo Buffers 7999488 bytes

ORA-00214: control file '/u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl' version 2701 inconsistent with file

'/u01/app/oracle/oradata/OCPLHR1/control01.ctl' version 2699

SYS@OCPLHR1> ! cp /u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl /u01/app/oracle/oradata/OCPLHR1/control01.ctl

SYS@OCPLHR1> alter database mount;

Database altered.

SYS@OCPLHR1> alter database open;

& 说明：

有关控制文件在缺失归档日志的情况下的恢复可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152506/

真题1、Oracle的控制文件在缺失归档日志的情况下的恢复步骤有哪些？

在恢复控制文件时“recover database”命令可能需要使用归档日志。所谓缺失归档日志，是指控制文件从备份还原之后，在执行“recover database”命令恢复时报告找不到相应的日志导致恢复终止的情况。

这种情况下的恢复操作主要步骤如下：

①　首先还原控制文件，方式不限。

②　执行“recover database”命令将报RMAN-06054错误，即找不到某归档日志。

③　查看相关的动态性能视图，对问题定位，确认问题与控制文件，而不是数据文件相关（与数据文件相关必须进行不完全恢复）。

④　利用create controlfile 命令重建控制文件。

⑤　再次执行“recover database”命令，还会报RMAN-06054错误，这次是找不到另一个归档日志，其序列号应该大于第二步中的。

⑥　查看v$log视图确定第5步中所要的是哪个日志。

⑦　执行SQLPLUS的”recover database using backup controlfile“命令，等”Specify log：“提示符出现后给出正确的在线日志路径，直到命令成功结束。

⑧　以resetlogs方式打开数据库。

⑨　由于创建的控制文件内不会有临时数据文件的信息，需要重新将其添加回临时表空间。

⑩　将控制文件内其他丢失的信息用catalog和configure等命令再添加回去。

& 说明：

有关控制文件在缺失归档日志的情况下的恢复可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152115/

一.1.1.1 丢失了控制文件

丢失了控制文件如果控制文件丢失或损坏，则实例通常会中止。? 如果控制文件存储在 ASM 磁盘组中，则恢复方案如下：– 使用 Enterprise Manager 执行指导式恢复。– 将数据库置于NOMOUNT模式，然后使用 RMAN 命令从现有控制文件恢复控制文件。RMAN> restore controlfile from**'+DATA/orcl/controlfile/current.260.695209463';**? 如果控制文件存储为常规文件系统文件，则：– 关闭数据库。– 复制现有的控制文件来替代丢失的控制文件。成功恢复控制文件后，打开数据库。 版权所有 © 2010，Oracle。保留所有权利。

丢失了控制文件后，可选的恢复方案取决于控制文件的存储配置以及是至少还有一个控制文件还是丢失了所有文件。

如果使用ASM存储，并且至少还有一个控制文件副本，您可以使用Enterprise Manager执行指导式恢复，或者使用RMAN执行手动恢复，如下所示：

将数据库置于NOMOUNT模式。

连接到 RMAN 并发出restore controlfile命令来从现有的控制文件恢复控制文件，例如：

restore controlfile from '+DATA/orcl/controlfile/current.260.695209463';

成功恢复控制文件后，打开数据库。

如果您的控制文件存储为常规文件系统文件并且至少还有一个控制文件副本，这样，在数据库处于关闭状态时，您只需将剩余的控制文件中的一个复制到丢失文件的位置。如果介质故障是由于磁盘驱动器或控制器缺失而造成的，则将剩余的控制文件中的一个复制到其它某个位置，然后通过更新实例的参数文件来指向新位置。或者，可从初始化参数文件中删除对丢失的控制文件的引用。请注意：Oracle 建议始终至少保留两个控制文件。

注：《Oracle Database 11g：数据库管理－课堂练习 II》课程中介绍了如何在丢失了所有控制文件后进行恢复。

一.1.1.2 控制文件恢复前的准备

为了恢复控制文件，实例应该处于nomount状态，如果发现问题的时候实例还未关闭，首先应该使用“shutdown abort”命令关闭实例，接着虽然可以使用“startup nomount”命令，但是建议使用“startup” 命令启动实例，使其自然卡在 “nomount”状态，这样做可能会在警告日志和追踪日志中产生更多有用有价值的信息，并且对数据恢复顾问也有好处。

----跳过某个已经删除的表空间：

控制文件自动备份打开的情况下：

一.1.1.3 有备份情况下的恢复

一、 控制文件之一丢失**(单个控制文件丢失或损坏)**

正确的处理步骤：

① 关闭数据库

② 从其它位置拷贝一个

③ 启动数据库

我们知道数据库的控制文件都不止一个（一般为3个），这些控制文件互相为镜像，所以只需要将其他没损坏的控 制 文件重命名为损坏的控制文件即可。

我现在有三个控制文件

-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control01.ctl

-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control02.ctl

-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control03.ctl

现在删除一个控制文件control01.ctl

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 104859216 bytes

Database Buffers 176160768 bytes

Redo Buffers 2973696 bytes

ORA-00205: error in identifying control file, check alert log for more info

将control02.ctl复制为control01.ctl

[oracle@localhost orcl]$ cp control02.ctl control01.ctl

成功启动数据库

SQL> alter database mount;

Database altered.

SQL> alter database open；

Database altered.

注：单个控制文件损坏也可以使用下面所讲的全部控制文件损坏时的恢复方法，只是这里直接重命名其他控制文件的方法比较快，也是推荐的一种恢复方法。

二、 各种情况下的丢失

1、 已知备份文件位置

restore controlfile from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak';

restore controlfile to '/home/oracle/a.ctl' from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak' ;

如果丢失或损坏所有的控制文件就需要从备份中还原控制文件。restore controlfile命令用来还原控制文件。在还原控制文件后需要对数据库执行完全介质恢复并以resetlog选项来打开数据库。RMAN可以将控制文件还原到它的默认存储位置，也可以使用restore controlfile ... to destination来指定位置。

从已经知的控制文件备份中还原控制文件

SQL> show parameter control_files NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_files string /u01/app/oracle/oradata/test/c ontrol01.ctl, /u01/app/oracle/ oradata/test/control02.ctl, /u 01/app/oracle/oradata/test/con trol03.ctl

显示当前可用的备份

RMAN> list backup; List of Backup Sets=================== BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------77 693.50K DISK 00:00:02 28-JAN-15 BP Key: 75 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131713 Piece Name: /u02/test_df870182233_s95_s1 List of Archived Logs in backup set 77 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 13 2928236 28-JAN-15 2928830 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------78 Full 166.91M DISK 00:01:19 28-JAN-15 BP Key: 76 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131716 Piece Name: /u02/test_df870182236_s96_s1 List of Datafiles in backup set 78 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/system01.dbf 2 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/users01.dbf 5 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/example01.dbf 6 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/test01.dbf BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------79 7.50K DISK 00:00:01 28-JAN-15 BP Key: 77 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131841 Piece Name: /u02/test_df870182321_s97_s1 List of Archived Logs in backup set 79 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 2928830 28-JAN-15 2928868 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------80 Full 9.42M DISK 00:00:02 28-JAN-15 BP Key: 78 Status: AVAILABLE Compressed: NO Tag: TAG20150128T131843 Piece Name: /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d Control File Included: Ckp SCN: 2928874 Ckp time: 28-JAN-15 SPFILE Included: Modification time: 28-JAN-15

从上面的信息可以看到备份集80是控制文件与spfile文件的备份

下面来删除当前数据库的所有控制文件：

[root@oracle11g ~]# cd /u01/app/oracle/oradata/test/[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 11804672 Feb 1 11:36 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 11:36 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo01.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 11:36 example01.dbf-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 19:05 system01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 19:05 undotbs01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 19:05 sysaux01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:10 redo03.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:13 redo03.log-rw-r----- 1 oracle oinstall 11804672 Feb 1 19:14 users01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 19:14 undotbs01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 19:14 test01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 19:14 system01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 19:14 sysaux01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:14 redo01.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 19:14 example01.dbf

向测试表t2中插入一些数据库

SQL> insert into t2 select from dba_objects; 51319 rows created. SQL> select count() from t2; COUNT(*)---------- 102560 SQL> commit; Commit complete.

这里因为是从linux操作系统层面删除了所有控制文件，因为在数据库没有关闭的情况下文件的句柄没有释放所以数据库还能运行。

人为将数据库异常终止

[root@oracle11g test]# ps -ef | grep smonoracle 3463 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3463

启动数据库：

SQL> startupORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytesORA-00205: error in identifying control file, check alert log for more info

alert日志的内容如下:

ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/test/control01.ctl'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Sun Feb 01 19:18:18 CST 2015ORA-205 signalled during: ALTER DATABASE MOUNT...

找不到控制文件不能将数据库置于mount状态.现在通过备份来还原控制文件执行完全数据库恢复:

RMAN> restore controlfile from '/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d'; Starting restore at 01-FEB-15using channel ORA_DISK_1 channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:05output filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15 RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 SQL> select status from v$instance; STATUS------------------------MOUNTED

执行完全恢复

RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 17 is already on disk as file /u01/app/oracle/oradata/test/redo02.logarchive log thread 1 sequence 18 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 19 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u02/1_15_870133266.dbf thread=1 sequence=15archive log filename=/u02/1_16_870133266.dbf thread=1 sequence=16archive log filename=/u01/app/oracle/oradata/test/redo02.log thread=1 sequence=17archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=18archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=19media recovery complete, elapsed time: 00:00:06Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs SQL> select status from v$instance; STATUS------------OPEN SQL> select count() from t2; COUNT()---------- 102560

表t2中的记录与恢复之前相同，说明恢复成功。

当还原控制文件时，控制文件的默认位置是由参数control_files控制的。如果没有设置control_files参数，那么数据库判断还原控制文件存储位置的规则将会与没有设置control_files参数时创建控制文件时使用的规则一样。

2、 使用了**恢复目录**

当没有使用恢复目录时，必须从控制文件自动备份中还原控制文件。如果从控制文件自动备份中还原控制文件，数据库必须置于nomount状态。必须首先设置数据库的DBID，然后执行restore controlfile from autobackup命令

1.人为删除所有控制文件

[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log

2.人为将数据库异常终止

[root@oracle11g test]# ps -ef | grep smonoracle 3063 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3063

3.将数据库启动到nomount状态

SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes

4.从以前的备份信息中可以找到如下信息，其中c-2155613261-20150201-03中的2155613261就是DBID

Starting Control File and SPFILE Autobackup at 01-FEB-15piece handle=/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-03 comment=NONEFinished Control File and SPFILE Autobackup at 01-FEB-15

5.还原控制文件

RMAN> show controlfile autobackup format; RMAN configuration parameters are:CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'STB' TO '%F';CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F'; RMAN> set dbid 2155613261; executing command: SET DBID RMAN> restore controlfile from autobackup; Starting restore at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: no autobackups found in the recovery areachannel ORA_DISK_1: looking for autobackup on day: 20150201channel ORA_DISK_1: autobackup found: c-2155613261-20150201-03channel ORA_DISK_1: control file restore from autobackup completeoutput filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15

6.恢复数据库

RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 13 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/oradata/test/redo02.logarchive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log filename=/u01/app/oracle/oradata/test/redo02.log thread=1 sequence=2archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3media recovery complete, elapsed time: 00:00:03Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs

RMAN会使用自动备份的格式与DBID来判断在什么存储位置来搜索控制文件自动备份。如果找到，RMAN就会从备份中将控制文件还原到由control_files参数所指定的所有位置

RMAN使用恢复目录还原控制文件

1.人为删除所有控制文件

[root@oracle11g test]# ls -lrt

total 2213868

-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf

-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf

-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf

-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log

-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log

-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf

-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf

-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf

-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf

-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log

-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl

-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl

-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl

[root@oracle11g test]# rm -rf control*.ctl

[root@oracle11g test]# ls -lrt

total 2185068

-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf

-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf

-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf

-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log

-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log

-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf

-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf

-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf

-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf

-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log

2.人为将数据库异常终止

[root@oracle11g test]# ps -ef | grep smonoracle 4135 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 4135

3.将数据库启动到nomount状态

SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes

4.还原控制文件

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy Recovery Manager: Release 10.2.0.5.0 - Production on Sun Feb 1 23:04:03 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: test (not mounted)connected to recovery catalog database RMAN> restore controlfile; Starting restore at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISK channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: reading from backup piece /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01 tag=TAG20150201T213315channel ORA_DISK_1: restore complete, elapsed time: 00:00:04output filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15

5.执行完全恢复

RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 8 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=4media recovery complete, elapsed time: 00:00:01Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogsnew incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete

①、 将控制文件还原到新目录

有一种将控制文件还原到一个或多个新目录的方法是修改control_files参数，然后用没有任何参数的restore controlfile命令将控制文件还原到默认位置。例如，如果在有些控制文件目录所在的磁盘出现故障还原控制文件，可以修改control_files参数将出现故障的磁盘使用其它的磁盘来替代，然后执行restore controlfile命令来还原控制文件。

如果不修改control_files参数也可以使用restore controlfile to 'filename' [from autobackup]命令来将控制文件还原到你所指定的位置。

示例：

RESTORE CONTROLFILE TO '/tmp/my_controlfile';

下面的命令将使用自动备份将控制文件还原到'/u01/app/oracle/‘目录下

RMAN> restore controlfile to '/u01/app/oracle/control_temp.ctl' from autobackup; Starting restore at 02-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkpchannel ORA_DISK_1: control file restore from autobackup completeFinished restore at 02-FEB-15

上面的命令可以在数据库为nomount,mount,open状态下进行，因为不会覆盖任何当前使用的控制文件。在将控制文件还原到新目录后，可以修改control_files参数来引用新目录下的控制文件。

使用备份控制文件的限制

在使用备份控制文件还原数据库后，你必须执行recover database来恢复数据库并且必须执行alter database open resetlogs来打开数据库。

3、 设置了**设置闪回区**

设置闪回区的情况下还原控制文件所使用的命令是相同的。然而如果当前数据库正在使用闪回区，RMAN通过对所有基于控制文件中的基于磁盘的备份和镜像副本和任何在闪回区中而不在还原的控制文件中的备份执行隐式的crosscheck来更新从备份中还原的控制文件。因此还原后的控制文件会完整的和精确的记录在闪回区中的所有备份和其它任何在备份该控制文件时所知道的备份。这提高了在数据库还原操作中的可用性。

下面来看一个使用闪回区还原控制文件的实例：

1.环境检查，看是否已经启用闪回区与设置控制文件自动备份

SQL> show parameter db_recover NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string /u01/app/oracle/flash_recovery_areadb_recovery_file_dest_size big integer 2G SQL> select flashback_on from v$database; FLASHBACK_ON------------------YES RMAN> show controlfile autobackup; RMAN configuration parameters are:CONFIGURE CONTROLFILE AUTOBACKUP ON;

从上面信息可知已经启用了闪回区并设置了控制文件自动备份

2.创建一个表空间,在数据库结构发生变化时，就会自动备份控制文件

SQL> create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management auto; Tablespace created.

从alert日志中可以看到产生的控制文件自动备份的文件信息

create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management autoMon Feb 02 00:17:28 CST 2015Starting control autobackupControl autobackup written to DISK device handle '/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkp'Completed: create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management auto

其实控制文件和spfile同时被自动备份了

查看闪回区是否存在自动备份文件

[root@oracle11g 2015_02_02]# ls -lrttotal 19360-rw-r----- 1 oracle oinstall 9895936 Feb 2 00:17 o1_mf_s_870567448bdwndtqk.bkp

3.人为删除所有控制文件

[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log

4.人为将数据库异常终止

[root@oracle11g test]# ps -ef | grep smonoracle 3068 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3068

5.将数据库启动到nomount状态

SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes

6.恢复控制文件

RMAN> restore controlfile from autobackup; Starting restore at 02-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkpchannel ORA_DISK_1: control file restore from autobackup completeFinished restore at 02-FEB-15

在上面的还原控制文件的过程可以看到如下内容说明是使用存储在闪回区中的控制文件自动备份来还原控制文件

channel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkp

磁带备份在还原控制文件后不会自动执行crosscheck。如果正使用磁带备份，那么在还原控制文件并将数据库置于mount状态后，必须手工执行crosscheck.

RMAN> CROSSCHECK BACKUP DEVICE TYPE SBT;

7.执行完全恢复

RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 8 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=4media recovery complete, elapsed time: 00:00:01Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogsnew incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete

4、 缺失归档日志的情况下的恢复

众所周知，恢复控制文件时“recover database”命令可能需要使用归档日志。所谓缺失归档日志，是指控制文件从备份还原之后，在执行“recover database”命令恢复时报告找不到相应的日志导致恢复终止的情况。

这种情况下的恢复操作主要步骤如下：

① 首先还原控制文件，方式不限

② 执行“recover database”命令将报RMAN-06054错误，即找不到某归档日志

③ 查看相关的动态性能视图，对问题定位，确认问题与控制文件，而不是数据文件相关（与数据文件相关必须进行不完全恢复）

④ 利用create controlfile 命令重建控制文件

⑤ 再次执行“recover database”命令，还会报RMAN-06054错误，这次是找不到另一个归档日志，其序列号应该大于第二步中的

⑥ 查看v$log视图确定第5步中所要的是哪个日志

⑦ 执行SQLPLUS的”recover database using backup controlfile“命令，等”Specify log：“提示符出现后给出正确的在线日志路径，直到命令成功结束。

⑧ 以resetlogs方式打开数据库

⑨ 由于创建的控制文件内不会有临时数据文件的信息，需要重新将其添加回临时表空间

⑩ 将控制文件内其他丢失的信息用catalog和configure等命令再添加回去。

一、当前current日志序列号为：5，此时进行控制文件备份

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL>

RMAN> backup current controlfile;

Starting backup at 2015-02-04 16:28:13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 2015-02-04 16:28:14

channel ORA_DISK_1: finished piece 1 at 2015-02-04 16:28:15

piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1vpuel4t_1_1 tag=TAG20150204T162813 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2015-02-04 16:28:15

RMAN>

二、多次切换日志后，现在的CURRENT日志是20号，所有控制文件丢失并且第15号归档日志丢失，数据库启动后停留在了nomount状态：

SQL> alter system switch logfile;

。。。。。。。。

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 18

Next log sequence to archive 20

Current log sequence 20

SQL>

RMAN> delete archivelog sequence 15;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=257 device type=DISK

List of Archived Log Copies for database with db_unique_name LILOVE

=====================================================================

Key Thrd Seq S Low Time

44 1 15 X 2015-02-04 16:29:58

​ Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_15_870711361.dbf

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archived log

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_15_870711361.dbf RECID=44 STAMP=870798598

Deleted 1 objects

RMAN>

[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/*

-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/control01.ctl

-rw-r----- 1 oracle oinstall 10076160 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/control02.ctl

-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo01.log

-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/redo02.log

-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo03.log

-rw-r----- 1 oracle asmadmin 608182272 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/sysaux01.dbf

-rw-r----- 1 oracle asmadmin 775954432 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/system01.dbf

-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:15 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf

-rw-r----- 1 oracle asmadmin 20979712 Feb 4 11:15 /u01/app/oracle/oradata/utf8test/temp01.dbf

-rw-r----- 1 oracle asmadmin 52436992 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/undotbs01.dbf

-rw-r----- 1 oracle asmadmin 10493952 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/users01.dbf

[root@rhel6_lhr ~]# rm -rf /u01/app/oracle/oradata/utf8test/control0*

[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/*

-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo01.log

-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/redo02.log

-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo03.log

-rw-r----- 1 oracle asmadmin 608182272 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/sysaux01.dbf

-rw-r----- 1 oracle asmadmin 775954432 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/system01.dbf

-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:15 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf

-rw-r----- 1 oracle asmadmin 20979712 Feb 4 11:15 /u01/app/oracle/oradata/utf8test/temp01.dbf

-rw-r----- 1 oracle asmadmin 52436992 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/undotbs01.dbf

-rw-r----- 1 oracle asmadmin 10493952 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/users01.dbf

[root@rhel6_lhr ~]#

SQL> startup force;

ORACLE instance started.

Total System Global Area 501059584 bytes

Fixed Size 2229744 bytes

Variable Size 356518416 bytes

Database Buffers 134217728 bytes

Redo Buffers 8093696 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL>

告警文件报错：

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/utf8test/control02.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 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/utf8test/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE MOUNT...

三、下面，我们开始恢复：

RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1vpuel4t_1_1';

Starting restore at 2015-02-04 16:44:10

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/utf8test/control01.ctl

output file name=/u01/app/oracle/oradata/utf8test/control02.ctl

Finished restore at 2015-02-04 16:44:11

RMAN>

查看控制文件的确已经恢复：

[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/con*

-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:44 /u01/app/oracle/oradata/utf8test/control01.ctl

-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:44 /u01/app/oracle/oradata/utf8test/control02.ctl

[root@rhel6_lhr ~]#

四、下面我们挂载数据库：

RMAN> mount database;

database mounted

released channel: ORA_DISK_1

RMAN>

五、下边恢复数据库将报错，表示找不到15号归档文件：

RMAN> recover database;

Starting recover at 2015-02-04 16:47:55

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

datafile 5 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/oradata/utf8test/redo03.log

archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/oradata/utf8test/redo01.log

archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/oradata/utf8test/redo02.log

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_870711361.dbf thread=1 sequence=5

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_870711361.dbf thread=1 sequence=6

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_870711361.dbf thread=1 sequence=7

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_8_870711361.dbf thread=1 sequence=8

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_9_870711361.dbf thread=1 sequence=9

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_10_870711361.dbf thread=1 sequence=10

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_11_870711361.dbf thread=1 sequence=11

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_870711361.dbf thread=1 sequence=12

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_13_870711361.dbf thread=1 sequence=13

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_14_870711361.dbf thread=1 sequence=14

unable to find archived log

archived log thread=1 sequence=15

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/04/2015 16:47:58

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 15 and starting SCN of 1927288

RMAN>

若此时打开数据库，将报很多的错误：

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/04/2015 16:50:38

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/04/2015 16:50:49

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'

六、分析原因，首先查看目前已知的归档文件最大的日志序列号是多少？

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

20

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL> select GROUP#,SEQUENCE#,MEMBERS,STATUS,ARCHIVED from v$log;

GROUP# SEQUENCE# MEMBERS STATUS ARC

1 4 1 INACTIVE YES

3 3 1 INACTIVE YES

2 5 1 CURRENT NO

SQL>

答案为20，如果归档已经是20了，那么current日志一定是大于20的，而我的数据库的在线日志组数量为3个，也就是说在线日志的最小序列号大于17，进而得知所有数据文件的完全检查点必然超过了17号日志的最后一条重做记录。那么结论就是数据文件最多只需要17号之后的日志就能将恢复完成。

那么控制文件是从几号开始恢复的呢？由v$log可知是从5号开始恢复的，恢复到15号日志的时候报错了，所以我们只需要让控制文件放弃17号就可以顺利过关了。这个方法就是使用”create controlfile“创建一个新的控制文件。这个新的控制文件不知道current日志的序列号，不会强制所要任何日志对其恢复。

首先生成创建命令并重启至nomount状态：

SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt';

Database altered.

SQL> startup force nomount;

ORACLE instance started.

Total System Global Area 501059584 bytes

Fixed Size 2229744 bytes

Variable Size 356518416 bytes

Database Buffers 134217728 bytes

Redo Buffers 8093696 bytes

SQL>

我们在trace文件中得到并执行noresetlogs版本的”create controlfile“命令：

CREATE CONTROLFILE REUSE DATABASE "lilove" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/utf8test/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/utf8test/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/utf8test/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/utf8test/system01.dbf',

'/u01/app/oracle/oradata/utf8test/sysaux01.dbf',

'/u01/app/oracle/oradata/utf8test/undotbs01.dbf',

'/u01/app/oracle/oradata/utf8test/users01.dbf'

CHARACTER SET AL32UTF8

;

将以上命令在sqlplus中执行，等”Control file created.“出现，数据库已经自动mount了。然后再执行recover database命令就将至少从17号日志开始，越过了15号这个阻碍：

RMAN> recover database;

Starting recover at 2015-02-04 17:21:17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery

unable to find archived log

archived log thread=1 sequence=20

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/04/2015 17:21:18

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 20 and starting SCN of 1927308

RMAN>

从结果得到，15号不用了，但是报20号找不到，而20号归档是存在的，是在线日志，导致此问题的原因是新创建的控制文件有一个缺陷：使用这种控制文件恢复时RMAN通道只会一直地找归档日志，而无视在线日志。所以，恢复到尾声阶段的时候一定会报RMAN-06054错误，此时再查下v$log：

SQL> select GROUP#,SEQUENCE#,MEMBERS,STATUS,ARCHIVED from v$log;

GROUP# SEQUENCE# MEMBERS STATUS ARC

1 19 1 INACTIVE NO

3 18 1 INACTIVE NO

2 20 1 CURRENT NO

SQL>

原来20号是在线日志，接下来使用sqlplus的”recover database using backup controlfile“命令，可以手动指定恢复过程中所使用的日志，然后resetlogs打开数据库：

SQL> recover database using backup controlfile;

ORA-00279: change 1927308 generated at 02/04/2015 16:30:05 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_20_870711361.dbf

ORA-00280: change 1927308 for thread 1 is in sequence #20

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/utf8test/redo02.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

最后根据得到的控制文件trace中的内容执行如下语句：

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 3 DAYS');

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/changetracking/rman_change_track.ctf' REUSE;

ALTER DATABASE RENAME FILE 'MISSING00005' TO '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf';

ALTER TABLESPACE "TBS_READ" ONLINE;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/utf8test/temp01.dbf' REUSE;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

VARIABLE RECNO NUMBER;

SQL>

PL/SQL procedure successfully completed.

SQL> SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 3 DAYS');

PL/SQL procedure successfully completed.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/changetracking/rman_change_track.ctf' REUSE;

Database altered.

SQL> ALTER DATABASE RENAME FILE 'MISSING00005' TO '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf';

Database altered.

SQL> ALTER TABLESPACE "TBS_READ" ONLINE;

Tablespace altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/utf8test/temp01.dbf' REUSE;

Tablespace altered.

SQL>

最后不要忘记全备数据库。

①、 以noresetlogs结尾

三、 使用控制文件快照

使用控制文件的快照进行恢复，其实也是一种采用备份来进行恢复的策略。

SYS@OCPLHR1> select name from v$controlfile;

NAME

----------------------------------------------------------------------------

/u01/app/oracle/oradata/OCPLHR1/control01.ctl

/u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl

[oracle@OCPLHR dbs]$ ll

total 9796

-rw-rw---- 1 oracle oinstall 1544 Mar 25 21:00 hc_OCPLHR1.dat

-rw-rw---- 1 oracle oinstall 1544 Mar 22 20:02 hc_OCPLHR2.dat

-rw-r----- 1 oracle oinstall 24 Mar 25 20:26 lkDUMMY

-rw-r----- 1 oracle oinstall 24 Jan 17 20:08 lkOCPLHR1

-rw-r----- 1 oracle oinstall 24 Jan 17 20:16 lkOCPLHR2

-rw-r----- 1 oracle oinstall 1536 Mar 25 20:09 orapwOCPLHR1

-rw-r----- 1 oracle oinstall 1536 Jan 17 20:25 orapwOCPLHR2

-rw-r----- 1 oracle oinstall 9977856 Mar 25 20:58 snapcf_OCPLHR1.f

-rw-r----- 1 oracle oinstall 3584 Mar 25 20:54 spfileOCPLHR1.ora

-rw-r----- 1 oracle oinstall 2560 Mar 25 19:54 spfileOCPLHR2.ora

[oracle@OCPLHR dbs]$ cp snapcf_OCPLHR1.f /u01/app/oracle/oradata/OCPLHR1/control01.ctl

[oracle@OCPLHR dbs]$ cp snapcf_OCPLHR1.f /u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl

[oracle@OCPLHR dbs]$

[oracle@OCPLHR dbs]$

[oracle@OCPLHR dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 21:01:41 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: OCPLHR1 (not mounted)

RMAN> alter database mount;

using target database control file instead of recovery catalog

database mounted

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 03/25/2018 21:02:09

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open RESETLOGS;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 03/25/2018 21:02:17

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/OCPLHR1/system01.dbf'

RMAN> recover datafile 1;

Starting recover at 2018-03-25 21:02:30

Starting implicit crosscheck backup at 2018-03-25 21:02:30

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 device type=DISK

Crosschecked 28 objects

Finished implicit crosscheck backup at 2018-03-25 21:02:31

Starting implicit crosscheck copy at 2018-03-25 21:02:31

using channel ORA_DISK_1

Crosschecked 4 objects

Finished implicit crosscheck copy at 2018-03-25 21:02:32

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 03/25/2018 21:02:32

RMAN-06067: RECOVER DATABASE required with a backup or created control file

RMAN> recover database;

Starting recover at 2018-03-25 21:03:17

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/OCPLHR1/redo01.log

archived log file name=/u01/app/oracle/oradata/OCPLHR1/redo01.log thread=1 sequence=1

media recovery complete, elapsed time: 00:00:01

Finished recover at 2018-03-25 21:03:18

RMAN> alter database open ;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 03/25/2018 21:03:32

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open NORESETLOGS;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"

RMAN-01008: the bad identifier was: NORESETLOGS

RMAN-01007: at line 1 column 21 file: standard input

RMAN> alter database open RESETLOGS;

database opened

RMAN>

一.1.1.4 重建控制文件**---无备份情况下的恢复**

alter database backup controlfile to trace as '/home/oracle/aa.txt';

告警信息：

Sun Mar 25 21:06:42 2018

alter database backup controlfile to trace as '/home/oracle/aa.txt'

Completed: alter database backup controlfile to trace as '/home/oracle/aa.txt'

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "OCPLHR1" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/OCPLHR1/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/OCPLHR1/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/OCPLHR1/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/OCPLHR1/system01.dbf',

'/u01/app/oracle/oradata/OCPLHR1/sysaux01.dbf',

'/u01/app/oracle/oradata/OCPLHR1/undotbs01.dbf',

'/u01/app/oracle/oradata/OCPLHR1/users01.dbf',

'/u01/app/oracle/oradata/OCPLHR1/example01.dbf'

CHARACTER SET ZHS16GBK

;

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 3');

-- Configure RMAN configuration record 2

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

-- Configure RMAN configuration record 3

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- Configure RMAN configuration record 4

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUPRESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/bak/cf%F.ctl''');

-- Configure RMAN configuration record 5

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET');

-- Configure RMAN configuration record 6

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DATAFILE BACKUP COPIES FOR DEVICE TYPE','DISK TO 1');

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_11%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_11%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_11%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_11%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_11%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- Block change tracking was enabled, so re-enable it now.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE '/home/oracle/bct_ocplhr1.log' REUSE;

-- Set Database Guard and/or Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/OCPLHR1/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

一、 重建控制文件resetlogs和noresetlogs的区别

重建控制文件又分两种情况：resetlogs 和noresetlogs。使用使用resetlogs 重建控制文件后，RMAN恢复又会涉及到incarnation 问题，resetlogs只需要数据文件在位，而Noresetlogs 就不会出现incarnation的问题，但是必须是数据文件和online log文件都在位。

如何选择哪个脚本来恢复控制文件的关键就在于：

1.Set NORESETLOGS case

The following commands will create a new control file and use it to open the database.

Data used by Recovery Manager will be lost.

Additional logs may be required for media recovery of offline.

Use this only if the current versions of all online logs are available.

2.Set RESETLOGS case

The following commands will create a new control file and use it to open the database.

Data used by Recovery Manager will be lost.

The contents of online logs will be lost and all backups will be invalidated.

Use this only if online logs are damaged.

一：注意事项：

1 指定reuse

表明被初始化参数CONTROL_FILES 识别的控制文件能够被覆盖使用。如果忽略该参数，任何已经存在的控制文件被数据库检测到，则返回一个报错。

2 指定SET DATABASE

表明要更改数据库的名字，名字长度能达到8个字节。除此之外，你必须指定resetlogs语句，如果你想重新命名数据库的名字，并保留已经存在的日志文件，则创建控制文件语句执行后

使用alter database recover using bakcup controlfile 语句执行一个完全数据库恢复。

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。