现象：
因为网络问题导致Stream Site之间的链路中断，重启apply端后Source端的数据没有传输过来，经过检查，发现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进程，系统抛出如下错误信息：
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次数据库也没能解决，最后放弃回家休息等第二天早上再去检查发现好了，偶然发现了这个不是解决办法的办法～～～～等啊@.@
与这一现象相关的几个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的环境,从去年7月份到现在还没报过这个错误，这说明Oracle是不是真的解决了呢？！
阅读(2716) | 评论(0) | 转发(0) |
