DBMS_JOB – Behavior Change in Oracle 19c during upgrade

DBMS_JOB - Behavior Change in Oracle 19c during upgrade

Actually I missed to blog about this change but luckily a colleague did ask a question the other week about the migration of jobs when you upgrade to Oracle 19c. Let me shed some light on DBMS_JOB – Behavior Change in Oracle 19c.

DBMS_JOB vs DBMS_SCHEDULER

Back in the 10g days, we introduced a new interface to run and monitor jobs in the database: The scheduler. The scheduler with DBMS_SCHEDULER is way more powerful than the old job interface. And we use it internally a lot as well. But as things appear in the real world. the introduction of a better interface doesn’t lead to rapid migrations.

In addition, in case you haven’t see this, we deprecated the DBMS_JOB interface with Oracle Database 12.2.0.1.

Conversion during the upgrade

With the upgrade to Oracle Database 19c there will be a conversion of jobs under the hood of the old DBMS_JOB interface. No worries – relax!

What happens? And what does that mean for your jobs in DBA_JOBS?

First of all, you can’t prevent or skip the migration under the scheduler’s control. But there is also no reason to do so. During the preupgrade check you may see the following warning:

DBMS_JOB - Behavior Change in Oracle 19c during upgrade

MOS Note: 2380601.1 – Database Preupgrade tool check list

No panic. There are three clarifications to mention:

  1. During the 19c upgrade for each job in DBMS_JOB a corresponding entry will be created with DBMS_SCHEDULER
  2. The old DBMS_JOB interface still works. But using it will always create a corresponding entry in the scheduler
  3. The check in preupgrade.jar is only checking for inconsistencies or any issues

What this means is: You can still use DBMS_JOB but under the cover we are using DBMS_SCHEDULER. The internal procedures have been changed. Your calls will work the same way, but DBMS_JOB is now a legacy interface to the DBMS_SCHEDULER.

But please be aware of this mentioned in the documentation in the first paragraph:

Oracle continues to support the DBMS_JOB package. However, you must grant the CREATE JOB privilege to the database schemas that submit DBMS_JOB jobs.

A simple testcase

As I always like to try out things, here’s a very simple testcase creating a job with DBMS_JOB in Oracle 11.2.0.4. Then I upgrade the database – and check afterwards.

connect system/oracle
drop user crc cascade;
grant connect, resource, dba to crc identified by crc;
connect crc/crc
alter session set nls_language = american;
alter session set nls_date_format= 'DD-MM-YY HH24:MI:SS';
create table jobtest ( a  date);

var jobno number
var jobstring VARCHAR2(2000)

-- setup of a simple job inserting a value into a test table every 3 minutes:
execute dbms_job.submit(:jobno, 'BEGIN insert into jobtest values (sysdate); commit; END;', sysdate, interval=> 'SYSDATE + 3/1440');

SELECT job,what,next_date,interval
FROM dba_jobs
WHERE job = :jobno;

This is what I have in my database before the upgrade – and of course this job is not visible in DBA_SCHEDULER_JOBS.

JOB  WHAT                                                       NEXT_DATE            INTERVAL
---  ---------------------------------------------------------- -------------------- ------------------
1    BEGIN insert into jobtest values (sysdate); commit; END;   24-05-19 09:36:32    SYSDATE + 3/1440

But after the upgrade, the informatin in DBA_JOBS is still present. And in addition, in DBA_SCHEDULER_JOBS I have a new entry:

JOB_NAME
--------------------
DBMS_JOB$_1

In DBMS_JOB, jobs have no name. Instead they get identified by an ID. For mapping purposes a new dictionary table scheduler$_dbmsjob_map has been defined.

This works transparently during upgrade and downgrade. And finally, this isn’t really a behavior change as everything happens underneath the covers.

More information and Links?

–Mike

Share this: