一:通过警告日志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;
转载自chenoracle

最后修改:2022 年 02 月 27 日
如果觉得我的文章对你有用,请随意赞赏