oracle中无备份丢失当前日志组的恢复-CSDN博客

oracle中无备份丢失当前日志组的恢复

1.当前日志组有镜像

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/szscdb/redo01.log

1 NO CURRENT /ORADATA/szscdb/redo01a.log

2 YES INACTIVE /ORADATA/szscdb/redo02a.log

2 YES INACTIVE /ORADATA/szscdb/redo02.log

3 YES INACTIVE /ORADATA/szscdb/redo03a.log

3 YES INACTIVE /ORADATA/szscdb/redo03.log

6 rows selected.

SQL> !mv /ORADATA/szscdb/redo01.log /tmp

Tue Sep 10 15:55:32 2013

Errors in file /u02/app/oracle/diag/rdbms/szscpdb/szscdb/trace/szscdb_m000_32697.trc:

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

ORA-00312: online log 1 thread 1: '/ORADATA/szscdb/redo01.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_m000_32697.trc:

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 ACTIVE /ORADATA/szscdb/redo01.log

1 YES ACTIVE /ORADATA/szscdb/redo01a.log

2 YES ACTIVE /ORADATA/szscdb/redo02a.log

2 YES ACTIVE /ORADATA/szscdb/redo02.log

3 NO CURRENT /ORADATA/szscdb/redo03a.log

3 NO CURRENT /ORADATA/szscdb/redo03.log

6 rows selected.

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

Database altered.

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

Database altered.

2.当前日志组的所有成员丢失

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 ACTIVE /ORADATA/szscdb/redo01.log

1 YES ACTIVE /ORADATA/szscdb/redo01a.log

2 YES ACTIVE /ORADATA/szscdb/redo02a.log

2 YES ACTIVE /ORADATA/szscdb/redo02.log

3 NO CURRENT /ORADATA/szscdb/redo03a.log

3 NO CURRENT /ORADATA/szscdb/redo03.log

6 rows selected.

SQL> !mv /ORADATA/szscdb/redo03* /tmp

现在出现switch logfile卡死

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/szscdb/redo01.log

1 NO INACTIVE /ORADATA/szscdb/redo01a.log

2 NO CURRENT /ORADATA/szscdb/redo02a.log

2 NO CURRENT /ORADATA/szscdb/redo02.log

3 NO INACTIVE /ORADATA/szscdb/redo03a.log

3 NO INACTIVE /ORADATA/szscdb/redo03.log

6 rows selected.

#确认丢失的是当前redo log file

#尝试清空日志组失败

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'

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

4 ORDER BY l.GROUP#;

GROUP# ARC STATUS MEMBER

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

1 NO INACTIVE /ORADATA/szscdb/redo01.log

1 NO INACTIVE /ORADATA/szscdb/redo01a.log

2 NO CURRENT /ORADATA/szscdb/redo02a.log

2 NO CURRENT /ORADATA/szscdb/redo02.log

3 NO INACTIVE /ORADATA/szscdb/redo03a.log

3 NO INACTIVE /ORADATA/szscdb/redo03.log

6 rows selected.

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'

ORA-00312: online log 2 thread 1: '/ORADATA/szscdb/redo02a.log'

###启动隐含参数_allow_resetlogs_corruption，该参数指定数据库在启动的时候跳过一致性检查，否则会出现如下错误

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/ORADATA/szscdb/system01.dbf

###该参数修改只有在数据库重启时生效

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

SQL> show parameter allow

NAME TYPE VALUE

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

_allow_resetlogs_corruption boolean TRUE

SQL> recover database until cancel;

##以resetlogs的方式打开，由于这两启动了隐含参数，说没有做一致性检查

SQL> alter database open resetlogs;

##去掉该隐含参数

SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile;

SQL> shutdown immediate;

SQL> startup

注意：

1.在完成这一系列的操作之后建议对数据库做一个full export，然后重新创建数据库。因为在我的实验环境里面已经开始报各种莫名其妙的错误了。

2.建议给reodo log file组的日志加上member是非常有必要的，而且要房子不同的磁盘上，实现真正的冗余。

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

转载于:http://blog.itpub.net/26169542/viewspace-772537/