Oracle 联机日志文件损坏的几种场景和恢复方法

Oracle 联机日志文件损坏的几种场景和恢复方法

Oracle联机日志文件记录数据库运行过程中数据块改变的日志，在数据库出现介质损坏或者异常挂掉后，需要通过联机日志（或归档）日志，重演数据库发生的改变。
 
在日志文件本身出现损坏（丢失）的情况下，数据库可能出现无法正常打开，本文就是针对这种情况下进行的恢复测试（仅供参考）：

Oracle调整联机重做日志大小(change redo log size)

Oracle 联机重做日志文件(ONLINE LOG FILE)

Oracle联机重做日志丢失的恢复

Oracle 联机重做日志文件(online redo log file) 详述

Oracle重做日志文件版本不一致问题处理

【备份与恢复】恢复受损的复用联机重做日志文件

一、日志文件损坏分类：
 
1、inactive 状态（不会造成数据丢失）
 
2、active、current状态（一般会造成数据丢失）
 
查看方法：
 
SQL> select group#,thread#,archived,status from v$log;
 
    GROUP#    THREAD# ARC STATUS
 ---------- ---------- --- ----------------
          1          1 YES INACTIVE
          2          1 NO  CURRENT
          3          1 YES INACTIVE

二、测试环境：
 
•OS: Linux xxxxxxxx  2.6.18-238.el5 #1 SMP Thu Jan 13 15:51:15 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
•DB: oracle 11.2.0.1.6（非RAC)
 

三、inactive 状态日志文件损坏的恢复测试：
 
startup时错误日志：
 
SQL> startup
 ORACLE instance started.
 

Total System Global Area 3156877312 bytes
 Fixed Size                  2217424 bytes
 Variable Size            989858352 bytes
 Database Buffers        2147483648 bytes
 Redo Buffers              17317888 bytes
 Database mounted.
 ORA-03113: end-of-file on communication channel
 Process ID: 29499
 Session ID: 2273 Serial number: 5
 

alert错误日志：
 
Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_lgwr_29457.trc:
 ORA-00313: open failed for members of log group 1 of thread 1
 ORA-00312: online log 1 thread 1: '/u01/test/test/redo01.log'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_lgwr_29457.trc:
 ORA-00313: open failed for members of log group 1 of thread 1
 ORA-00312: online log 1 thread 1: '/u01/test/test/redo01.log'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29499.trc:
 ORA-00313: open failed for members of log group 1 of thread
 ORA-00312: online log 1 thread 1: '/u01/test/test/redo01.log'
 USER (ospid: 29499): terminating the instance due to error 313
 Instance terminated by USER, pid = 29499
 

这种情况下，只需将active的日志组删除，然后startup，如下：
 
SQL> startup mount;
 ORACLE instance started.
 Total System Global Area 3156877312 bytes
 Fixed Size                  2217424 bytes
 Variable Size            989858352 bytes
 Database Buffers        2147483648 bytes
 Redo Buffers              17317888 bytes
 Database mounted.
 SQL> alter database drop logfile group 1;
 Database altered.
 SQL> alter database open;
 Database altered.

四、active、current日志文件损坏：
 
startup时错误：
 
SQL> startup
 ORACLE instance started.
 Total System Global Area 3156877312 bytes
 Fixed Size                  2217424 bytes
 Variable Size            989858352 bytes
 Database Buffers        2147483648 bytes
 Redo Buffers              17317888 bytes
 Database mounted.
 ORA-00313: open failed for members of log group 3 of thread 1
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 SQL> select group#,thread#,archived,status from v$log;
    GROUP#    THREAD# ARC STATUS
 ---------- ---------- --- ----------------
          1          1 NO  CURRENT
          3          1 YES ACTIVE
          2          1 YES INACTIVE 

alert日志错误：
 
Completed: ALTER DATABASE  MOUNT
 Fri Apr 25 16:49:21 2014
 ALTER DATABASE OPEN
 Beginning crash recovery of 1 threads
  parallel recovery started with 2 processes
 Started redo scan
 Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29862.trc:
 ORA-00313: open failed for members of log group 3 of thread 1
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 Aborting crash recovery due to error 313
 Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29862.trc:
 ORA-00313: open failed for members of log group 3 of thread 1
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29862.trc:
 ORA-00313: open failed for members of log group 3 of thread 1
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 ORA-313 signalled during: ALTER DATABASE OPEN...
 Fri Apr 25 16:49:21 2014
 Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_m000_29874.trc:
 ORA-00313: open failed for members of log group 3 of thread 1
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 Checker run found 2 new persistent data failures

这种情况下无法删除active联机日志，尝试各种删除方式都会报错，如下：
 
SQL> alter database drop logfile group 3;
 alter database drop logfile group 3
 *
 ERROR at line 1:
 ORA-01624: log 3 needed for crash recovery of instance test (thread 1)
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'
 SQL> alter database clear logfile group 3;
 alter database clear logfile group 3
 *
 ERROR at line 1:
 ORA-01624: log 3 needed for crash recovery of instance test (thread 1)
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'
 SQL> alter database clear unarchived logfile group 3;
 alter database clear unarchived logfile group 3
 *
 ERROR at line 1:
 ORA-01624: log 3 needed for crash recovery of instance test (thread 1)
 ORA-00312: online log 3 thread 1: '/u01/test/test/redo03.log'

更多详情见请继续阅读下一页的精彩内容：