I was asked a question the other day:
When upgrading an Oracle Database, do we need to disable the scheduler (DBMS_SCHEDULER)?
The short answer is: No …. or perhaps.
What Happens During Analyze
When you use AutoUpgrade in Analyze mode (java -jar autoupgrade.jar -mode analyze), it will check your database. It is a non-intrusive check, and normal operations can continue, including use of the scheduler.
What Happens During Deploy
When downtime starts, and you are ready to upgrade your database, you start AutoUpgrade in Deploy mode (java -jar autoupgrade.jar -mode deploy).
Analyze And Fixups
First, AutoUpgrade will re-analyze the database, and based on the findings; it will run pre-upgrade fixups. The fixups make changes to the database, like gathering dictionary statistics, emptying recycle bin and other administrative tasks. The scheduler remains active during this period, so if you have any jobs that do administrative things on the database, like gathering statistics, there is a chance that they will collide. But typically not a problem.
Upgrade
Then the actual upgrade of the database can start. This happens while the database is started in upgrade mode (STARTUP UPGRADE)
When the database is started in upgrade mode, many things are disabled automatically. The scheduler being one of them.
Examples of other changes that happen in upgrade mode:
- System triggers are disabled
- Certain parameters are changed
- Resource Manager is disabled
You can check the alert log for more information. Here is a snippet:
2022-05-17T11:56:54.585122+02:00
AQ Processes can not start in restrict mode
Post-Upgrade
After the actual upgrade, the database is restarted in normal mode. The scheduler becomes enabled again.
In this phase, AutoUpgrade is recompiling invalid objects and performing post-upgrade fixups. Changes will be made to the database, like re-gathering dictionary statistics. Similar to the pre-upgrade fixups, depending on the nature of your scheduler jobs, there is a risk of things colliding. That can cause waits or concurrency issues.
Finally, the time zone file is upgraded. This process requires the database to be started in upgrade mode again. Again, the scheduler will be automatically disabled.
What Is The Answer?
From a functional point of view the scheduler is enabled and working during some parts of an upgrade. Only during the most critical parts is it automatically disabled.
So, the answer is: No, you do not need to disable the scheduler during upgrade. The database will automatically disable it when needed.
But the database is restarted multiple times which of course will affect any running scheduler jobs. Depending on the nature of your scheduler jobs, you might decide to disable it completely during the entire database upgrade. For instance, if you have long-running jobs or jobs that are sensitive to being interrupted. On the other hand, if your jobs are short-running, restart easily, or you basically don’t care, then it is perfectly fine to leave it all running during a database upgrade.
Manually Disable The Scheduler
If you decide to disable the scheduler manually, you should temporarily change job_queue_processes:
SQL> alter system set job_queue_processes=0 scope=both;
Don’t forget to set it to the original value after the upgrade.
You can find more information in MOS note How to disable the scheduler using SCHEDULER_DISABLED attribute in 10g (Doc ID 1491941.1).
Upgrade Mode
A few more words about upgrade mode:
When you start Oracle Database in upgrade mode, you can only run queries on fixed views. If you attempt to run other views or PL/SQL, then you receive errors.
When the database is started in upgrade mode, only queries on fixed views execute without errors. This restriction applies until you either run the Parallel Upgrade Utility (catctl.pl) directly, or indirectly by using the dbupgrade script). Before running an upgrade script, using PL/SQL on any other view, or running queries on any other view returns an error.
About Starting Oracle Database in Upgrade Mode, Upgrade Guide 19c
Starts the database in OPEN UPGRADE mode and sets system initialization parameters to specific values required to enable database upgrade scripts to be run. UPGRADE should only be used when a database is first started with a new version of the Oracle Database Server.
When run, upgrade scripts transform an installed version or release of an Oracle database into a later version, for example, to upgrade an Oracle9i database to Oracle Database 10g. Once the upgrade completes, the database should be shut down and restarted normally.
Databases Are Fun 
Unfortunately when you set job_queue_processes=0, then you lose the ‘parallel’ functionality of AutoUpgrade isn’t it? On one of our recent RU update, we do find the need to disable the jobs where we have materialize view refreshes scheduled on dba_jobs as it is reporting blocking session all day until we have to kill the running job or set it to broken totally. There seems to be a bug of sort when AU migrated dba_jobs to dba_scheduler especially when it comes to enabling the job from the scheduler.
LikeLike
Did further test to setting the jobs to be broken, looks like the Year 4000 thing is not a 19C/AU ‘feature’, we set the job to broken pre-upgrade and noted the next start date shows Year 4000
LikeLike
Hi Ed,
The parallel capability of AutoUpgrade (or any upgrade method) does not rely on “job_queue_processes”. You can safely set it to 0 and still use parallel upgrades.
The conversion from DBMS_JOB to DBMS_SCHEDULER happens transparently during the upgrade. I have not heard about any issues like yours. However, it does not sound like intended use to set a job to start date in year 4000. What is the reason for that? In DBMS_SCHEDULER it is better to “disable” the job instead.
Regards,
Daniel
LikeLike
Hi Daniel.
Any thoughts about setting system level plsql_code_type=native and then run autoupgrade deploy mode. In the sense of errors, elapsed time, dictionary objects and final compilation using utlrp.sql. ?
I know is a generic question . Recently I had an upgrade and I had to change one internal package to interpreted in order to be able to finish the autoupgrade and compile.
LikeLike
Hi,
I would advise strongly against that. The stuff in the dictionary is managed by us. When you move away from the defaults, you are “sailing into uncharted territory”. It might work, but there is no guarantee and I fear that you eventually (even long after the upgrade) run into weird problems.
Regards,
Daniel
LikeLike
Hi Daniel.
Thanks for your answers. On more thing. Will I be in safe site if before upgrade I change the parameter plsql_code_type=interpreted (default value) and after the upgrade returned it back to native value. That’s because I don’t know know why,who,when change it. I’m just the DBA doing the upgrade.
Best regards
LikeLike
Hi,
That should be fine. When you set it to the default during upgrade, those objects that was created/compiled during upgrade will have the “interpreted” flag set and will then onwards all be compiled that way – even if you change the parameter. However, you must remember to change that during upgrades, patching and other sorts of maintenance operations.
I would recommend switching to the default, and then as alter session switch the setting when you compile those objects that really needs that setting (if any).
Regards,
Daniel
LikeLike