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.
Then the actual upgrade of the database can start. This happens while the database is started in upgrade mode (
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
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
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).
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.
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.