ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle中无备份丢失当前日志组的恢复

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

原创 Linux操作系统 作者:DBA_oracle_java 时间:2013-09-11 09:48:55 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2013-07-02

  • 博文量
    31
  • 访问量
    116883