job_queue_processes question
MSKFeb 15 2010 — edited Feb 15 2010Database version:11.1.0.7
JOB_QUEUE_PROCESSES is set to 16.
Our database crashed and I see these messages in alert.log
ORA-28 : opiodr aborting process unknown ospid (3702_1)
opidrv aborting process J000 ospid (13218_1) due to error ORA-28.
There was a sql job that compiles INVALID materialized views and this is done through dbms_job. The program is written such that, it picks invalid MVs and submits a dbms_job which would compile the invalid MV. This sql failed with unique constraint error as shown in the alert.log and I counted the number of times it failed trying to run and it was 15.
I am thinking that for some reason the dbms_job kept submitting itself but the job was not sucessful and new jobs were being submitted when the old one was unsuccessful and finally it reached the job queue processes limit of 16 and no more jobs could be submitted and the instance crashed.
I can only see the above errors in the alert log before crash and I see this error after instance was crashed (or getting crashed)
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
which I think is ok because it was trying to spawn job processes as instance was crashing..
The program logic which compiles these materialized views may need to be changed but before that I want to clarify that what I described above is indeed what caused the instance crash.
Thank you
MSK