重做日志有以下几种状态（主要看前三种），如下：
CURRENT：此状态表示正在被LGWR使用的日志组；
ACTIVE：重做日志组中仍含有实例恢复所需的重做数据；
INACTIVE：表示实例恢复不再需要此日志组，可以覆盖；
UNUSED：未使用；
CLEARING：对已存在的日志组执行clear操作的后的状态；
CLEARING_CURRENT：日志正在清空。当清空出错时，该日志组被置于这种状态。

一般情况下，我们只能查询到如下三种状态：
SYS@PROD1>select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 INACTIVE
3 2 ACTIVE
一.数据库联机的情况下，删除INACTIVE日志组进行恢复
1.查看当前日志组状态
SYS@PROD1>select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 UNUSED

SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile order by 1;

GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/redo01.log
1 /u01/app/oracle/oradata/PROD1/redo01_a.log
2 /u01/app/oracle/oradata/PROD1/redo02_a.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
3 /u01/app/oracle/oradata/PROD1/redo03_a.log
3 /u01/app/oracle/oradata/PROD1/redo03.log

6 rows selected.

2.操作系统层删除第一组日志组（第一组为inactive）
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01.log

SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01_a.log

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02.log /u01/app/oracle/oradata/PROD1/redo03.log

3. 发现丢失后，没等数据库反应过来呢，就立即恢复
SYS@PROD1>alter database drop logfile group 1;

Database altered.

SYS@PROD1>select group#,members,status,bytes/1024/1024 m from v$log;

GROUP# MEMBERS STATUS M
---------- ---------- ---------------- ----------
2 2 CURRENT 50
3 2 UNUSED 50

SYS@PROD1>alter database add logfile group 1 ('/u01/app/oracle/oradata/PROD1/redo01.log','/u01/app/oracle/oradata/PROD1/redo01_a.log') size 50m;

Database altered.

SYS@PROD1>select group#,members,status,bytes/1024/1024 m from v$log;

GROUP# MEMBERS STATUS M
---------- ---------- ---------------- ----------
1 2 UNUSED 50
2 2 CURRENT 50
3 2 UNUSED 50

4.恢复完成
SYS@PROD1>select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 ACTIVE
3 2 UNUSED

SYS@PROD1>alter system switch logfile;

SYS@PROD1>select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 ACTIVE
2 2 ACTIVE
3 2 CURRENT

自测：数据库处于归档模式，数据库联机的情况下，丢失了一个INACTIVE状态的日志文件，此时数据库还在使用，
读者可以模拟建表、插入数据，切换日志，当然也可以切换到丢失的日志组，多切换几次，此时数据库会hang住，原因是日志无法归档，

二.删除INACTIVE日志组，重启数据库后恢复

1.查看当前日志组状态
SYS@PROD1>alter system checkpoint;

System altered.

SYS@PROD1>select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 INACTIVE

2.删除第3组日志
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo03*

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo03*
ls: /u01/app/oracle/oradata/PROD1/redo03*: No such file or directory

3.重启数据库
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

一致性关闭没问题，启动的时候，只能启动到mount状态（其实只是看着应该是mount状态，其实数据库目前的状态shutdown状态），
open时报错，并没有明确的指明是因为丢失了日志文件导致的数据库open不了（oracle10g中会明确的指明）

SYS@PROD1>startup;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 25417
Session ID: 1 Serial number: 5

4.查看告警日志
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD1/redo03.log'
ORA-27037: unable to obtain file status

5.对3号日志组执行clear操作，open数据库
[oracle@host01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 17 10:36:19 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SYS@PROD1>startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.

SYS@PROD1>alter database clear logfile group 3;

Database altered.

(clear的动作相当于将在操作系统层重建了第三组日记，且里面没有记录，也就是没有使用过unused)

SYS@PROD1>alter database open;

Database altered.

6.查看日志组状态
SYS@PROD1>select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 UNUSED

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo01_a.log /u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03.log

ITPub博客

【备份恢复】恢复INACTIVE状态的日志文件

最新文章