Oracle数据库丢失控制文件的恢复四则

下文介绍了Oracle数据库中丢失控制文件的几种处理方法。

丢失单个控制文件

报错信息：2013-05-08 03:00:29.678000 +08:00Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_5204.trc:ORA-00210: cannot open the specifiedcontrol fileORA-00202: control file:"/u02/oradat/bkt/control01.ctl"ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m001_5289.trc:ORA-00210: cannot open the specifiedcontrol fileORA-00202: control file:"/u02/oradat/bkt/control01.ctl"ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file ordirectory1、直接手动关闭了，其实数据库会自动的关闭。shutdown abort ;2、以下有两种方法2.1、拷贝控制文件到原来的目录cp/u02/flash_recovery_area/bkt/control02.ctl /u02/oradat/bkt/control01.ctl2.2、启动到nomount后设置control_files的位置，将丢失的控制文件路径去掉alter system setcontrol_files="/u02/flash_recovery_area/bkt/control02.ctl" scope=spfile ;3.启动数据库即可startup下面介绍丢失所有控制文件的时候应该怎么做

使用冷备份的控制文件恢复

以下为详细的示例：

1. backup controlfile

show controlfilecopyrman target /backup controlfile current format "" ;output :sys@BKT> show parameter controlNAME TYPE VALUE----------------------------------------------- ------------------------------control_file_record_keep_time integer 7control_files string /u02/oradat/bkt/control01.ctl, /u02/flash_recovery_area/bkt/ control02.ctlcontrol_management_pack_access string DIAGNOSTIC+TUNING--instance still running ...[oracle@master ~]$ cp/u02/oradat/bkt/control01.ctl /tmp/control01.ctl

2. create tablespace

conn / as sysdbadefine tbsname1="tbs1"define tbsname2="tbs2"define dfpath1="/u02/oradat/bkt/tbs101.dbf"define dfpath2="/u02/oradat/bkt/tbs201.dbf"create tablespace &tbsname1 datafile"&dfpath1" size 100m ;create tablespace &tbsname2 datafile"&dfpath2" size 100m ;create table &tbsname1 tablespace&tbsname1 as select * from all_objects ;create table &tbsname2 tablespace&tbsname2 as select * from all_objects ;select count（*） from &tbsname1 ;select count（*） from &tbsname2 ;alter tablespace &tbsname1 read only ;output :sys@BKT> conn / as sysdbaConnected.sys@BKT>sys@BKT> define tbsname1="tbs1"sys@BKT> define tbsname2="tbs2"sys@BKT>sys@BKT> definedfpath1="/u02/oradat/bkt/tbs101.dbf"sys@BKT> definedfpath2="/u02/oradat/bkt/tbs201.dbf"sys@BKT> create tablespace &tbsname1datafile "&dfpath1" size 100m ;old 1: create tablespace &tbsname1 datafile "&dfpath1" size 100mnew 1: create tablespace tbs1 datafile "/u02/oradat/bkt/tbs101.dbf" size100mTablespace created.sys@BKT> create tablespace &tbsname2datafile "&dfpath2" size 100m ;old 1: create tablespace &tbsname2 datafile "&dfpath2" size 100mnew 1: create tablespace tbs2 datafile "/u02/oradat/bkt/tbs201.dbf" size100mTablespace created.sys@BKT> create table &tbsname1tablespace &tbsname1 as select * from all_objects ;old 1: create table &tbsname1 tablespace &tbsname1 as select * fromall_objectsnew 1: create table tbs1 tablespace tbs1 as select * from all_objectsTable created.sys@BKT> create table &tbsname2tablespace &tbsname2 as select * from all_objects ;old 1: create table &tbsname2 tablespace &tbsname2 as select * fromall_objectsnew 1: create table tbs2 tablespace tbs2 as select * from all_objectsTable created.sys@BKT> select count（*） from&tbsname1 ;old 1: select count（*） from &tbsname1new 1: select count（*） from tbs1 COUNT（*）---------- 72780sys@BKT> select count（*） from&tbsname2 ;old 1: select count（*） from &tbsname2new 1: select count（*） from tbs2 COUNT（*）---------- 72781sys@BKT> alter tablespace &tbsname1read only ;old 1: alter tablespace &tbsname1 read onlynew 1: alter tablespace tbs1 read onlyTablespace altered.sys@BKT>

