遇到一个案例,数据库open报ORA-16038,ORA-00354等错误
查询该redo状态(使用Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本收集),确认为inactive
由于inactive 状态的redo损坏,无法被arch进程归档导致数据库无法正常open,尝试强制clear联机日志
由于25号文件属于offline状态,导致联机日志无法正常被clear,报ORA-00393 ORA-00312等错误.通过试验重现该问题.
SQL> alter database datafile 5 offline;
Database altered.
--使用一些技巧让数据库无法归档
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARC
---------- ---------------- ---
1 INACTIVE NO
2 ACTIVE NO
3 CURRENT NO
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 234882088 bytes
Database Buffers 385875968 bytes
Redo Buffers 3313664 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00393: log 1 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf'
SQL> !oerr ora 393
00393, 00000, "log %s of thread %s is needed for recovery of offline datafiles"
// *Cause: Log cannot be cleared because the redo in it is needed to recover
// offline datafiles. It has not been archived so there is no
// other copy available. If the log is cleared the tablespaces
// containing the files will have to be dropped.
// *Action: Archive the log then repeat the clear command. If archiving is not
// possible, and dropping the tablespaces is acceptible, then add the
// clause UNRECOVERABLE DATAFILE at the end of the clear command.
SQL> alter database clear unarchived logfile group 1 unrecoverable datafile;
Database altered.
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARC
---------- ---------------- ---
1 UNUSED YES
3 CURRENT NO
2 ACTIVE NO
客户的问题也是通过unrecoverable datafile 方式强制clear联机日志成功,数据库open成功
加我QQ
