合 如果控制文件损坏那么如何恢复?恢复控制文件的方式有哪几种?
Tags: Oracle
如果控制文件损坏那么如何恢复?恢复控制文件的方式有哪几种?
答案:如果控制文件有多个,而只损坏了单个控制文件,那么只需要关闭数据库,拷贝其它好的控制文件覆盖掉坏的控制文件即可。也可以修改参数文件,只保留1个控制文件。如果损坏了全部控制文件,那么需要重新创建控制文件或从备份恢复。在有控制文件备份的情况下,restore controlfile命令可以用来还原控制文件。在还原控制文件后需要对数据库执行完全介质恢复并以resetlogs选项来打开数据库。
RMAN可以将控制文件还原到它的默认存储位置,也可以使用restore controlfile ... to destination来指定控制文件的恢复位置。当还原控制文件时,控制文件的默认位置是由参数control_files控制的。如果没有设置control_files参数,那么数据库判断还原控制文件存储位置的规则将会与没有设置control_files参数时创建控制文件时使用的规则一样。
如下命令可以从备份集中恢复控制文件:
restore controlfile from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak';
restore controlfile to '/home/oracle/a.ctl' from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak' ;
在将控制文件还原到默认位置时,数据库必须处于nomount状态。如果从自动备份中还原控制文件,那么必须首先设置数据库DBID,然后执行restore controlfile from autobackup命令。
最后,可以考虑使用控制文件快照进行恢复。如果没有任何备份的控制文件,那么需要重建控制文件。重建控制文件的脚本可以通过命令“ALTER DATABASE BACKUP CONTROLFILE TO TRACE;”获取。
在启动数据库的时候,如果报控制文件的版本不一致(ORA-00214),那么只需要将高版本的数据库的控制文件覆盖低版本的数据库的控制文件即可,如下所示:
SYS@OCPLHR1> startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 301992264 bytes
Database Buffers 209715200 bytes
Redo Buffers 7999488 bytes
ORA-00214: control file '/u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl' version 2701 inconsistent with file
'/u01/app/oracle/oradata/OCPLHR1/control01.ctl' version 2699
SYS@OCPLHR1> ! cp /u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl /u01/app/oracle/oradata/OCPLHR1/control01.ctl
SYS@OCPLHR1> alter database mount;
Database altered.
SYS@OCPLHR1> alter database open;
& 说明:
有关控制文件在缺失归档日志的情况下的恢复可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2152506/
真题1、Oracle的控制文件在缺失归档日志的情况下的恢复步骤有哪些?
在恢复控制文件时“recover database”命令可能需要使用归档日志。所谓缺失归档日志,是指控制文件从备份还原之后,在执行“recover database”命令恢复时报告找不到相应的日志导致恢复终止的情况。
这种情况下的恢复操作主要步骤如下:
① 首先还原控制文件,方式不限。
② 执行“recover database”命令将报RMAN-06054错误,即找不到某归档日志。
③ 查看相关的动态性能视图,对问题定位,确认问题与控制文件,而不是数据文件相关(与数据文件相关必须进行不完全恢复)。
④ 利用create controlfile 命令重建控制文件。
⑤ 再次执行“recover database”命令,还会报RMAN-06054错误,这次是找不到另一个归档日志,其序列号应该大于第二步中的。
⑥ 查看v$log视图确定第5步中所要的是哪个日志。
⑦ 执行SQLPLUS的”recover database using backup controlfile“命令,等”Specify log:“提示符出现后给出正确的在线日志路径,直到命令成功结束。
⑧ 以resetlogs方式打开数据库。
⑨ 由于创建的控制文件内不会有临时数据文件的信息,需要重新将其添加回临时表空间。
⑩ 将控制文件内其他丢失的信息用catalog和configure等命令再添加回去。
& 说明:
有关控制文件在缺失归档日志的情况下的恢复可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2152115/
一.1.1.1 丢失了控制文件
丢失了控制文件如果控制文件丢失或损坏,则实例通常会中止。? 如果控制文件存储在 ASM 磁盘组中,则恢复方案如下:– 使用 Enterprise Manager 执行指导式恢复。– 将数据库置于NOMOUNT模式,然后使用 RMAN 命令从现有控制文件恢复控制文件。RMAN> restore controlfile from**'+DATA/orcl/controlfile/current.260.695209463';**? 如果控制文件存储为常规文件系统文件,则:– 关闭数据库。– 复制现有的控制文件来替代丢失的控制文件。成功恢复控制文件后,打开数据库。 版权所有 © 2010,Oracle。保留所有权利。
丢失了控制文件后,可选的恢复方案取决于控制文件的存储配置以及是至少还有一个控制文件还是丢失了所有文件。
如果使用ASM存储,并且至少还有一个控制文件副本,您可以使用Enterprise Manager执行指导式恢复,或者使用RMAN执行手动恢复,如下所示:
将数据库置于NOMOUNT模式。
连接到 RMAN 并发出restore controlfile命令来从现有的控制文件恢复控制文件,例如:
restore controlfile from '+DATA/orcl/controlfile/current.260.695209463';
成功恢复控制文件后,打开数据库。
如果您的控制文件存储为常规文件系统文件并且至少还有一个控制文件副本,这样,在数据库处于关闭状态时,您只需将剩余的控制文件中的一个复制到丢失文件的位置。如果介质故障是由于磁盘驱动器或控制器缺失而造成的,则将剩余的控制文件中的一个复制到其它某个位置,然后通过更新实例的参数文件来指向新位置。或者,可从初始化参数文件中删除对丢失的控制文件的引用。请注意:Oracle 建议始终至少保留两个控制文件。
注:《Oracle Database 11g:数据库管理-课堂练习 II》课程中介绍了如何在丢失了所有控制文件后进行恢复。
一.1.1.2 控制文件恢复前的准备
为了恢复控制文件,实例应该处于nomount状态,如果发现问题的时候实例还未关闭,首先应该使用“shutdown abort”命令关闭实例,接着虽然可以使用“startup nomount”命令,但是建议使用“startup” 命令启动实例,使其自然卡在 “nomount”状态,这样做可能会在警告日志和追踪日志中产生更多有用有价值的信息,并且对数据恢复顾问也有好处。
----跳过某个已经删除的表空间:
控制文件自动备份打开的情况下:
一.1.1.3 有备份情况下的恢复
一、 控制文件之一丢失**(单个控制文件丢失或损坏)**
正确的处理步骤:
① 关闭数据库
② 从其它位置拷贝一个
③ 启动数据库
我们知道数据库的控制文件都不止一个(一般为3个),这些控制文件互相为镜像,所以只需要将其他没损坏的控 制 文件重命名为损坏的控制文件即可。
我现在有三个控制文件
-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control01.ctl
-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control02.ctl
-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control03.ctl
现在删除一个控制文件control01.ctl
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
将control02.ctl复制为control01.ctl
[oracle@localhost orcl]$ cp control02.ctl control01.ctl
成功启动数据库
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
注:单个控制文件损坏也可以使用下面所讲的全部控制文件损坏时的恢复方法,只是这里直接重命名其他控制文件的方法比较快,也是推荐的一种恢复方法。
二、 各种情况下的丢失
1、 已知备份文件位置
restore controlfile from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak';
restore controlfile to '/home/oracle/a.ctl' from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak' ;
如果丢失或损坏所有的控制文件就需要从备份中还原控制文件。restore controlfile命令用来还原控制文件。在还原控制文件后需要对数据库执行完全介质恢复并以resetlog选项来打开数据库。RMAN可以将控制文件还原到它的默认存储位置,也可以使用restore controlfile ... to destination来指定位置。
从已经知的控制文件备份中还原控制文件
SQL> show parameter control_files NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_files string /u01/app/oracle/oradata/test/c ontrol01.ctl, /u01/app/oracle/ oradata/test/control02.ctl, /u 01/app/oracle/oradata/test/con trol03.ctl
显示当前可用的备份
RMAN> list backup; List of Backup Sets=================== BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------77 693.50K DISK 00:00:02 28-JAN-15 BP Key: 75 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131713 Piece Name: /u02/test_df870182233_s95_s1 List of Archived Logs in backup set 77 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 13 2928236 28-JAN-15 2928830 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------78 Full 166.91M DISK 00:01:19 28-JAN-15 BP Key: 76 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131716 Piece Name: /u02/test_df870182236_s96_s1 List of Datafiles in backup set 78 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/system01.dbf 2 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/users01.dbf 5 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/example01.dbf 6 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/test01.dbf BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------79 7.50K DISK 00:00:01 28-JAN-15 BP Key: 77 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131841 Piece Name: /u02/test_df870182321_s97_s1 List of Archived Logs in backup set 79 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 2928830 28-JAN-15 2928868 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------80 Full 9.42M DISK 00:00:02 28-JAN-15 BP Key: 78 Status: AVAILABLE Compressed: NO Tag: TAG20150128T131843 Piece Name: /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d Control File Included: Ckp SCN: 2928874 Ckp time: 28-JAN-15 SPFILE Included: Modification time: 28-JAN-15
从上面的信息可以看到备份集80是控制文件与spfile文件的备份
下面来删除当前数据库的所有控制文件:
[root@oracle11g ~]# cd /u01/app/oracle/oradata/test/[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 11804672 Feb 1 11:36 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 11:36 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo01.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 11:36 example01.dbf-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 19:05 system01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 19:05 undotbs01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 19:05 sysaux01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:10 redo03.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:13 redo03.log-rw-r----- 1 oracle oinstall 11804672 Feb 1 19:14 users01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 19:14 undotbs01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 19:14 test01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 19:14 system01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 19:14 sysaux01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:14 redo01.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 19:14 example01.dbf
向测试表t2中插入一些数据库
SQL> insert into t2 select from dba_objects; 51319 rows created. SQL> select count() from t2; COUNT(*)---------- 102560 SQL> commit; Commit complete.
这里因为是从linux操作系统层面删除了所有控制文件,因为在数据库没有关闭的情况下文件的句柄没有释放所以数据库还能运行。
人为将数据库异常终止
[root@oracle11g test]# ps -ef | grep smonoracle 3463 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3463
启动数据库:
SQL> startupORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytesORA-00205: error in identifying control file, check alert log for more info
alert日志的内容如下:
ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/test/control01.ctl'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Sun Feb 01 19:18:18 CST 2015ORA-205 signalled during: ALTER DATABASE MOUNT...
找不到控制文件不能将数据库置于mount状态.现在通过备份来还原控制文件执行完全数据库恢复:
RMAN> restore controlfile from '/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d'; Starting restore at 01-FEB-15using channel ORA_DISK_1 channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:05output filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15 RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 SQL> select status from v$instance; STATUS------------------------MOUNTED
执行完全恢复
RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 17 is already on disk as file /u01/app/oracle/oradata/test/redo02.logarchive log thread 1 sequence 18 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 19 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u02/1_15_870133266.dbf thread=1 sequence=15archive log filename=/u02/1_16_870133266.dbf thread=1 sequence=16archive log filename=/u01/app/oracle/oradata/test/redo02.log thread=1 sequence=17archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=18archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=19media recovery complete, elapsed time: 00:00:06Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs SQL> select status from v$instance; STATUS------------OPEN SQL> select count() from t2; COUNT()---------- 102560
表t2中的记录与恢复之前相同,说明恢复成功。
当还原控制文件时,控制文件的默认位置是由参数control_files控制的。如果没有设置control_files参数,那么数据库判断还原控制文件存储位置的规则将会与没有设置control_files参数时创建控制文件时使用的规则一样。
2、 使用了**恢复目录**
当没有使用恢复目录时,必须从控制文件自动备份中还原控制文件。如果从控制文件自动备份中还原控制文件,数据库必须置于nomount状态。必须首先设置数据库的DBID,然后执行restore controlfile from autobackup命令
1.人为删除所有控制文件
[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
2.人为将数据库异常终止
[root@oracle11g test]# ps -ef | grep smonoracle 3063 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3063
3.将数据库启动到nomount状态
SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes
4.从以前的备份信息中可以找到如下信息,其中c-2155613261-20150201-03中的2155613261就是DBID
Starting Control File and SPFILE Autobackup at 01-FEB-15piece handle=/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-03 comment=NONEFinished Control File and SPFILE Autobackup at 01-FEB-15
5.还原控制文件
RMAN> show controlfile autobackup format; RMAN configuration parameters are:CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'STB' TO '%F';CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F'; RMAN> set dbid 2155613261; executing command: SET DBID RMAN> restore controlfile from autobackup; Starting restore at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: no autobackups found in the recovery areachannel ORA_DISK_1: looking for autobackup on day: 20150201channel ORA_DISK_1: autobackup found: c-2155613261-20150201-03channel ORA_DISK_1: control file restore from autobackup completeoutput filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15
6.恢复数据库
RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 13 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/oradata/test/redo02.logarchive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log filename=/u01/app/oracle/oradata/test/redo02.log thread=1 sequence=2archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3media recovery complete, elapsed time: 00:00:03Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs
RMAN会使用自动备份的格式与DBID来判断在什么存储位置来搜索控制文件自动备份。如果找到,RMAN就会从备份中将控制文件还原到由control_files参数所指定的所有位置
RMAN使用恢复目录还原控制文件
1.人为删除所有控制文件
[root@oracle11g test]# ls -lrt
total 2213868
-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf
-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf
-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf
-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl
-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl
-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl
[root@oracle11g test]# rm -rf control*.ctl
[root@oracle11g test]# ls -lrt
total 2185068
-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf
-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf
-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf
-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
2.人为将数据库异常终止
[root@oracle11g test]# ps -ef | grep smonoracle 4135 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 4135
3.将数据库启动到nomount状态
SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes
4.还原控制文件
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy Recovery Manager: Release 10.2.0.5.0 - Production on Sun Feb 1 23:04:03 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: test (not mounted)connected to recovery catalog database RMAN> restore controlfile; Starting restore at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISK channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: reading from backup piece /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01 tag=TAG20150201T213315channel ORA_DISK_1: restore complete, elapsed time: 00:00:04output filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15
5.执行完全恢复
RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 8 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=4media recovery complete, elapsed time: 00:00:01Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogsnew incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete
①、 将控制文件还原到新目录
有一种将控制文件还原到一个或多个新目录的方法是修改control_files参数,然后用没有任何参数的restore controlfile命令将控制文件还原到默认位置。例如,如果在有些控制文件目录所在的磁盘出现故障还原控制文件,可以修改control_files参数将出现故障的磁盘使用其它的磁盘来替代,然后执行restore controlfile命令来还原控制文件。
如果不修改control_files参数也可以使用restore controlfile to 'filename' [from autobackup]命令来将控制文件还原到你所指定的位置。
示例:
RESTORE CONTROLFILE TO '/tmp/my_controlfile';
下面的命令将使用自动备份将控制文件还原到'/u01/app/oracle/‘目录下
RMAN> restore controlfile to '/u01/app/oracle/control_temp.ctl' from autobackup; Starting restore at 02-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkpchannel ORA_DISK_1: control file restore from autobackup completeFinished restore at 02-FEB-15
上面的命令可以在数据库为nomount,mount,open状态下进行,因为不会覆盖任何当前使用的控制文件。在将控制文件还原到新目录后,可以修改control_files参数来引用新目录下的控制文件。
使用备份控制文件的限制
在使用备份控制文件还原数据库后,你必须执行recover database来恢复数据库并且必须执行alter database open resetlogs来打开数据库。
3、 设置了**设置闪回区**
设置闪回区的情况下还原控制文件所使用的命令是相同的。然而如果当前数据库正在使用闪回区,RMAN通过对所有基于控制文件中的基于磁盘的备份和镜像副本和任何在闪回区中而不在还原的控制文件中的备份执行隐式的crosscheck来更新从备份中还原的控制文件。因此还原后的控制文件会完整的和精确的记录在闪回区中的所有备份和其它任何在备份该控制文件时所知道的备份。这提高了在数据库还原操作中的可用性。
下面来看一个使用闪回区还原控制文件的实例:
1.环境检查,看是否已经启用闪回区与设置控制文件自动备份
SQL> show parameter db_recover NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string /u01/app/oracle/flash_recovery_areadb_recovery_file_dest_size big integer 2G SQL> select flashback_on from v$database; FLASHBACK_ON------------------YES RMAN> show controlfile autobackup; RMAN configuration parameters are:CONFIGURE CONTROLFILE AUTOBACKUP ON;
从上面信息可知已经启用了闪回区并设置了控制文件自动备份
2.创建一个表空间,在数据库结构发生变化时,就会自动备份控制文件
SQL> create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management auto; Tablespace created.
从alert日志中可以看到产生的控制文件自动备份的文件信息
create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management autoMon Feb 02 00:17:28 CST 2015Starting control autobackupControl autobackup written to DISK device handle '/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkp'Completed: create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management auto
其实控制文件和spfile同时被自动备份了
查看闪回区是否存在自动备份文件
[root@oracle11g 2015_02_02]# ls -lrttotal 19360-rw-r----- 1 oracle oinstall 9895936 Feb 2 00:17 o1_mf_s_870567448bdwndtqk.bkp
3.人为删除所有控制文件
[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
4.人为将数据库异常终止
[root@oracle11g test]# ps -ef | grep smonoracle 3068 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3068
5.将数据库启动到nomount状态
SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes
6.恢复控制文件
RMAN> restore controlfile from autobackup; Starting restore at 02-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkpchannel ORA_DISK_1: control file restore from autobackup completeFinished restore at 02-FEB-15
在上面的还原控制文件的过程可以看到如下内容说明是使用存储在闪回区中的控制文件自动备份来还原控制文件
channel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448bdwndtqk.bkp
磁带备份在还原控制文件后不会自动执行crosscheck。如果正使用磁带备份,那么在还原控制文件并将数据库置于mount状态后,必须手工执行crosscheck.
RMAN> CROSSCHECK BACKUP DEVICE TYPE SBT;
7.执行完全恢复
RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 8 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=4media recovery complete, elapsed time: 00:00:01Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogsnew incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete
4、 缺失归档日志的情况下的恢复
众所周知,恢复控制文件时“recover database”命令可能需要使用归档日志。所谓缺失归档日志,是指控制文件从备份还原之后,在执行“recover database”命令恢复时报告找不到相应的日志导致恢复终止的情况。
这种情况下的恢复操作主要步骤如下:
① 首先还原控制文件,方式不限
② 执行“recover database”命令将报RMAN-06054错误,即找不到某归档日志
③ 查看相关的动态性能视图,对问题定位,确认问题与控制文件,而不是数据文件相关(与数据文件相关必须进行不完全恢复)
④ 利用create controlfile 命令重建控制文件
⑤ 再次执行“recover database”命令,还会报RMAN-06054错误,这次是找不到另一个归档日志,其序列号应该大于第二步中的
⑥ 查看v$log视图确定第5步中所要的是哪个日志
⑦ 执行SQLPLUS的”recover database using backup controlfile“命令,等”Specify log:“提示符出现后给出正确的在线日志路径,直到命令成功结束。
⑧ 以resetlogs方式打开数据库
⑨ 由于创建的控制文件内不会有临时数据文件的信息,需要重新将其添加回临时表空间
⑩ 将控制文件内其他丢失的信息用catalog和configure等命令再添加回去。
一、当前current日志序列号为:5,此时进行控制文件备份
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
RMAN> backup current controlfile;
Starting backup at 2015-02-04 16:28:13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2015-02-04 16:28:14
channel ORA_DISK_1: finished piece 1 at 2015-02-04 16:28:15
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1vpuel4t_1_1 tag=TAG20150204T162813 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-02-04 16:28:15
RMAN>
二、多次切换日志后,现在的CURRENT日志是20号,所有控制文件丢失并且第15号归档日志丢失,数据库启动后停留在了nomount状态:
SQL> alter system switch logfile;
。。。。。。。。
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL>
RMAN> delete archivelog sequence 15;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
List of Archived Log Copies for database with db_unique_name LILOVE
=====================================================================
Key Thrd Seq S Low Time
44 1 15 X 2015-02-04 16:29:58
Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_15_870711361.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_15_870711361.dbf RECID=44 STAMP=870798598
Deleted 1 objects
RMAN>
[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/*
-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/control01.ctl
-rw-r----- 1 oracle oinstall 10076160 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/control02.ctl
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo03.log
-rw-r----- 1 oracle asmadmin 608182272 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/system01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:15 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Feb 4 11:15 /u01/app/oracle/oradata/utf8test/temp01.dbf
-rw-r----- 1 oracle asmadmin 52436992 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/undotbs01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/users01.dbf
[root@rhel6_lhr ~]# rm -rf /u01/app/oracle/oradata/utf8test/control0*
[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/*
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo03.log
-rw-r----- 1 oracle asmadmin 608182272 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/system01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:15 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Feb 4 11:15 /u01/app/oracle/oradata/utf8test/temp01.dbf
-rw-r----- 1 oracle asmadmin 52436992 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/undotbs01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/users01.dbf
[root@rhel6_lhr ~]#
SQL> startup force;
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2229744 bytes
Variable Size 356518416 bytes
Database Buffers 134217728 bytes
Redo Buffers 8093696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
告警文件报错:
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/utf8test/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/utf8test/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
三、下面,我们开始恢复:
RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1vpuel4t_1_1';
Starting restore at 2015-02-04 16:44:10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/utf8test/control01.ctl
output file name=/u01/app/oracle/oradata/utf8test/control02.ctl
Finished restore at 2015-02-04 16:44:11
RMAN>
查看控制文件的确已经恢复:
[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/con*
-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:44 /u01/app/oracle/oradata/utf8test/control01.ctl
-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:44 /u01/app/oracle/oradata/utf8test/control02.ctl
[root@rhel6_lhr ~]#
四、下面我们挂载数据库:
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
RMAN>
五、下边恢复数据库将报错,表示找不到15号归档文件:
RMAN> recover database;