Stream環(huán)境中網絡問題觸發(fā)的一個Oracle Bug

itpub.com.cn

現象：
因為網絡問題導致Stream Site之間的鏈路中斷，重啟apply端后Source端的數據沒有傳輸過來，經過檢查，發(fā)現propagation進程被disable掉了，alert.log日志顯示如下錯誤信息：
Mon Apr 11 11:48:38 2011
LOGMINER: Begin mining logfile for session 22 thread 1 sequence 107406, /u03/oradata/hzbakdb/redo02.log
Mon Apr 11 11:52:55 2011
Propagation Schedule for (STRMADMIN.CAP_QUEUE_MARSNGV4, "STRMADMIN"."DESTQ_MARSNGV4"@GZBKDB.DEST) encountered following error:
ORA-04031: unable to allocate  bytes of shared memory ("","","","")
Mon Apr 11 11:53:26 2011
Propagation Schedule for (STRMADMIN.CAP_QUEUE_MARSNGV4, "STRMADMIN"."DESTQ_MARSNGV4"@GZBKDB.DEST) encountered following error:
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DATABASESYS","trigger inform","kglhin: temp")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select user#,type# from user...","sql area","tmp")
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 1087
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7636
ORA-06512: at "SYS.DBMS_AQADM", line 631
ORA-06512: at line 1
... ...
Mon Apr 11 13:02:53 2011
Propagation Schedule for (STRMADMIN.CAP_QUEUE_MARSNGV4, "STRMADMIN"."DESTQ_MARSNGV4"@GZBKDB.DEST) encountered following error:
ORA-04052: error occurred when looking up remote object STRMADMIN.DBMS_AQADM@GZBKDB.DEST
ORA-00604: error occurred at recursive SQL level 4
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DATABASESYS","trigger inform","kglhin: temp")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select user#,type# from user...","sql area","tmp")
ORA-02063: preceding 3 lines from GZBKDB.DEST
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 1087
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7636
ORA-06512: at "SYS.DBMS_AQADM", line 631
ORA-06512: at line 1
Propagation Schedule for (STRMADMIN.CAP_QUEUE_MARSNGV4, "STRMADMIN"."DESTQ_MARSNGV4"@GZBKDB.DEST) disabled due to 16 consecutive errors.
從紅色的這一行可以看到，由于這個錯誤出現了16次導致了propagation進程的disable!
從錯誤信息可以看出這個是共享池碎片化導致分配不到內存而拋出的異常，重啟數據庫后，在開啟propagation進程，系統(tǒng)拋出如下錯誤信息：
Sat Apr 16 14:18:55 2011
Errors in file /app/oracle/admin/hzbakdb/bdump/hzbakdb_j001_1399.trc:
ORA-00600: internal error code, arguments: [kwqpcbk179], [74], [1361], [], [], [], [], []
Sat Apr 16 14:18:58 2011
Errors in file /app/oracle/admin/hzbakdb/bdump/hzbakdb_j001_1399.trc:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7233
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7511
ORA-06512: at "SYS.DBMS_AQADM", line 978
ORA-06512: at line 1
......
Sat Apr 16 14:20:34 2011
Errors in file /app/oracle/admin/hzbakdb/bdump/hzbakdb_j002_1963.trc:
ORA-00600: internal error code, arguments: [kwqpcbk179], [118], [1361], [], [], [], [], []
Sat Apr 16 14:20:37 2011
Errors in file /app/oracle/admin/hzbakdb/bdump/hzbakdb_j002_1963.trc:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7233
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7511
ORA-06512: at "SYS.DBMS_AQADM", line 978
ORA-06512: at line 1
Sat Apr 16 14:21:01 2011
Thread 1 advanced to log sequence 107854 (LGWR switch)
  Current log# 3 seq# 107854 mem# 0: /u03/oradata/hzbakdb/redo03.log
Sat Apr 16 14:21:02 2011
LOGMINER: End mining logfile: /u03/oradata/hzbakdb/redo02.log
Sat Apr 16 14:21:02 2011
LOGMINER: Begin mining logfile for session 21 thread 1 sequence 107854, /u03/oradata/hzbakdb/redo03.log
Sat Apr 16 14:21:06 2011
LOGMINER: End mining logfile: /u02/archivedlog/1_107406_657119277.arc
Sat Apr 16 14:21:06 2011
LOGMINER: Begin mining logfile for session 22 thread 1 sequence 107407, /u02/archivedlog/1_107407_657119277.arc
Sat Apr 16 14:21:10 2011
C002: large txn committed, xid: 0x002c.01f.7fff00422927
Sat Apr 16 14:21:30 2011
C002: large txn committed, xid: 0x002c.021.7fff0042291e
Sat Apr 16 14:21:31 2011
LOGMINER: End mining logfile: /u02/archivedlog/1_107407_657119277.arc
從日志中可以看出，這個錯誤出現一段時間后會自動恢復！兩年前解決這個錯誤的時候由于沒有耐心等待2分多鐘的時間導致我重啟了N次數據庫也沒能解決，最后放棄回家休息等第二天早上再去檢查發(fā)現好了，偶然發(fā)現了這個不是解決辦法的辦法～～～～等啊@.@
   與這一現象相關的幾個Note：
Memory Leak from Propagation Job followed by : Ora-600[Kwqpcbk179] [ID 423110.1]
Bug 5933656 - OERI[kwqpcbk179] from AQ [ID 5933656.8]
ORA-600 [kwqpcbk179] [ID 462902.1]
Oracle 說在10.2.0.5里解決了，我這個剛好升級了一個10.2.0.5的環(huán)境,從去年7月份到現在還沒報過這個錯誤，這說明Oracle是不是真的解決了呢？！