Oracle 联机日志文件损坏的几种场景和恢复方法

Oracle联机日志文件记录数据库运行过程中数据块改变的日志，在数据库出现介质损坏或者异常挂掉后，需要通过联机日志（或归档）日志，重演数据库发生的改变。

在日志文件本身出现损坏（丢失）的情况下，数据库可能出现无法正常打开，本文就是针对这种情况下进行的恢复测试（仅供参考）：Oracle调整联机重做日志大小（change redo log size） http://www.linuxidc.com/Linux/2013-03/81453.htmOracle 联机重做日志文件（ONLINE LOG FILE） http://www.linuxidc.com/Linux/2011-03/33068.htmOracle联机重做日志丢失的恢复 http://www.linuxidc.com/Linux/2014-04/100893.htmOracle 联机重做日志文件（online redo log file）详述 http://www.linuxidc.com/Linux/2013-06/86290.htmOracle重做日志文件版本不一致问题处理 http://www.linuxidc.com/Linux/2012-08/69026.htm【备份与恢复】恢复受损的复用联机重做日志文件 http://www.linuxidc.com/Linux/2012-06/63497.htm
一、日志文件损坏分类：

1、inactive 状态（不会造成数据丢失）

2、active、current状态（一般会造成数据丢失）

查看方法：

SQL> select group#,thread#,archived,status from v$log;

GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 1 NO CURRENT
3 1 YES INACTIVE二、测试环境：

•OS: Linux xxxxxxxx 2.6.18-238.el5 #1 SMP Thu Jan 13 15:51:15 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
•DB: oracle 11.2.0.1.6（非RAC）
三、inactive 状态日志文件损坏的恢复测试：

startup时错误日志：

SQL> startup
ORACLE instance started.
Total System Global Area 3156877312 bytes
Fixed Size 2217424 bytes
Variable Size 989858352 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17317888 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 29499
Session ID: 2273 Serial number: 5
alert错误日志：

Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_lgwr_29457.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: "/u01/test/test/redo01.log"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_lgwr_29457.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: "/u01/test/test/redo01.log"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29499.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: "/u01/test/test/redo01.log"
USER （ospid: 29499）: terminating the instance due to error 313
Instance terminated by USER, pid = 29499
这种情况下，只需将active的日志组删除，然后startup，如下：

SQL> startup mount;
ORACLE instance started.
Total System Global Area 3156877312 bytes
Fixed Size 2217424 bytes
Variable Size 989858352 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17317888 bytes
Database mounted.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database open;
Database altered.四、active、current日志文件损坏：

startup时错误：

SQL> startup
ORACLE instance started.
Total System Global Area 3156877312 bytes
Fixed Size 2217424 bytes
Variable Size 989858352 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17317888 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select group#,thread#,archived,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 NO CURRENT
3 1 YES ACTIVE
2 1 YES INACTIVE
alert日志错误：

Completed: ALTER DATABASE MOUNT
Fri Apr 25 16:49:21 2014
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29862.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Aborting crash recovery due to error 313
Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29862.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_ora_29862.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-313 signalled during: ALTER DATABASE OPEN...
Fri Apr 25 16:49:21 2014
Errors in file /u01/oracle/diag/rdbms/test/test/trace/test_m000_29874.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Checker run found 2 new persistent data failures
这种情况下无法删除active联机日志，尝试各种删除方式都会报错，如下：

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance test （thread 1）
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log"
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance test （thread 1）
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log"
SQL> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance test （thread 1）
ORA-00312: online log 3 thread 1: "/u01/test/test/redo03.log" 更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-05/101943p2.htm

Oracle回滚机制深入研究Oracle获取周几以及每周对应得开始日期和结束日期相关资讯 Oracle日志 Oracle日志文件

Oracle数据库在线重做日志被删除的（12/21/2015 15:16:49）
Oracle创建日志文件（03/13/2015 18:39:37）
修改Oracle重做日志文件大小（11/17/2014 09:13:00）

Oracle手动切换日志文件和清空日志（03/13/2015 18:41:05）
Oracle删除日志文件（03/13/2015 18:37:56）
Oracle 10g 添加、删除日志组（09/27/2014 06:39:21）

本文评论查看全部评论（0）