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

[[email protected] yoon]# rm -rf redo01.log

 

[[email protected] 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

 

非歸檔模式，資料庫關閉，不是正在使用的記錄檔損壞，解決方案同上。

oracle - redo 損壞或刪除處理方法