探索ORACLE之RMAN_07 重做日志redu檔案丢失恢複

探索ORACLE之RMAN_07 重做日志redu檔案丢失恢複

作者：吳偉龍Name：Prodence Woo

QQ：286507175  msn:[email protected]       

       重做日志檔案記錄了資料庫的變更資料。一般重做日志檔案的失敗不會使資料庫資料丢失，但是會影響資料庫的恢複。重做日志分為兩種狀态目前聯機重做日志和非目前的聯機重做日志

4.1

非目前redo(聯機重做日志)檔案丢失恢複

資料庫運作的時候，日志中報如下錯誤：

ORA-00313: openfailed for members of log group 1 of thread 1

ORA-00312: onlinelog 1 thread 1: '/DBData/WWL/redo01.log'

檢視日志組，判斷損壞的日志組是否為目前日志組

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ----------------------------- ---------

         1          1         32  31457280          1 YES INACTIVE               1063037 04-JUL-12

         3          1         34  31457280          1 NO  CURRENT                1118555 05-JUL-12

         2          1        33   31457280          1 YES INACTIVE               1086278 05-JUL-12

我們可以看到損壞的那組日志不是目前的日志，這個時候我麼可以通過使用clear指令來重建該日志檔案組。

通過重建來恢複非目前日志組，實作資料庫的打開。

SQL> startup

ORACLE instancestarted.

Total SystemGlobal Area  100663296 bytes

Fixed Size                  1217884 bytes

Variable Size              88083108 bytes

DatabaseBuffers            8388608 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL>alter database clear logfile group 1;

Databasealtered.

重建完之後資料庫可以打開了，至此恢複完成

SQL> alter database open;

Database altered.

4.2目前redo(聯機重做日志)檔案丢失恢複

資料庫啟動的時候報如下錯誤

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size                 

1217884 bytes

Variable Size             88083108 bytes

Database Buffers           

8388608 bytes

Redo Buffers               

2973696 bytes

ORA-00313: open failed for members of log group 1 ofthread 1

ORA-00312: online log 1 thread 1:'/DBData/WWL/redo01.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

---------- ---------- ---------- ---------- ---------- ------------------- ------------- ---------

         1          1         35  31457280          1 NO  CURRENT                1139915 05-JUL-12

         3          1         34  31457280          1 YES INACTIVE               1118555 05-JUL-12

         2          1         33  31457280          1 YES INACTIVE               1086278 05-JUL-12

在這裡可以看到損壞的為目前日志組，那麼意味着會有線上資料丢失，因為重做日志裡面目前狀态裡面存放的是是沒有歸檔及寫入到資料檔案的活動資料，那麼這種恢複必然是會導緻資料的不同步，進而使資料丢失。

我們可以首先嘗試清空日志組資訊并重建的方式來進行恢複：

SQL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instanceWWL (thread 1)

如上方法不行，可以采取基于SCN，取消的方法來恢複資料庫。

使用基于控制檔案的redo恢複：

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 1139916 generated at 07/05/201221:49:48 needed for thread 1

ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf

ORA-00280: change 1139916 for thread 1 is in sequence #35

Specify log: {<RET>=suggested | filename | AUTO |CANCEL}

auto

ORA-00308: cannot open archived log '/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf'

ORA-00308: cannot open archived log'/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf'

SQL> alter system set"_allow_resetlogs_corruption" = true scope = spfile;

System altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

SQL> alter system reset"_allow_resetlogs_corruption" scope = spfile sid = '*';

SQL> alter database open resetlogs;

SQL> select instance_name,status from v$instance;

INSTANCE_NAME   STATUS

---------------- ------------

WWL             OPEN

         1          1          1  31457280          1 NO  CURRENT                1200799 06-JUL-12

         2          1          0  31457280          1 YES UNUSED                       0

         3          1          0  31457280          1 YES UNUSED                       0

SQL>

    GROUP#    THREAD#  SEQUENCE#     BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

         1          1         17  31457280          1 YES INACTIVE               1241271 06-JUL-12

         2          1         18  31457280          1 YES INACTIVE               1241273 06-JUL-12

         3          1         19  31457280          1 NO  CURRENT                1241275 06-JUL-