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

最新推荐文章于 2023-03-25 15:12:50 发布

hailang08

最新推荐文章于 2023-03-25 15:12:50 发布

阅读量1.6k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

转载：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

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文件不是当前日志情况下，上面的解决方法亲测是成功的！谢谢原创作者！