AUTO_SPACE_ADVISOR_JOB执行期间报Global Enqueue Services Deadlock detected
请问刘大，下面的报错是为什么，如何规避和解决？------------------
alter:
Wed Sep 07 22:36:25 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_lmd0_29234.trc.
Wed Sep 07 22:45:12 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 22:45:12 2016
Dumping diagnostic data in directory=, requested by (instance=2, osid=26996 (J000)), summary=.
Wed Sep 07 22:47:00 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 22:47:00 2016
Dumping diagnostic data in directory=, requested by (instance=2, osid=26996 (J000)), summary=.
Wed Sep 07 22:55:53 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_lmd0_29234.trc.
Wed Sep 07 23:01:11 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_lmd0_29234.trc.
Wed Sep 07 23:01:22 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 23:01:22 2016
Dumping diagnostic data in directory=, requested by (instance=2, osid=26996 (J000)), summary=.
Wed Sep 07 23:03:05 2016
Global Enqueue Services Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc.
Wed Sep 07 23:03:05 2016
Dumping diagnostic data in directory=, requested by (instance=2, osid=26996 (J000)), summary=.
Wed Sep 07 23:03:32 2016
Thread 2 advanced to log sequence 190382 (LGWR switch)
Current log# 16 seq# 190382 mem# 0: +DATA/kcptdg/redo06b.dbf
Wed Sep 07 23:03:42 2016
Archived Log entry 429905 added for thread 2 sequence 190381 ID 0x3103a67a dest 1:
Wed Sep 07 23:03:58 2016
Errors in file /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc:
ORA-12012: error on auto execute of job "SYS"."AUTO_SPACE_ADVISOR_JOB"
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at "SYS.DBMS_SPACE", line 2465
ORA-06512: at "SYS.DBMS_SPACE", line 2538
---------
trace:
Trace file /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_j000_26996.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: kcptdb2
Release: 2.6.18-308.el5
Version: #1 SMP Tue Feb 21 20:06:06 EST 2012
Machine: x86_64
Instance name: kcptdg2
Redo thread mounted by this instance: 2
Oracle process number: 181
Unix process pid: 26996, image: oracle@kcptdb2 (J000)
*** 2016-09-07 22:02:34.380
*** SESSION ID:(4971.2573) 2016-09-07 22:02:34.380
*** CLIENT ID:() 2016-09-07 22:02:34.380
*** SERVICE NAME:(SYS$USERS) 2016-09-07 22:02:34.380
*** MODULE NAME:(DBMS_SCHEDULER) 2016-09-07 22:02:34.380
*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2016-09-07 22:02:34.380
-------------------------------------------------------------------------------
DEADLOCK DETECTED
Performing diagnostic dump and signaling ORA-00060
Complete deadlock information is located in the trace file of process (pid: 10, osid: 29230, DIA0)
trace file: /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_dia0_29230.trc
-------------------------------------------------------------------------------
*** 2016-09-07 22:02:34.380
-------------------------------------------------------------------------------
HUNG PROCESS DIAGNOSTIC DUMP BEGIN:
dump requested by process (pid: 10, osid: 29230, DIA0)
trace file: /u01/app/oracle/diag/rdbms/kcptdg/kcptdg2/trace/kcptdg2_dia0_29230.trc
-------------------------------------------------------------------------------
----- Current SQL Statement for this session (sql_id=22w4adu41x3fm) -----
drop table "KCPT".DBMS_TABCOMP_TEMP_UNCMP purge
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0xb4ed60960 790 package body SYS.PRVT_COMPRESSION
0xb4ed60960 774 package body SYS.PRVT_COMPRESSION
0xb3f475298 214 package body SYS.DBMS_COMPRESSION
0xb37720b80 812 SYS.WRI$_ADV_OBJSPACE_TREND_T
0xb37720b80 1953 SYS.WRI$_ADV_OBJSPACE_TREND_T
0xb47a7f970 587 package body SYS.PRVT_ADVISOR
0xb47a7f970 2655 package body SYS.PRVT_ADVISOR
0xb3fa2b7d8 241 package body SYS.DBMS_ADVISOR
0xb3fa2b7d8 196 package body SYS.DBMS_ADVISOR
0xb53759340 2465 package body SYS.DBMS_SPACE
0xb53759340 2538 package body SYS.DBMS_SPACE
Bug 15975838 - ORA-60 between MMON slave and auto space advisor (Doc ID 15975838.8)
Bug 15975838 ORA-60 between MMON slave and auto space advisor
This note gives a brief overview of bug 15975838.
The content was last updated on: 29-NOV-2013
Click here for details of each of the sections below.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected (Not specified)
Versions confirmed as being affected
11.2.0.3
Platforms affected Generic (all / most platforms affected)
Fixed:
The fix for 15975838 is first included in
12.2 (Future Release)
12.1.0.2 (Future Patch Set)
Interim patches may be available for earlier versions - click here to check.
Symptoms:
Related To:
Deadlock
Error May Occur
ORA-60
WRI$_SEGADV_OBJLIST
Description
An ORA-60 deadlock can occur in Auto Space Advisor.
Rediscovery Notes:
If you observe "TX" deadlocks with SYS.WRI$_SEGADV_OBJLIST involved, then you have
likely hit this bug.
Workaround:
Deactivate AUTO_SPACE_ADVISOR_JOB by
EXECUTE DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');
页:
[1]