两个有趣的REDO LOG ERROR 处理方法

两个有趣的REDO LOG ERROR处理方法

系统环境：

操作系统： AIX-5300

数据库： Oracle 10g(10.2.0.1.0)

案例描述：

数据库(归档模式）非当前日志组被破坏，但由于破坏方式不同，在解决问题的方式稍有不同，很有意思.

案例1：非当前日志组文件被删除

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------------------------------

/dsk1/oradata/prod/redo03a.log

/dsk1/oradata/prod/redo02a.log

/dsk1/oradata/prod/redo01a.log

/dsk2/oradata/prod/redo01b.log

/dsk2/oradata/prod/redo02b.log

/dsk2/oradata/prod/redo03b.log

6 rows selected.

删除非当前日志组：

[oracle@aix211 ~]$cd /dsk1/oradata/prod/

[oracle@aix211 prod]$ls

control02.ctl redo01a.log redo02a.log redo03a.log

[oracle@aix211 prod]$rm redo01a.log

[oracle@aix211 prod]$cd /dsk2/oradata/prod/

[oracle@aix211 prod]$ls

redo01b.log redo02b.log redo03b.log

[oracle@aix211 prod]$rm redo01b.log

关闭数据库，并重新启动：

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2020552 bytes

Variable Size 318769976 bytes

Database Buffers 922746880 bytes

Redo Buffers 14753792 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/dsk1/oradata/prod/redo01a.log'

ORA-00312: online log 1 thread 1: '/dsk2/oradata/prod/redo01b.log'

告警日志：（提示：日志组文件找不到，无法读取）

Errors in file /u01/app/oracle/admin/prod/udump/prod_ora_536600.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/dsk2/oradata/prod/redo01b.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/dsk1/oradata/prod/redo01a.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

Completed: alter database clear logfile group 1

Mon May 26 10:02:36 2014

alter database open

Mon May 26 10:02:36 2014

Block change tracking file is current.

解决方法：

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 452 52428800 2 YES INACTIVE 806225 26-MAY-14

3 1 451 52428800 2 YES INACTIVE 803970 26-MAY-14

2 1 453 52428800 2 NO CURRENT 806237 26-MAY-14

由于是非当前日志组，并且已经完成归档：

SQL> alter database clear logfile group 1;

Database altered.

打开数据库成功：

SQL> alter database open;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 454 52428800 2 NO CURRENT 826578 26-MAY-14

2 1 453 52428800 2 YES INACTIVE 806237 26-MAY-14

3 1 451 52428800 2 YES INACTIVE 803970 26-MAY-14

SQL> alter system switch logfile;

System altered.

案例2：非当前日志组文件头部被破坏

[oracle@aix211 prod]$dd if=/dev/zero of=/dsk1/oradata/prod/redo03a.log bs=8192 count=3

3+0 records in

3+0 records out

[oracle@aix211 prod]$dd if=/dev/zero of=/dsk2/oradata/prod/redo03b.log bs=8192 count=3

3+0 records in

3+0 records out

关库并启动Instance：

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2020552 bytes

Variable Size 318769976 bytes

Database Buffers 922746880 bytes

Redo Buffers 14753792 bytes

Database mounted.

ORA-00316: log 3 of thread 1, type in header is not log file

ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'

ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'

告警日志：（提示：日志组文件头部不可读取）

Errors in file /u01/app/oracle/admin/prod/bdump/prod_lgwr_499718.trc:

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'

Mon May 26 10:16:37 2014

解决方法：

Clear非当前日志组：

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 457 52428800 2 YES INACTIVE 827041 26-MAY-14

3 1 458 52428800 2 YES INACTIVE 827053 26-MAY-14

2 1 459 52428800 2 NO CURRENT 827067 26-MAY-14

SQL> alter database clear logfile group 3;

Database altered.

直接open database失败：

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00327: log 3 of thread 1, physical size less than needed

ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'

ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'

告警日志：（提示：redo 日志组文件头部仍然有错误）

Errors in file /u01/app/oracle/admin/prod/bdump/prod_lgwr_499718.trc:

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'

Mon May 26 10:16:37 2014

ARC0: STARTING ARCH PROCESSES

Mon May 26 10:16:37 2014

ORA-316 signalled during: ALTER DATABASE OPEN...

重新启动Instance，重新加载controlfile，问题解决：

SQL> startup force;

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2020552 bytes

Variable Size 318769976 bytes

Database Buffers 922746880 bytes

Redo Buffers 14753792 bytes

Database mounted.

Database opened.

SQL>

以上两个案例都是针对redo log文件被破坏的情况下，解决问题的方法，破坏方式稍有不同，解决方法也有差异，应在实践中注意总结和归纳。