oracle recover redo,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 recoverySQL> 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

2CURRENT

/*

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;