DBMS_JOB – Behavior Change in Oracle 19c during upgrade

Radu Pârvu says:

Very interesting article.
Is the DBMS_JOB package de’supported in 19c?
Thanks!
Radu

Roberto Hernandez says:

Hi Mike,

Recreating the file watcher program and the file watcher job does not work in my case. I mean, the file watcher does not execute anything. As a workaround, I did the following

1. Create a Shell script which runs these SQL commands

oradebug setmypid
oradebug tracefile_name
begin
dbms_ischedfw.file_watch_job;
end;
/

2. Create a Job in DBMS_SCHEDULER using as repeat_interval the filewatcher calendar ( in my case every 30 minutes ) and as type EXTERNAL_SCRIPT with the credentials of the oracle owner

3. The shell basically triggers the sql if it finds the file(s) I am looking for. Obviously, I had to be smart enough to put a flag that in case the filewatcher runs again after 20 minutes and the chain that is triggered by it is running, it does not run again.

Anyway, I open a case in Oracle Support to verify whether this a bug or not, but it looks like it is. I will keep you updated.

Thank you
3. The job triggers the SQL Script

, I created a job that runs the package using the calendar schedule expression defined for the filewatcher. That way, it works.

In my case, as workaround, I used the following:

– I create a shell script which runs the commands

Edwin says:

Just want to confirm, having job_queue_processes to a non-zero value is NOT a pre-requisite for AutoUpgrade, is that correct? We want to set it to zero because datapatch is reporting locking issue on one of the databases as there is a materialized view refresh that runs every 15minutes. We will be re-instating the original value post-refresh.

Edwin says:

We set broken=Y and then run AutoUpgrade. The jobs are migrated to scheduler which is good. There seems to be some kind of relationship between job and scheduler. If we broken=Y/N on job it disable/enable on scheduler. If we enable on scheduler it doesn’t set the next run date properly, it is schedule in the Year4000. We can get around this, running broken=Y/N again. SO, now we are in a bit of dilemma and in some sort of guessing game. If we do a remove on job, will it remove on scheduler as well and vice versa? Is there no way to break these ‘relationship’ so we can just remove the dba job one. I suppose the only way is to recreate the job on scheduler? That completely negate the fact that AutoUpgrade has just done it for you though 🙁 …

P.S. – running some test atm

Edwin says:

HI Mike
Finally managed to test as below:
ON a 12.1.0.2 database, set broken to Y, and the start date changes to the Year 4000. So this is not a 19c bug, maybe the job/scheduler team has set this to be the case

SQL> select * from v$version ;

BANNER CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production 0
PL/SQL Release 12.1.0.2.0 – Production 0
CORE 12.1.0.2.0 Production 0
TNS for Solaris: Version 12.1.0.2.0 – Production 0
NLSRTL Version 12.1.0.2.0 – Production 0

5 rows selected.

WHAT
——————————————————————————————————————————————————————————————————–
JOB NEXT_DATE
———- ——————–
dbms_output.put_line(‘ok’);
345 04-JUL-2022 13:56:06

1 row selected.

SQL> exec dbms_job.broken( &job, TRUE);
Enter value for job: 345

PL/SQL procedure successfully completed.

SQL> select what, job, next_date from dba_jobs where job=345 ;

WHAT
——————————————————————————————————————————————————————————————————–
JOB NEXT_DATE
———- ——————–
dbms_output.put_line(‘ok’);
345 01-JAN-4000 00:00:00

1 row selected.

For jobs that AU has migrated to scheduler, we do want to move forward and just use the scheduler. The migrated jobs that are now on the scheduler are working fine so we are now wanting to remove the ones in dba_job, doing dbms_jobs.remove however also removes the job from dba_scheduler_jobs. The same thing happens if we are to remove from the scheduler. Not sure if there is any way around this. Same behaviour after the 19c upgrade when we create a new job in dba_job, it creates one in scheduler too. Can’t find a doc to say this is the expected behaviour from 19c onward. Not sure if there is a parameter to turn off/on this behaviour.