Oracle 隱含引數 : _allow_resetlogs_corruption

tolywang發表於2008-02-13
Oracle
提示：Oracle的隱含引數只應該在測試環境或者在Oracle Support的支援下使用。
在使用進一步的測試中，試圖透過switch logfile進行日誌切換，結果重起居然報出日誌檔案損壞。
 
SQL> startup
ORACLE instance started.

Total System Global Area   97588504 bytes
Fixed Size                   451864 bytes
Variable Size              33554432 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table t as select * from dba_users;

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
        12


試圖透過switch logfile觸發檢查點:
 
SQL> alter system switch logfile;

System altered.


SQL> insert into t select * from t;

12 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)
----------
        24


日誌檔案損壞(未測試是否可以重複出現):
 
SQL> startup force;
ORACLE instance started.

Total System Global Area   97588504 bytes
Fixed Size                   451864 bytes
Variable Size              33554432 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 897612314 time 10/19/2005 14:19:34
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/conner/redo03.log'


損壞的是active的日誌檔案:
 
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        159   10485760          1 NO  INACTIVE             897592312 19-OCT-05
         2          1        158   10485760          1 NO  INACTIVE             897572310 19-OCT-05
         3          1        160   10485760          1 NO  ACTIVE               897612314 19-OCT-05
         4          1        161    1048576          1 NO  CURRENT              897612440 19-OCT-05


只好使用另外一個隱含引數_allow_resetlogs_corruption強制啟動資料庫，設定此引數之後，在資料庫Open過程中，Oracle會跳過某些一致性檢查，從而使資料庫可能跳過不一致狀態，Open開啟:
 
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;
ORACLE instance started.

Total System Global Area   97588504 bytes
Fixed Size                   451864 bytes
Variable Size              33554432 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 897612315 generated at 10/19/2005 16:54:18 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/conner/archive/1_160.dbf
ORA-00280: change 897612315 for thread 1 is in sequence #160


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/conner/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area   97588504 bytes
Fixed Size                   451864 bytes
Variable Size              33554432 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.


幸運的時候資料庫就可以成功Open，如果不幸可能會遇到一系列的錯誤（最常見的是）此時就需要使用多種手段繼續進行調整恢復。
如果注意觀察alert日誌，我們可能會發現類似以下日誌:
 
Fri Jun 10 16:30:25 2005
alter database open resetlogs
Fri Jun 10 16:30:25 2005
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 240677200
Resetting resetlogs activation ID 3171937922 (0xbd0fee82)


Oracle告訴我們，強制resetlogs跳過了一致性檢查，可能導致資料庫損壞，資料庫應當重建。
不一致恢復最後恢復到的Change號是：240677200

通常使用此方法Open資料庫之後，應該立即透過匯出、匯入重建資料庫

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-627328/，如需轉載，請註明出處，否則將追究法律責任。

相關文章