昨天一備份數(shù)據(jù)庫(kù),在open read only的過程中出現(xiàn)故障，從而導(dǎo)致在standby上備份失敗。alert log 中信息如下： Errors in file /opt/Oracle/admin/crmhz/udump/crmhz_ora_13914.trc:ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only accessError 604 happened during db open, shutting down databaseUSER: terminating instance due to error 604Instance terminated by USER, pid = 13914ORA-1092 signalled during: alter database open read only...Tue Aug 10 09:31:51 2004 再查看trace file信息 [root@crmsb bdump]# more?? /opt/oracle/admin/crmhz/udump/crmhz_ora_13914.trc/opt/oracle/admin/crmhz/udump/crmhz_ora_13914.trcOracle9i EnterPRise Edition Release 9.2.0.1.0 - ProdUCtionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - ProductionORACLE_HOME = /opt/oracle/product/9.2.0System name:??? linuxNode name:????? crmsbRelease:??????? 2.4.9-e.3smpVersion:??????? #1 SMP Fri May 3 16:48:54 EDT 2002Machine:??????? i686Instance name: crmhzRedo thread mounted by this instance: 1Oracle process number: 11Unix process pid: 13914, image: oracle@crmsb (TNS V1-V3) *** session ID:(10.3) 2004-08-10 09:30:37.408*** 2004-08-10 09:30:37.408Managed Recovery: Cancel posted.ORA-00604: error occurred at recursive SQL level 1ORA-16000: database open for read-only access 都沒有提供什么有價(jià)值的信息，很希奇會(huì)出現(xiàn)604錯(cuò)誤 于是決定trace? open過程 startup nomount alter database mount standby? database; select sid from v$mystat where rownum = 1; select sid,serial# from v$session where sid = ?; exec dbms_system.set_ev(sid,serial#,10046,12,''); alter database open read only; 這是數(shù)據(jù)庫(kù)crash ,找到trace file，發(fā)現(xiàn)末尾部分為 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#END OF STMTPARSE #13:c=0,e=485,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=1066522815818430BINDS #13:?bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
?? bfp=40647620 bln=22 avl=02 flg=05?? value=72EXEC #13:c=0,e=518,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1066522815819173FETCH #13:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1066522815819296=====================PARSING IN CURSOR #12 len=34 dep=1 uid=0 oct=7 lid=0 tim=1066522815819480 hv=3008185193 ad='56fcbaac'delete from idl_ub1$ where obj#=:1END OF STMTPARSE #12:c=10000,e=14804,p=6,cr=79,cu=0,mis=1,r=0,dep=1,og=0,tim=1066522815819472BINDS #12:?bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0?? bfp=40645a48 bln=22 avl=06 flg=05?? value=4294951150WAIT #12: nam='db file sequential read' ela= 41 p1=1 p2=22839 p3=1WAIT #12: nam='db file sequential read' ela= 27 p1=1 p2=9 p3=1EXEC #12:c=0,e=884,p=2,cr=4,cu=2,mis=0,r=0,dep=1,og=4,tim=1066522815820884ERROR #12:err=16000 tim=1837753940ORA-00604: error occurred at recursive SQL level 1ORA-16000: database open for read-only accessEXEC #1:c=370000,e=375493,p=304,cr=5505,cu=4,mis=0,r=0,dep=0,og=4,tim=1066522815826303ERROR #1:err=1092 tim=1837753940 很希奇，正常standby數(shù)據(jù)庫(kù)open read only的時(shí)候是沒有delete的，在這里居然出現(xiàn)dml。于是一開始我嘗試去研究idl_ub1$是個(gè)什么表，結(jié)果發(fā)現(xiàn)是存儲(chǔ) pl/sql代碼的，很希奇，這個(gè)數(shù)據(jù)庫(kù)為什么會(huì)出現(xiàn)個(gè)狀況，繼續(xù)在 $ORACLE_HOME/rdbms/admin 下 grep -ir idl_ub1$ * ，結(jié)果找到和一個(gè)x$ 表掛上關(guān)系做連接了，未果，又通過 delete from idl_ub1$ where obj#=:1 中綁定變量value=4294951150 去結(jié)合搜索出來的view查找，也沒什么進(jìn)展。實(shí)際上，根據(jù) WAIT #12: nam='db file sequential read' ela= 27 p1=1 p2=9 p3=1 可以知道這個(gè)delete是訪問了file# =1 block#=9 的數(shù)據(jù)塊，很顯然，這是系統(tǒng)回滾段的塊頭，根據(jù)這里我們可以知道，這是一個(gè)回滾操作，也就是說，在主數(shù)據(jù)庫(kù)中由于DDL 的失敗，使得系統(tǒng)回滾段中記錄了該操作的回退信息，但是數(shù)據(jù)庫(kù)又還沒有回滾完成，到了standby中就出現(xiàn)這個(gè)狀況了。 為了嘗試解決這個(gè)問題，首先繼續(xù)應(yīng)用幾個(gè)日志，再open read only，結(jié)果失敗，也就是說該失敗的ddl在主數(shù)據(jù)庫(kù)一直沒有回滾，而數(shù)據(jù)庫(kù)中已經(jīng)查找不到任何異常進(jìn)程或者事務(wù)、鎖，只能認(rèn)為是系統(tǒng)出現(xiàn)異常。于是，今天在征得相關(guān)負(fù)責(zé)人的同意的情況下，我們決定在下班后將主數(shù)據(jù)庫(kù)重新啟動(dòng)一次看看，這樣強(qiáng)迫系統(tǒng)回滾失敗的ddl。下班重新啟動(dòng)數(shù)據(jù)庫(kù)，切換日志并歸檔，在standby上應(yīng)用日志到重起數(shù)據(jù)庫(kù)之后產(chǎn)生的歸檔日志，再open read only 成功，問題得到解決。