3. switch logfile

alter system switch logfile ;alter system switch logfile ;alter system switch logfile ;alter system switch logfile ;alter system switch logfile ;output :sys@BKT> alter system switchlogfile ;alter system switch logfile ;alter system switch logfile ;alter system switch logfile ;System altered.sys@BKT>System altered.sys@BKT>System altered.sys@BKT>System altered.

4. remove controlfile

rm /u02/oradat/bkt/control01.ctlrm/u02/flash_recovery_area/bkt/control02.ctloutput :[oracle@master ~]$ rm/u02/oradat/bkt/control01.ctl[oracle@master ~]$ rm/u02/flash_recovery_area/bkt/control02.ctl--from alert .Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6172.trc:ORA-00210: cannot open the specifiedcontrol fileORA-00202: control file:"/u02/oradat/bkt/control01.ctl"ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3

5. shutdown database

shutdown abortoutput :sys@BKT> shutdown abort ;ORACLE instance shut down.

6. using backup controlfile

--copy backup controlfile to thecontrolfile path which defined in the parameter .cp /tmp/control01.ctl/u02/oradat/bkt/control01.ctlcp /tmp/control01.ctl/u02/flash_recovery_area/bkt/control02.ctlrecoveroutput :cp /tmp/control01.ctl /u02/oradat/bkt/control01.ctlcp /tmp/control01.ctl/u02/flash_recovery_area/bkt/control02.ctlsys@BKT> startupORACLE instance started.Total System Global Area 417546240 bytesFixed Size 2228944 bytesVariable Size 318770480 bytesDatabase Buffers 88080384 bytesRedo Buffers 8466432 bytesDatabase mounted.ORA-01122: database file 1 failedverification checkORA-01110: data file 1:"/u02/oradat/bkt/system01.dbf"ORA-01207: file is more recent than controlfile - old control file--from alertALTER DATABASE OPENErrors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_ora_6327.trc:ORA-01122: database file 1 failedverification checkORA-01110: data file 1:"/u02/oradat/bkt/system01.dbf"<<<ORA-01207: file is more recentthan control file - old control file>>>ORA-1122 signalled during: ALTER DATABASEOPEN...Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:ORA-00338: log 1 of thread 1 is more recentthan control fileORA-00312: online log 1 thread 1:"/u02/oradat/bkt/redo01.log"Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:ORA-00338: log 1 of thread 1 is more recentthan control fileORA-00312: online log 1 thread 1:"/u02/oradat/bkt/redo01.log"Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:ORA-00338: log 2 of thread 1 is more recentthan control fileORA-00312: online log 2 thread 1:"/u02/oradat/bkt/redo02.log"Errors in file /u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:ORA-00338: log 2 of thread 1 is more recentthan control fileORA-00312: online log 2 thread 1:"/u02/oradat/bkt/redo02.log"Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:ORA-00338: log 3 of thread 1 is more recentthan control fileORA-00312: online log 3 thread 1:"/u02/oradat/bkt/redo03.log"Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6331.trc:ORA-00338: log 3 of thread 1 is more recentthan control fileORA-00312: online log 3 thread 1:"/u02/oradat/bkt/redo03.log"Checker run found 1 new persistent datafailures[oracle@master ~]$ oerr ora 0120701207, 00000, "file is more recentthan control file - old control file"// *Cause: The control file change sequence number in the data file is// greater than the number in the control file. This implies that// the wrong control file is being used. Note that repeatedly causing// this error can make it stop happening without correcting the real// problem. Every attempt to open the database will advance the// control file change sequence number until it is great enough.// *Action: Use the current control file ordo backup control file recovery to// make the control file current. Be sure to follow all restrictions// on doing a backup control file recovery.--显然没有我们最近创建的两个表空间sys@BKT> select name from v$datafile ;NAME----------------------------------------------------------------------------------------------------/u02/oradat/bkt/system01.dbf/u02/oradat/bkt/sysaux01.dbf/u02/oradat/bkt/undotbs01.dbf/u02/oradat/bkt/users01.dbf/u02/oradat/bkt/example01.dbf/u02/oradat/bkt/test1_01.dbf6 rows selected.--controlfile_change# low rba ,checkpoint_change#sys@BKT> select checkpoint_change#,CONTROLFILE_SEQUENCE# ,CONTROLFILE_CHANGE# fromv$database ;CHECKPOINT_CHANGE# CONTROLFILE_SEQUENCE#CONTROLFILE_CHANGE#------------------ ---------------------------------------- 1281710 3287 1293684sys@BKT> select min（checkpoint_change#）from V$datafile_header ;MIN（CHECKPOINT_CHANGE#）----------------------- 1270438sys@BKT> selectgroup#,first_change#,next_change# from v$log ; GROUP# FIRST_CHANGE# NEXT_CHANGE#---------- ------------- ------------ 1 1273500 1273646 3 1274338 2.8147E+14 2 1273646 1274338sys@BKT> recover database;ORA-00283: recovery session canceled due toerrorsORA-01122: database file 1 failedverification checkORA-01110: data file 1:"/u02/oradat/bkt/system01.dbf"ORA-01207: file is more recent than controlfile - old control filesys@BKT> recover database using backup controlfile ;ORA-00279: change 1293684 generated at05/08/2013 14:29:38 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arcORA-00280: change 1293684 for thread 1 isin sequence #93Specify log: {<RET>=suggested |filename | AUTO | CANCEL}autoORA-00283: recovery session canceled due toerrorsORA-01244: unnamed datafile（s） added tocontrol file by media recoveryORA-01110: data file 7:"/u02/oradat/bkt/tbs101.dbf"ORA-01112: media recovery not started这时候第七号文件已经加回来了。sys@BKT> recover database using backupcontrolfile ;ORA-00283: recovery session canceled due toerrorsORA-01111: name for data file 7 is unknown- rename to correct fileORA-01110: data file 7:"/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007"ORA-01157: cannot identify/lock data file 7- see DBWR trace fileORA-01111: name for data file 7 is unknown- rename to correct fileORA-01110: data file 7: "/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007"sys@BKT> select name from v$datafile ;NAME----------------------------------------------------------------------------------------------------/u02/oradat/bkt/system01.dbf/u02/oradat/bkt/sysaux01.dbf/u02/oradat/bkt/undotbs01.dbf/u02/oradat/bkt/users01.dbf/u02/oradat/bkt/example01.dbf/u02/oradat/bkt/test1_01.dbf/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED000077 rows selected. offline以下再恢复sys@BKT> alter database datafile 7offline ;Database altered.sys@BKT> recover database using backupcontrolfile ;ORA-00279: change 1293752 generated at05/13/2013 14:42:44 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arcORA-00280: change 1293752 for thread 1 isin sequence #93Specify log: {<RET>=suggested |filename | AUTO | CANCEL}autoORA-00283: recovery session canceled due toerrorsORA-01244: unnamed datafile（s） added tocontrol file by media recoveryORA-01110: data file 8: "/u02/oradat/bkt/tbs201.dbf"ORA-01112: media recovery not started这时候最后一个数据文件也加回来了sys@BKT> select name from v$datafile ;NAME----------------------------------------------------------------------------------------------------/u02/oradat/bkt/system01.dbf/u02/oradat/bkt/sysaux01.dbf/u02/oradat/bkt/undotbs01.dbf/u02/oradat/bkt/users01.dbf/u02/oradat/bkt/example01.dbf/u02/oradat/bkt/test1_01.dbf/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED000088 rows selected.因为是表空间是readonly的，数据文件这里需要修改一下名称sys@BKT> alter database rename file"/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00008" to"/u02/oradat/bkt/tbs201.dbf" ;Database altered.sys@BKT> alter database rename file"/u01/apps/oracle/product/11gr2/db_1/dbs/UNNAMED00007" to "/u02/oradat/bkt/tbs101.dbf"; Database altered.sys@BKT> alter database datafile 7online ;Database altered.再次恢复sys@BKT> recover database using backup controlfile ;ORA-00279: change 1294029 generated at05/13/2013 14:42:58 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arcORA-00280: change 1294029 for thread 1 isin sequence #93Specify log: {<RET>=suggested |filename | AUTO | CANCEL}autoORA-00279: change 1294568 generated at05/13/2013 14:44:22 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_94_8s2qoqjj_.arcORA-00280: change 1294568 for thread 1 isin sequence #94ORA-00278: log file"/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_93_8s2qoprf_.arc"no longer needed for this recoveryORA-00279: change 1294571 generated at05/13/2013 14:44:22 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_95_8s2qovq1_.arcORA-00280: change 1294571 for thread 1 isin sequence #95ORA-00278: log file"/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_94_8s2qoqjj_.arc"no longer needed for this recoveryORA-00279: change 1294574 generated at05/13/2013 14:44:27 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_96_8s2qovsh_.arcORA-00280: change 1294574 for thread 1 isin sequence #96ORA-00278: log file"/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_95_8s2qovq1_.arc"no longer needed for this recoveryORA-00279: change 1294577 generated at05/13/2013 14:44:27 needed for thread 1ORA-00289: suggestion : /u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_97_8s2qow0z_.arcORA-00280: change 1294577 for thread 1 isin sequence #97ORA-00278: log file"/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_96_8s2qovsh_.arc"no longer needed for this recoveryORA-00279: change 1294580 generated at05/13/2013 14:44:27 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_98_8s2qs51d_.arcORA-00280: change 1294580 for thread 1 isin sequence #98ORA-00278: log file "/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_97_8s2qow0z_.arc"no longer needed for this recoveryORA-00279: change 1294620 generated at05/13/2013 14:46:13 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_99_8s2qs62m_.arcORA-00280: change 1294620 for thread 1 isin sequence #99ORA-00278: log file"/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_98_8s2qs51d_.arc"no longer needed for this recoveryORA-00279: change 1294623 generated at05/13/2013 14:46:14 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arcORA-00280: change 1294623 for thread 1 isin sequence #100ORA-00278: log file"/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_99_8s2qs62m_.arc"no longer needed for this recoveryORA-00308: cannot open archived log"/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arc"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3[oracle@master 2013_05_13]$ ls -ltrtotal 31744-rw-r----- 1 oracle oinstall 1024 May 13 14:44 o1_mf_1_94_8s2qoqjj_.arc-rw-r----- 1 oracle oinstall 32441344 May13 14:44 o1_mf_1_93_8s2qoprf_.arc-rw-r----- 1 oracle oinstall 1024 May 13 14:44 o1_mf_1_96_8s2qovsh_.arc-rw-r----- 1 oracle oinstall 3072 May 13 14:44 o1_mf_1_95_8s2qovq1_.arc-rw-r----- 1 oracle oinstall 1024 May 13 14:44 o1_mf_1_97_8s2qow0z_.arc-rw-r----- 1 oracle oinstall 1536 May 13 14:46 o1_mf_1_98_8s2qs51d_.arc-rw-r----- 1 oracle oinstall 1024 May 13 14:46 o1_mf_1_99_8s2qs62m_.arcidle> select sequence# , group# , statusfrom v$log ; SEQUENCE# GROUP# STATUS---------- ---------- ---------------- 91 1 INACTIVE 93 3 CURRENT 92 2 INACTIVEidle> select group#,member fromv$logfile ; GROUP# MEMBER------------------------------------------------------------ 2 /u02/oradat/bkt/redo02.log 1 /u02/oradat/bkt/redo01.log 3 /u02/oradat/bkt/redo03.logsys@BKT> recover database using backupcontrolfile ;ORA-00279: change 1294623 generated at05/13/2013 14:46:14 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arcORA-00280: change 1294623 for thread 1 isin sequence #100Specify log: {<RET>=suggested |filename | AUTO | CANCEL}/u02/oradat/bkt/redo03.logORA-00310: archived log contains sequence99; sequence 100 requiredORA-00334: archived log:"/u02/oradat/bkt/redo03.log"sys@BKT> recover database using backupcontrolfile ;ORA-00279: change 1294623 generated at 05/13/201314:46:14 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_13/o1_mf_1_100_%u_.arcORA-00280: change 1294623 for thread 1 isin sequence #100Specify log: {<RET>=suggested |filename | AUTO | CANCEL}/u02/oradat/bkt/redo01.logLog applied.Media recovery complete.sys@BKT> alter database open resetlogs ;Database altered.因为使用了备份的控制文件，所以必须resetlogs

