oracle起库日志组不存在,对于oracle中丢失非当前日志组的恢复

注：以下操作都是局限于日志文件组没有冗余的情况下做的。

当前数据库版本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