ORA-00060: deadlock resolved & ORA-12012: error on auto execute of job

We have recently upgraded a dataset to 12c and started receiving below errors in alert.log

ORA-00060: deadlock resolved; details in file /home/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_j001_200521.trc
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_5723"
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

After investigation found that there is a bug in 12.2 Release and Oracle working to it. You can refer below oracle support notes on this.

Bug 24687075 - SPACE ADVISOR TASKS/JOBS HITTING DEADLOCKS WITH GATHER DB STATS JOBS
In 12.2 Auto Space Advisor Job Fails With ORA-60 (Doc ID 2321020.1)

If you have setup alerts for ORA- errors then either you add exclusions to your script or you can run below command to disable the job Until Bug 24687075 gets fixed

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL, NULL);

Regards

Satishbabu Gunukula, Oracle ACE

https://oracleracexpert.com

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib

Normally users see ORA-04030 error with shortage of RAM or small PGA and kernel parameter config.

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8937/ORCL_ora_11258_i8937.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8938/ORCL_ora_11258_i8938.trc" Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Users can refer metalink Note 233869.1 titled "Diagnosing and Resolving ORA-4030 errors"

By looking the error it is clear that “out of process memory”, where oracle cannot get the required RAM. But note that you need to analyze the trace to find out the root cause

In my case I found that one of the un-optimized PL/SQL job consuming the resources and caused the issue.

Current SQL Statement for this session (sql_id=gjn38xnrxcfbj) -----
BEGIN orcl_server_bom_script.all_level(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ) ; END;
57% pid 67: 4089 MB used of 4092 MB allocated <= CURRENT PROC
Dump of Real-Free Memory Allocator Heap [0x7f2c640b4000]
mag=0xfefe0001 flg=0x5000003 fds=0x6 blksz=65536
blkdstbl=0x7f2c640b4010, iniblk=66560 maxblk=524288 numsegs=115
In-use num=65424 siz=4290117632, Freeable num=0 siz=0, Free num=1 siz=20512768

In our case max_map_count value is not enough and needs to investigate why the pl/sql processes requires such a large amount of memory. In this case adjusting PGA_AGGREGATE_TARGET or MEMORY_TARGET will not limit the size processes can grow and will not help.

To fix the issue increase the value of max_map_count. To find out current vale of max_map_count run below command

$cat /proc/sys/vm/max_map_count
65530

To change the value run below command
sysctl -w vm.max_map_count= <greater than 65530>

Follow below metalink note to change the value of vm.max_map_count.
Modification of "vm.max_map_count" on Oracle Linux 6 (Doc ID 2195159.1) 

Regards

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com