redo檔案破壞使用隱含參數恢複資料庫

資料庫在沒有歸檔的情況下active狀態日誌損壞

SQL> select * from v$Log; 

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

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

        1          1          2  52428800          1 NO  ACTIVE                  462055 07-SEP-12

        2          1          3  52428800          1 NO  CURRENT                 462062 07-SEP-12

        3          1          1  52428800          1 NO  ACTIVE                  461887 07-SEP-12

 

SQL> select * from v$logfile;

 

   GROUP# STATUS  TYPE    MEMBER                                   IS_

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

        3         ONLINE  /u01/tiger/oradata/orcl/redo03.log       NO

        2         ONLINE  /u01/tiger/oradata/orcl/redo02.log       NO

        1         ONLINE  /u01/tiger/oradata/orcl/redo01.log       NO

 

SQL> !cp /etc/passwd/u01/tiger/oradata/orcl/redo01.log

 

SQL> shut abort

Oracle instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219136 bytes

Variable Size             109053376 bytes

Database Buffers          197132288 bytes

Redo Buffers                7168000 bytes

Database mounted.

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

ORA-00312: online log 1 thread 1:'/u01/tiger/oradata/orcl/redo01.log'

ORA-27046: file size is not a multiple oflogical block size

Additional information: 1

------啟動的時候發現日誌組1出現了問題

SQL> select * from v$log;

 

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

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

        1          1          2  52428800          1 NO  ACTIVE                  462055 07-SEP-12

        3          1          1  52428800          1 NO  ACTIVE                  461887 07-SEP-12

        2          1          3  52428800          1 NO  CURRENT                 462062 07-SEP-12

----查看得出日誌組1是active狀態

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence           3

------再看資料庫的模式：得出是非歸檔狀態

SQL> recover database;

ORA-00283: recovery session canceled due toerrors

ORA-00313: open failed for members of loggroup 1 of thread 1

ORA-00312: online log 1 thread 1:'/u01/tiger/oradata/orcl/redo01.log'

ORA-27046: file size is not a multiple oflogical block size

Additional information: 1

 

 

SQL> recover database until cancel;

ORA-00279: change 462055 generated at09/07/2012 14:03:23 needed for thread 1

ORA-00289: suggestion :/u01/tiger/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_%u_.arc

ORA-00280: change 462055 for thread 1 is insequence #2

 

 

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

 

ORA-00308: cannot open archived log '/u01/tiger/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded butOPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to beconsistent

ORA-01110: data file 1:'/u01/tiger/oradata/orcl/system01.dbf'

------上面恢複的時候看到資料庫不是歸檔但是在恢複的時候他們要找歸檔日誌

這時候我們不恢複了直接啟動資料庫（藉助隱含參數）設定此參數之後，在資料庫Open過程中，Oracle會跳過某些一致性檢查，從而使資料庫可能跳過不一致狀態，Open開啟

Oracle的隱含參數只應該在測試環境或者在Oracle Support的支援下使用

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

System altered.

 

隱含參數：redo塊損壞時

第一，如果損壞的是非當前redo檔案並且已經歸檔我們可以使用

alter database clear logfile group 1清空該記錄檔

第二，如果損壞的是非當前redo檔案並且該檔案組沒有歸檔此時使用

alter database clear unarchived logfilegroup 1強行清空日誌

第三，歸檔模式下當前日誌的損壞有兩種情況：

1、是資料庫是正常關閉，記錄檔中沒有未決的事務需要執行個體恢複，當前日誌組的損壞就可以直接用alter database clear unarchived logfile group n來重建。

2、是日誌組中有活動的事務，資料庫需要介質恢複，日誌組需要用來同步，有兩種補救辦法

A:最好的辦法就是通過不完全恢複，可以保證資料庫的一致性，但是這種辦法要求在歸檔方式下，並且有可用的備份

B:通過強制性恢複，但是可能導致資料庫不一致。

SQL> shutdown immediate

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219136 bytes

Variable Size             109053376 bytes

Database Buffers          197132288 bytes

Redo Buffers                7168000 bytes

Database mounted.

ORA-01589: must use RESETLOGS orNORESETLOGS option for database open

 

 

SQL> alter database open resetlogs;

 

Database altered.