12.2 Auto Space Advisor Job Fails ORA-12012,ORA-00060

郑全

在12.2里面，每天晚上自动空间advisor执行报 ORA-00060死锁的问题，
该问题是一个BUG，

具体报错信息：

2020-06-07T23:10:34.940789+08:00

ORA-00060:deadlock resolved; details in file/software/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j001_63824.trc

2020-06-07T23:10:34.944245+08:00

Errorsin file /software/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j001_63824.trc:

ORA-12012:error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_54413"

ORA-00060:deadlock detected while waiting for resource

ORA-06512:at "SYS.DBMS_SPACE", line 2741

ORA-06512:at "SYS.DBMS_HEAT_MAP_INTERNAL", line 716

ORA-06512:at "SYS.DBMS_HEAT_MAP_INTERNAL", line 1164

ORA-06512:at "SYS.DBMS_HEAT_MAP", line 228

ORA-06512:at "SYS.DBMS_SPACE", line 2747

BUG号:24687075

解决办法：禁用这个 AUTO SPACE ADVISOR 即可。

附官方文档：

In 12.2 Auto Space Advisor Job Fails With ORA-60 (文档 ID 2321020.1)

In this Document

Symptoms

Changes

Cause

Solution

References

Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
After upgrade to 12.2 , space advisor job fails and the following message appears on the alert log:
ORA-00060: deadlock resolved; details in file /opt/oracle/diag/rdbms/oratest/oratest/trace/oratest_j001_162692.trc <<< Deadlock Trace File
Errors in file /opt/oracle/diag/rdbms/oratest/oratest/trace/oratest_j001_162692.trc
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_2629"
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_SPACE", line 2741
ORA-06512: at "SYS.DBMS_HEAT_MAP_INTERNAL", line 716
ORA-06512: at "SYS.DBMS_HEAT_MAP_INTERNAL", line 1164
ORA-06512: at "SYS.DBMS_HEAT_MAP", line 228
In Deadlock trace file, PROCESS STATE section shows action name: ORA$AT_SA_SPC_SY_2629
PROCESS STATE
-------------
Process global information:
----------------------------------------
SO: 0x4427da460, type: 4, owner: 0x4a2410040, flag: INIT/-/-/-/0x00 if: 0x3 c: 0x3
proc=0x4a2410040, name=session, file=ksu.h LINE:15737, pg=0, conuid=0
(session) sid: 1641 ser: 63815 trans: 0x494b3d730, creator: 0x4a2410040
flags: (0x8210041) USR/- flags2: (0x44009) XXX/-/XXX
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
DID: 0001-012C-00000BF90000-0000-00000000, short-term DID:
txn branch: (nil)
edition#: 133 user#/name: 0/SYS
oct: 6, prv: 0, sql: 0x49fb13990, psql: 0x49fb13990
stats: 0x4fb3e4c68, PX stats: 0x108b3404
service name: SYS$USERS
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 162692
machine: abc.xxx.xxxprogram: oracle@abc.xxx.xxx(J001)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: ORA$AT_SA_SPC_SY_2629, hash value=3167306278 <<<<<<<<<<<<<<<<<<< action name: ORA$AT_SA_SPC_SY_2629

Changes
Upgrade to 12.2.0.1
Cause
This issue is identified as below bug:
Bug 24687075 - SPACE ADVISOR TASKS/JOBS HITTING DEADLOCKS WITH GATHER DB STATS JOBS
Fixed in version :20.1

Solution
Apply the Generic patch available for your database version, its already available for 12.2 and 18c.
You can also disable the space advisory job as workaround:
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL, NULL);

References