[重庆思庄每日技术分享]-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