oracle - redo 损坏或删除处理方法

OS:

Oracle Linux Server release 5.7

DB:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

 

数据库打开模式，非归档模式，非当前日志损坏

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     234
Current log sequence           236
SQL>

 

SQL> select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
         1          1        234   52428800        512          1 NO       INACTIVE               3298602 2013/10/31       3298605 2013/10/31
         2          1        236   52428800        512          1 NO       CURRENT                3298609 2013/10/31  281474976710
         3          1        235   52428800        512          1 NO       INACTIVE               3298605 2013/10/31       3298609 2013/10/31
 

[root@b28-122 yoon]# rm -rf redo01.log

 

[root@b28-122 yoon]# ls
control01.ctl  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

 

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

 

执行dml语句，以及切换日志都能成功，但是数据库重启就报错

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

SQL>

 

SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2232960 bytes
Variable Size            2046823808 bytes
Database Buffers         1275068416 bytes
Redo Buffers               16326656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 32731
Session ID: 1 Serial number: 5

SQL>

 

SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.
SQL>

 

解决方法，数据库启动到mount，将损坏的日志文件清除日志组：

SQL> startup mount
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2232960 bytes
Variable Size            2046823808 bytes
Database Buffers         1275068416 bytes
Redo Buffers               16326656 bytes
Database mounted.
SQL>

 

SQL> alter database clear logfile group 1;

Database altered.

 

SQL> alter database open;

Database altered.

 

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

Total System Global Area 3340451840 bytes
Fixed Size                  2232960 bytes
Variable Size            2046823808 bytes
Database Buffers         1275068416 bytes
Redo Buffers               16326656 bytes
Database mounted.
Database opened.
SQL>

 

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/yoon/redo03.log
/u01/oracle/oradata/yoon/redo02.log
/u01/oracle/oradata/yoon/redo01.log

 

非归档模式，数据库关闭，不是正在使用的日志文件损坏，解决方法同上。