兩個(gè)有趣的REDO LOG ERROR處理方法
創(chuàng)新互聯(lián)專注于孝南企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站設(shè)計(jì),商城網(wǎng)站建設(shè)。孝南網(wǎng)站建設(shè)公司,為孝南等地區(qū)提供建站服務(wù)。全流程按需求定制制作，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)
系統(tǒng)環(huán)境：
操作系統(tǒng)： AIX-5300
數(shù)據(jù)庫(kù)： Oracle 10g(10.2.0.1.0)
案例描述：
數(shù)據(jù)庫(kù)(歸檔模式）非當(dāng)前日志組被破壞，但由于破壞方式不同，在解決問題的方式稍有不同，很有意思.
案例1：非當(dāng)前日志組文件被刪除
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------
/dsk1/oradata/prod/redo03a.log
/dsk1/oradata/prod/redo02a.log
/dsk1/oradata/prod/redo01a.log
/dsk2/oradata/prod/redo01b.log
/dsk2/oradata/prod/redo02b.log
/dsk2/oradata/prod/redo03b.log
6 rows selected.
刪除非當(dāng)前日志組：
[oracle@aix211 ~]$cd /dsk1/oradata/prod/
[oracle@aix211 prod]$ls
control02.ctl redo01a.log redo02a.log redo03a.log
[oracle@aix211 prod]$rm redo01a.log
[oracle@aix211 prod]$cd /dsk2/oradata/prod/
[oracle@aix211 prod]$ls
redo01b.log redo02b.log redo03b.log
[oracle@aix211 prod]$rm redo01b.log
關(guān)閉數(shù)據(jù)庫(kù)，并重新啟動(dòng)：
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2020552 bytes
Variable Size 318769976 bytes
Database Buffers 922746880 bytes
Redo Buffers 14753792 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/dsk1/oradata/prod/redo01a.log'
ORA-00312: online log 1 thread 1: '/dsk2/oradata/prod/redo01b.log'
告警日志：（提示：日志組文件找不到，無(wú)法讀取）
Errors in file /u01/app/oracle/admin/prod/udump/prod_ora_536600.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/dsk2/oradata/prod/redo01b.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/dsk1/oradata/prod/redo01a.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Completed: alter database clear logfile group 1
Mon May 26 10:02:36 2014
alter database open
Mon May 26 10:02:36 2014
Block change tracking file is current.
解決方法：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 452 52428800 2 YES INACTIVE 806225 26-MAY-14
3 1 451 52428800 2 YES INACTIVE 803970 26-MAY-14
2 1 453 52428800 2 NO CURRENT 806237 26-MAY-14
由于是非當(dāng)前日志組，并且已經(jīng)完成歸檔：
SQL> alter database clear logfile group 1;
Database altered.
打開數(shù)據(jù)庫(kù)成功：
SQL> alter database open;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 454 52428800 2 NO CURRENT 826578 26-MAY-14
2 1 453 52428800 2 YES INACTIVE 806237 26-MAY-14
3 1 451 52428800 2 YES INACTIVE 803970 26-MAY-14
SQL> alter system switch logfile;
System altered.
案例2：非當(dāng)前日志組文件頭部被破壞
[oracle@aix211 prod]$dd if=/dev/zero of=/dsk1/oradata/prod/redo03a.log bs=8192 count=3
3+0 records in
3+0 records out
[oracle@aix211 prod]$dd if=/dev/zero of=/dsk2/oradata/prod/redo03b.log bs=8192 count=3
3+0 records in
3+0 records out
關(guān)庫(kù)并啟動(dòng)Instance：
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2020552 bytes
Variable Size 318769976 bytes
Database Buffers 922746880 bytes
Redo Buffers 14753792 bytes
Database mounted.
ORA-00316: log 3 of thread 1, type in header is not log file
ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'
ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'
告警日志：（提示：日志組文件頭部不可讀取）
Errors in file /u01/app/oracle/admin/prod/bdump/prod_lgwr_499718.trc:
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'
Mon May 26 10:16:37 2014
解決方法：
Clear非當(dāng)前日志組：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 457 52428800 2 YES INACTIVE 827041 26-MAY-14
3 1 458 52428800 2 YES INACTIVE 827053 26-MAY-14
2 1 459 52428800 2 NO CURRENT 827067 26-MAY-14
SQL> alter database clear logfile group 3;
Database altered.
直接open database失敗：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00327: log 3 of thread 1, physical size less than needed
ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'
ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'
告警日志：（提示：redo 日志組文件頭部仍然有錯(cuò)誤）
Errors in file /u01/app/oracle/admin/prod/bdump/prod_lgwr_499718.trc:
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dsk2/oradata/prod/redo03b.log'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dsk1/oradata/prod/redo03a.log'
Mon May 26 10:16:37 2014
ARC0: STARTING ARCH PROCESSES
Mon May 26 10:16:37 2014
ORA-316 signalled during: ALTER DATABASE OPEN...
重新啟動(dòng)Instance，重新加載controlfile，問題解決：
SQL> startup force;
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2020552 bytes
Variable Size 318769976 bytes
Database Buffers 922746880 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.
SQL>
以上兩個(gè)案例都是針對(duì)redo log文件被破壞的情況下，解決問題的方法，破壞方式稍有不同，解決方法也有差異，應(yīng)在實(shí)踐中注意總結(jié)和歸納。
重慶分公司
重慶分公司
