非归档模式下在线日志文件破坏后实例恢复案例

公司测试部一数据库无法正常启动，找到了我帮忙解决！具体故障现象如下：
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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38542/viewspace-972189/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38542/viewspace-972189/