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