非歸檔模式下，丟失日志文件的一次恢復(fù)過程

場景描述：

Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

非歸檔模式下，備份控制文件后，切換日志文件，并執(zhí)行完全檢查點(diǎn)操作。并模擬數(shù)據(jù)庫斷電關(guān)閉。然后刪除現(xiàn)有控制文件后，還原備份的控制文件，然后打開庫。
曲折過程如下：

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

打開數(shù)據(jù)庫報(bào)錯：

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'

使用備份的控制文件恢復(fù)：

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:{<RET>=suggested | filename | AUTO | CANCEL}

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

由于數(shù)據(jù)庫處于非歸檔狀態(tài)，找不到對應(yīng)的歸檔日志文件，恢復(fù)未成功

嘗試不完全恢復(fù)，無效：

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:{<RET>=suggested | filename | AUTO | CANCEL}

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

--非法關(guān)閉數(shù)據(jù)庫

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

--數(shù)據(jù)文件一致性檢查未通過

ORA-1194 signalled during: alter database openresetlogs...

通過數(shù)據(jù)文件及重做日志文件重建控制文件，還是跟上面的錯誤一樣。后來一不小心把所有重做日志文件刪除了，之后就是以下的情況

修改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

至此，數(shù)據(jù)庫正常打開。

小插曲：

期間若出現(xiàn)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此參數(shù)即可。