Scheduled Jobs Hanging  

Posted by Mawahid

Problem:

Jobs are not executing automatically on its scheduled intervals, next_date is not updating though it is run manually.

Resolution:

1. If it is not executing automatically means, it is not entering into queue at the first place.

2. Generally any job scheduled through dbms_job will run in background, means helper processes(ora_j00,....etc) runs it, so, its queue-coordinator's (CJQ bg-process) responsibilty to create helper process to execute scheduled jobs. Here in this case, check at OS level if helper processes are exists or not.

ps -ef | grep <sid> | ora_j

If this return nothing means, cjq0 not doing its responsibility.

3. Check cjq0 process and kill it.

SELECT A1.SID, A2.SPID FROM V$SESSION A1,V$PROCESS A2
WHERE A1.PADDR = A2.ADDR AND TYPE='BACKGROUND' AND A1.PROGRAM LIKE '%CJQ%';

4. Set job_queue_process=0, this would kill all the helper process (ora_j001, ora_j002 etc) then after couple of mins, set it to old value.

5. Once we set to a value > 0, oracle automatically creates these processes. Now it will start executing scheduled jobs according to their interval.

6. If you still see any problems, check in dba_jobs_running and see if there is any locks, if yes, try to kill or release them.

0 comments »

Resetting Sequence  

Posted by Mawahid

Problem:

Some refresh or migration happened and because of that, users seeing their sequence last number is reset to 1 or some other value smaller to max(id) of the sequence table, and hence they are not able to insert the data.

Resolution:

Suppose there is a column in a table(TAG) called tag_id, which should be unique. Some applications designed in such a way that data will get inserted through sequence number or similarly. So, in general in such case, sequence - last_number should be greater than max(tag_id), in order to sucussful insertion of data.

Here the last_number of sequence is reset to 1 because of which users unable to insert data, as it throws tag_id with value 1 is already exists.

            select sequence_name,last_number from dba_sequences where sequence_name='<sequence_name>';
           
            select max(tag_id) from TAG;

Here TAG is the sequence table.

Check the metadata of the sequence, and found increment is set to 1.

            alter sequence <sequence_name> increment by 26589;  - This is the vale greater than max(tag_id) or put the value which match with production.

Now check its nextval

            select <sequence_name>.nextval from dual;

Now change the increment value to 1 as before, in order to match the metadata.

            alter sequence <sequence_name> increment by 1;
or
            alter sequence <sequence_name> increment by -2;   - We can also give minus values in order to set accordingly.

Now the last_number would be set as we required.

            select sequence_name,last_number from dba_sequences where sequence_name='<sequence_name>';

0 comments »
<< Newer Posts