Oracle REDO損壞

Oracle REDO損壞

 

一：透過警告日誌alert_PROD2.log定位損壞的redolog名稱和所屬日誌組；

二：透過v$log檢視損壞redolog狀態；

三：

(1)如果狀態為STATUS=INACTIVE，ARC=YES；

說明已經完成歸檔，可以透過下面的命令清空損壞的日誌組，並且不會造成資料丟失；

SQL> alter database clear logfile group 2;
---數字代表組號

(2)如果狀態為STATUS=ACTIVE，表示正在歸檔，清空日誌組可能會丟失部分資料；

SQL> alter database clear unarchived logfile group 2;

---DG環境下如果執行了這個命令需要重新搭建DG

(3)如果狀態為STATUS=CURRENT，即當前日誌組損壞；

非歸檔模式無備份：

sql>startup mount;

sql>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
sql>shutdown immediate;
sql>startup mount;

sql>recover database until cancel;

sql>alter database open resetlogs;

歸檔模式下，有備份

# database point-in-time recovery
SQL> startup mountSQL> restore database until scn 1335185;
SQL> recover database until scn 1335185;
SQL> alter database open resetlogs;

=========================================================== 

案例：

DB:11.2.0.3.0

OS:Enterprise Linux Enterprise Linux Server release 5.4

故障：啟動資料庫報錯ORA-00333

SQL> startup

ORACLE instance started.

Total System Global Area  941600768 bytes

Fixed Size                  1348860 bytes

Variable Size             524290820 bytes

Database Buffers          411041792 bytes

Redo Buffers                4919296 bytes

Database mounted.

ORA-00333: redo log read error block 12158 count 4011

警告日誌：

Started redo scan

Incomplete read from log member '/u01/app/oracle/oradata/PROD2/redo02.log'. Trying next member.

Aborting crash recovery due to error 333

Errors in file /u01/app/oracle/diag/rdbms/prod2/PROD2/trace/PROD2_ora_5470.trc:

ORA-00333: redo log read error block 12158 count 4011

Errors in file /u01/app/oracle/diag/rdbms/prod2/PROD2/trace/PROD2_ora_5470.trc:

ORA-00333: redo log read error block 12158 count 4011

ORA-333 signalled during: ALTER DATABASE OPEN...

SQL> select group#,status from v$log;

    GROUP# STATUS

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

         1 INACTIVE

         3 INACTIVE

         2 CURRENT

/*

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance PROD2 (thread 1)

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD2/redo02.log'

 

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance PROD2 (thread 1)

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD2/redo02.log'

*/

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

SQL> shutdown immediate

SQL> startup

ORACLE instance started.

Total System Global Area  941600768 bytes

Fixed Size                  1348860 bytes

Variable Size             524290820 bytes

Database Buffers          411041792 bytes

Redo Buffers                4919296 bytes

Database mounted.

ORA-00333: redo log read error block 12158 count 4011

 

SQL> recover database until cancel;

ORA-00279: change 990943 generated at  needed for thread 1

 

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: '/u01/app/oracle/oradata/PROD2/system01.dbf'

 

ORA-01112: media recovery not started

 

SQL> alter database open resetlogs;