最近有个单位数据库服务器“死机”了，他们把服务器重启后，数据库无法打开，遇到ORA-00313和ORA-00312错误。

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/oradata/PROD/disk1/redo01.log'

我QQ远程登录主机后，发现导致他们说的“死机”的原因是根分区满了，操作系统夯住了，而服务器是直接断点的方式关闭的，导致数据库当前REDO日志损坏。我在自己的虚拟机上没有模拟出样的案例，即使根分区写满，直接将虚拟机断电，在清理根分区空间后，数据库都是直接就可以打开，本案例是模拟当前REDO日志损坏或丢失的情况下的修复，当然这种情况通常会丢失部分数据。

实验环境：OEL5.7 X86_64 ORACLE 11.2.0.3.0

以下是实验过程，由于当前日志损坏和丢失的恢复操作基本一样，下文操作将两种情况二合一。

1. 模拟REDO文件损坏或丢失

损坏：

[oracle@secdb1 disk1]$ echo > redo01.log
[oracle@secdb1 disk1]$ echo > redo02.log
[oracle@secdb1 disk1]$ echo > redo03.log

丢失：

[oracle@secdb1 disk1]$ rm -rf *.log

2. 启动数据库报错

损坏：

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/oradata/PROD/disk1/redo01.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

丢失：

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/oradata/PROD/disk1/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

3. 修改数据库隐含参数

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

设置此参数之后，在数据库Open过程中，Oracle会跳过某些一致性检查，从而使数据库可能跳过不一致状态，Open打开。

4. 基于取消的不完全恢复

SQL> recover database until cancel;

ORA-00279: change 257478 generated at 12/13/2012 19:26:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_1_801948404.dbf
ORA-00280: change 257478 for thread 1 is in sequence #1
Specify log: {<RET>=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/PROD/disk1/system01.dbf'
ORA-01112: media recovery not started

5. OPEN数据库

此时如果够幸运，可以OPEN数据库

SQL> alter database open resetlogs;

Database altered.

如果不够幸运，很可能遇到ORA-03113错误

SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

遇到ORA-03113错误也很好解决，只需要推出SQLPLUS，重新登录SQLPLUS，启动数据库即可OPEN数据库。

[oracle@secdb1 disk1]$ sqlplus / as sysdba
SQL> startup

ORACLE instance started.
Database mounted.
Database opened.

更惨一些，可能在OPEN数据库时遇到ORA-00603错误。

alter database open

*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

这是由于UNDO数据不一致，通常需要将undo_management和：_corrupted_rollback_segments参数设置为手动管理。如果运气好的话，将undo_management设置为手动即可OPEN数据库，如果打不开就需要修改_corrupted_rollback_segments隐含参数了。

SQL> alter system set undo_management='manual' scope=spfile;

System altered.
SQL> alter database open;

Database altered

如果打不开，修改_corrupted_rollback_segments隐含参数

SQL> alter system set "_corrupted_rollback_segments" ='manual' scope=spfile;

System altered.

SQL> alter database open;

Database altered

本文固定链接: http://www.dbdream.com.cn/2012/12/redo%e6%97%a5%e5%bf%97%e6%96%87%e4%bb%b6%e6%8d%9f%e5%9d%8f%e6%88%96%e4%b8%a2%e5%a4%b1%e6%81%a2%e5%a4%8d/ | 信春哥，系统稳，闭眼上线不回滚！