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

恢复重做日志

重做日志有以下几种状态（主要看前三种），如下：

CURRENT：此状态表示正在被LGWR使用的日志组；

ACTIVE：重做日志组中仍含有实例恢复所需的重做数据；

INACTIVE：表示实例恢复不再需要此日志组，可以覆盖；

UNUSED：未使用；

CLEARING：对已存在的日志组执行clear操作的后的状态；

CLEARING_CURRENT：日志正在清空。当清空出错时，该日志组被置于这种状态。

一般情况下，我们只能查询到如下三种状态：

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

 

    GROUP#    MEMBERS STATUS

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

         1          2 CURRENT

         2          2 INACTIVE

         3          2 ACTIVE

 

1.2.1丢失INACTIVE状态日志文件

一、数据库联机的情况下，删除INACTIVE日志组进行恢复

1.查看当前日志组状态

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

 

    GROUP#    MEMBERS STATUS

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

         1          2 INACTIVE

         2          2 INACTIVE

         3          2 CURRENT

 

SYS@ORA11GR2>select group#,member from v$logfile order by 1;

 

    GROUP# MEMBER

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

         1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

         1 /u01/app/oracle/oradata/ORA11GR2/redo01_a.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02_a.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02.log

         3 /u01/app/oracle/oradata/ORA11GR2/redo03_a.log

         3 /u01/app/oracle/oradata/ORA11GR2/redo03.log

 

6 rows selected.

 

2.操作系统层删除第一组日志组（第一组为inactive）

——先查看：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

 

——执行删除：

SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/redo01*

 

——验证：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

 

3. 发现丢失后，没等数据库反应过来呢，就立即恢复

SYS@ORA11GR2>alter database drop logfile group 1;

 

Database altered.

（操作系统层的第一组日志文件已经删除了，且第一组日志状态为inactive表示已经归档完成，而且没有在进行实例恢复，因为已经归档了，所以可以删除第一组日志，假如其他事物等要恢复的话可以用归档日志里关于该组的信息；没开归档则就不完全恢复）

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

 

    GROUP#    MEMBERS STATUS                    M

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

         2          2 INACTIVE                 50

         3          2 CURRENT                  50

 

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

 

Database altered.

 

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

 

    GROUP#    MEMBERS STATUS                    M

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

         1          2 UNUSED                   50

         2          2 INACTIVE                 50

         3          2 CURRENT                  50

 

SYS@ORA11GR2>

4.恢复完成

SYS@ORA11GR2>alter system switch logfile;

 

System altered.

 

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

 

    GROUP#    MEMBERS STATUS

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

         1          2 CURRENT

         2          2 INACTIVE

         3          2 ACTIVE

 

———————————————————————————————————————

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

———————————————————————————————————————

 

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

1.当前日志组状态

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

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

2.删除第3组日志

——查看：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

 

——执行删除：

SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/redo03*

 

——验证：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

3.重启数据库

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

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             452984896 bytes

Database Buffers          272629760 bytes

Redo Buffers                2842624 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 13210

Session ID: 1 Serial number: 5

 

4.由于open的错误看不出问题，首先看一下alert日志，我们会发现如下错误，可以很清楚的看出来3号日志组找不到了

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@wang trace]$

[oracle@wang trace]$

[oracle@wang trace]$ tail -100f alert_ORA11GR2.log                                                       

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

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03_a.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 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03.log'

ORA-27037: unable to obtain file status

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

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

5.对3号日志组执行clear操作，open数据库

 

SYS@ORA11GR2>conn / as sysdba

Connected to an idle instance.

SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             452984896 bytes

Database Buffers          272629760 bytes

Redo Buffers                2842624 bytes

Database mounted.

SYS@ORA11GR2>

SYS@ORA11GR2>alter database clear logfile group 3;

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

Database altered.

 

SYS@ORA11GR2>alter database open;

 

Database altered.

 

6.查看日志组状态

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

 

    GROUP#    MEMBERS STATUS

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

         1          2 CURRENT

         2          2 INACTIVE

         3          2 UNUSED

 

7.查看物理文件，已恢复完成

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

修复完成，启库alter database open;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126516/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126516/