Wednesday, May 13, 2015

QMON Session waiting for 'latch free'


We are observing close to 30 sessions waiting on 'latch free' most of them logged on to the system around same time. All these sessions belong to streams. We dont use streams based replica in our system.All these sessions where qmon slaves 

All the sessions where on node 1 of a 3 node RAC


support suggested to run Note 273674.1 : Streams Configuration Report and Health Check Script  to check the streams setup. 
Streams was never setup or used

then the suspect was data pump , we checked our data pump logs
There is a data pump metadata export JOB which failed , it started at May 9 23:00:01 2015 


Export: Release 11.2.0.4.0 - Production on Sat May 9 23:00:01 2015 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Starting "SYSTEM"."SYS_EXPORT_FULL_02": system/******** DIRECTORY=datapump_metadata DUMPFILE=0509_XXXXXXX_metadata.dmp FULL=Y LOGFILE=0509_XXXXXXXXX_metadata.log content=metadata_only 
Processing object type DATABASE_EXPORT/TABLESPACE 
... 
... 
.... 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE 
ORA-39097: Data Pump job encountered unexpected error -12805 
ORA-39065: unexpected master process exception in MAIN 
ORA-12805: parallel query server died unexpectedly 
Job "SYSTEM"."SYS_EXPORT_FULL_02" stopped due to fatal error at Sun May 10 02:30:27 2015 elapsed 0 03:30:20 


We checked the datapump job status from the dba_datapump_jobs and below job orphaned

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS 
---------- -------------------------------------------------- ---------- ---------- ------------ ----------------- 
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0 
SYS SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0 
SYSTEM SYS_EXPORT_FULL_02 EXPORT FULL NOT RUNNING 0 


We went ahead purged the job table. 
drop table SYSTEM.SYS_EXPORT_FULL_02 ; 

still there is queue , which stated with the datapump job 

select * from gv$buffered_queues;

INST_ID QUEUE_ID QUEUE_SCHEMA QUEUE_NAME STARTUP_TIME NUM_MSGS SPILL_MSGS 
---------- ---------- ------------------------------ ------------------------------ ----------------- ---------- ---------- 
1 14217557 SYS KUPC$S_1_20150509230004 09/05/15 23:00:05 0 0 


to clean the queue , we manually stopped and dropped the queue

SQL> exec dbms_aqadm.stop

SQL> exec dbms_aqadm.stop_queue('KUPC$S_1_20150509230004');

PL/SQL procedure successfully completed.

SQL>  exec dbms_aqadm.drop_queue('KUPC$S_1_20150509230004',TRUE); 

PL/SQL procedure successfully completed.

SQL> select INST_ID,QUEUE_ID,QUEUE_SCHEMA,QUEUE_NAME,STARTUP_TIME,NUM_MSGS,SPILL_MSGS from gv$buffered_queues;

no rows selected


All the sessions waiting on latch free got cleared , and the instance is back to normal.




No comments:

Post a Comment