控制文件丢失恢复测试
控制文件(controlfile)丢失恢复基于控制文件的复合多路径性，它的丢失分为两种，一种是其中某个控制文件的损坏或丢失，另外一种是所有控制文件均丢失。基于第一种情况，只需把好的控制文件复制一份在损坏或丢失的那个控制文件路径下即可。第二种情况下则需要通过备份信息来对控制文件进行恢复或手工重建控制文件。丢失单一控制文件的判断及恢复/u01/app/oracle/oradata/test0924/control01.ctl 数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号 sys@TEST0924> shutdown immediate;ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3 必须强制关闭数据库 sys@TEST0924> shutdown abort;ORACLE instance shut down. 1、启动数据库报控制文件验证失败，检查告警日志文件。sys@TEST0924> startupORACLE instance started.Total System Global Area 3340451840 bytesFixed Size 2232960 bytesVariable Size 1543507328 bytesDatabase Buffers 1778384896 bytesRedo Buffers 16326656 bytesORA-00205: error in identifying control file, check alert log for more info 2、查看告警日志，报提示找不到control01.ctl ri Oct 11 22:39:57 2013ALTER DATABASE MOUNTORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-205 signalled during: ALTER DATABASE MOUNT... 3、从上面的信息我们可以得出是由于控制文件丢失导致了数据库无法正常的启动和关闭，下面我们要做的就是对控制文件进行做恢复，因为我们知道控制文件具有重复多路径属性，11g默认会有两个控制文件。现在日志中看到的是控制文件1丢失，找不到，我们可以通过控制文件2来恢复1。 基于正常控制文件恢复损坏的控制文件 1、查看控制文件存在路径sys@TEST0924> show parameter control_fileNAME TYPE------------------------------------ ---------------------------------VALUE------------------------------control_file_record_keep_time integer7control_files string/u01/app/oracle/oradata/test0924/control01.ctl, /u01/app/oracle/fast_recovery_area/test0924/control02.ctl 我们可以从如上看到，该套数据库存在2个控制文件其中一个控制文件存放在 /u01/app/oracle/oradata/test0924/control01.ctl，另外1个控制文件存在/u01/app/oracle/fast_recovery_area/test0924/control02.ctl ，从上面刚才的信息中我们可以得之是control01.ctl控制文件丢失导致数据库故障。 2、检查下控制文件是不存在还是损坏了[oracle@rtest test0924]$ ls /u01/app/oracle/oradata/test0924/example01.dbf fla_tbs02.dbf redo01.log redo03.log sysaux01.dbf temp01.dbf undotbs02.dbffla_tbs01.dbf inventory01.dbf redo02.log rman_cbt.log system01.dbf undotbs01.dbf users01.dbf[oracle@rtest test0924]$ ls /u01/app/oracle/oradata/test0924/control01.ctlls: /u01/app/oracle/oradata/test0924/control01.ctl: No such file or directory 看看控制文件2是否存在。 [oracle@rtest test0924]$ ls /u01/app/oracle/fast_recovery_area/test0924/control02.ctl/u01/app/oracle/fast_recovery_area/test0924/control02.ctl controlfile2还是存在的，这样我们就可以通过controlfile2来恢复controlfile1了。 3、关闭数据库 sys@TEST0924> shutdown abortORACLE instance shut down. 4、恢复损坏丢失的控制文件 [oracle@rtest test0924]$ cp /u01/app/oracle/fast_recovery_area/test0924/control02.ctl /u01/app/oracle/oradata/test0924/control01.ctl 5、启动数据库sys@TEST0924> startupORACLE instance started.Total System Global Area 3340451840 bytesFixed Size 2232960 bytesVariable Size 1543507328 byteDatabase Buffers 1778384896 bytesRedo Buffers 16326656 bytesDatabase mounted.Database opened.所有控制文件全部丢失[oracle@rtest test0924]$ rm /u01/app/oracle/fast_recovery_area/test0924/control02.ctl[oracle@rtest test0924]$ rm /u01/app/oracle/oradata/test0924/control01.ctl 数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号。 sys@TEST0924> shutdowm immediate;SP2-0734: unknown command beginning "shutdowm i..." - rest of line ignored.sys@TEST0924> shutdown immediate;Database closed.ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3 必须强制关闭数据库 sys@TEST0924> shutdown abort;ORACLE instance shut down. 启动数据库报控制文件验证失败，检查告警日志文件 sys@TEST0924> startupORACLE instance started.Total System Global Area 3340451840 bytesFixed Size 2232960 bytesVariable Size 1543507328 bytesDatabase Buffers 1778384896 bytesRedo Buffers 16326656 bytesORA-00205: error in identifying control file, check alert log for more info 检查告警日志，两个控制文件都找不到了，丢失了： Fri Oct 11 22:51:44 2013ALTER DATABASE MOUNTORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/fast_recovery_area/test0924/control02.ctl'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-205 signalled during: ALTER DATABASE MOUNT...Fri Oct 11 22:51:45 2013Checker run found 1 new persistent data failuresTime drift detected. Please check VKTM trace file for more details. 通过RMAN来进行控制文件的恢复： 1、强制启动数据库到nomount状态。 sys@TEST0924> startup force nomount;ORACLE instance started.Total System Global Area 3340451840 bytesFixed Size 2232960 bytesVariable Size 1543507328 bytesDatabase Buffers 1778384896 bytesRedo Buffers 16326656 bytes 2、另开一个窗口，连接rman，执行restore控制文件恢复。 [oracle@rtest ~]$ rman target /Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 11 22:54:28 2013Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: TEST0924 (not mounted) RMAN> restore controlfile from autobackup;Starting restore at 2013-10-11:22:55:45using channel ORA_DISK_1recovery area destination: /u01/app/oracle/fast_recovery_areadatabase name (or database unique name) used for search: TEST0924channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST0924/autobackup/2013_10_11/o1_mf_s_828559212_95k1xfbj_.bkp found in the recovery areaAUTOBACKUP search with format "%F" not attempted because DBID was not setchannel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST0924/autobackup/2013_10_11/o1_mf_s_828559212_95k1xfbj_.bkpchannel ORA_DISK_1: control file restore from AUTOBACKUP completeoutput file name=/u01/app/oracle/oradata/test0924/control01.ctloutput file name=/u01/app/oracle/fast_recovery_area/test0924/control02.ctlFinished restore at 2013-10-11:22:55:50 3、装载数据库 SQL>alter database mount;Database altered. 4、恢复数据库 RMAN> recover database;Starting recover at 2013-10-11:22:58:48Starting implicit crosscheck backup at 2013-10-11:22:58:48allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=189 device type=DISKCrosschecked 2 objectsFinished implicit crosscheck backup at 2013-10-11:22:58:51Starting implicit crosscheck copy at 2013-10-11:22:58:51using channel ORA_DISK_1Crosschecked 1 objectsFinished implicit crosscheck copy at 2013-10-11:22:58:52searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/app/oracle/fast_recovery_area/TEST0924/backupset/2013_10_06/o1_mf_nnnd0_TAG20131006T204117_9540sgxc_.bkpFile Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_149_95k4l193_.arcFile Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_150_95kcbqv2_.arcFile Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_152_95kg22cg_.arcFile Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_151_95kchq3m_.arcFile Name: /u01/app/oracle/fast_recovery_area/TEST0924/autobackup/2013_10_11/o1_mf_s_828559212_95k1xfbj_.bkpusing channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 149 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_149_95k4l193_.arcarchived log for thread 1 with sequence 150 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_150_95kcbqv2_.arcarchived log for thread 1 with sequence 151 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_151_95kchq3m_.arcarchived log for thread 1 with sequence 152 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_152_95kg22cg_.arcarchived log for thread 1 with sequence 153 is already on disk as file /u01/app/oracle/oradata/test0924/redo03.logarchived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_149_95k4l193_.arc thread=1 sequence=149archived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_150_95kcbqv2_.arc thread=1 sequence=150archived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_151_95kchq3m_.arc thread=1 sequence=151archived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_152_95kg22cg_.arc thread=1 sequence=152archived log file name=/u01/app/oracle/oradata/test0924/redo03.log thread=1 sequence=153media recovery complete, elapsed time: 00:00:31Finished recover at 2013-10-11:22:59:27 5、打开数据库 RMAN> alter database open resetlogs;database opened
