oracle直播,直播：案例诊断进行中。 欢迎讨论提供思路建议

观察了一下，发现MMON进程似乎hang住了，很奇怪。至于为什么会hang住，没有了头绪。

尝试检查alert.log有没有线索。

alert.log:

Thu Mar 29 13:15:19 2012

Archived Log entry 11055 added for thread 1 sequence 11055 ID 0x8d7a7da8 dest 1:

Thu Mar 29 16:34:14 2012

ALTER SYSTEM ARCHIVE LOG

Thu Mar 29 16:34:14 2012

Thread 1 cannot allocate new log, sequence 11057

Private strand flush not complete

Current log# 1 seq# 11056 mem# 0: /ora03/u01/oracle/bdr1/redo_1/redo01g1.log

Current log# 1 seq# 11056 mem# 1: /ora03/u01/oracle/bdr1/redo_2/redo02g1.log

Thread 1 advanced to log sequence 11057 (LGWR switch)

Current log# 2 seq# 11057 mem# 0: /ora03/u01/oracle/bdr1/redo_1/redo01g2.log

Current log# 2 seq# 11057 mem# 1: /ora03/u01/oracle/bdr1/redo_2/redo02g2.log

Archived Log entry 11056 added for thread 1 sequence 11056 ID 0x8d7a7da8 dest 1:

Thu Mar 29 16:40:41 2012

alter database backup controlfile to trace

Backup controlfile written to trace file /ora03/dba01/oracle/bdr1/audit/diag/rdbms/bdr1/bdr1/trace/bdr1_ora_13502.trc

Completed: alter database backup controlfile to trace

Thu Mar 29 16:41:26 2012

DM00 started with pid=76, OS id=15289, job SYSTEM.SYS_EXPORT_FULL_01

Thu Mar 29 16:41:28 2012

DW00 started with pid=78, OS id=15291, wid=1, job SYSTEM.SYS_EXPORT_FULL_01

Thu Mar 29 16:42:32 2012

Memory Notification: Library Cache Object loaded into SGA

Heap size 53347K exceeds notification threshold (51200K)

Details in trace file /ora03/dba01/oracle/bdr1/audit/diag/rdbms/bdr1/bdr1/trace/bdr1_dw00_15291.trc

KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.TSTZ_COLS ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PARENT_OBJ.NAME ,KU$.PARENT_OBJ.OWNER_NAME ,KU$.PROPERTY ,KU$.REFPAR_LEVEL ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_IOTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.P

Thu Mar 29 16:43:02 2012

Memory Notification: Library Cache Object loaded into SGA

Heap size 53042K exceeds notification threshold (51200K)

Details in trace file /ora03/dba01/oracle/bdr1/audit/diag/rdbms/bdr1/bdr1/trace/bdr1_dw00_15291.trc

KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.TSTZ_COLS ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PARENT_OBJ.NAME ,KU$.PARENT_OBJ.OWNER_NAME ,KU$.PROPERTY ,KU$.REFPAR_LEVEL ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.P

Thu Mar 29 16:45:04 2012

Memory Notification: Library Cache Object loaded into SGA

Heap size 57610K exceeds notification threshold (51200K)

Details in trace file /ora03/dba01/oracle/bdr1/audit/diag/rdbms/bdr1/bdr1/trace/bdr1_dw00_15291.trc

KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('M_VIEW_LOG_T', '7')), 0 ,KU$.MVIEWLOG.MASTER ,KU$.MVIEWLOG.MOWNER ,'TABLE' ,KU$.MVIEWLOG.LOG ,'MATERIALIZED_VIEW_LOG' ,KU$.MVIEWLOG.MOWNER FROM SYS.KU$_M_VIEW_LOG_PFH_VIEW KU$ WHERE  KU$.MVIEWLOG.MOWNER LIKE '%' AND  NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND A.NAME=KU$.MVIEWLOG.MASTER AND A.SCHEMA=KU$.MVIEWLOG.MOWNER) AND NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.MVI

Thu Mar 29 16:51:14 2012

ALTER SYSTEM ARCHIVE LOG

Thu Mar 29 16:51:14 2012

Thread 1 cannot allocate new log, sequence 11058

Private strand flush not complete

Current log# 2 seq# 11057 mem# 0: /ora03/u01/oracle/bdr1/redo_1/redo01g2.log

Current log# 2 seq# 11057 mem# 1: /ora03/u01/oracle/bdr1/redo_2/redo02g2.log

Thread 1 advanced to log sequence 11058 (LGWR switch)

Current log# 3 seq# 11058 mem# 0: /ora03/u01/oracle/bdr1/redo_1/redo01g3.log

Current log# 3 seq# 11058 mem# 1: /ora03/u01/oracle/bdr1/redo_2/redo02g3.log

Archived Log entry 11057 added for thread 1 sequence 11057 ID 0x8d7a7da8 dest 1:

Thu Mar 29 19:00:40 2012

Suspending MMON action 'AWR Auto CPU USAGE Task' for 82800 seconds

Thu Mar 29 19:10:42 2012

Suspending MMON slave action kewfmadsa_ for 82800 seconds

Thu Mar 29 23:05:58 2012

Suspending MMON action 'undo usage' for 82800 secondsFri Mar 30 04:24:05 2012

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

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

注意红字部分，果然猜测是正确的，MMON 被hang住了，同时其仍然在ping着那个undo segment header block， 阻塞了我的查询。