redo文件被删除或者redo文件所在的磁盘故障的处理方法

转载:http://blog.itpub.net/24500180/viewspace-777261/

1 非当前redo 文件损坏
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1          4   52428800        512          1 YES INACTIVE               1198503 20-NOV-13      1219428 21-NOV-13
         2          1          5   52428800        512          1 NO  CURRENT                1219428 21-NOV-13   2.8147E+14
         3          1          3   52428800        512          1 YES INACTIVE               1197847 20-NOV-13      1198503 20-NOV-13
SQL> select member from v$logfile;

MEMBER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/redo03.log
/u01/app/oracle/oradata/dong/redo02.log
/u01/app/oracle/oradata/dong/redo01.log
删除redo03文件。
SQL> !rm -rf /u01/app/oracle/oradata/dong/redo03.log

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2092498944 bytes
Fixed Size                  1337604 bytes
Variable Size             268437244 bytes
Database Buffers         1811939328 bytes
Redo Buffers               10784768 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2941
Session ID: 125 Serial number: 5
[root@baobao trace]# tail -20 alert_dong.log 
ARC1 started with pid=21, OS id=2954 
Errors in file /u01/app/oracle/diag/rdbms/dong/dong/trace/dong_lgwr_2894.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/dong/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dong/dong/trace/dong_lgwr_2894.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/dong/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dong/dong/trace/dong_ora_2941.trc:
ORA-00313: open failed for members of log group 1 of thread 
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/dong/redo03.log'
USER (ospid: 2941): terminating the instance due to error 313
Thu Nov 21 15:34:16 2013
ARC2 started with pid=22, OS id=2957 
Instance terminated by USER, pid = 2941
SQL> select * from v$log;----可以再次确定redo03文件不是当前日志,且已经归档

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1          4   52428800        512          1 YES INACTIVE               1198503 20-NOV-13      1219428 21-NOV-13
         3          1          3   52428800        512          1 YES INACTIVE               1197847 20-NOV-13      1198503 20-NOV-13
         2          1          5   52428800        512          1 NO  CURRENT                1219428 21-NOV-13   2.8147E+14
解决办法为clear logfile
SQL>alter database clear logfile group 3;
SQL>alter database open;

但如果redo03还没归档的话,那就需要加unarchived了,
SQL>alter database clear unarchived logfile group 3;

Database altered.


2 当前redo 文件损坏
如果db是正常关闭的,那就可以确定提交的事务都安全了,那就用unarchived clear的方式将redo给clear即可。但如果db正在运行或者数据库崩了,那就基本肯定会丢数据了。
构造当前redo被删除的场景:
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1          4   52428800        512          1 YES INACTIVE               1198503 20-NOV-13      1219428 21-NOV-13
         2          1          5   52428800        512          1 NO  CURRENT                1219428 21-NOV-13   2.8147E+14
         3          1          0   52428800        512          1 YES UNUSED                 1197847 20-NOV-13      1198503 20-NOV-13

SQL> !rm -rf /u01/app/oracle/oradata/dong/redo02.log

SQL> create table ttt as select * from dba_objects;

Table created.

SQL> insert into ttt select * from ttt;

71893 rows created.

SQL> /

143786 rows created.

SQL> /

287572 rows created.

SQL> /

开另一个终端试试查询,还是可以查的:
SQL> select count(*) from ttt where rownum=1;

  COUNT(*)
----------
         1
SQL> select count(*) from t1;

  COUNT(*)
----------
     71892
看下警告日志:
Thu Nov 21 16:01:28 2013
Thread 1 advanced to log sequence 6 (LGWR switch)
  Current log# 3 seq# 6 mem# 0: /u01/app/oracle/oradata/dong/redo03.log
Thu Nov 21 16:01:28 2013
Errors in file /u01/app/oracle/diag/rdbms/dong/dong/trace/dong_arc0_3170.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/dong/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dong/dong/trace/dong_arc0_3170.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/dong/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ARCH: Archival stopped, error occurred. Will continue retrying
故障解决:
对于当前redo的故障,基本就是丢失这个redo的数据了,闲杂主要就是用备份将数据库恢复到最近的状态,肯定是不完全恢复了。要不就是强制将db拉起来。
用备份恢复,这里就不写了,基本就是restore,recover until cancel的。现在做一下强制拉起来的情况:
SQL> alter database clear unarchived logfile group 1;--尝试clear,失败
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance dong (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/dong/redo01.log'


SQL> shutdown abort
ORACLE instance shut down.
通过修改隐含参数来启动:
将*._allow_resetlogs_corruption=TRUE加入到参数文件。
[oracle@baobao dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 21 16:47:57 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount pfile='$ORACLE_HOME/dbs/initdong.ora';
ORACLE instance started.

Total System Global Area 2092498944 bytes
Fixed Size                  1337604 bytes
Variable Size             268437244 bytes
Database Buffers         1811939328 bytes
Redo Buffers               10784768 bytes
Database mounted.
SQL> alter database open resetlogs;--这里一定要用resetlogs呀,因为你要是不用的话,就是代表要用redo进行实例恢复了,那肯定会和刚才一样报错了。
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;--这里我们弄一个假的恢复骗过oracle,因为resetlogs要求一定是恢复完才可以使用。
Media recovery complete.
SQL> alter database open resetlogs;--打开db,完毕

Database altered.

打开后,一定要full db backup。此时db是不一致的状态,所以需要将数据导出再导入。

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

redo文件不是当前日志情况下,上面的解决方法亲测是成功的!谢谢原创作者!


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值