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

 

【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;

&说明:

有关控制文件在缺失归档日志的情况下的恢复可以参考我的BLOGhttp://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方式打开数据库。

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

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

&说明:

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

 

 

 








 


一.1.1.1  丢失了控制文件

 

丢失了控制文件

如果控制文件丢失或损坏,则实例通常会中止。

 如果控制文件存储在 ASM 磁盘组中,则恢复方案如下:

 使用 Enterprise Manager 执行指导式恢复。

 将数据库置于NOMOUNT模式,然后使用 RMAN 命令从现有控制文件恢复控制文件。

RMAN> restore controlfile from

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

 如果控制文件存储为常规文件系统文件,则:

 关闭数据库。

 复制现有的控制文件来替代丢失的控制文件。

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

 

版权所有 © 2010Oracle。保留所有权利。

 

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

如果使用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”状态,这样做可能会在警告日志和追踪日志中产生更多有用有价值的信息,并且对数据恢复顾问也有好处。

 

wpsAE0.tmp 

 

wpsAE1.tmp 

 

wpsAF1.tmp 

wpsB02.tmp 

 

 

wpsB03.tmp 

wpsB14.tmp 

wpsB15.tmp 

wpsB25.tmp 

 

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

wpsB36.tmp 

 

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

wpsB37.tmp 

 

一.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>

wpsB67.tmp

我们在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结尾

wpsB77.tmp 

wpsB88.tmp 

wpsB89.tmp 

wpsB9A.tmp 

三、 使用控制文件快照

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

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  重建控制文件---无备份情况下的恢复

 

wpsBAA.tmp 

wpsBBB.tmp 

 

 

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.