无备份直接重建控制文件

sys@BKT> show parameter controlNAME TYPE VALUE----------------------------------------------- ------------------------------control_file_record_keep_time integer 7control_files string /u02/oradat/bkt/control01.ctl,/u02/flash_recovery_area/bkt/control02.ctl[root@master ~]# rm /u02/oradat/bkt/control01.ctlrm: remove regular file`/u02/oradat/bkt/control01.ctl"？ y[root@master ~]# rm/u02/flash_recovery_area/bkt/control02.ctlrm: remove regular file`/u02/flash_recovery_area/bkt/control02.ctl"？ ysys@BKT> alter system switch logfile ;System altered.sys@BKT> alter system archive logcurrent ;System altered.看来不是立刻写入控制文件中。Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_6382.trc:ORA-00210: cannot open the specified controlfileORA-00202: control file:"/u02/oradat/bkt/control01.ctl"ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3sys@BKT> create tablespace test1datafile "/u02/oradat/bkt/test1_01.dbf" size 10m ;Tablespace created.如果及时的发现数据库未关闭，可以使用下面的命令将重建控制文件的脚本输出到trace文件中，方便后面的控制文件重建操作。sys@BKT> alter database backupcontrolfile to trace ;Database altered.alter :Backup controlfile written to trace file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_ora_5933.trcCompleted: alter database backupcontrolfile to trace如果没有及时的发现已经丢失了所有的控制文件，这里重建控制文件需要知道一些额外的信息，包括redolog的位置、数据文件的位置还有字符集。（这里也可以使用snapshot控制文件来生成trace文件）sys@BKT> shutdown immediateDatabase closed.ORA-00210: cannot open the specifiedcontrol fileORA-00202: control file:"/u02/oradat/bkt/control01.ctl"ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3sys@BKT> shutdown abort ;sys@BKT> alter database mount ;alter database mount*ERROR at line 1:ORA-00205: error in identifying controlfile, check alert log for more info如果这里重做日志没有损坏，则可以以noresetlogs的方式重建控制文件CREATE CONTROLFILE REUSE DATABASE"BKT" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 "/u02/oradat/bkt/redo01.log" SIZE 50M BLOCKSIZE 512, GROUP 2 "/u02/oradat/bkt/redo02.log" SIZE 50M BLOCKSIZE 512, GROUP 3 "/u02/oradat/bkt/redo03.log" SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE "/u02/oradat/bkt/system01.dbf", "/u02/oradat/bkt/sysaux01.dbf", "/u02/oradat/bkt/undotbs01.dbf", "/u02/oradat/bkt/test1_01.dbf", "/u02/oradat/bkt/users01.dbf", "/u02/oradat/bkt/example01.dbf"CHARACTER SET ZHS16GBK;这里改变了数据文件的位置sys@BKT> CREATE CONTROLFILE REUSEDATABASE "BKT" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1"/u02/oradat/bkt/redo01.log" SIZE 50MBLOCKSIZE 512, 9 GROUP 2"/u02/oradat/bkt/redo02.log" SIZE 50MBLOCKSIZE 512, 10 GROUP 3 "/u02/oradat/bkt/redo03.log" SIZE 50M BLOCKSIZE 512 11 --STANDBY LOGFILE 12 DATAFILE 13 "/u02/oradat/bkt/system01.dbf", 14 "/u02/oradat/bkt/sysaux01.dbf", 15 "/u02/oradat/bkt/undotbs01.dbf", 16 "/u02/oradat/bkt/test1_01.dbf", 17 "/u02/oradat/bkt/users01.dbf", 18 "/u02/oradat/bkt/example01.dbf" 19 CHARACTER SET ZHS16GBK 20 ;Control file created.sys@BKT> select status from v$instance ;STATUS------------MOUNTED也可能需要手动的recover一下recover databasesys@BKT> alter database open ;Database altered.添加临时文件sys@BKT> ALTER TABLESPACE TEMP ADDTEMPFILE "/u02/oradat/bkt/temp01.dbf";RMAN> list backup ;specification does not match any backup inthe repositoryRMAN> catalog db_recovery_file_dest ;RMAN> list backup ;List of Backup Sets===================BS Key Type LV Size Device TypeElapsed Time Completion Time------- ---- -- ---------- ----------------------- ---------------1 Full 1.07G DISK 00:00:00 18-APR-13 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130418T223159 Piece Name:/u02/flash_recovery_area/BKT/backupset/2013_04_18/o1_mf_nnndf_TAG20130418T223159_8q00yzs1_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN CkpTime Name ---- -- ---- ---------- --------- ---- 1 Full 1095500 18-APR-13 /u02/oradat/bkt/system01.dbf 2 Full 1095500 18-APR-13 /u02/oradat/bkt/sysaux01.dbf 3 Full 1095500 18-APR-13 /u02/oradat/bkt/undotbs01.dbf 4 Full 1095500 18-APR-13 /u02/oradat/bkt/users01.dbf 5 Full 1095500 18-APR-13 /u02/oradat/bkt/example01.dbfBS Key Type LV Size Device TypeElapsed Time Completion Time------- ---- -- ---------- ----------------------- ---------------2 Full 9.36M DISK 00:00:00 18-APR-13 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130418T223159 Piece Name:/u02/flash_recovery_area/BKT/backupset/2013_04_18/o1_mf_ncsnf_TAG20130418T223159_8q016gmf_.bkp SPFILE Included: Modification time: 18-APR-13 SPFILE db_unique_name: BKT Control File Included: Ckp SCN: 1095500 Ckp time: 18-APR-13RMAN> list incarnation2> ;List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ------------------- ---------- ----------1 1 BKT 536381303 CURRENT 995548 18-APR-13RMAN>Continue applying redo log files until thelast log has been applied to the restoreddata files, then cancel recovery byexecuting the following command:CANCELThe database indicates whether recovery issuccessful. If you cancel before all thedata files have been recovered to a consistentSCN and then try to open thedatabase, then you get an ORA-1113error ifmore recovery is necessary. You canquery V$RECOVER_FILEto determine whethermore recovery is needed, or if abackup of a data file was notrestoredbefore starting incomplete recovery.

