非歸檔模式下線上日誌檔案破壞後例項恢復案例

公司測試部一資料庫無法正常啟動，找到了我幫忙解決！具體故障現象如下：
SQL> startup
ORACLE instance started.

Total System Global Area 353857616 bytes
Fixed Size 737360 bytes
Variable Size 318767104 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 74032 change 1121332681927 time 09/25/2007
10:47:51
ORA-00312: online log 3 thread 1: '/oracle/oradata/ora9i/redo03.log'

[@more@]
提示線上日誌檔案遭到了破壞，導致資料庫無法正常啟動。將資料庫shutdown後在啟動引數中新增隱含引數如下：
*._allow_resetlogs_corruption=TRUE
緊接著執行恢復命令如下：
SQL>
SQL> recover database until cancel;
ORA-00279: change 1121332681926 generated at 09/25/2007 10:47:51 needed for
thread 1
ORA-00289: suggestion : /oracle/product/9.2.0.4/dbs/arch1_37807.dbf
ORA-00280: change 1121332681926 for thread 1 is in sequence #37807

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata/ora9i/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 353857616 bytes
Fixed Size 737360 bytes
Variable Size 318767104 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
到這裡可以看到資料庫已經正常啟動了。但是線上日誌組裡的日誌資訊被reset後從0開始計數。見下面的查詢：
SQL>
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 104857600 1 YES UNUSED
0

2 1 0 104857600 1 YES UNUSED
0

3 1 1 104857600 1 NO CURRENT
1.1213E+12 25-SEP-07

SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/oracle/oradata/ora9i/redo03.log

2 ONLINE
/oracle/oradata/ora9i/redo02.log

1 ONLINE
/oracle/oradata/ora9i/redo01.log