redo文件破坏恢复

1.正常关机情况下redo文件被破坏<11.2.0.3>

SQL> shutdown immediate;

[oracle@localhost wangbeng]$ rm redo02*

SQL> startup

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 335547696 bytes

Database Buffers 75497472 bytes

Redo Buffers 4272128 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 12065

Session ID: 1 Serial number: 5

查看alert文件发现如下内容

ARC3 started with pid=23, OS id=12074

Errors in file /u01/app/oracle/diag/rdbms/wangbeng/wangbeng/trace/wangbeng_lgwr_12029.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/wangbeng/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/wangbeng/wangbeng/trace/wangbeng_lgwr_12029.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/wangbeng/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/wangbeng/wangbeng/trace/wangbeng_ora_14718.trc:

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/wangbeng/redo01.log'

SQL> startup mount;SQL> select group#,sequence#,archived,status from v$log;    GROUP# SEQUENCE# ARCHIV STATUS---------- ---------- ------ --------------------------------         1 19 NO CURRENT         3 18 YES INACTIVE         2 17 YES INACTIVE

SQL> alter database clear logfile group 2; --会重新生成日志文件

SQL> alter database clear logfile group 3;

如果未归档使用

SQL> alter database clear unarchived logfile group 1

SQL> alter database open;

由于在关机的时候所有文件都已经写盘。所以不会发生数据的丢失。

2.非正常关机情况下已归档active redo文件被破坏<11.2.0.3>

SQL> shutdown abort;

SQL> startup

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 335547696 bytes

Database Buffers 75497472 bytes

Redo Buffers 4272128 bytes

Database mounted.

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/wangbeng/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

SQL> select group#,sequence#,archived,status from v$log;

    GROUP# SEQUENCE# ARCHIV STATUS

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

         1 25 YES ACTIVE

         3 24 YES INACTIVE

         2 26 NO CURRENT

SQL> recover database until cancel;

ORA-00279: change 5143930 generated at 02/25/2014 04:11:44 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/WANGBENG/archivelog/2014_02_25/o1_mf_1_25_9js 2x09s_.arc 

ORA-00280: change 5143930 for thread 1 is in sequence #25

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/fast_recovery_area/WANGBENG/archivelog/2014_02_25/o1_mf_1_25_9js2x09s_.arc --输入损坏的active redo文件的归档文件目录

ORA-00279: change 5144460 generated at 02/25/2014 04:25:04 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/WANGBENG/archivelog/2014_02_25/o1_mf_1_26_%u_ .arc 

ORA-00280: change 5144460 for thread 1 is in sequence #26

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/WANGBENG/archivelog/2014_02_25/o1_mf_1_25_9j

s2x09s_.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/wangbeng/redo02.log --输入当前的redo文件名

Log applied.

Media recovery complete.

3.非正常关机情况下未归档active redo文件被破坏<11.2.0.3>

[oracle@localhost wangbeng]$ rm redo01.log 

SQL> shutdown abort;

SQL> startup

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 335547696 bytes

Database Buffers 75497472 bytes

Redo Buffers 4272128 bytes

Database mounted.

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/wangbeng/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> select group#,sequence#,archived,status from v$log;

    GROUP# SEQUENCE# ARCHIV STATUS

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

         1 4 NO CURRENT

         3 3 YES INACTIVE

         2 2 YES INACTIVE

SQL> create pfile ='/u01/pfile3' from spfile;

在pfile里面加入

_allow_resetlogs_corruption=true

SQL> recover database until cancel

ORA-00279: change 5144938 generated at 02/25/2014 04:38:20 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/WANGBENG/archivelog/2014_02_25/o1_mf_1_4_%u_.

arc

ORA-00280: change 5144938 for thread 1 is in sequence #4

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/WANGBENG/archivelog/2014_02_25/o1_mf_1_4_%u_ .arc'  --没有找到第四个归档文件

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

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

ORA-01110: data file 1: '/u01/app/oracle/oradata/wangbeng/system01.dbf'  --这个报错是正常的

SQL> alter database open resetlogs;