ORA-16038 ORA-00354故障处理---惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-16038 ORA-00354故障处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

遇到一个案例,数据库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成功

• Buffer I/O error on dev故障数据库恢复
• Oracle Recovery Tools解决ORA-00279 ORA-00289 ORA-00280故障
• 当前联机日志损坏恢复
• 一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools
• ora-600 kccpb_sanity_check_2故障处理
• 数据库open状态下日志异常处理
• Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障
• 再一例asm disk被误加入vg并且扩容lv恢复
• 因尝试恢复未破坏数据库实现数据0丢失恢复
• system01.dbf文件被offline,导致数据库报ORA-01245 ORA-01110故障恢复
• Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题）
• 修改dataguard主库redo组数和大小