Oracle 11g控制檔案損壞問題分析

對於Oracle 11g版本以下資料庫當控制檔案損壞後，我們在mount資料庫時，會有很明顯的ora-600錯誤，這樣就很容易知道控制檔案損壞的錯誤，但是對於oracle 11g R2就不是很明顯了，

當時是一個ORACLE 11g 的RAC系統，出現問題時資料庫執行個體可以nomount開啟但是在mount控制檔案時就會出現如下警示：

ORA-3113 "end of file on communication channel"

然後整個sqlplus串連終止,需要重新串連，當然我們知道通常mount階段無法進行，問題就出在控制檔案本身的存在損壞的問題，但是對於專業的人員來說，如果僅僅滿足這樣的心態，顯然是不行的，所以需要對其進行進一步分析：

但是在ASM日誌中我們可以看到如下資訊：

Tue Mar 27 13:35:11 2012
NOTE: client PROD1:PROD registered, osid 6726, mbr 0x1
Tue Mar 27 13:35:24 2012
NOTE: ASM client PROD1:PROD disconnected unexpectedly.
NOTE: check client alert log.
NOTE: Trace records dumped in trace file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_6726.trc
Tue Mar 27 13:40:35 2012
NOTE: client PROD1:PROD registered, osid 7477, mbr 0x1
Tue Mar 27 13:41:45 2012
NOTE: ASM client PROD1:PROD disconnected unexpectedly.
NOTE: check client alert log.
NOTE: Trace records dumped in trace file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_7477.trc
Tue Mar 27 13:41:47 2012
NOTE: client PROD1:PROD registered, osid 7736, mbr 0x1
Tue Mar 27 13:42:01 2012
NOTE: ASM client PROD1:PROD disconnected unexpectedly.
NOTE: check client alert log.
NOTE: Trace records dumped in trace file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_7736.tr

對於產生的trace檔案我們僅能夠看到如下些資訊：

2012-03-27 13:41:08.022438 :802EEFE8:KFNS:kfn.c@702:kfnDispatch(): calling server stub for KFNOP=5
2012-03-27 13:41:13.027006 :802EF0F4:KFNU:kfns.c@1924:kfnsBackground(): kfnsBackground completed in 5 seconds (KFNPM=0)
2012-03-27 13:41:13.027012 :802EF0F5:KFNS:kfn.c@729:kfnDispatch(): completed KFNOP=5
2012-03-27 13:41:13.027122 :802EF0F6:KFNS:kfn.c@702:kfnDispatch(): calling server stub for KFNOP=5

對於此問題顯然沒什麼用處，並且問題應該還是在資料庫方面。

所以對資料庫執行個體的alert警示檢查，當執行alter database mount狀態時的日誌如下：

Tue Mar 27 11:42:01 2012
alter database mount
This instance was first to mount
Tue Mar 27 11:42:01 2012
NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
NOTE: Loaded library: System
Tue Mar 27 11:42:01 2012
SUCCESS: diskgroup PRODDATA was mounted
Tue Mar 27 11:42:01 2012
NOTE: dependency between database PROD and diskgroup resource ora.PRODDATA.dg is established
USER (ospid: 26774): terminating the instance
Tue Mar 27 11:42:07 2012
System state dump requested by (instance=1, osid=26774), summary=[abnormal instance termination].
System State dumped to trace file /d01/oracle/11.2.0/admin/PROD1_db01/diag/rdbms/prod/PROD1/trace/PROD1_diag_26656.trc
Dumping diagnostic data in directory=[cdmp_20120327114207], requested by (instance=1, osid=26774), summary=[abnormal instance termination].
Instance terminated by USER, pid = 26774

還是不明顯的日誌提示，檢查警示trace檔案：/d01/oracle/11.2.0/admin/PROD1_db01/diag/rdbms/prod/PROD1/trace/PROD1_diag_26656.trc也無明細的資訊

後來採用10046事件來跟蹤mount這個過程，才看到了比較明細的提示，

alter session set events='10046 trace name context forever,level 12';
Trace file /d01/oracle/11.2.0/admin/PROD1_db01/diag/rdbms/prod/PROD1/trace/PROD1_ora_7764.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /d01/oracle/11.2.0
System name:    Linux
Node name:      db01.clc.com
Release:        2.6.18-238.el5
Version:        #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine:        x86_64
Instance name: PROD1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 31
Unix process pid: 7764, image: oracle@db01.clc.com (TNS V1-V3)


*** 2012-03-27 13:41:55.101
*** SESSION ID:(1751.3) 2012-03-27 13:41:55.101
*** CLIENT ID:() 2012-03-27 13:41:55.101
*** SERVICE NAME:() 2012-03-27 13:41:55.101
*** MODULE NAME:(oraagent.bin@db01.clc.com (TNS V1-V3)) 2012-03-27 13:41:55.101
*** ACTION NAME:() 2012-03-27 13:41:55.101

Error: kccpb_sanity_check_2
Control file sequence number mismatch!
fhcsq: 312916 bhcsq: 313137 cfn 0

*** 2012-03-27 13:41:55.101
Submitting synchronized dump request [268435460]. summary=[Controlfile header dump (kccpbsc)].

*** 2012-03-27 13:41:57.102
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+461<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+53<-ksuitm()+1325<-kccpb_sanity_check()+341<-kccbmp_get()+309<-kccsed_rbl()+111<-kccocx()+1154<-kccocf()+136<-kcfcmb()+1025<-kcfmdb()+54<-adbdrv()+63122<-opiexe()+18173<-opiosq0()+3993<-kpooprx()+274
<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+1670
----- End of Abridged Call Stack Trace -----

*** 2012-03-27 13:41:57.141
USER (ospid: 7764): terminating the instance
ksuitm: waiting up to [5] seconds before killing DIAG(7652)

如上紅色欄位可以看到，是控制檔案中序號不匹配造成控制檔案一致性驗證損壞，而無法正常mount資料庫。

這樣問題就明了了，可以修改或重建控制檔案方式來開啟資料庫。

更多Oracle相關資訊見Oracle 專題頁面 http://www.bkjia.com/topicnews.aspx?tid=12