Oracle Redo log丢失几种情况的修复

caixuqad

DATE:2014年6月17日
AUTHOR:chinaguanghui@gmail.com


NOTE:群里兄弟在讨论关于redo log丢失的几种情况，我这里实验一下。本次试验基于无备份且无归档的情况下模拟丢失重做日志的修复步骤。丢失重做日志的可能性有如下几种:

/*
首先是数据库是正常关闭还是非正常关闭。
其次要分析丢失的日志组是否为当前日志组
  对于非当前日志组的处理要容易些。那么就有四中可能。


当前日志，正常关闭。      
当前日志，非正常关闭。
非当前日志，正常关闭。   
非当前日志，非正常关闭。

这里我们先不讨论一组日志有多个成员的情况
*/




/*1.模拟丢失当前日志组,非正常关闭的修复*/
SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
freebase         OPEN

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/products/11.2.0.3/dbs/arch
Oldest online log sequence     3
Current log sequence           5

SQL> col member for a35
SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#
  4  ORDER BY l.GROUP#;

    GROUP# ARC STATUS           MEMBER
---------- --- ---------------- -----------------------------------
         1 NO  CURRENT          /ORADATA/freebase/redo01.log
         2 NO  INACTIVE         /ORADATA/freebase/redo02.log
         3 NO  INACTIVE         /ORADATA/freebase/redo03.log

SQL> !mv /ORADATA/freebase/redo02.log /tmp



SQL> alter system switch logfile;

System altered.

SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#
  4  ORDER BY l.GROUP#;


    GROUP# ARC STATUS           MEMBER
---------- --- ---------------- -----------------------------------
         1 NO  INACTIVE         /ORADATA/freebase/redo01.log
         2 NO  CURRENT          /ORADATA/freebase/redo02.log
         3 NO  INACTIVE         /ORADATA/freebase/redo03.log

                 
SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
freebase         OPEN

SQL> !ls -l /ORADATA/freebase/redo02.log
ls: /ORADATA/freebase/redo02.log: No such file or directory

/*注意，这里实例并没有down掉，因为linux删除文件之后进程会继续持有语句柄*/
[root@vrhdg1 ~]# lsof | grep redo
oracle      405    oracle  258u      REG                8,1   52429312     655361 /ORADATA/freebase/redo01.log
oracle      405    oracle  259u      REG                8,1   52429312     655364 /ORADATA/freebase/redo02.log (deleted)
oracle      405    oracle  260u      REG                8,1   52429312     655362 /ORADATA/freebase/redo03.log

[root@vrhdg1 ~]# ps -ef | grep  -v grep | grep 405
oracle     405     1  0 14:07 ?        00:00:00 ora_lgwr_freebase

[root@vrhdg1 ~]# cd /proc/405/fd/
[root@vrhdg1 fd]# ls -l 259
lrwx------ 1 oracle oinstall 64 Jun 17 14:12 259 -> /ORADATA/freebase/redo02.log (deleted)

[root@vrhdg1 fd]# kill -9 405


SQL> startup mount;
SQL> col member for a35   
SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#
  4  ORDER BY l.GROUP#;

    GROUP# ARC STATUS           MEMBER
---------- --- ---------------- -----------------------------------
         1 NO  INACTIVE         /ORADATA/freebase/redo01.log
         2 NO  CURRENT          /ORADATA/freebase/redo02.log
         3 NO  INACTIVE         /ORADATA/freebase/redo03.log

SQL> !ls -l /ORADATA/freebase/redo02.log
ls: /ORADATA/freebase/redo02.log: No such file or directory

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/ORADATA/freebase/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance freebase (thread 1)
ORA-00312: online log 2 thread 1: '/ORADATA/freebase/redo02.log


SQL> recover database until cancel;
ORA-00279: change 1158338 generated at 06/17/2014 14:38:15 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/products/11.2.0.3/dbs/arch1_5_850487771.dbf
ORA-00280: change 1158338 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ORADATA/freebase/system01.dbf'

Adam丶鱼

fteair

caixuqad