Oracle当前日志组出错,Oracle当前联机日志组损坏的处理

二数据库一直open当前联机日志组内成员全部损坏

此种情况最终就是日志不能切换，影响到数据库的应用时我们才可能发现。如下所示日志状态：

1 数据库日志状态如下：

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

GROUP# ARC SEQUENCE# STATUS

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

1 NO 44 INACTIVE

2 NO 40 INACTIVE

3 NO 41 INACTIVE

4 NO 42 INACTIVE

5 NO 45 CURRENT

6 NO 43 INACTIVE

6 rows selected.

2 使用alter database clear unarchived logfile group n 命令处理sequence#号为最小的一组日志，等待片刻，让所有未归档的日志归档完成，(当告警日志中出现如下字样Archiver process freed from errors. No longer stopped)之后查询日志状态如下：

SQL> alter database clear unarchived logfile group 2;

Database altered.

select group#,archived,sequence#,status from v$log

GROUP# ARC SEQUENCE# STATUS

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

1 YES 44 INACTIVE

2 YES 0 UNUSED

3 YES 41 INACTIVE

4 YES 42 INACTIVE

5 NO 45 CURRENT

6 YES 43 INACTIVE

注意虽然现在的日志归档状态已经正常，但初始化后的日志组2内的日志文件可能并未生效。我们再数次切换下日志，告警文件便会产生如下报错：

Errors in file/Oracle/app/admin/CRM/bdump/crm_arc1_3977.trc:

ORA-00314: log 2 of thread 1, expected sequence# 46 doesn't match 0

ORA-00312: online log 2 thread 1: '/oracle/CRM2/CRM/redo02b.log'

ORA-00314: log 2 of thread 1, expected sequence# 46 doesn't match 0

ORA-00312: online log 2 thread 1: '/oracle/app/db1/dbs/log2CRM.dbf'

而此刻的日志归档状态如下：

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

GROUP# ARC SEQUENCE# STATUS

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

1 NO 50 INACTIVE

2 NO 46 INACTIVE

3 NO 47 INACTIVE

4 NO 48 INACTIVE

5 NO 51 CURRENT

6 NO 49 INACTIVE

6 rows selected.

总结：以上测试为10g环境，若使用alter database clear unarchived logfile group n初始化了之前的当前日志后，为了避免上面的问题。我们可以通过重启数据库，或者重建该日志组。11g测试没发现此类问题。

三数据库启动时报当前联机日志文件丢失

1

SQL> startup

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size2020480 bytes

Variable Size92277632 bytes

Database Buffers222298112 bytes

Redo Buffers6365184 bytes

Database mounted.

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

ORA-00312: online log 2 thread 1: '/oracle/app/db1/dbs/log2CRM.dbf'

ORA-00312: online log 2 thread 1: '/oracle/CRM2/CRM/redo02b.log'

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

GROUP# ARC SEQUENCE# STATUS

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

1 YES 50 INACTIVE

2 NO 52 CURRENT

6 YES 49 INACTIVE

4 YES 48 INACTIVE

5 YES 51 INACTIVE

3 YES 47 INACTIVE

6 rows selected.

注意，由于是当前日志组，所以不能用alter database clear unarchived logfile group 2对该日志组进行初始化。不过我们可以执行基于取消的恢复如下所示：

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

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

ORA-00312: online log 2 thread 1: '/oracle/CRM2/CRM/redo02b.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: '/oracle/app/db1/dbs/log2CRM.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

2 执行基于取消的恢复

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

总结：

1 对于当前日志组所有成员损坏的情况，按上面方式处理即可。不过仍需注意的是，初始化日志组后，会丢失该日志组对应的归档，造成归档不连续，应该做一次全库备份。

2 对于当前日志组成员的损坏，切换当前日志后，drop掉损坏的日志成员然后再添加新的成员。

3 对于非当前的日志组成员损坏，直接drop掉损坏的日志成员，然后添加新的日志成员。

4 对于非当前日志组损坏的情况，可以直接用alter database clear unarchived logfile group n 或者alter database clear logfile group n 进行初始化处理。