ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 对于oracle中丢失非当前日志组的恢复

对于oracle中丢失非当前日志组的恢复

原创 Linux操作系统 作者:DBA_oracle_java 时间:2013-09-10 14:16:48 0 删除 编辑
 注:以下操作都是局限于日志文件组没有冗余的情况下做的。
 当前数据库版本11.2.0.3
1.查看当前日志组的状态

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

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


需要注意“STATUS”和“ARC”两个字段

2.模拟丢失ARC='YES',STATUS='ACTIVE'状态的非当前日志丢失的恢复
SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#;

    GROUP# ARC STATUS           MEMBER
---------- --- ---------------- ------------------------------
         3 NO  CURRENT          /ORADATA/szscdb/redo03.log
         2 YES ACTIVE           /ORADATA/szscdb/redo02.log
         1 YES INACTIVE         /ORADATA/szscdb/redo01.log
         
SQL> !mv /ORADATA/szscdb/redo02.log /tmp

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 szscdb (thread 1)
ORA-00312: online log 2 thread 1: '/ORADATA/szscdb/redo02.log'

SQL> alter system checkpoint;

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

    GROUP# ARC STATUS           MEMBER
---------- --- ---------------- ------------------------------
         3 NO  CURRENT          /ORADATA/szscdb/redo03.log
         2 YES INACTIVE           /ORADATA/szscdb/redo02.log
         1 YES INACTIVE         /ORADATA/szscdb/redo01.log
         
SQL> alter database clear logfile group 2;

Database altered.


3.模拟ARC='NO'状态的非当前日志丢失的恢复
SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#;

    GROUP# ARC STATUS           MEMBER
---------- --- ---------------- ------------------------------
         3 NO  INACTIVE         /ORADATA/szscdb/redo03.log
         2 NO  CURRENT          /ORADATA/szscdb/redo02.log
         1 YES INACTIVE         /ORADATA/szscdb/redo01.log
         
SQL>  alter database clear logfile group 3;
 alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance szscdb (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/ORADATA/szscdb/redo03.log'

SQL> alter database clear unarchived logfile group 3;

Database altered.
         
注:如果reodo logfile group的成员有镜像的话,要好做很多,操作如下:
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 YES INACTIVE         /ORADATA/szscdb/redo01.log
         1 YES INACTIVE         /ORADATA/szscdb/redo01a.log
         2 NO  CURRENT          /ORADATA/szscdb/redo02a.log
         2 NO  CURRENT          /ORADATA/szscdb/redo02.log
         3 YES INACTIVE         /ORADATA/szscdb/redo03a.log
         3 YES INACTIVE         /ORADATA/szscdb/redo03.log
         
         
SQL> !mv /ORADATA/szscdb/redo03.log /tmp
SQL> alter system switch logfile;


Errors in file /u02/app/oracle/diag/rdbms/szscpdb/szscdb/trace/szscdb_arc3_31480.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/ORADATA/szscdb/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u02/app/oracle/diag/rdbms/szscpdb/szscdb/trace/szscdb_arc3_31480.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/ORADATA/szscdb/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Archived Log entry 142 added for thread 1 sequence 39 ID 0x91246a73 dest 1:
Tue Sep 10 14:30:11 2013
alter database drop logfile '/ORADATA/szscdb/redo03.log'
ORA-1514 signalled during: alter database drop logfile '/ORADATA/szscdb/redo03.log'...


SQL> alter database drop logfile member '/ORADATA/szscdb/redo03.log';

Database altered.


SQL> alter database add logfile member '/ORADATA/szscdb/redo03.log' to group 3;

Database altered.

SQL> alter system switch logfile;

         
         
QQ交流群:300392987

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

请登录后发表评论 登录
全部评论

注册时间:2013-07-02

  • 博文量
    31
  • 访问量
    116910