检查oracle inactive清除,oracle丢失inactive日志文件的恢复操作过程

丢失非活动日志组的故障恢复如果数据库丢失的是非活动(inactive)日志组,由于非活动日志组已经完成检查点,数据库不会发生数据损失,此时只需要通过clear重建该日志组即可恢复.

先删除一个非活动日志组,模拟一次故障损失:

SQL> !rm /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

此时启动数据库,数据库会提示日志丢失

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              67111156 bytes

Database Buffers           96468992 bytes

Redo Buffers                2973696 bytes

Database mounted.

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

ORA-00312: online log 3 thread 1:

'/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log'

此时在mount状态,可以查看各日志组及日志文件的状态:

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

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

FIRST_CHANGE# FIRST_TIME

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

1          1         26   52428800          1 NO  INACTIVE

913689 06-JAN-13

3          1         28   52428800          1 NO  CURRENT

913701 06-JAN-13

2          1         27   52428800          1 NO  INACTIVE

913696 06-JAN-13

SQL> select * from v$logfile;

GROUP# STATUS  TYPE

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

MEMBER

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

IS_

---

3         ONLINE

/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log

NO

2         ONLINE

/u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log

NO

GROUP# STATUS  TYPE

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

MEMBER

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

IS_

---

1         ONLINE

/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log

NO

清除该日志组后即可启动数据库:

SQL>alter database clear logfile group 3;

Database altered

SQL>alter database open;

Database altered

注意,如果数据库处于归档模式下,并且该日志组未完成归档则需要使用如下命令强制清除

alter database clear unarchived logfile group 3;

打开数据库之后,状态为stale的日志文件,在下次正常写入后,状态即可恢复正常:

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

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

FIRST_CHANGE# FIRST_TIME

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

1          1          2   52428800          1 NO  INACTIVE

914153 06-JAN-13

2          1          3   52428800          1 NO  INACTIVE

914157 06-JAN-13

3          1          4   52428800          1 NO  CURRENT

914164 06-JAN-13