11.2.0.2.0 数据库一个节点hang

xinxin415415

os：redhat 5.5
db: oracle 11.2.0.2.0
19号的早上8点多hang

1. DISTRIB TRAN DDLDB.bbcc2fd.6365.29.1622
   
2.   is local tran 6365.29.1622 (hex=18dd.1d.656))
   
3.   delete pending committed tran, scn=20830620629 (hex=4.d99a0bd5)
   
4. Tue Mar 19 02:00:00 2013
   
5. Closing scheduler window
   
6. Closing Resource Manager plan via scheduler window
   
7. Clearing Resource Manager plan via parameter
   
8. Tue Mar 19 02:00:19 2013
   
9. Auto-tuning: Starting background process GTX1
   
10. Starting background process GTX1
    
11. Tue Mar 19 02:00:19 2013
    
12. GTX1 started with pid=215, OS id=28043
    
13. Tue Mar 19 02:02:38 2013
    
14. Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc  (incident=333947):
    
15. ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
    
16. Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333947/DDLDB2_m000_28045_i333947.trc
    
17. Use ADRCI or Support Workbench to package the incident.
    
18. See Note 411.1 at My Oracle Support for error and packaging details.
    
19. Tue Mar 19 02:02:40 2013
    
20. Dumping diagnostic data in directory=[cdmp_20130319020240], requested by (instance=1, osid=24162 (ARC0)), summary=[incident=289003].
    
21. Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc  (incident=333948):
    
22. ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
    
23. ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
    
24. Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333948/DDLDB2_m000_28045_i333948.trc
    
25. Use ADRCI or Support Workbench to package the incident.
    
26. See Note 411.1 at My Oracle Support for error and packaging details.
    
27. Use ADRCI or Support Workbench to package the incident.
    
28. See Note 411.1 at My Oracle Support for error and packaging details.
    
29. Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc  (incident=333949):
    
30. ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
    
31. Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333949/DDLDB2_m000_28045_i333949.trc
    
32. Use ADRCI or Support Workbench to package the incident.
    
33. See Note 411.1 at My Oracle Support for error and packaging details.
    
34. Tue Mar 19 02:02:43 2013
    
35. Sweep [inc][333949]: completed
    
36. Sweep [inc][333948]: completed
    
37. Sweep [inc][333947]: completed
    
38. Sweep [inc2][333948]: completed
    
39. Sweep [inc2][333947]: completed
    
40. Dumping diagnostic data in directory=[cdmp_20130319020243], requested by (instance=2, osid=28045 (M000)), summary=[incident=333949].
    
41. Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc  (incident=333950):
    
42. ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
    
43. bytes (,)
    
44. Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333950/DDLDB2_m000_28045_i333950.trc
    
45. Use ADRCI or Support Workbench to package the incident.
    
46. See Note 411.1 at My Oracle Support for error and packaging details.
    
47. Tue Mar 19 02:03:43 2013
    
48. Sweep [inc][333950]: completed
    
49. Sweep [inc2][333950]: completed
    
50. Sweep [inc2][333949]: completed

复制代码

xinxin415415

alert见附件

Maclean Liu(刘相兵

1. Tue Mar 19 02:02:40 2013
   
2. Dumping diagnostic data in directory=[cdmp_20130319020240], requested by (instance=1, osid=24162 (ARC0)), summary=[incident=289003].
   
3. Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc  (incident=333948):
   
4. ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
   
5. ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
   
6. Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333948/DDLDB2_m000_28045_i333948.trc
   
7. Use ADRCI or Support Workbench to package the incident.
   
8. See Note 411.1 at My Oracle Support for error and packaging details.
   
9. Use ADRCI or Support Workbench to package the incident.
   
10. See Note 411.1 at My Oracle Support for error and packaging details.
    
11. Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc  (incident=333949):
    
12. ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
    
13. Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333949/DDLDB2_m000_28045_i333949.trc
    
14. Use ADRCI or Support Workbench to package the incident.
    
15. See Note 411.1 at My Oracle Support for error and packaging details.
    
16. Tue Mar 19 02:02:43 2013
    
17. Sweep [inc][333949]: completed
    
18. Sweep [inc][333948]: completed
    
19. Sweep [inc][333947]: completed
    
20. Sweep [inc2][333948]: completed
    
21. Sweep [inc2][333947]: completed
    
22. Dumping diagnostic data in directory=[cdmp_20130319020243], requested by (instance=2, osid=28045 (M000)), summary=[incident=333949].
    
23. Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc  (incident=333950):
    
24. ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
    
25. bytes (,)
    
26. Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333950/DDLDB2_m000_28045_i333950.trc
    
27. Use ADRCI or Support Workbench to package the incident.
    
28. See Note 411.1 at My Oracle Support for error and packaging details.
    
29. Tue Mar 19 02:03:43 2013
    
30. Sweep [inc][333950]: completed
    
31. Sweep [inc2][333950]: completed
    
32. Sweep [inc2][333949]: completed
    
33. Tue Mar 19 02:08:19 2013
    
34. DISTRIB TRAN DDLDB.bbcc2fd.6130.29.3340
    
35.   is local tran 6130.29.3340 (hex=17f2.1d.d0c))
    
