Tuesday, March 22, 2011

Unable to spawn jobq slave process

Some day one Oracle database stopped launching job processes, and it was because there was a lot of jobs spawned but neither responding or finishing its execution:


SQL> select count(*) from dba_SCHEDULER_RUNNING_CHAINS;

COUNT(*)
----------
0

SQL> select count(*) from dba_SCHEDULER_RUNNING_JOBS;

COUNT(*)
----------
0

SQL> select LOG_DATE, OWNER, JOB_NAME from dba_SCHEDULER_JOB_RUN_DETAILS
where LOG_DATE > (select STARTUP_TIME from v$instance) order by LOG_DATE;

LOG_DATE OWNER JOB_NAME
----------------------------------- ------------------------------ -------------------------
25-MAR-10 09.00.13.415604 PM -06:00 ORACLE_OCM MGMT_CONFIG_JOB
25-MAR-10 09.00.17.223307 PM -06:00 ORACLE_OCM MGMT_CONFIG_JOB_1
25-MAR-10 09.04.02.054297 PM -06:00 SYS JOB_DEPURA_AUD_TAB
25-MAR-10 09.05.52.943314 PM -06:00 SYS AUTO_SPACE_ADVISOR_JOB
25-MAR-10 09.19.52.891362 PM -06:00 SYS GATHER_STATS_JOB
26-MAR-10 01.45.01.080361 AM -06:00 SYS JOB_DAILY_SCHEMA_STATS
26-MAR-10 02.00.03.321536 AM -06:00 SYS PURGE_LOG
26-MAR-10 09.00.09.986098 PM -06:00 ORACLE_OCM MGMT_CONFIG_JOB
26-MAR-10 09.00.13.454394 PM -06:00 ORACLE_OCM MGMT_CONFIG_JOB_1
26-MAR-10 09.04.02.741707 PM -06:00 SYS JOB_DEPURA_AUD_TAB
26-MAR-10 09.07.58.011419 PM -06:00 SYS AUTO_SPACE_ADVISOR_JOB
26-MAR-10 09.10.58.165905 PM -06:00 SYS GATHER_STATS_JOB
27-MAR-10 01.00.02.342037 AM -06:00 SYS JOB_WEEKLY_SHRINK
27-MAR-10 02.00.03.041060 AM -06:00 SYS PURGE_LOG
27-MAR-10 05.00.08.906376 AM -06:00 ORACLE_OCM MGMT_CONFIG_JOB
27-MAR-10 05.00.09.878583 AM -06:00 ORACLE_OCM MGMT_CONFIG_JOB_1
27-MAR-10 05.05.28.660496 AM -06:00 SYS AUTO_SPACE_ADVISOR_JOB
27-MAR-10 05.07.10.629614 AM -06:00 SYS GATHER_STATS_JOB
16-APR-10 09.18.47.772170 PM -05:00 SYS JOB_DEPURA_AUD_TAB

============================================

myserver:mydb> tail -13l alert_mydb.log
Sat Mar 27 05:05:28 2010
Thread 1 advanced to log sequence 3440 (LGWR switch)
Current log# 2 seq# 3440 mem# 0: /dat_mydb/logfile/redo02a.rdo
Current log# 2 seq# 3440 mem# 1: /dat_mydb/logfile/redo02b.rdo
Sat Mar 27 10:21:56 2010
kkjcre1p: unable to spawn jobq slave process
Sat Mar 27 10:21:56 2010
Errors in file /oracle_dba10g/app/oracle/admin/mydb/bdump/mydb_cjq0_778366.trc:

Sat Mar 27 21:25:22 2010
Thread 1 advanced to log sequence 3441 (LGWR switch)
Current log# 3 seq# 3441 mem# 0: /dat_mydb/logfile/redo03a.rdo
Current log# 3 seq# 3441 mem# 1: /dat_mydb/logfile/redo03b.rdo

myserver:mydb> ps ax|grep j00
381146 - T 0:00 ora_j001_mydb
1437892 - A 115:53 ora_j007_mydb
1638584 - A 118:10 ora_j003_mydb
1700004 - A 115:59 ora_j006_mydb
1736896 - A 115:41 ora_j009_mydb
1798374 - A 0:00 ora_j000_mxod0002
1822936 - A 116:00 ora_j005_mydb
1917100 - A 116:00 ora_j004_mydb
1966264 - A 118:12 ora_j000_mydb
2064440 - A 118:11 ora_j002_mydb
2068652 - A 115:48 ora_j008_mydb
2109622 pts/1 A 0:00 grep j00

No comments:

Post a Comment