如果控制文件损坏那么如何恢复？恢复控制文件的方式有哪几种
【RMAN】如果控制文件损坏那么如何恢复？恢复控制文件的方式有哪几种？
真题1、如果控制文件损坏那么如何恢复？恢复控制文件的方式有哪几种？
答案：如果控制文件有多个，而只损坏了单个控制文件，那么只需要关闭数据库，拷贝其它好的控制文件覆盖掉坏的控制文件即可。也可以修改参数文件，只保留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 命令从现有控制文件恢复控制文件。
• 如果控制文件存储为常规文件系统文件，则： – 关闭数据库。 – 复制现有的控制文件来替代丢失的控制文件。 成功恢复控制文件后，打开数据库。
版权所有 © 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_870567448_bdwndtqk_.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_870567448_bdwndtqk_.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_870567448_bdwndtqk_.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_870567448_bdwndtqk_.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_870567448_bdwndtqk_.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: {<RET>=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_BACKUP_RESTORE.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_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%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 语句执行一个完全数据库恢复。
3 指定resetlogs
indicate 忽略日志文件内容，或者日志文件不存在。
指定datafile
除了只读表空间的文件（可以之后添加）和临时表空间的数据文件，列出所有数据文件，就算这些文件需要进行恢复。
4 ARCHIVELOG | NOARCHIVELOG
如果忽略了ARCHIVELOG | NOARCHIVELOG oracle默认采用非归档模式。
二实验步骤：
1 以noresetlogs方式创建控制文件，控制文件内容
[oracle@oracle backup]$ cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/CRM/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oracle/CRM/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oracle/CRM/redo03.log' SIZE 200M BLOCKSIZE 512,
GROUP 4 '/oracle/CRM/redo02.dbf' SIZE 200M BLOCKSIZE 512
DATAFILE
'/oracle/CRM/system01.dbf',
'/oracle/CRM/sysaux01.dbf',
'/oracle/CRM/undotbs01.dbf',
'/backup/users01.dbf',
'/oracle/CRM/pos.dbf',
'/oracle/CRM/erp.dbf',
'/oracle/CRM/user01.dbf',
'/oracle/CRM/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
2 以resetlogs方式创建控制文件，控制文件内容
cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/CRM/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oracle/CRM/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oracle/CRM/redo03.log' SIZE 200M BLOCKSIZE 512,
GROUP 4 '/oracle/CRM/redo02.dbf' SIZE 200M BLOCKSIZE 512
DATAFILE
'/oracle/CRM/system01.dbf',
'/oracle/CRM/sysaux01.dbf',
'/oracle/CRM/undotbs01.dbf',
'/backup/users01.dbf',
'/oracle/CRM/pos.dbf',
'/oracle/CRM/erp.dbf',
'/oracle/CRM/user01.dbf',
'/oracle/CRM/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
3 恢复过程
noresetlogs方式
SQL> @/backup/control.sql
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 1006635112 bytes
Database Buffers 234881024 bytes
Redo Buffers 8921088 bytes
Control file created.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open;
Database altered.
resetlogs方式
SQL> @/backup/control.sql
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 1006635112 bytes
Database Buffers 234881024 bytes
Redo Buffers 8921088 bytes
Control file created.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 2526007 generated at 08/26/2012 01:13:10 needed for thread 1
ORA-00289: suggestion : /oracle/archive/1_8_791790817.dbf
ORA-00280: change 2526007 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/archive/1_8_791790817.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/archive/1_8_791790817.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2526007 generated at 08/26/2012 01:13:10 needed for thread 1
ORA-00289: suggestion : /oracle/archive/1_8_791790817.dbf
ORA-00280: change 2526007 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
重建控制文件时resetlogs与noresetlogs的使用情况
控制文件中记录着数据库的数据文件,日志文件,备份数据等信息,更为重要的,控制文件中还记录了数据库的检查点
和scn信息,这些信息在数据恢复的过程中将起到关键性作用.
一个正常运行的数据库,通常控制文件都存在多份镜像,这些镜像的内容是完全相同的,oracle缺省就创建多份控制
文件更说明了控制文件的重要:
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl
/u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl
可以通过如下一条命令将控制文件的创建语句备份到跟踪文件中:
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
SQL> host sz /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
rz
Starting zmodem transfer. Press Ctrl+C to cancel.
100% 8 KB 8 KB/s 00:00:01 0 Errors
此跟踪文件中会记录控制文件的创建脚本,脚本包含两个主要的段落,其中一段如下所示:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
CHARACTER SET ZHS16GBK
;
当数据库处于nomount状态下时,可以通过运行这段脚本创建控制文件,控制文件会自动创建到参数文件中
记录控制文件的位置(原来的控制文件在创建过程会被覆盖).这里需要理解的一个主要选项是:
noresetlogs/resetlogs.在跟踪文件中包含如下注释,详细解释了这两个选项的含义:
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.
当数据库当前的redo log都可用时,可以通过noresetlogs参数重建控制文件,此时oracle能够从日志文件中
读取redo信息,记录到控制文件中,由于redo中记录的信息足以重演所有提交成功的事务,所以最终能够实现
完全恢复,成功打开数据库,这时的数据库就如同进行了一次断电之后的实例恢复,数据没有损失,重做日志
可以继续向前写入:
下面测试来看一下以noresetlogs重建控制文件进行数据库恢复的过程
先在数据库正常运行状态下对控制文件执行一次转储:
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19350.trc
这个转储文件中将包含数据库的检查点,redo thread信息,数据文件等信息,看一下
log file records内容:
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 3, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea466
Low scn: 0x0000.000ea474 05/02/2013 11:40:58
Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea474
Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000e8ed8
Low scn: 0x0000.000ea466 05/02/2013 11:40:52
Next scn: 0x0000.000ea474 05/02/2013 11:40:58
从记录信息中我们可以看到redo02.log文件的next scn:0xffff.ffffffff,所以redo02.log文件是当前的
日志文件,我们可以从v$log视图中查看当前的重做日志组
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
接下来通过shutdown abort模拟一次数据库故障:
SQL> shutdown abort;
ORACLE instance shut down.
启动数据库到nomount状态,再来使用noresetlogs参数来重建控制文件:
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 83886080 bytes
Redo Buffers 2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
14 '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
15 '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
16 '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
17 '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
18 '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
此时再来对控制文件进行一次转储,检查log file records部分:
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x0 dup: 1
Archive links: fwrd: 2 back: 3 Prev scn: 0x0000.000ea466
Low scn: 0x0000.000ea474 05/02/2013 11:40:58
Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
Archive links: fwrd: 0 back: 1 Prev scn: 0x0000.000ea474
Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x0 dup: 1
Archive links: fwrd: 1 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.000ea466 05/02/2013 11:40:52
Next scn: 0x0000.000ea474 05/02/2013 11:40:58
从上面的记录我们可以看到重建的控文件能够从当前的日志文件获得正确的SCN及时间点等信息.同样地,控制
文件也能够从数据文件中获得详细的检查点信息:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
.....
DATA FILE #2:
name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
.....
DATA FILE #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
.....
DATA FILE #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
DATA FILE #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
DATA FILE #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
.....
从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:
Stop scn: 0xffff.ffffffff,接下来对数据库执行恢复,当恢复完成后再对控制文件进行转储:
SQL> recover database;
Media recovery complete.
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc
来观察此跟踪文件中的数据文件信息:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x2 head=9 tail=9 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
....
DATA FILE #2:
name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x2 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
....
DATA FILE #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
....
DATA FILE #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
DATA FILE #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
DATA FILE #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
....
经过恢复之后,数据文件达到了一致状态,checkpoint scn(0x0000.000efd7d)和Stop scn(0x0000.000efd7d)
达到了一致,此时数据库就完成了恢复,数据库可以顺利启动:
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
2 SIZE 30M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 40M;
Tablespace altered.
现在我们来实验使用resetlogs方式来重建控制文件:
模拟数据库故障
SQL> shutdown abort;
ORACLE instance shut down.
以resetlogs来重建控制文件
SQL> startup nomount
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 83886080 bytes
Redo Buffers 2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
14 '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
15 '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
16 '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
17 '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
18 '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
此时对控制文件进行一次转储
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc
观察转储的跟踪文件中的log file record的信息:
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #2:
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #3:
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
从上面的信息可以看到此时控制文件中的日志信息都是空的,oracle认为resetlogs方式下,当前的日志文件
已经损坏,那么就意味着oracle可能会丢失提交成功的数据,恢复将是一次不完全的介质恢复.
此时的数据文件信息如下:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
....
DATA FILE #2:
name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
....
DATA FILE #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
....
DATA FILE #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
DATA FILE #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
DATA FILE #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
....
从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:
Stop scn: 0xffff.ffffffff
不完全恢复最终要求数据库通过resetlogs方式打开,resetlogs将会强制清空或重建联机重做日志文件.
此时执行恢复必须使用backup controlfile选项,否则将会报错:
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 982400 generated at 05/02/2013 12:53:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_18_%u_.arc
ORA-00280: change 982400 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
如果在线日志操坏,那么恢复到最后,oracle提示的最后一个归档日志将是不存在的(如果在线日志没有
损坏,则可以指定在线日志文件执行恢复),此时可以输入cancel取消恢复,然后可以强制打开数据库:
alter database open resetlogs;
SQL> recover database using backup controlfile;
ORA-00279: change 1003572 generated at 05/02/2013 13:20:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_19_%u_.arc
ORA-00280: change 1003572 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
2 SIZE 30M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 40M;
Tablespace altered.
二、 在线日志无损情况下的重建
然后就是添加临时数据文件，注册备份的信息，最后做备份等后续操作。
三、 在线日志损坏情况下的重建
四、 具有只读数据文件情况下的重建
一.1.1.1 OCP
【OCP真题1】 Are using Recovery Manager (RMAN) with a recovery catalog to perform the backup operation at regular intervals使用恢复管理器（RMAN）对恢复目录定期执行备份操作
Set the control file autobackup to “on” 设置控制文件自动备份为“开”
Are maintaining image copies of the database files维持数据库映像文件的副本
You have lost the server parameter file (SPFILE) and the control file. Which option must you consider before restoring the SPFILIE: and the control file by using the control file autobackup? 你已经失去了服务器参数文件（SPFILE）和控制文件。恢复SPFILIE之前你认为哪个选项必须考虑：使用控制文件自动备份的控制文件？
A. setting DBID for the database
B. using the RMAN SWITCH command
C. using the RMAN SWITCH command使用RMAN切换命令 B和C一样
D. starting up the database Instance In the NOMOUNT state
Answer: C
【OCP真题2】 Your database is up and running and one of your three control files is accidentally erased. You start RMAN and run the following command:
RESTORE CONTROLFILE FROM AUTOBACKUP;
Which of the following statements is true? (Choose all that apply.)
A. The command restores only the missing control file.
B. The command restores all the control files.
C. The command fails because the database is running.
D. This is the correct way to address this problem.
E. This is not the correct way to address this problem.
Answer: CE
【OCP真题3】 You have discovered that one of three control files has been lost. What steps would you follow to recover that control file? 你已经发现三个控制文件之一已丢失。为恢复控制文件，你会遵循以下什么样的步骤？
a. Shut down the database. 关闭数据库
b. Restore a control-file copy from backup media.
c. Use the create control file command to create a new control file.
d. Copy the backup control file into place.
e. Create a new copy of the control file from one of the surviving control files. 从一个幸存的控制文件创建一个新的控制文件的副本。
f. Recover the database using the recover database using backup controlfile command.
g. Start up the database. 启动数据库。
A. a, b, f, g
B. c, f, g
C. a, d, f, g
D. a, f, g
E. a, e, g
关闭数据库; 从一个幸存的控制文件创建一个新的控制文件的副本; 启动数据库
Answer: E
【OCP真题4】 Your database is up and running and one of your three control files is accidentally erased. You start RMAN and run the following command: 你的数据库在启动和运行，你的三个控制文件之一被意外删除。启动RMAN并运行以下命令：
RESTORE CONTROLFILE FROM AUTOBACKUP;从自动备份中还原控制文件
Which of the following statements is true? (Choose all that apply .)
A. The command restores only the missing control file.
B. The command restores all the control files.
C. The command fails because the database is running. 该命令失败，因为数据库正在运行。
D. This is the correct way to address this problem.
E. This is not the correct way to address this problem. 这不是正确的方法来解决这个问题
This is not the correct way to address this problem. The command will fail because the database is
running. Additionally, this is not the correct way to approach the loss of one of several control files.
The better way to approach this loss is to shut down the database and simply copy one of the
surviving control files over to where the missing control file existed. 这是不是正确的方法来解决这个问题，该命令将失败，因为数据库正在运行。此外，这是不正确的方式来处理多个控制文件的损失之一。更好的方式来处理这方面的损失是关闭数据库，并简单地复制一个幸存的控制文件存在丢失的控制文件的地方。
Answer:
C,E
【OCP真题5】 You have lost all your database control files. To recover them, you are going to use the results of the alter database backup controlfile to trace command. Your datafiles and your online redo logs are all intact.
Which of the following is true regarding your recovery?
A. You will need to open the database with the resetlogs command.
B. All you need to do is execute the trace file from SQL*Plus and it will perform the recovery for you
C. You will use the resetlogs version of the create controlfile command.
D. You will use the noresetlogs version of the create controlfile command.
E. You will use the trace file to create a backup control file, and then you will recover the database with the recover database using backup controlfile command
Answer: D
About Me
.............................................................................................................................................
● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号：230161599（满）、618766405
● 微信群：可加我微信，我拉大家进群，非诚勿扰
● 联系我请加QQ好友（646634621），注明添加缘由
● 于 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成
● 最新修改时间：2018-04-01 06:00 ~ 2018-04-31 24:00
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
.............................................................................................................................................
● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/
● 小麦苗OCP、OCM、高可用网络班：http://blog.itpub.net/26736162/viewspace-2148098/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群 小麦苗的微店
.............................................................................................................................................
|
............................................................................................ ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ............................................................................................ 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（DB宝），学习最实用的数据库技术。
|
