A while ago I blogged already about this behavior change for DBMS_JOB in Oracle Database 19c. Many of you are aware of this. But today I learned something new about it. Kudos to Alexey Makhmutov for bringing this to my attention. There is a very important fix you will need on top in 19c to avoid performance issues. If you rely on jobs with the old job interface, take note: DBMS_JOB one-off patch needed for Oracle 19.3.0 – 19.7.0
Photo by Christopher Burns on Unsplash
What is the problem?
As you can read from there is a internal translation between DBMS_JOB and DBMS_SCHEDULER. And so far, I’m not aware of issues with this. But an internal check happens for duplicate names in DBMS_JOB_MAP table. And this check could lead to a hang. You can reproduce this quite easily by creating a job with DBMS_JOB in one session, but do not commit. Then create another job in second session, and it will hang.
I’m posting this here on purpose as I work with two very large customers who still rely on DBMS_JOB. And I guess, there may be many more out there.
What is the solution?
[See the comments below: The fix is included from 19.9.0 (October 2020) onwards]
You need a patch. As described in MOS Note: 2645984.1 – Delete On SYS.SCHEDULER$_DBMSJOB_MAP Causing Row Locks, you need to apply Patch 30835853 to your Oracle 19c home. This issue hopefully will be fixed in a future RU – my colleagues are taking the necessary steps already.
At the moment, I see one-offs on top of all RUs until 19.7.0, but not for all platforms. Especially the one-off for Linux for 19.7.0 is missing right now. This may change within the next days. If you need it, please request it via an SR if you can’t find it here.
More Information and Links
- Original blog post: Behavior Change in DBMS_JOB in Oracle 19c
- MOS Note: 2645984.1 – Delete On SYS.SCHEDULER$_DBMSJOB_MAP Causing Row Locks
- Patch 30835853
–Mike
Just wondering, if it’s possible now to migrate ugly “mv_refresh” dbms_jobs to scheduler jobs?
It’s funny, that oracle pushes users to use new and shiny feature, however still continues with the deprecated features itself
It gets transformed internally already – no way out.
Cheers,
Mike
Does not look like the patch is available to Windows users (yet) 🙁
No, you have to request it please.
Open an SR and request the backport for your platform, your database version and your patch level.
Make sure you upload an “opatch lsinventory” as well (I hope this works on Win as well).
Cheers,
Mike
SR raised 2 days ago – nothing back yet.
cheers Liz
Increase the severity to sev.1 (but “non 24×7”)
Place several updates into the SR
Call the hub (your local support telephone number in your country) and request an escalation and a management callback if that doesn’t progress things.
Cheers,
Mike
Arghhh still waiting – its with Dev. They did say they would put it in the next patchset release but I was not having that. Patching now stalled as we don’t want double down time.
🙁
Hi Mike,
Starting 19.7 RU , we see a auto task job ORA$_ATSK_AUTOSTS, and there is no information on what this job is doing and why its intriduced as auto to run, from 19.7 RU and if this can be disabled, after upgrade one of my ct reset ob_queue_processes back to 0 like they had in 11g, and now they are wondering if not running this job will cause any impact ? MOS is not updated with any info on ORA$_ATSK_AUTOSTS from 19.7 ru
Hi Geeta,
here we go:
https://mikedietrichde.com/2020/05/28/do-you-love-unexpected-surprises-sys_auto_sts-in-oracle-19-7-0/
Cheers,
Mike
Fix is included in 19.9.0.0.DBRU:201020 (OCT 2020)!
Thanks a lot, Martin!
I will update the blog post as well!
Cheers,
Mike
Hi Mike, is there any other reason to install the patch than having duplicate jobs?
if no, I can simply inform my appl. owner not using the old interface instead patching all the production db having a downtime each …
thx
Rainer
Hi Rainer,
the best would be to use 19.9.0 where this issue is fixed.
Otherwise, yes, don’t use the old interface and the issue won’t happen.
Cheers,
Mike
Does this internal check not occur when explicitly using the DBMS_Scheduler interface?
We specifically used dbms_job (in 12) as there was a functional difference with between it and the newer dbms_scheduler in terms of how we could incorporate it into a transaction. We have recently hit this bug since upgrading to 19c.
Will dbms_job still continue to work as it did even though it is internally executing dbms_scheduler?
Hi Gazza,
it is supposed to be this way. But I haven’t tested enough with it yet. But more will be on the blog soon.
Cheers,
Mike