oracle redo文件修复,REDO日志文件损坏或丢失恢复 | 信春哥，系统稳，闭眼上线不回滚！...

最近有个单位数据库服务器“死机”了，他们把服务器重启后，数据库无法打开，遇到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

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

模拟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

启动数据库报错

损坏：

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

修改数据库隐含参数

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

System altered.

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

基于取消的不完全恢复

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: {=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

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