36.   delete pending committed tran, scn=20830665088 (hex=4.d99ab980)
    
37. DISTRIB TRAN DDLDB.bbcc2fd.6172.33.3179
    
38.   is local tran 6172.33.3179 (hex=181c.21.c6b))
    
39.   delete pending committed tran, scn=20830665075 (hex=4.d99ab973)
    
40. Tue Mar 19 02:08:37 2013
    
41. DISTRIB TRAN DDLDB.bbcc2fd.6398.1.723
    
42.   is local tran 6398.1.723 (hex=18fe.01.2d3))
    
43.   delete pending committed tran, scn=20830663272 (hex=4.d99ab268)
    
44. DISTRIB TRAN DDLDB.bbcc2fd.6497.2.491

复制代码

现象 4030+大量DISTRIB TRAN 　分布式事务


/u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_dia0_23705.trc
/u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_332292/DDLDB2_dia0_23705_i332292.trc

这2个TRACE打包上传， 再弄一个快照时段 或者附近的一个小时的AWR

xinxin415415

trace见附件

Maclean Liu(刘相兵

There are resolvable hangs on your system.  Hang Manger will
attempt to resolve these hangs.  Some information about these
is output below.  Complete information is available in an
incident trace file on instance 1.

If the hang is to be resolved by terminating the session or
process that is the root or victim of the hang, additional
information will be output on the local instance of that
session or process.  Below are the hangs for which resolution
will be attempted.


*** 2013-03-19 08:13:42.142
Resolvable Hangs in the System
                      Root       Chain Total               Hang
   Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution
     ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action
  ----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
       54 HANG RSLNPEND    2  1423     2     2   HIGH  LOCAL Terminate Process
  Hang Resolution Reason: Although the number of affected sessions did not
    justify automatic hang resolution initially, this previously ignored
    hang was automatically resolved.

      inst# SessId  Ser#     OSPID Event
      ----- ------ ----- --------- -----
          2     26   891     92787 enq: WF - contention
          2   1423   165     21105 not in wait

Dumping process info of pid[59.2110] (sid:1423, ser#:165)
    requested by master DIA0 process on instance 1.


*** 2013-03-19 08:13:42.143
Process diagnostic dump for oracle@crmrac2.tslcrm.com (M000), OS id=2110,
pid: 59, proc_ser: 30, sid: 1423, sess_ser: 165


os thread scheduling delay history: (sampling every 1.000000 secs)
  0.000000 secs at [ 08:13:41 ]
    NOTE: scheduling delay has not been sampled for 0.232123 secs  0.000000 secs from [ 08:13:37 - 08:13:42 ], 5 sec avg
  0.000000 secs from [ 08:12:42 - 08:13:42 ], 1 min avg
  0.000000 secs from [ 08:08:42 - 08:13:42 ], 5 min avg
loadavg : 3.65 3.28 3.23
Memory (Avail / Total) = 44021.43M / 64446.93M
Swap (Avail / Total) = 64001.13M /  64001.13M
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 R oracle    2110     1 99  85   0 - 2161886 stext 07:00 ?       01:13:24 ora_m000_DDLDB2
Short stack dump:
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()+112<-__sighandler()<-kglic0()+1192<-kksIterCursorStat()+300<-kewrrtsq_rank_tops
ql()+240<-kewrbtsq_build_topsql()+128<-kewrftsq_flush_topsql()+679<-kewrft_flush_table()+397<-kewrftec_flush_table_ehdlcx()+752<-kewrf
at_flush_all_tables()+1406<-kewrfos_flush_onesnap()+170<-kewrfsc_flush_snapshot_c()+612<-kewrafs_auto_flush_slave()+757<-kebm_slave_ma
in()+586<-ksvrdp()+1779<-opirip()+674<-opidrv()+603<-sou2o()+103<-opimai_real()+266<-ssthrdmain()+252<-main()+201<-__libc_start_main()
+244<-_start()+36


[0004]: pga_aggregate_target=5412749312 ==> 5162MB














          2     26   891     92787 enq: WF - contention  ==> AWR Flush
                  
                  查一下， 考虑 先KILL 前台进程
                  
                  select * from dba_hist_wr_control;

xinxin415415

设置了memory_target
memory_max_target                    big integer
19G
memory_target                        big integer
16G

xinxin415415

会不会由于bug导致的？
Bug 13405202 : ORA-7445 [KGLIC0()+1081] [SIGSEGV] OCCURRED

xinxin415415

332292 trace

Maclean Liu(刘相兵

xinxin415415 发表于 2013-3-19 23:09
会不会由于bug导致的？
Bug 13405202 : ORA-7445 [KGLIC0()+1081] [SIGSEGV] OCCURRED

我的理解 一个独立的7445 只是某个征兆 不会引起 大面积的HANG