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'

 

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