oracle Redo损坏处理方法

说明：以下步骤为实验模拟redo损坏 

一、非当前redo损坏

1、查看当前redo:

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

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

     1 1 4 52428800 512 1 YES ACTIVE 1854143 03-MAR-14 1854148 03-MAR-14

     2 1 5 52428800 512 1 NO CURRENT 1854148 03-MAR-14 2.8147E+14

     3 1 3 52428800 512 1 YES ACTIVE 1854138 03-MAR-14 1854143 03-MAR-14

2、损坏非当前redo

以redo01为例：

[oracle@11gupgrade sky]$ pwd

/u01/app/oracle/oradata/sky

[oracle@11gupgrade sky]$ dd if=/dev/zero of=/u01/app/oracle/oradata/sky/redo01.log bs=4096 count=1;

1+0 records in

1+0 records out

4096 bytes (4.1 kB) copied, 0.0445078 s, 92.0 kB/s

3、关闭实例

sql>shutdown abort

sql>startup mount

ORACLE instance started.

Total System Global Area 1202556928 bytes

Fixed Size 2252704 bytes

Variable Size 771752032 bytes

Database Buffers 419430400 bytes

Redo Buffers 9121792 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:   

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

Process ID: 7805

Session ID: 191 Serial number: 3

查看alert日志中报错如下：

Errors in file /u01/app/oracle/diag/rdbms/sky/sky/trace/sky_lgwr_7783.trc:

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

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

ORA-27048: skgfifi: file header information is invalid

Additional information: 11

Errors in file /u01/app/oracle/diag/rdbms/sky/sky/trace/sky_lgwr_7783.trc:

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

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

ORA-27048: skgfifi: file header information is invalid

Additional information: 11

Errors in file /u01/app/oracle/diag/rdbms/sky/sky/trace/sky_ora_7805.trc:

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

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

Mon Mar 03 12:17:48 2014

ARC1 started with pid=24, OS id=7817

Mon Mar 03 12:17:48 2014

System state dump requested by (instance=1, osid=7805), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/sky/sky/trace/sky_diag_7773_20140303121748.trc

USER (ospid: 7805): terminating the instance due to error 313

Dumping diagnostic data in directory=[cdmp_20140303121748], requested by (instance=1, osid=7805), summary=[abnormal instance termination].

Instance terminated by USER, pid = 7805

4、启动实例

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1202556928 bytes

Fixed Size 2252704 bytes

Variable Size 771752032 bytes

Database Buffers 419430400 bytes

Redo Buffers 9121792 bytes

Database mounted.

5、清除问题日志组

SQL> alter database clear logfile group 1; (或alter database clear unarchived logfile group)

Database altered.

SQL> alter database open;

Database altered.

SQL> set lines 200

SQL> select * from v$log;

    GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

         1 1 6 52428800 512 1 NO CURRENT 1874990 03-MAR-14 2.8147E+14

         2 1 5 52428800 512 1 YES INACTIVE 1854148 03-MAR-14 1874990 03-MAR-14

         3 1 3 52428800 512 1 YES INACTIVE 1854138 03-MAR-14 1854143 03-MAR-14

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /arch

Oldest online log sequence 10

Next log sequence to archive 12

Current log sequence 12

SQL>

数据库恢复正常。

二、当前redo损坏

1、查看当前redo

SQL> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

         1 1 12 52428800 512 1 YES ACTIVE 1875175 03-MAR-14 1875237 03-MAR-14

         2 1 14 52428800 512 1 NO CURRENT 1875256 03-MAR-14 2.8147E+14

         3 1 13 52428800 512 1 YES ACTIVE 1875237 03-MAR-14 1875256 03-MAR-14

2、损坏redo02

[oracle@11gupgrade /]$ dd if=/dev/zero of=/u01/app/oracle/oradata/sky/redo02.log bs=4096 count=1;

1+0 records in

1+0 records out

4096 bytes (4.1 kB) copied, 0.00153151 s, 2.7 MB/s

3、关闭实例

shutdown abort

4、启动实例

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1202556928 bytes

Fixed Size 2252704 bytes

Variable Size 771752032 bytes

Database Buffers 419430400 bytes

Redo Buffers 9121792 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/sky/redo02.log'

ORA-27048: skgfifi: file header information is invalid

Additional information: 13

5、执行清除

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 sky (thread 1)

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/sky/redo02.log'

6、添加隐含参数

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

System altered.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1202556928 bytes

Fixed Size 2252704 bytes

Variable Size 771752032 bytes

Database Buffers 419430400 bytes

Redo Buffers 9121792 bytes

Database mounted.

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

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/sky/redo02.log'

ORA-27048: skgfifi: file header information is invalid

Additional information: 13

sql> recover database until cancel;

ORA-00279: change 1875256 generated at 03/03/2014 12:27:26 needed for thread 1

ORA-00289: suggestion : /arch/1_14_841233479.dbf

ORA-00280: change 1875256 for thread 1 is in sequence #14

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

cancel

ORA-00308: cannot open archived log '/arch/1_14_841233479.dbf'

ORA-27037: unable to obtain file status

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

Additional information: 3

ORA-00308: cannot open archived log '/arch/1_14_841233479.dbf'

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/sky/system01.dbf'

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [1875266], [0], [1875334], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [1875265], [0], [1875334], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [1875263], [0], [1875334], [12583040], [], [], [], [], [], []

Process ID: 9594

Session ID: 191 Serial number: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1202556928 bytes

Fixed Size 2252704 bytes

Variable Size 771752032 bytes

Database Buffers 419430400 bytes

Redo Buffers 9121792 bytes

Database mounted.

Database opened.

SQL> 

注：open数据库后建议将数据库通过逻辑导出导入的方式重建一遍。