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;

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2139043/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2139043/