由於機房斷電,導致資料庫異常down 機，重啟的時候報錯：

Errors in file /opt/oracle/admin/aliuid/bdump/aliuid_arc1_19960.trc:

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 26402 change 0 time 02/14/2012 01:32:17

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/aliuid/redo01.log'

Mon Feb 20 15:57:00 2012

Errors in file /opt/oracle/admin/aliuid/udump/aliuid_ora_20140.trc:

ORA-00600: internal error code, arguments: [2662], [0], [94450252], [0], [94509989], [8388625], [], []

ORA-00354 ,ORA-00353,ORA-00312 報錯是由於線上日誌塊損壞導致！

針對日誌壞塊，我使用了 _disable_logging 隱含引數，然後清理了線上日誌！

@>alter system set "_disable_logging"=false scope=both;

System altered.

@>alter database clear unarchived logfile '/opt/oracle/oradata/aliuid/redo01.log';

Database altered.

@>select * from v$log;

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

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

1 1 0 104857600 1 YES UNUSED 94410240 2012-02-13 21:35:38

2 1 381 104857600 1 NO INACTIVE 94430242 2012-02-20 15:35:43

3 1 382 104857600 1 NO CURRENT 94450247 2012-02-20 15:56:57

@>exit

oracle@c9e016r3ectk1xl67j8p:aliuid /home/oracle>sqlplus "/as sysdba"

@>startup mount;

ORACLE instance started.

Total System Global Area 3693056168 bytes

Fixed Size 745640 bytes

Variable Size 469762048 bytes

Database Buffers 3221225472 bytes

Redo Buffers 1323008 bytes

Database mounted.

@>

@>col name for a60

@> select * from v$log;

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

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

1 1 383 104857600 1 NO CURRENT 94470251 2012-02-20 20:12:56

2 1 381 104857600 1 YES INACTIVE 94430242 2012-02-20 15:35:43

3 1 382 104857600 1 YES INACTIVE 94450247 2012-02-20 15:56:57

@>alter database clear unarchived logfile '/opt/oracle/oradata/aliuid/redo01.log';

alter database clear unarchived logfile '/opt/oracle/oradata/aliuid/redo01.log'

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/aliuid/redo01.log'

@>alter database clear unarchived logfile '/opt/oracle/oradata/aliuid/redo02.log';

Database altered.

@>col name for a60

@> select * from v$log;

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

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

1 1 383 104857600 1 NO CURRENT 94470251 2012-02-20 20:12:56

2 1 0 104857600 1 YES UNUSED 94430242 2012-02-20 15:35:43

3 1 382 104857600 1 YES INACTIVE 94450247 2012-02-20 15:56:57

@>alter database clear unarchived logfile '/opt/oracle/oradata/aliuid/redo03.log';

Database altered.

@>alter database open;

alter database open

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

oracle@c9e016r3ectk1xl67j8p:aliuid /home/oracle>sqlplus "/as sysdba"

@>startup mount;

ORACLE instance started.

Total System Global Area 3693056168 bytes

Fixed Size 745640 bytes

Variable Size 469762048 bytes

Database Buffers 3221225472 bytes

Redo Buffers 1323008 bytes

Database mounted.

@>col name for a60

@> select * from v$log;

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

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

1 1 383 104857600 1 YES INACTIVE 94470251 2012-02-20 20:12:56

2 1 384 104857600 1 NO CURRENT 94490254 2012-02-20 20:15:36

3 1 0 104857600 1 YES UNUSED 94450247 2012-02-20 15:56:57

ORA-600 [2662] "Block SCN is ahead of Current SCN"，說明當前資料庫的資料塊的SCN早於當前的SCN，主要是和儲存在UGA變數中的dependent SCN進行比較，如果當前的SCN小於它，資料庫就會產生這個ORA-600 [2662]的錯誤了。這個錯誤一共有五個引數，分別代表不同的含義，

ORA-600 [2662] [a] [b] [c] [d] [e]

Arg [a] Current SCN WRAP

Arg [b] Current SCN BASE

Arg [c] dependent SCN WRAP

Arg [d] dependent SCN BASE

Arg [e] Where present this is the DBA where the dependent SCN came from.

當前的報錯是當前的SCN=94450252 小於dependent SCN 94509989！

文件上介紹

"1.資料庫crash後設定了_DISABLE_LOGGING隱含引數

2.硬體錯誤引起資料庫沒法寫控制檔案和重做日誌檔案

3.錯誤的部分恢復資料庫

4.恢復了控制檔案但是沒有使用recover database using backup controlfile進行恢復

5.使用隱含引數_ALLOW_RESETLOGS_CORRUPTION後resetlogs開啟資料庫

6.在並行伺服器環境中DLM存在問題 "

參考了eygle 的文章使用ADJUST_SCN事件來調整當前的SCN，使其大於dependent SCN!(然後保證資料庫可以全庫的匯出，然後重建資料庫匯入資料)

@>alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1';

ERROR:

ORA-00600: internal error code, arguments: [kcsadjn1], [], [], [], [], [], [], []

這一步執行錯誤，本應該在mount狀態執行此命令的！eygle的文章：

增進SCN有兩種常用方法:

1.透過immediate trace name方式(在資料庫Open狀態下)

alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';

2.透過10015事件(在資料庫無法開啟，mount狀態下)

alter session set events '10015 trace name adjust_scn level x';

注:level 1為增進SCN 10億 (1 billion) (1024*1024*1024),通常Level 1已經足夠。也可以根據實際情況適當調整。

本例由於資料庫無法開啟，只能使用的二種方法。

之後多次shutdown ，startup 之後 scn 的報錯消失，出現關於undo的ora-600 [4193] 報錯，次錯誤是和undo表空間相關的報錯

“ While backing out an undo record (i.e. at the time of rollback) we found a transaction id mis-match indicating either a corruption in the rollback segment or corruption in an object which the rollback segment is trying to apply undo records on.This would indicate corrupted rollback segment.”

其解決辦法是(針對非open狀態的資料庫)

1 在pfile裡修改

*.undo_management='MANUAL'

*.rollback_segments='SYSTEM'

2 重新啟動資料庫

3 drop tablespace undotbs1 including contents and datafiles;

4 create undo tablespace undotbs2 datafile '/opt/oracle/oradata/aliuid/undotbs2.dbf' size 500m;

5 shutdown immediate;

6 重新修改資料庫引數檔案為新的undo表空間！

*.undo_management='AUTO'

*.undo_retention=900

*.undo_tablespace='UNDOTBS2'

7 建立新的spfile檔案並再次重新啟動資料庫

最終資料庫問題解決！

參考文章

ORA-600 [4193]錯誤解決方法

current online redo和undo 損壞解決方法

【Oracle】Current online Redo 和 Undo 損壞的處理方法

相關文章