Stream異常導致不斷產生trc文件

某省的生產庫，收到告警短信oracle目錄100%，登陸系統查看兼職被報錯刷屏了。

Mon Oct 23 23:25:18 EAT 2017

Thread 1 advanced to log sequence 39420 (LGWR switch)

  Current log# 7 seq# 39420 mem# 0: /vgxxx03/oradata/xxx/vg03_1_rd71.log

  Current log# 7 seq# 39420 mem# 1: /vgxxx04/oradata/xxx/vg04_1_rd72.log

Tue Oct 24 01:02:04 EAT 2017

Errors in file /oracle/admin/esshlj/udump/xxx1_ora_11790.trc:

ORA-07445: 出現異常錯誤: 核心轉儲 [kadfih()+33] [SIGSEGV] [Address not mapped to object] [0x91A1A1A2A000F] [] []

Tue Oct 24 01:02:06 EAT 2017

Trace dumping is performing id=[cdmp_20171024010206]

Tue Oct 24 01:02:41 EAT 2017

Errors in file /oracle/admin/1_pmon_27125.trc:

ORA-07445: exception encountered: core dump [kadfih()+33] [SIGSEGV] [Address not mapped to object] [0x91A1A1A2A000F] [] []

Tue Oct 24 01:02:43 EAT 2017

Errors in file /oracle/admin/1_pmon_27125.trc:

ORA-00001: unique constraint (108.) violated

ORA-00001: unique constraint (108.) violated

ORA-00001: unique constraint (.) violated

ORA-00602: internal programming exception

ORA-00602: internal programming exception

ORA-00602: internal programming exception

ORA-07445: exception encountered: core dump [kadfih()+33] [SIGSEGV] [Address not mapped to object] [0x91A1A1A2A000F] [] []

Tue Oct 24 01:07:10 EAT 2017

Errors in file /oracle/admin/1_ora_16532.trc:

ORA-00600: 內部錯誤代碼, 參數: [kghfrmrg:prv], [0xC00000073B406C28], [], [], [], [], [], []

Tue Oct 24 01:08:15 EAT 2017

Errors in file /oracle/admin/1_ora_16532.trc:

ORA-00600: 內部錯誤代碼, 參數: [KGHFRE3], [0xC0000007433BACB0], [], [], [], [], [], []

Tue Oct 24 01:10:13 EAT 2017

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Error 22813 occured while spilling buffered messages

Tue Oct 24 01:10:18 EAT 2017

Errors in file /oracle/admin/1_q003_26244.trc:

ORA-00600: internal error code, arguments: [kpotcprc: uga depth exceeded], [16], [], [], [], [], [], []

Error 600 occured while spilling buffered messages

Tue Oct 24 01:10:20 EAT 2017

Errors in file /oracle/admin/1_q003_26244.trc:

ORA-00600: internal error code, arguments: [kpotcprc: uga depth exceeded], [16], [], [], [], [], [], []

Error 600 occured while spilling buffered messages

Tue Oct 24 01:10:23 EAT 2017

Errors in file /oracle/admin/1_q003_26244.trc:

ORA-00600: internal error code, arguments: [kpotcprc: uga depth exceeded], [16], [], [], [], [], [], []

..........................................................................................................................................

Tue Oct 24 15:00:26 EAT 2017

Errors in file /oracle/admin/1_q007_20218.trc:

ORA-00600: internal error code, arguments: [kgqbt_search_block198], [], [], [], [], [], [], []

Tue Oct 24 15:00:26 EAT 2017

Errors in file /oracle/admin/1_q005_20204.trc:

ORA-00600: internal error code, arguments: [kgqbt_search_block198], [], [], [], [], [], [], []

Tue Oct 24 15:00:27 EAT 2017

Errors in file /oracle/admin/1_q000_19435.trc:

ORA-07445: exception encountered: core dump [kocgpn2()+224] [SIGSEGV] [Address not mapped to object] [0x600000002] [] []

Tue Oct 24 15:00:28 EAT 2017

Errors in file /oracle/admin/1_q004_20225.trc:

ORA-00600: internal error code, arguments: [kgqbt_search_block198], [], [], [], [], [], [], []

Tue Oct 24 15:00:28 EAT 2017

Errors in file /oracle/admin/1_q007_20218.trc:

ORA-00600: internal error code, arguments: [kgqbt_search_block198], [], [], [], [], [], [], []

Tue Oct 24 15:00:29 EAT 2017

Errors in file /oracle/admin/1_q005_20234.trc:

ORA-00600: internal error code, arguments: [kgqbt_search_block198], [], [], [], [], [], [], []

Tue Oct 24 15:00:29 EAT 2017

Errors in file /oracle/admin/1_q004_20225.trc:

ORA-00600: internal error code, arguments: [kgqbt_search_block198], [], [], [], [], [], [], []

Tue Oct 24 15:00:30 EAT 2017

Errors in file /oracle/admin/1_q003_19467.trc:

ORA-07445: exception encountered: core dump [kocgpn2()+224] [SIGSEGV] [Address not mapped to object] [0x600000002] [] []

初判應該是stream引起的。節點二也有相關stream的cdmp文件產生

