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.

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:

6 thoughts on “DBMS_JOB – Behavior Change in Oracle 19c during upgrade

  1. We have seen issues with the migration of DBMS_JOB to DBMS_SCHEDULER.

    I deliberately created a DBMS_JOB with no start time and interval on an Oracle 12.2 DB. This job was not reported during the preupgrade run and it was dropped/deleted after the upgrade to Oracle 19c.

    • Narendra,

      preupgrade.jar does not check for incomplete jobs. But we are discussing this now with the owner of DBMS_JOB/SCHEDULER.
      We can only rely on queries given to us.

      I will follow up with you via email and update the blog post once we have a conclusion.

      Cheers,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.