无备份使用snap控制文件

RMAN> show all2> ;using target database control file insteadof recovery catalogRMAN configuration parameters for databasewith db_unique_name BKT are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1;# defaultCONFIGURE BACKUP OPTIMIZATION OFF; #defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; #defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; #defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FORDEVICE TYPE DISK TO "%F"; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICETYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FORDEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; #defaultCONFIGURE ENCRYPTION ALGORITHM "AES128"; #defaultCONFIGURE COMPRESSION ALGORITHM "BASIC" ASOF RELEASE "DEFAULT" OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TONONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO"/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f"; # default删除后alert日志报错：Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_15066.trc:ORA-00210: cannot open the specifiedcontrol fileORA-00202: control file:"/u02/oradat/bkt/control01.ctl"ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3sys@BKT> shutdown immediateDatabase closed.ORA-00210: cannot open the specifiedcontrol fileORA-00202: control file:"/u02/oradat/bkt/control01.ctl"ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3sys@BKT> shutdown abort ;ORACLE instance shut down.sys@BKT> startupORACLE instance started.Total System Global Area 417546240 bytesFixed Size 2228944 bytesVariable Size 322964784 bytesDatabase Buffers 83886080 bytesRedo Buffers 8466432 bytesORA-00205: error in identifying controlfile, check alert log for more info[root@master ~]# cp/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f/u02/oradat/bkt/control01.ctl[root@master ~]# cp/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f/u02/flash_recovery_area/bkt/control02.ctl[root@master ~]# chown oracle:oinstall/u02/oradat/bkt/control01.ctl[root@master ~]# chown oracle:oinstall/u02/flash_recovery_area/bkt/control02.ctlsys@BKT> recover database using backupcontrolfile ;ORA-00279: change 1266893 generated at05/09/2013 03:27:56 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_08/o1_mf_1_28_%u_.arcORA-00280: change 1266893 for thread 1 isin sequence #28Specify log: {<RET>=suggested |filename | AUTO | CANCEL}因为是老的controlfile，所以sequence都是老旧的，dle> select group#,sequence#,status fromv$log ; GROUP# SEQUENCE# STATUS---------- ---------- ---------------- 1 25 INACTIVE 3 24 INACTIVE 2 26 CURRENTidle> archive log list ;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 24Next log sequence to archive 26Current log sequence 26查看相应的目录[oracle@master ~]$ ll/u02/flash_recovery_area/BKT/archivelog/2013_05_08/total 62224-rw-r----- 1 oracle oinstall 177152 May 7 12:10 o1_mf_1_10_8rlng20m_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:12 o1_mf_1_11_8rlnkd1g_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:12 o1_mf_1_12_8rlnkfdr_.arc-rw-r----- 1 oracle oinstall 10752 May 7 12:17 o1_mf_1_13_8rlnt65r_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_14_8rlnt6r7_.arc-rw-r----- 1 oracle oinstall 1536 May 7 12:17 o1_mf_1_15_8rlnt7b1_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_16_8rlnt7x7_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_17_8rlnt8g7_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_18_8rlnt8wk_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_19_8rlnt9bn_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_20_8rlnt9p8_.arc-rw-r----- 1 oracle oinstall 184832 May 7 12:30 o1_mf_1_21_8rlokyy2_.arc-rw-r----- 1 oracle oinstall 259584 May 7 12:55 o1_mf_1_22_8rlq1j4j_.arc-rw-r----- 1 oracle oinstall 1024 May 7 12:55 o1_mf_1_23_8rlq1tck_.arc-rw-r----- 1 oracle oinstall 1024 May 7 13:29 o1_mf_1_23_8rls13lv_.arc-rw-r----- 1 oracle oinstall 2048 May 7 12:56 o1_mf_1_24_8rlq3pxx_.arc-rw-r----- 1 oracle oinstall 2048 May 7 13:29 o1_mf_1_24_8rls13lj_.arc-rw-r----- 1 oracle oinstall 243712 May 7 13:29 o1_mf_1_25_8rls13pd_.arc-rw-r----- 1 oracle oinstall 42203648May 8 07:01 o1_mf_1_26_8rnpoljr_.arc-rw-r----- 1 oracle oinstall 10391552May 8 12:28 o1_mf_1_27_8ro9sxgq_.arc-rw-r----- 1 oracle oinstall 10078720May 7 11:58 o1_mf_1_9_8rlmpf3o_.arcidle> select * from v$logfile ; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------------------------------------- --- 1 STALE ONLINE /u02/oradat/bkt/redo01.log NO 3 STALE ONLINE /u02/oradat/bkt/redo03.log NO 2 ONLINE /u02/oradat/bkt/redo02.log NO顺序3、1、2sys@BKT> recover database using backupcontrolfile ;ORA-00279: change 1266893 generated at05/09/2013 03:27:56 needed for thread 1ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_08/o1_mf_1_28_%u_.arcORA-00280: change 1266893 for thread 1 isin sequence #28Specify log: {<RET>=suggested |filename | AUTO | CANCEL} /u02/oradat/bkt/redo03.logORA-00310: archived log contains sequence27; sequence 28 requiredORA-00334: archived log:"/u02/oradat/bkt/redo03.log"这里必须resetlogssys@BKT> alter database open noresetlogs;alter database open noresetlogs*ERROR at line 1:ORA-01588: must use RESETLOGS option fordatabase opensys@BKT> alter database open resetlogs ;sys@BKT> ALTER TABLESPACE TEMP ADDTEMPFILE "/u02/oradat/bkt/temp01.dbf" REUSE;Tablespace altered.RMAN> catalog db_recovery_file_dest ;using target database control file insteadof recovery catalogsearching for all files in the recoveryarea更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址