Tue Oct 24 04:56:50 EAT 2017
Trace dumping is performing id=[cdmp_20171024015310]
Streams process APPLY_xxx altered (1) on instance 1
Tue Oct 24 05:11:34 EAT 2017
Trace dumping is performing id=[cdmp_20171024015317]
Streams process APPLY_xxx altered (1) on instance 1
Streams process APPLY_xxx altered (1) on instance 1
Tue Oct 24 05:26:18 EAT 2017
Trace dumping is performing id=[cdmp_20171024015321]

隨即給廠家人員打了電話，讓他們看看stream是否正常，反應狀態異常。kill其進程p000、p001，立馬就停止apply進程否則報錯！！！

SQL> show user

USER is "STRMADMIN"

SQL> execute dbms_apply_adm.stop_apply (apply_name => 'apply_esshlj');

PL/SQL procedure successfully completed.

SQL>  select apply_name,queue_name,status from dba_apply;

APPLY_NAME                     QUEUE_NAME                     STATUS

------------------------------ ------------------------------ --------

APPLY_ESSHLJ                   ESSHLJ_QUEUE                   DISABLED  ###這會兒狀態為DISABLE###啟動后狀態為ENABLE。

隨即又清理了一下緩存，該在 strmadmin下執行

declare

  options dbms_aqadm.aq$_purge_options_t;

begin

  options.delivery_mode := dbms_aqadm.buffered;

  dbms_aqadm.purge_queue_table('ESSHLJ_QUEUE_TABLE', null, options); 

end;

/

報錯了

ERROR at line 1:

ORA-03113: end-of-file on communication channel

1節點alert的trc文件一直產生，一秒鐘可生成5個左右文件，一個2M。oracle目錄只有20G。編寫腳本自動刪除trc文件。

1，嘗試了flush buffer_cache；

2，嘗試了flush share_pool；

因為該報錯的只是節點一，隨即打算將stream切換至節點二，觀察是否報錯依舊，在目標端中心庫上面執行了如下命令；

exec DBMS_AQADM.ALTER_QUEUE_TABLE@esshlj(queue_table => 'ESSHLJ_QUEUE_TABLE', primary_instance => 2, secondary_instance => 1);

觀察p000和p001進程在1節點已經不在了。2節點stream狀態正常，ORA-600報錯沒了，但是ORA-7445報錯依舊。因為報錯中一直有小工進程q00這樣的進程，暫時修改aq_tm_processes參數為0，看看報錯還有沒有。

SQL> !ps -ef |grep q00

  oracle  8955  8883  1 15:22:05 pts/5     0:00 /sbin/sh -c ps -ef |grep q00

  oracle  8957  8955  0 15:22:05 pts/5     0:00 grep q00

  oracle 27097     1  0 14:37:32 ?         0:00 ora_q006_esshlj1

  oracle  8820     1 51 15:21:52 ?         0:00 ora_q003_esshlj1

  oracle  8018     1  0 15:21:41 ?         0:00 ora_q008_esshlj1

  oracle  6752     1  0 15:21:12 ?         0:00 ora_q007_esshlj1

  oracle  7641     1  0 15:21:27 ?         0:00 ora_q004_esshlj1

  oracle  8863     1  1 15:21:58 ?         0:00 ora_q000_esshlj1

  oracle  8839     1  0 15:21:55 ?         0:00 ora_q009_esshlj1

  oracle  8931     1  4 15:22:01 ?         0:00 ora_q001_esshlj1

  oracle  8954     1  6 15:22:04 ?         0:00 ora_q002_esshlj1

SQL> alter system set aq_tm_processes = 0;           #####修改參數為0#######

System altered.

SQL> !ps -ef |grep q00

  oracle 11224  8883  0 15:23:08 pts/5     0:00 /sbin/sh -c ps -ef |grep q00

  oracle 11226 11224  0 15:23:08 pts/5     0:00 grep q00

小工進程已經沒有，但是報錯依舊~頭疼，在改回去吧。

SQL> alter system set aq_tm_processes = 1;

System altered.

SQL> !ps -ef |grep q00

  oracle 12198  8883  0 15:23:35 pts/5     0:00 /sbin/sh -c ps -ef |grep q00

  oracle 12203 12198  0 15:23:35 pts/5     0:00 grep q00

  oracle 12135     1 20 15:23:35 ?         0:00 ora_q000_esshlj1

  oracle 12199     1  3 15:23:35 ?         0:00 ora_q002_esshlj1

  oracle 12167     1  7 15:23:35 ?         0:00 ora_q001_esshlj1

有同事大膽猜想既然不在1節點跑，是否可以將1節點的aq_tm_processes參數設置為0， 將2節點的aq_tm_processes參數設置為1，并且指定SID。

SQL>  alter system set aq_tm_processes = 0 sid='esshlj1';

System altered.

隨后觀察1節點并沒有產生煩人的7445告警

Tue Oct 24 15:36:08 EAT 2017

ALTER SYSTEM SET aq_tm_processes=0 SCOPE=BOTH SID='esshlj1';

Tue Oct 24 15:45:58 EAT 2017

Trace dumping is performing id=[cdmp_20171024152311]

Tue Oct 24 16:43:44 EAT 2017

Thread 1 advanced to log sequence 39430 (LGWR switch)

  Current log# 1 seq# 39430 mem# 0: /vghlj/oradata/redo01.log

  Current log# 1 seq# 39430 mem# 1: /vghlj/oradata/1_rd11.log