非归档模式下，丢失日志文件的一次恢复过程
场景描述：
Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
非归档模式下，备份控制文件后，切换日志文件，并执行完全检查点操作。并模拟数据库断电关闭。然后删除现有控制文件后，还原备份的控制文件，然后打开库。
曲折过程如下：
SQL>alter database backup controlfile to '/u01/controlfile.bak';
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>alter system checkpoint;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@RAC1 ~]$rm /u01/app/oracle/oradata/wailon/control*.ctl
删除原控制文件后，用备份的控制文件还原：
[oracle@RAC1 ~]$cp/u01/controlfile.bak /u01/app/oracle/oradata/wailon/control01.ctl
[oracle@RAC1 ~]$cp/u01/controlfile.bak /u01/app/oracle/oradata/wailon/control02.ctl
打开数据库报错：
SQL> alterdatabase open resetlogs;
alter databaseopen resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: datafile 1: '/u01/app/oracle/oradata/wailon/system01.dbf'
使用备份的控制文件恢复：
SQL> recover database using backup controlfile;
ORA-00279: change1074079 generated at 06/05/2013 16:31:21 needed for thread 1
ORA-00289:suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_8_817166132.dbf
ORA-00280: change1074079 for thread 1 is in sequence #8
Specify log:{
auto
ORA-00308: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_8_817166132.dbf'
ORA-27037: ??
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
由于数据库处于非归档状态，找不到对应的归档日志文件，恢复未成功
尝试不完全恢复，无效：
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1074079 generated at 06/05/2013 16:31:21 needed for thread 1
ORA-00289:suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_8_817166132.dbf
ORA-00280: change 1074079 for thread 1 is in sequence #8
Specify log:{
cancel
ORA-01547: :RECOVER PEN RESETLOGS
ORA-01194: 1 ?
ORA-01110: 1:'/u01/app/oracle/oradata/wailon/system01.dbf'
ORA-01112: media recovery not started
alert 日志文件信息：
Wed Jun 0519:28:59 2013
--非法关闭数据库
WARNING!Recovering data file 1 from a fuzzy file. If not the current file
it might be anonline backup taken without entering the begin backup command.
WARNING!Recovering data file 2 from a fuzzy file. If not the current file
it might be anonline backup taken without entering the begin backup command.
WARNING!Recovering data file 3 from a fuzzy file. If not the current file
it might be anonline backup taken without entering the begin backup command.
WARNING!Recovering data file 4 from a fuzzy file. If not the current file
it might be anonline backup taken without entering the begin backup command.
WARNING!Recovering data file 5 from a fuzzy file. If not the current file
it might be anonline backup taken without entering the begin backup command.
Parallel MediaRecovery started with 2 slaves
ORA-279 signalledduring: alter database recover if needed
start until cancel using backup controlfile
...
alter databaserecover cancel
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_pr00_3857.trc:
ORA-01547: : RECOVER OPEN RESETLOGS
ORA-01194: 1 ?
ORA-01110: 1:'/u01/app/oracle/oradata/wailon/system01.dbf'
Slave exiting withORA-1547 exception
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_pr00_3857.trc:
ORA-01547: :RECOVER OPEN RESETLOGS
ORA-01194: 1 ?
ORA-01110: 1:'/u01/app/oracle/oradata/wailon/system01.dbf'
ORA-1547 signalledduring: alter database recover cancel...
Wed Jun 0519:29:14 2013
alter databaseopen
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_3851.trc:
ORA-01589: mustuse RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalledduring: alter database open...
Wed Jun 0519:29:24 2013
alter databaseopen resetlogs
--数据文件一致性检查未通过
ORA-1194 signalled during: alter database openresetlogs...
通过数据文件及重做日志文件重建控制文件，还是跟上面的错误一样。后来一不小心把所有重做日志文件删除了，之后就是以下的情况
修改init.ora 添加以下语句，跳过一致性检查
*._allow_resetlogs_corruption=TRUE
再次尝试启动
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora'
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 276827440 bytes
Database Buffers 130023424 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
alert 日志文件信息：
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result in a corrupted database. Thedatabase should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1074079
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 1 of thread 1
ORA-00312:online log 1 thread 1: '/u01/app/oracle/oradata/wailon/redo01.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Clearing online redo logfile 1 /u01/app/oracle/oradata/wailon/redo01.log
Clearing onlinelog 1 of thread 1 sequence number 7
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 1 of thread 1
ORA-00312: onlinelog 1 thread 1: '/u01/app/oracle/oradata/wailon/redo01.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 1 of thread 1
ORA-00312: onlinelog 1 thread 1: '/u01/app/oracle/oradata/wailon/redo01.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Clearing online redo logfile 1 complete
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 2 of thread 1
ORA-00312:online log 2 thread 1: '/u01/app/oracle/oradata/wailon/redo02.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Clearing online redo logfile 2 /u01/app/oracle/oradata/wailon/redo02.log
Clearing onlinelog 2 of thread 1 sequence number 8
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 2 of thread 1
ORA-00312: onlinelog 2 thread 1: '/u01/app/oracle/oradata/wailon/redo02.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 2 of thread 1
ORA-00312: onlinelog 2 thread 1: '/u01/app/oracle/oradata/wailon/redo02.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Clearing online redo logfile 2 complete
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 3 of thread 1
ORA-00312:online log 3 thread 1: '/u01/app/oracle/oradata/wailon/redo03.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Clearing online redo logfile 3 /u01/app/oracle/oradata/wailon/redo03.log
Clearing onlinelog 3 of thread 1 sequence number 6
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 3 of thread 1
ORA-00312: onlinelog 3 thread 1: '/u01/app/oracle/oradata/wailon/redo03.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Errors in file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_4673.trc:
ORA-00313: openfailed for members of log group 3 of thread 1
ORA-00312: onlinelog 3 thread 1: '/u01/app/oracle/oradata/wailon/redo03.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
Clearing online redo logfile 3 complete
Resettingresetlogs activation ID 2746928689 (0xa3bace31)
Online log/u01/app/oracle/oradata/wailon/redo01.log: Thread 1 Group 1 was previouslycleared
Online log/u01/app/oracle/oradata/wailon/redo02.log: Thread 1 Group 2 was previouslycleared
Online log/u01/app/oracle/oradata/wailon/redo03.log: Thread 1 Group 3 was previouslycleared
Thu Jun 0600:13:34 2013
Setting recoverytarget incarnation to 3
Thu Jun 0600:13:34 2013
Assigningactivation ID 2747175177 (0xa3be9109)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0:/u01/app/oracle/oradata/wailon/redo01.log
Successful open ofredo thread 1
MTTR advisory isdisabled because FAST_START_MTTR_TARGET is not set
Thu Jun 0600:13:34 2013
SMON: enablingcache recovery
[4673]Successfully onlined Undo Tablespace 2.
Undoinitialization finished serial:0 start:32944304 end:32944694 diff:390 (3seconds)
Dictionary checkbeginning
Dictionary checkcomplete
Verifying fileheader compatibility for 11g tablespace encryption..
Verifying 11g fileheader compatibility for tablespace encryption completed
SMON: enabling txrecovery
DatabaseCharacterset is ZHS16GBK
No ResourceManager plan active
Thu Jun 0600:13:37 2013
replication_dependency_trackingturned off (no async multimaster replication found)
Startingbackground process QMNC
Thu Jun 0600:13:38 2013
QMNC started withpid=20, OS id=4681
LOGSTDBY:Validating controlfile with logical metadata
LOGSTDBY:Validation complete
Completed:alter database open resetlogs
至此，数据库正常打开。
小插曲：
期间若出现ORA-00600: internal error code, arguments: [2662], [0], [1096111], [0], [1096312], [4194432], [],[], [], [], [], []
Trace file/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_5779.trc
Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: RAC1
Release: 2.6.39-200.24.1.el6uek.x86_64
Version: #1 SMP Sat Jun 23 02:39:07 EDT 2012
Machine: x86_64
Instance name:wailon
Redo threadmounted by this instance: 1
Oracle processnumber: 19
Unix process pid:5779, image: oracle@RAC1 (TNS V1-V3)
*** 2013-06-0601:16:22.327
*** SESSIONID:(125.5) 2013-06-06 01:16:22.327
*** CLIENT ID:()2013-06-06 01:16:22.327
*** SERVICENAME:() 2013-06-06 01:16:22.327
*** MODULENAME:(sqlplus@RAC1 (TNS V1-V3)) 2013-06-06 01:16:22.327
*** ACTION NAME:()2013-06-06 01:16:22.327
*** 2013-06-0601:16:22.324 5402 dbsdrv.c
*** 2013-06-0601:16:40.327
Successfully allocated 2 recovery slaves
Using 67 overflowbuffers per recovery slave
Thread 1checkpoint: logseq 1, block 2, scn 1076101
on-disk rba: logseq 1, block 3, scn 1076103
start recovery at logseq 1, block 3, scn1076103
*** 2013-06-0601:16:40.367
Started writing zero blks thread 1 seq 1 blocks 3-10
*** 2013-06-0601:16:40.401
Completed writing zero blks thread 1 seq 1
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC):0Kb in 0.06s => 0.00 Mb/sec
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets= 262144
Longest hash chain= 0
Average hash chain= 0/0 = 0.0
Max compares perlookup = 0
Avg compares perlookup = 0/0 = 0.0
----------------------------------------------
*** 2013-06-0601:16:40.403
KCRA: startrecovery claims for 0 data blocks
*** 2013-06-0601:16:40.404
KCRA: blocksprocessed = 0/0, claimed = 0, eliminated = 0
*** 2013-06-06 01:16:40.405
Recovery of OnlineRedo Log: Thread 1 Group 1 Seq 1 Reading mem 0
*** 2013-06-0601:16:40.406
Completed redoapplication of 0.00MB
*** 2013-06-0601:16:40.406
Completed recoverycheckpoint
----- RecoveryHash Table Statistics ---------
Hash table buckets= 262144
Longest hash chain= 0
Average hash chain= 0/0 = 0.0
Max compares perlookup = 0
Avg compares perlookup = 0/0 = 0.0
----------------------------------------------
Recovery sets nabof thread 1 seq 1 to 3 with 8 zeroblks
*** 2013-06-0601:16:40.829
Incident 36153created, dump file:/u01/app/oracle/diag/rdbms/wailon/wailon/incident/incdir_36153/wailon_ora_5779_i36153.
trc
ORA-00600:internal error code, arguments: [2662], [0], [1096111], [0], [1096312],[4194432], [], [], [], [], [], []
ORA-00600:internal error code, arguments: [2662], [0], [1096111], [0], [1096312],[4194432], [], [], [], [], [], []
ORA-00600:internal error code, arguments: [2662], [0], [1096111], [0], [1096312],[4194432], [], [], [], [], [], []
*** 2013-06-0601:16:41.963
USER (ospid:5779): terminating the instance due to error 600
下次启动时不要使用*._allow_resetlogs_corruption=TRUE此参数即可。
