【备份恢复】恢复丢失的current状态的日志文件

丢失CURRENT状态日志文件

1. 查看当前日志组状态，数据库为归档模式

——查验否为归档：

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10

SYS@ORA11GR2>

——查看当前日志组状态：1号日志组当前应用，未归档

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 NO  CURRENT

         2          2 YES INACTIVE

         3          2 YES UNUSED

2.做DML操作，生成日志

SYS@ORA11GR2>create table scott.t tablespace users as select * from dba_objects where 1=2;

 

Table created.

 

SYS@ORA11GR2>insert into scott.t select * from dba_objects;

 

87282 rows created.

 

SYS@ORA11GR2>commit;

 

Commit complete.

 

SYS@ORA11GR2>select count(*) from scott.t;

 

  COUNT(*)

----------

     87282

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

   1号日志组当前应用，未归档

    GROUP#    MEMBERS ARC STATUS

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

         1          2 NO  CURRENT

         2          2 YES INACTIVE

         3          2 YES UNUSED

 

3.删除1号日志组（即current状态的日志文件）

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

 

    GROUP#    MEMBERS STATUS

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

         1          2 CURRENT

         2          2 INACTIVE

         3          2 UNUSED

 

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>

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

 

SYS@ORA11GR2>

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

 

4.查看日志组状态，手工切换一下日志，1号日志组为ACTIVE且未归档（那是一定的），此时数据库是可以正常对外提供服务的，但是，当再次切换到1号日志组的时候，将由于归档问题而hang住

——查看日志组，第一组状态为无归档，目前正在使用

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 NO  CURRENT

         2          2 YES INACTIVE

         3          2 YES UNUSED

——手工切换下日志：

SYS@ORA11GR2>alter system switch logfile;

 

System altered.

 

——再次查看日志组状态，发现第一组为活跃，但没有归档（正常，因为第一组日志物理层已经删除了）

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 NO  ACTIVE

         2          2 YES INACTIVE

         3          2 NO  CURRENT

 

SYS@ORA11GR2>

——再次切换当前使用的日志组到1号日志组：

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 NO  INACTIVE

         2          2 NO  CURRENT

         3          2 NO  ACTIVE

 

SYS@ORA11GR2>alter system switch logfile;

当再次切换到3号日志组的时候，将由于归档问题而hang住！！！！！！！

 

（注：为什么切换前current日志组是1号，切换后怎么变成3号了？正常不应该是        1->2->3这样循环吗？原因是，做实验的测试环境的日志组做过删除和添加的动作，如果，3号日志 组为current的时候，你增加新日志组，那么顺序就是1->2->3->4这样循环，如果2号日志组为current状态时，增加一个4号日志组，那么日志组的顺序就非按顺序循环了，有兴趣的读者请自行测试一下。）

 

5.尝试在命令行drop掉1号日志组，显然是不行的，提示的很清楚，1号日志组需要归档先（另开一个窗口操作）

SYS@ORA11GR2>alter database drop logfile group 1;

alter database drop logfile group 1

*

ERROR at line 1:

ORA-00350: log 1 of instance ORA11GR2 (thread 1) needs to be archived

ORA-00312: online log 1 thread 1:

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo01.log'

 

 

6.解决办法一：改为非归档

删除日志组的错误是由于归档所间接造成的，我们可以将数据库改为非归档模式，然后重建重做日志，如下：

SYS@ORA11GR2>shutdown immediate;（一致性关库）

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

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 noarchivelog;

 

Database altered.

 

——删除第一组日志;

SYS@ORA11GR2>alter database drop logfile group 1;

 

Database altered.

 

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

 

    GROUP#    MEMBERS ARC STATUS

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

         3          2 NO  INACTIVE

         2          2 NO  CURRENT

 

——再次增加第一组日志：

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

 

Database altered.

——查看状态及验证：

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 YES UNUSED

         3          2 NO  INACTIVE

         2          2 NO  CURRENT

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>alter database archivelog;

 

Database altered.

 

SYS@ORA11GR2>alter database open;

 

Database altered.

——查看：

SYS@ORA11GR2>select count(*) from scott.t;

 

  COUNT(*)

----------

     87282
（未有归档动作，但切换日志组实际上隐式触发ckpt，相关最新数据实际已经落盘，所以可以查到最新信息）

 

解决办法二：采用recover的方式完成重做日志恢复

注：如果所有日志组全部丢失，用此方法也可恢复，请自行在测试环境测试！

前提已开归档：检查：

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     18

Next log sequence to archive   20

Current log sequence           20

准备环境：

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 YES INACTIVE

         2          2 YES INACTIVE

         3          2 NO  CURRENT

 

——进行操作：

SYS@ORA11GR2>create table sfe(x int);

 

Table created.

 

SYS@ORA11GR2>insert into sfe values(1);

 

1 row created.

 

SYS@ORA11GR2>commit;

 

Commit complete.

 

SYS@ORA11GR2>select * from sfe;

 

         X

----------

         1

——查看：

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

——查看当前日志组，即第3组

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 YES INACTIVE

         2          2 YES INACTIVE

         3          2 NO  CURRENT

——删除对应的第三组日志的物理文件：

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

——查看日志组状态：

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 YES INACTIVE

         2          2 YES INACTIVE

         3          2 NO  CURRENT

——切换日志组进行归档，当再次切换到第三组日志是hang住了，因为无物理文件去归档

SYS@ORA11GR2>alter system switch logfile;

Hang住了

——解决：（另开一个窗口执行）

SYS@ORA11GR2>shutdown immediate;（一致性关库，保持三大文件一致状态）

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

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>recover database until cancel;

（恢复数据库至能恢复的点为止，为不完全恢复）

Media recovery complete.

SYS@ORA11GR2>

SYS@ORA11GR2>alter database open resetlogs;

（重置日志组信息，重头记录，以前的没用了）

Database altered.

 

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 NO  CURRENT

         2          2 YES UNUSED

         3          2 YES UNUSED

 

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>select * from sfe;

 

         X

----------

         1
（未有归档动作，但切换日志组实际上隐式触发ckpt，相关最新数据实际已经落盘，所以可以查到最新信息）

 

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

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