場景描述：

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/arch1_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/arch1_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/arch1_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

--非法關閉資料庫

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], [], [], [], [], [], []

*** 2013-06-0601:16:41.963

USER (ospid:5779): terminating the instance due to error 600

下次啟動時不要使用*._allow_resetlogs_corruption=TRUE此引數即可。

非歸檔模式下，丟失日誌檔案的一次恢復過程

相關文章