Oracle数据库内存溢出或作业不可用导致数据库宕机 (kkjcre1p - unable to spawn jobq slave process)

相关报错信息如下所示：

查看数据库的警告日志alert*.log信息进行分析:

Process J000 died, see its trace file

kkjcre1p: unable to spawn jobq slave process

Errors in file /oradata/diag/rdbms/easdb/easdb/trace/easdb_cjq0_13883.trc:

Process W000 died, see its trace file

Process J000 died, see its trace file

kkjcre1p: unable to spawn jobq slave process

Errors in file /oradata/diag/rdbms/easdb/easdb/trace/easdb_cjq0_13883.trc:

Process J000 died, see its trace file

kkjcre1p: unable to spawn jobq slave process

Errors in file /oradata/diag/rdbms/easdb/easdb/trace/easdb_cjq0_13883.trc:

Process W000 died, see its trace file

Process J000 died, see its trace file

kkjcre1p: unable to spawn jobq slave process

Mon Feb 24 08:59:57 2020

MMON (ospid: 32361): terminating the instance due to error 472

Mon Feb 24 08:59:58 2020

System state dump requested by (instance=1, osid=32361 (MMON)), summary=[abnormal instance termination].

System State dumped to trace file /oradata/diag/rdbms/easdb/easdb/trace/easdb_diag_32343_20200224085958.trc

Dumping diagnostic data in directory=[cdmp_20200224085958], requested by (instance=1, osid=32361 (MMON)), summary=[abnormal instance termination].

Instance terminated by MMON, pid = 32361

easdb_diag*.trc:

0 remote Oradebug requests

13971236024 session pga memory

18216854712 session pga memory max

0 recursive system API invocations

542 enqueue timeouts

easdb_cjq0*.trc:

*** 2020-01-31 02:00:00.003

Closing Resource Manager plan via scheduler window

Clearing Resource Manager plan via parameter

Setting Resource Manager plan SCHEDULER[0x32DD]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2020-01-31 22:00:00.005

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Closing scheduler window

*** 2020-02-01 02:00:00.008

Closing Resource Manager plan via scheduler window

Clearing Resource Manager plan via parameter

Setting Resource Manager plan SCHEDULER[0x32DE]:DEFAULT_MAINTENANCE_PLAN via scheduler window

*** 2020-02-24 04:49:02.956

Process J000 is dead (pid=20793 req_ver=137303 cur_ver=137303 state=KSOSP_SPAWNED).

*** 2020-02-24 04:49:04.956

Process J000 is dead (pid=20798 req_ver=137304 cur_ver=137304 state=KSOSP_SPAWNED).

*** 2020-02-24 04:49:06.956

Process J000 is dead (pid=20802 req_ver=137305 cur_ver=137305 state=KSOSP_SPAWNED).

*** 2020-02-24 04:49:08.956

分析解答：

样例中的内存参数值修改仅供参考，要根据现场数据库服务器物理内存大小进行相应的分配调整：

1.数据库参数前期做过如下调整：

SQL>alter system set processes=12000 scope=spfile;

SQL>alter system set sessions=15000 scope=spfile;

SQL>alter system set job_queue_processes=1500