联机重做日志文件恢复

联机重做日志文件恢复

[@more@]

Ø 损坏状态为INACTIVE的联机日志文件

SQL> select group#,thread#,sequence#,members,archived,status,first_change# from v$log order by group#; GROUP# THREAD# SEQUENCE# MEMBERS ARCHIV STATUS FIRST_CHANGE# ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- 1 1 9 2 YES INACTIVE 30536577211 2 1 8 2 YES INACTIVE 30536577208 3 1 10 2 YES INACTIVE 30536577220 4 1 11 2 YES INACTIVE 30536577223 5 1 12 2 YES INACTIVE 30536577226 6 2 9 2 YES INACTIVE 30536577224 7 2 10 2 YES INACTIVE 30536577227 8 2 11 2 NO CURRENT 30536577230 9 2 5 2 YES INACTIVE 30536577206 10 2 6 2 YES INACTIVE 30536577209 11 1 13 2 YES INACTIVE 30536577229

GROUP# THREAD# SEQUENCE# MEMBERS ARCHIV STATUS FIRST_CHANGE# ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- 12 1 14 2 NO CURRENT 30536577232 16 2 8 2 YES INACTIVE 30536577221 17 2 7 2 YES INACTIVE 30536577218

红色表示为 GROUP# 4 的日志文件状态为 INACTIVE.

后台alert.log告警日志，报错显示该日志文件组2个成员，包括/dev/rsh_redo1_04、/dev/rsh_redo1_14已经全部损坏.

Errors in file /u01/oracle/admin/shestate/bdump/shestate_lgwr_970990.trc: ORA-00316: log 4 of thread 1, type 0 in header is not log file ORA-00312: online log 4 thread 1: '/dev/rsh_redo1_14' ORA-00316: log 4 of thread 1, type 0 in header is not log file ORA-00312: online log 4 thread 1: '/dev/rsh_redo1_04' Mon Dec 3 03:34:24 2012 Errors in file /u01/oracle/admin/shestate/bdump/shestate_lgwr_970990.trc: ORA-00316: log 4 of thread 1, type 0 in header is not log file ORA-00312: online log 4 thread 1: '/dev/rsh_redo1_14' ORA-00316: log 4 of thread 1, type 0 in header is not log file ORA-00312: online log 4 thread 1: '/dev/rsh_redo1_04' Mon Dec 3 03:34:24 2012 ORA-316 signalled during: ALTER DATABASE OPEN.. 打开数据库也报错： Total System Global Area 1411877936 bytes Fixed Size 744496 bytes Variable Size 1358954496 bytes Database Buffers 50331648 bytes Redo Buffers 1847296 bytes Database mounted. ORA-00316: log 4 of thread 1, type in header is not log file ORA-00312: online log 4 thread 1: '/dev/rsh_redo1_04' ORA-00312: online log 4 thread 1: '/dev/rsh_redo1_14'

下面开始恢复：

SQL> alter database clear logfile group 4; Database altered. SQL> alter database open; Database altered. SQL>

Ø 损坏状态为ACTIVE/CURRENT的联机日志文件

报错也与上述一样，只不过是current/active日志，下面只是进行恢复操作：

SQL> alter database clear logfile group 7; alter database clear logfile group 7 *ERROR at line 1: ORA-00350: log 7 of thread 2 needs to be archived ORA-00312: online log 7 thread 2: '/dev/rsh_redo2_02' ORA-00312: online log 7 thread 2: '/dev/rsh_redo2_12' SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01124: cannot recover data file 1 - file is in use or recovery ORA-01110: data file 1: '/dev/rsh_system_01' SQL> recover database using backup controlfile until cancel; ORA-00279: change 30793014259 generated at 12/07/2012 14:18:56 needed for thread 1 ORA-00289: suggestion : /u01/oradata/archivelog/1_143.dbf ORA-00280: change 30793014259 for thread 1 is in sequence #143 $ pwd /home/oracle $ sqlplus "/as sysdba" Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 1411877936 bytes Fixed Size 744496 bytes Variable Size 1358954496 bytes Database Buffers 50331648 bytes Redo Buffers 1847296 bytes SQL> alter database mount; Database altered. SQL> alter database open resetlogs; Database altered. SQL>

需要注意的是：

如果current online logfile丢失，分为两种情形，

第一种情形：数据库shutdown immediate正常关闭，恢复数据库上述的例子是第一种情形。

第二种情形：数据库shutdown abort异常关闭，第二种情形如果想要打开数据库，必须使用备份，进行全库恢复，然后apply数据库产生的最后一个归档日志，使用alter database open resetlogs打开数据库，全库恢复已经做了恢复，不再赘述。关于使用隐患参数"_allow_resetlogs_corruption"，跳过数据库不一致状态，打开数据库，oracle建议必须在oracle技术支持人员下进行。

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

转载于:http://blog.itpub.net/28227905/viewspace-1059888/