Upgrade, Data Guard and Downtime

How about downtime when you upgrade your Oracle Database with Data Guard?

Short answer: You should expect slightly more downtime. Unless you head into a rolling upgrade.

How Long Does An Upgrade Take?

If I had a dime for every time someone asked me this question, I would have bought a tractor, retired, and worked full-time at our farm.

What a handsome Ford

My boss, Mike, created a long blog post about it without giving a real answer. If he can’t estimate it, no one can!

But I will give it a try anyway. A ballpark figure is 15-45 minutes for the actual upgrade. Then add all the extra stuff that surrounds the upgrade.

Does Data Guard Change That?

Yes, it does. Extra downtime is needed.

You don’t add any extra time to the actual upgrade of the primary database. During the upgrade, redo transport is on, and the primary database ships redo to the standby database, but it has no significant impact on the duration of the upgrade.

But you do need a little extra time before and after the upgrade.

Primary Standby Added Time
Restart Data Guard in new Oracle Home 5 min
Upgrade primary database
Verify standby has applied all redo 2 min
Optionally, test a switchover 5 min
TOTAL 7-12 min

If you follow the standby offline approach, you need more downtime. In that approach, you keep the standby database offline during the entire upgrade, and it needs time to catch up afterward. I would estimate it adds 5 min of extra downtime, totaling 12-17 min instead.

This is the best you can do with a physical standby database. But …

Rolling Upgrades

If you want to upgrade with almost no downtime at all, you can do rolling upgrades. A rolling upgrade uses DBMS_ROLLING to reduce the downtime for an upgrade to the time it takes to perform a regular switchover.

Under the hood, a rolling upgrade uses Data Guard as well. This blog post series is about upgrading with physical standby databases, but a rolling upgrade requires a logical standby database. It’s out of scope for this blog post series.

Why don’t we all do rolling upgrades?

  • A logical standby database has more restrictions than a physical one.
  • A logical standby database uses SQL apply instead of redo apply. SQL apply is not as efficient as redo apply and can become a performance bottleneck.
  • It’s more complicated.

But rolling upgrades are really cool, as you will see in this demo.

You can find more details in our webinar How Low Can You Go? Zero Downtime Operations. Slides and recording is available on demand.

Other Blog Posts in This Series

Upgrade, Data Guard, and Restore Points

Flashback Database is the best way to protect your Oracle Database during upgrade. It requires that you create restore points before the upgrade. If the upgrade fails, getting back to the starting point is easy and fast.

Enterprise Edition

It requires Enterprise Edition, but so does Data Guard. So it is not an issue here.

Guaranteed Restore Points

The restore points that you create to protect your Oracle Database upgrade should be guaranteed restore points. This ensures that you can always go back to the starting point. If you run out of disk space in your Fast Recovery Area (FRA), the database will halt and thus not jeopardize your fallback plan. The alternative to regular restore points is that the database will overwrite flashback logs to maintain operation, which is not desirable in this situation. You are relying on the restore point as your fallback.

Order of Creation

Always follow this order when creating restore points:

  1. All standby databases
  2. Primary database

The SCN of the restore points on the standby database must be the same or lower than on the primary database.

Imagine this scenario:

SCN Primary Standby
100 Restore point created
105 Restore point created
110 Upgrade starts
200 Upgrade fails

To flashback:

Primary Standby
Flash back standby database to SCN 100
Flash back primary database to SCN 105
Open primary database with resetlogs. Creates a new incarnation as of SCN 105
Roll forward standby database from SCN 101 to 105 and follow new incarnation

If the restore point on the standby database was created at SCN 106, then the primary database is already at a new incarnation (as of SCN 105). The standby database can’t follow and needs to be completely rebuilt.

Primary Database

When you upgrade with AutoUpgrade, it will create a guaranteed restore point for you. It is the default behavior.

The restore point created by AutoUpgrade protects the primary database only.

Standby Database

Always create the restore points on the standby database:

From the docs:

Restore points that are created on a primary database are automatically replicated to the standby database. The restore points created on the standby database are called replicated restore points. Irrespective of whether a restore point on the primary database is a guaranteed restore point or a normal restore point, the corresponding replicated restore point is always a normal restore point.

By design, all replicated restore points are not guaranteed; even if it was guaranteed on the primary database. When you protect a standby database during upgrade, you want the restore point to be guaranteed; thus, you must create them manually.

To create a restore point on the standby database, you must cancel redo apply:

SQL> alter database recover managed standby database cancel;
SQL> create restore point STBY_GRP guarantee flashback database;
SQL> alter database recover managed standby database disconnect from session;

If you forget to cancel redo apply, you run into:

SQL> create restore point STBY_GRP guarantee flashback database;
create restore point STBY_GRP guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'STBY_GRP'.
ORA-01153: an incompatible media recovery is active

Remove Restore Points

Don’t forget to remove the restore points after the upgrade. Do so when you have run your tests, and you are sure you won’t go back to the old release:

SQL> drop restore point STBY_GRP;
  • The order of removal is not important
  • It does not require downtime to remove the restore points

If you instruct AutoUpgrade to remove the restore point after upgrade (drop_grp_after_upgrade), it will happen only on the primary database. You must manually remove the restore points from the standby database.

Failure to remove the restore points will eventually fill up the FRA bringing the database to a complete halt.

Other Blog Posts in This Series

How To Upgrade Data Guard – Standby Offline Method

Let me show you how to upgrade your database to Oracle Database 19c when it is protected by Data Guard. I will use AutoUpgrade and follow the Standby Offline method.

This is my demo environment: Overview of the environment that is used for this procedure

  • Grid Infrastructure is managing the database.
  • Data Guard is configured using Data Guard broker.

Overall, the process looks like this: Overview of upgrade with a data guard

This procedure starts right before I start AutoUpgrade in DEPLOY mode. Downtime has begun, and no users are connected to the database.

Before Upgrade

I always use the latest version of AutoUpgrade. Download it and put it into $ORACLE_HOME/rdbms/admin.

A newer version of AutoUpgrade can also upgrade to older database releases, so don’t worry if the AutoUpgrade version doesn’t match the Oracle Database release that you are upgrading to.

Disable Fast-Start Failover (FSFO)

Fast-Start Failover must be disabled during upgrade. I connect to Data Guard CLI (dgmgrl) and disable it:

DGMGRL> disable fast_start failover;

Restore Point

My standby database is protected against errors during the upgrade by keeping it offline. However, to be on the safe side, I also create a guaranteed restore point on the standby database:

STANDBY SQL> alter database recover managed standby database cancel;
STANDBY SQL> create restore point stdby_before_upg guarantee flashback database;
STANDBY SQL> alter database recover managed standby database disconnect from session;

Stop Data Guard

I shut down and disable the standby database. Disabling the database is strictly speaking not necessary, but a better-safe-than-sorry approach:

[oracle@standby]$ $ORACLE_HOME/bin/srvctl stop database -d $ORACLE_UNQNAME -stopoption immediate
[oracle@standby]$ $ORACLE_HOME/bin/srvctl disable database -d $ORACLE_UNQNAME

Upgrade

During the upgrade, I want to defer redo log shipping to the standby database. I use the AutoUpgrade config file parameter defer_standby_log_shipping (see appendix for complete config file):

upg1.defer_standby_log_shipping=yes

Next, I upgrade the primary database by starting AutoUpgrade in DEPLOY mode:

[oracle@primary]$ java -jar autoupgrade.jar -config DB.cfg -mode deploy

As the upgrade progresses, it makes a lot of changes to the database data dictionary. Those changes are written to redo, which will accumulate on the primary database. The database will not send the redo logs to the standby database (yet).

At the end of the upgrade, AutoUpgrade informs me that redo log transport has been deferred and that I must enable it again:

---- Data Guard broker configuration is disabled. Enable it once ready to sync up standby database  ----
Enable Data Guard from DB_BOSTON using the following DGMGRL command: 
EDIT DATABASE DB_BOSTON SET STATE=TRANSPORT-ON;

After the upgrade, you should perform the necessary tests to validate the new database release. You should continue only when you are convinced to go live on the new release.

Remember that the standby database was left behind before we started, so if all other fails, simply restart the standby database and connect your users to it.

After Upgrade

Restart Standby Database In New Oracle Home

  • Ideally, I have stored these files outside the Oracle Home; like ASM. In that case, I don’t need to do anything:

    • SPFile
    • Password file
    • Data Guard broker config files
    • Network files (sqlnet.ora, tnsnames.ora etc.)

    If I stored the files inside the Oracle Home, I must move them to the new Oracle Home (see appendix).

  • I change my profiles and /etc/oratab (see appendix).

  • I upgrade the database in Grid Infrastructure, which updates the Oracle Home information. Grid Infrastructure now starts the database in the new Oracle Home. If you are licensed for Active Data Guard, you can change the startoption from mount to read only:

    [oracle@standby]$ export NEW_ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
    [oracle@standby]$ export ORACLE_HOME=$NEW_ORACLE_HOME
    [oracle@standby]$ $ORACLE_HOME/bin/srvctl upgrade database -db $ORACLE_UNQNAME -o $ORACLE_HOME
    [oracle@standby]$ $ORACLE_HOME/bin/srvctl modify database -db $ORACLE_UNQNAME -s "mount" -r PHYSICAL_STANDBY
    [oracle@standby]$ $ORACLE_HOME/bin/srvctl enable database -d $ORACLE_UNQNAME
    [oracle@standby]$ $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME
    

Set Parameters

Suppose I made any changes to the database initialization parameters as part of the upgrade of the primary database. In that case, I probably also want to make the same changes on the standby database.

I can make changes to database initialization parameters using these AutoUpgrade config file parameters:

  • remove_underscore_parameters
  • add_after_upgrade_pfile
  • add_during_upgrade_pfile
  • del_after_upgrade_pfile
  • del_during_upgrade_pfile

I can also review the Preupgrade Summary Report for suggested changes to the database initialization parameters.

STANDBY SQL> alter system set ... scope=both;

Re-enable Data Guard

To re-enable the Data Guard config, I use Data Guard CLI (dgmgrl) and re-enable redo transport:

DGMGRL> edit database "DB_BOSTON" set state=transport-on;

Now, redo is shipping to the standby database, and it will apply it. When the redo generated during the upgrade is applied on the standby database, it is implicitly upgraded.

Wait For Redo Apply

Ensure the standby database has caught up and applied all the redo generated during the upgrade.

DGMGRL> show database "DB_fra24r"

Database - DB_fra24r

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          1 hours 56 seconds (computed 1 second ago)
  Average Apply Rate: 3.38 MByte/s
  Real Time Query:    OFF
  Instance(s):
    DB

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
WARNING

The database reports a WARNING because there is an apply lag. The standby database has not caught up yet.

Wait a while and repeat the show database command until status changes to SUCCESS and there is no apply lag. Use Data Guard Broker to verify data guard setup after upgrade

If Data Guard broker reports an error the standby database is not applying logs, I can try to enable the configuration again:

DGMGRL> enable configuration;

Validate Data Guard

I validate the setup and ensure both databases are ready for a switchover. The database will not allow a switchover if there are any problems in the Data Guard setup. It is a good way of checking things are fine:

DGMGRL> validate database "DB_BOSTON"
DGMGRL> validate database "DB_fra24r"

After upgrading a primary database (data guard) with autoupgrade you can use validate database to ensure everything is fine

Optionally, I perform a switchover as well:

DGMGRL> switchover to "DB_fra24r"

Re-enable Fast-Start Failover

I can now re-enable FSFO:

DGMGRL> enable fast_start failover;

Remove Restore Points

After the upgrade, I should perform the necessary tests to validate the new database release. I should remove the restore points on both databases when I am convinced to go live on the new release.

Miscellaneous

Check the database registration in listener.ora. I must update the Oracle Home information if there is a static configuration.

What If

  • What if your Oracle Database is not managed by Grid Infrastructure? You can still use the above procedure, but you must change the commands accordingly.

  • What if you don’t use Data Guard broker? Manually configured Data Guard environments are fully supported by AutoUpgrade, but you must change some commands accordingly.

Conclusion

It is not that complicated to upgrade your database, even if it is part of a Data Guard setup. Using AutoUpgrade is fully supported and highly recommended. A little extra legwork is needed to take care of the standby database. But the good thing is that your Data Guard setup is maintained throughout the process. This procedure showed how to use the standby offline approach, which requires slightly more downtime but gives you extra protection.

Other Blog Posts in This Series

Appendix

Broker Config Files

Here is a query to determine the location of the Data Guard broker config files. In this example, the files are stored outside the Oracle Home; in ASM:

SQL> select name, value from v$parameter where name like 'dg_broker_config_file%';

NAME                   VALUES
---------------------- --------------------------------
dg_broker_config_file1 +DATA/DB_FRA2PR/dr1db_fra2pr.dat
dg_broker_config_file2 +DATA/DB_FRA2PR/dr2db_fra2pr.dat

Database Files

Here is a command to see where the SPFile and password file are located. In this example, the SPFile is stored outside the Oracle Home. However, the password file is in the default location inside the Oracle Home. The latter must be moved when you restart a database in the new Oracle Home:

[oracle@standby]$ srvctl config database -d $ORACLE_UNQNAME | grep -E "Spfile|Password"
Spfile: +DATA/<DB_UNIQUE_NAME>/PARAMETERFILE/spfileDB.ora
Password file: 

Updating /etc/oratab

Here is a little snippet to update /etc/oratab to match the new release Oracle Home. Since I am using Grid Infrastructure to manage my database, I don’t set the database to start automatically:

export NEW_ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=DB
#Backup file
cp /etc/oratab /tmp/oratab
#Use sed to remove the line that starts with ORACLE_SID
sed '/^'"$ORACLE_SID"':/d' /tmp/oratab > /etc/oratab
#Add new entry
echo "$ORACLE_SID:$NEW_ORACLE_HOME:N" >> /etc/oratab

Updating .bashrc

Here is a little snippet to update .bashrc replacing the old Oracle Home with the new Oracle Home:

export OLD_ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export NEW_ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
cp ~/.bashrc ~/.bashrc-backup
sed 's|'"$OLD_ORACLE_HOME"'|'"$NEW_ORACLE_HOME"'|g' ~/.bashrc-backup > ~/.bashrc

Alert Log Messages

Before the upgrade of the primary database, I stop the standby database. Since I didn’t defer redo transport in the primary database, it will complain about losing connection to the standby database. The primary database alert log will contain an entry similar to this:

2022-08-19T05:21:39.389976+00:00
ARCH: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (1089)
ARCH: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
krsg_gap_ping: Error 1089 when pinging <standby> (Process:TT00) (PID:82492)
2022-08-19T05:21:46.606834+00:00
TT04: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
TT04: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2022-08-19T05:21:46.607255+00:00
Errors in file /u01/app/oracle/diag/rdbms/db_boston/DB/trace/DB_tt04_66302.trc:
ORA-03113: end-of-file on communication channel
Error 3113 for archive log file 3 to '<standby>'
2022-08-19T05:21:47.002610+00:00
Errors in file /u01/app/oracle/diag/rdbms/db_boston/DB/trace/DB_tt04_66302.trc:
ORA-03113: end-of-file on communication channel

I can safely ignore it. My monitoring system might detect this and start to complain.

Config File

For your reference, this is the config file I used. It contains only the required information. All other parameters have a default value:

upg1.sid=DB
upg1.source_home=/u01/app/oracle/product/12.2.0.1/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
upg1.defer_standby_log_shipping=yes

Synchronize Standby Database

When I run AutoUpgrade in ANALYZE mode and check the preupgrade summary report, I find this information message:

Synchronize your standby databases before database upgrade.

The standby database is not currently synchronized with its associated primary database.

To keep data in the source primary database synchronized with its associated standby databases, all standby databases must be synchronized before database upgrade. See My Oracle Support Note 2064281.1 for details.

Don’t worry about it. It tells me to ensure that all redo gets applied.

What does it say? Basically, it says that all redo generated on the primary database before the downtime window started, should be sent to and applied on the standby database. This way, my standby database is ready to replace your primary database at any time, if something goes really wrong. Strictly speaking it is not necessary to ensure that, but it is strongly recommended.

Further Reading

Changing COMPATIBLE Parameter and Data Guard

When you upgrade your Oracle Database, you should also decide how to raise the COMPATIBLE parameter. The considerations are the same when you use Data Guard, but the procedure is different.

Why

The main reason for raising COMPATIBLE is to enable new features.

Some new features are not backward compatible, e.g., blockchain tables. When someone introduces such features, the database:

  • is no longer backward compatible
  • can no longer be downgraded

When you upgrade, COMPATIBLE stays at the old setting. You need to actively raise it and allow the use of these new features. You are in total control.

Here is a video with more details about COMPATIBLE.

When

I recommend that you raise COMPATIBLE one or two weeks after the upgrade.

  • When you have seen that the new release works fine in production.
  • When you are confident that a database downgrade won’t be needed.

Raising COMPATIBLE requires a database restart, i.e., an outage. If such is unacceptable, you must raise COMPATIBLE as part of the upgrade. But be advised, it severely limits your fallback options.

AutoUpgrade does not change COMPATIBLE, unless you explicitly state it in the config file:

upg1.drop_grp_after_upgrade=yes
upg1.raise_compatible=yes

What

I recommend that you always set COMPATIBLE to the default of a database release:

  • 19.0.0
  • 21.0.0

If you only have non-CDBs then it might not matter much. But in a multitenant environment, having a uniform, COMPATIBLE setting is very beneficial. This allows PDBs to move between CDBs without problems.

How

You need to raise COMPATIBLE on all databases in your Data Guard configuration. The order is important:

  • First, standby databases (individual order is not important)
    STANDBY SQL> --Ensure redo apply is running
    STANDBY SQL> alter database recover managed standby database disconnect from session;
    STANDBY SQL> alter system set compatible='19.0.0' scope=spfile sid='*';
    STANDBY SQL> alter database recover managed standby database cancel;
    
    [oracle@standby]$ srvctl stop database -d $ORACLE_UNQNAME
    [oracle@standby]$ srvctl start database -d $ORACLE_UNQNAME -o mount
    
    STANDBY SQL> alter database recover managed standby database disconnect from session;
    
  • Last, primary database
    PRIMARY SQL> alter system set compatible='19.0.0' scope=spfile sid='*';
    
    [oracle@primary]$ srvctl stop database -d $ORACLE_UNQNAME
    [oracle@primary]$ srvctl start database -d $ORACLE_UNQNAME
    

Other Blog Posts in This Series

Further Reading

How To Upgrade Data Guard – MAA Method

Let me show you how to upgrade your database to Oracle Database 19c when it is protected by Data Guard. I will use AutoUpgrade and follow the MAA method.

This is my demo environment: Overview of the environment that is used for this procedure

  • Grid Infrastructure is managing the database.
  • Data Guard is configured using Data Guard broker.

Overall, the process looks like this: Overview of upgrade with a data guard

This procedure starts right before I start AutoUpgrade in DEPLOY mode. Downtime has begun, and no users are connected to the database.

Before Upgrade

I always use the latest version of AutoUpgrade. Download it and put it into $ORACLE_HOME/rdbms/admin.

A newer version of AutoUpgrade can also upgrade to older database releases, so don’t worry if the AutoUpgrade version doesn’t match the Oracle Database release that you are upgrading to.

Disable Fast-Start Failover (FSFO)

Fast-Start Failover must be disabled during upgrade. I connect to Data Guard CLI (dgmgrl) and disable it:

DGMGRL> disable fast_start failover;

Restore Point

I need to protect the standby database against errors during the upgrade. I create a guaranteed restore point on the standby database:

STANDBY SQL> alter database recover managed standby database cancel;
STANDBY SQL> create restore point stdby_before_upg guarantee flashback database;
STANDBY SQL> alter database recover managed standby database disconnect from session;

Set Parameters

If I plan on making changes to the database initialization parameters as part of the upgrade of the primary database, I probably also want to make the same changes on the standby database.

I can make changes to database initialization parameters using these AutoUpgrade config file parameters:

  • remove_underscore_parameters
  • add_after_upgrade_pfile
  • add_during_upgrade_pfile
  • del_after_upgrade_pfile
  • del_during_upgrade_pfile

I can also review the Preupgrade Summary Report for suggested changes to the database initialization parameters.

Save the changes to the SPFile only:

STANDBY SQL> alter system set ... scope=spfile;

Restart Standby Database In New Oracle Home

The standby database must be started in the new Oracle Home; the Oracle Home I upgrade to.

  • First, I stop the standby database:

    [oracle@standby]$ srvctl stop database -d $ORACLE_UNQNAME
    
  • Ideally, I have stored these files outside the Oracle Home; like ASM. In that case, I don’t need to do anything:

    • SPFile
    • Password file
    • Data Guard broker config files
    • Network files (sqlnet.ora, tnsnames.ora etc.)

    If I stored the files inside the Oracle Home, I must move them to the new Oracle Home (see appendix).

  • Next, I restart the database in the new Oracle Home. I must restart in mount mode:

    [oracle@standby]$ #Set environment to new Oracle Home
    [oracle@standby]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
    [oracle@standby]$ export PATH=$ORACLE_HOME/bin:$PATH
    [oracle@standby]$ srvctl upgrade database -d $ORACLE_UNQNAME -oraclehome $ORACLE_HOME
    [oracle@standby]$ srvctl start database -d $ORACLE_UNQNAME -o mount
    

    I don’t need to explicitly start redo apply. My Data Guard broker configuration has APPLY-ON for the standby database. Since the broker is still enabled, it will ensure that redo apply is started.

  • Finally, be sure also to change my profiles and /etc/oratab (see appendix).

Upgrade

Upgrade the primary database by starting AutoUpgrade in DEPLOY mode:

[oracle@primary]$ java -jar autoupgrade.jar -config DB.cfg -mode deploy

As the upgrade progresses, it makes a lot of changes to the database data dictionary. Those changes are written to redo, which the standby database will apply. Thus, the upgrade happens implicitly on the standby database via the redo.

After Upgrade

Check Standby Database

Ensure that the standby database has caught up and applied all the redo generated during the upgrade.

I use Data Guard CLI (dgmgrl) to check it. First, the configuration:

DGMGRL> show configuration

Configuration - DB_BOSTON_DB_fra2vp

  Protection Mode: MaxPerformance
  Members:
  DB_BOSTON - Primary database
    DB_fra2vp - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

Status must be SUCCESS.

Next, I check each of the databases:

DGMGRL> show database "DB_BOSTON"
DGMGRL> show database "DB_fra2vp"

Both databases should have status SUCCESS, and the standby database must report no apply lag. Use Data Guard Broker to verify data guard setup after upgrade

If Data Guard broker reports an erroror the standby database is not applying logs, I can try to enable the configuration again:

DGMGRL> enable configuration;

Validate Data Guard

I validate the setup and ensure both databases are ready for a switchover. The database will not allow a switchover if there are any problems in the Data Guard setup. It is a good way of checking things are fine:

DGMGRL> validate database "DB_BOSTON"
DGMGRL> validate database "DB_fra2vp"

After upgrading a primary database (data guard) with autoupgrade you can use validate database to ensure everything is fine

Optionally, I perform a switchover as well:

DGMGRL> switchover to "DB_fra2vp"

Re-enable Fast-Start Failover

I can now re-enable FSFO:

DGMGRL> enable fast_start failover;

Active Data Guard

If the database is licensed to use Active Data Guard, I can now open the standby database in READ ONLY mode.

Remove Restore Points

After the upgrade, I should perform the necessary tests to validate the new database release. Only when I am convinced to go live on the new release, should I remove the restore points on both databases.

Miscellaneous

Check the database registration in listener.ora. I must update the Oracle Home information if there is a static configuration.

What If

  • What if your Oracle Database is not managed by Grid Infrastructure? You can still use the above procedure, but you must change the commands accordingly.

  • What if you don’t use Data Guard broker? Manually configured Data Guard environments are fully supported by AutoUpgrade, but you must change some commands accordingly.

Conclusion

It is not that complicated to upgrade your database, even if it is part of a Data Guard setup. Using AutoUpgrade is fully supported and highly recommended. A little extra legwork is needed to take care of the standby database. But the good thing is that your Data Guard setup is maintained throughout the process.

Here is a cool demo of the entire process:

Other Blog Posts in This Series

Appendix

Broker Config Files

Here is a query to determine the location of the Data Guard broker config files. In this example, the files are stored outside the Oracle Home; in ASM:

SQL> select name, value from v$parameter where name like 'dg_broker_config_file%';

NAME                   VALUES
---------------------- --------------------------------
dg_broker_config_file1 +DATA/DB_FRA2PR/dr1db_fra2pr.dat
dg_broker_config_file2 +DATA/DB_FRA2PR/dr2db_fra2pr.dat

Database Files

Here is a command to see where the SPFile and password file are located. In this example, the SPFile is stored outside the Oracle Home. However, the password file is in the default location inside the Oracle Home. The latter must be moved when you restart a database in the new Oracle Home:

[oracle@standby]$ srvctl config database -d $ORACLE_UNQNAME | grep -E "Spfile|Password"
Spfile: +DATA/<DB_UNIQUE_NAME>/PARAMETERFILE/spfileDB.ora
Password file: 

Updating /etc/oratab

Here is a little snippit to update /etc/oratab to match the new release Oracle Home. Since I am using Grid Infrastructure to manage my database, I don’t set the database to start automatically:

export NEW_ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=DB
#Backup file
cp /etc/oratab /tmp/oratab
#Use sed to remove the line that starts with ORACLE_SID
sed '/^'"$ORACLE_SID"':/d' /tmp/oratab > /etc/oratab
#Add new entry
echo "$ORACLE_SID:$NEW_ORACLE_HOME:N" >> /etc/oratab

Updating .bashrc

Here is a little snippit to update .bashrc replacing the old Oracle Home with the new Oracle Home:

export OLD_ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export NEW_ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
cp ~/.bashrc ~/.bashrc-backup
sed 's|'"$OLD_ORACLE_HOME"'|'"$NEW_ORACLE_HOME"'|g' ~/.bashrc-backup > ~/.bashrc

Config File

For your reference, this is the config file I used. It contains only the required information. All other parameters have a default value:

upg1.sid=DB
upg1.source_home=/u01/app/oracle/product/12.2.0.1/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1

Synchronize Standby Database

When I run AutoUpgrade in ANALYZE mode and check the preupgrade summary report, I find this information message:

Synchronize your standby databases before database upgrade.

The standby database is not currently synchronized with its associated primary database.

To keep data in the source primary database synchronized with its associated standby databases, all standby databases must be synchronized before database upgrade. See My Oracle Support Note 2064281.1 for details.

Don’t worry about it. It tells me to ensure that all redo gets applied

What does it say? Basically, it says that all redo generated on the primary database before the downtime window started, should be sent to and applied on the standby database. This way, my standby database is ready to replace your primary database at any time, if something goes really wrong. Strictly speaking it is not necessary to ensure that, but it is strongly recommended.

Further Reading

Downgrade and Data Guard

Oracle Database is capable of downgrading, and it is a cool fallback mechanism. You can use it even after go-live. Plus, Data Guard plays nicely together with downgrade. You don’t have to rebuild the standby database following a downgrade if you follow the correct procedure.

When To Use Downgrade

You should only consider downgrading after go-live. When users have made changes to your Oracle Database. In that case, you can downgrade to the previous release without any data loss. Before go-live, Flashback Database is our preferred fallback mechanism.

A comparison between the two:

Flashback Database Downgrade
Data loss No data loss
Use before go-live Use after go-live
After flashback, database is identical with before-upgrade state After downgrade, database is compatible with before-upgrade state, but not identical
Requires Enterprise Edition Works in all editions
Preferred method

General Considerations

It is a requirement that you have not changed the COMPATIBLE parameter. As soon as you change COMPATIBLE after upgrade, you can no longer downgrade your Oracle Database. If you have already changed the COMPATIBLE parameter, you must use other fallback methods like Data Pump or RMAN restore.

The old release Oracle Home must still exist on primary and standby hosts. I recommend that you keep them until you are absolutely sure you will not downgrade your Oracle Database.

When you downgrade your database, I recommend leaving your Grid Infrastructure at the new release. Don’t downgrade Grid Infrastructure as well. Ideally, you upgraded Grid Infrastructure to the new release in advance in a previous maintenance window. Thus, you know it can handle the old release of the database. Save yourself the added complexity of also downgrading Grid Infrastructure.

Data Guard broker does not support going back to a previous version. You must disable the broker before downgrade and afterward create a new configuration or restore broker configuration files from the old release.

The following works for Data Guard configuration with a physical standby database. Other considerations and a different procedure apply if you have a logical standby database.

Downgrade

To downgrade an Oracle Database protected by Data Guard the following applies:

  • You can downgrade hours, days, or even months after the upgrade. As long as you haven’t changed the COMPATIBLE parameter.
  • You must mount the standby database and start redo apply in real-time apply mode. Keep it running during the entire process.
  • You must not open the standby database until the end of the process.
  • The downgrade happens on the primary database. A downgrade will make changes to the data dictionary. Those changes are applied on the standby via redo.
  • A downgrade with a standby database generally follows the same procedure as a regular database. However, at the end of each step, you must ensure that the standby database has applied all the redo generated by the primary database during that step. I usually issue a few log switches and check the current sequence (SEQUENCE#) on the primary database. Then I wait for that sequence to be applied on the standby database.

Check our video on YouTube for essential information on downgrade and flip through the slides.

How To Downgrade

AutoUpgrade can’t perform downgrades. You need to do this manually.

In the following, $NEW_ORACLE_HOME refers to the release you were upgrading to (e.g., 19c), and $OLD_ORACLE_HOME refers to the release you upgraded from (e.g., 12.1.0.2).

  1. Follow the pre-downgrade instructions.
  2. Set the environment in your session to the new Oracle Home.
  3. For RAC, set CLUSTER_DATABASE parameter:
    alter system set cluster_database=false scope=spfile sid='*'
    
  4. Stop the primary database:
    srvctl stop database -d $ORACLE_UNQNAME
    
  5. Start the primary database (if RAC, just one instance) in downgrade mode:
    startup downgrade
    
  6. Set the executable flag on downgrade script in the new Oracle Home:
    chmod +x $ORACLE_HOME/bin/dbdowngrade
    
  7. Start downgrade script on primary database. The environment is still set to the new release Oracle Home.
    $ORACLE_HOME/bin/dbdowngrade
    
  8. Important: Ensure that the standby database has applied all redo from the downgrade operation. Perform a few log switches and note the sequence number. Ensure the standby database has applied that sequence.
  9. Shut down primary database and standby database (if RAC, all instances).
  10. Downgrade the standby database in Grid Infrastructure. This tells Grid Infrastructure to start the database in the old Oracle Home (in this example, it is 12.1.0.2):
    $ORACLE_HOME/bin/srvctl downgrade database \
      -db $ORACLE_UNQNAME
      -oraclehome $OLD_ORACLE_HOME
      -targetversion 12.1.0.2
    
  11. Important: Switch the environment in your session to the old Oracle Home.
  12. Start the standby database (if RAC, all instances).
  13. Start redo apply:
    alter database recover managed standby database disconnect from session
    
  14. Start primary database in upgrade mode (if RAC, only one instance):
    startup upgrade
    
  15. Finish the downgrade:
    set echo on termout on serveroutput on timing on
    spool catrelod.log
    @?/rdbms/admin/catrelod.sql
    
  16. Recompile:
    @?/rdbms/admin/utlrp.sql
    
  17. Set CLUSTER_DATABASE parameter and shut down:
    alter system set cluster_database=true scope=spfile sid='*'
    shutdown immediate
    
  18. Downgrade the primary database in Grid Infrastructure. Grid Infrastructure will now start the database in the old Oracle Home (in this example it is 12.1.0.2):
    $NEW_ORACLE_HOME/bin/srvctl downgrade database \
      -db $ORACLE_UNQNAME
      -oraclehome $OLD_ORACLE_HOME
      -targetversion 12.1.0.2
    
  19. Start the primary database:
    $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME
    
  20. Ensure all components are valid or option off:
    select comp_id, status 
    from   dba_registry
    where  status not in ('VALID', 'OPTION OFF')
    
  21. Important: Ensure that the standby database has applied all redo from the downgrade operation. Perform a few log switches and note the sequence number. Ensure the standby database has applied that sequence.
  22. Start Data Guard broker on primary and standby database
    • Either restore broker config files from old release, and start Data Guard broker.
    • Or, start Data Guard broker and recreate the configuration.
  23. : Ensure that your Data Guard configuration works. Use validate database command in Data Guard broker on all databases and ensure they are ready for switchover.
  24. Optionally, test your Data Guard by doing a switchover.
  25. Perform the post-downgrade instructions.

That’s it!

CDB

No specific Data Guard-related changes. The above procedure is written for a non-CDB database. The procedure is slightly different for a CDB; check the documentation or the demo below.

RAC

On the standby database, you can leave all instances running, and you don’t need to change CLUSTER_DATABASE.

Demo

Downgrade of a CDB running RAC and Data Guard environment:

Other Blog Posts in This Series

Upgrade of Time Zone File and Flashback Database

Someone asked me on the blog:

Can you revert an upgrade of the time zone file using Flashback Database?

And the short answer: Yes!

A time zone file update is a two-step process:

  • Time zone patch to Oracle Home
  • Time zone file upgrade of the database

Applying the patch to the Oracle Home is a simple process. The Oracle Home already has a lot of time zone files present (the previous versions), so you can safely add a newer version. It doesn’t hurt anything; you should generally never roll them off again. The time zone patch adds a file with time zone definitions (in $ORACLE_HOME/oracore/zoneinfo).

The second step is to upgrade the time zone file inside the database. This step will make changes to the data dictionary using the information from the time zone file in the Oracle Home. All the changes made in this step are made inside the database. There are situations when you want to revert the second step: Upgrading the time zone file in the database. For instance:

  • If the time zone file upgrade fails.
  • You performed a database upgrade to a new release and upgraded the time zone file afterward. Now you find a critical issue in the new release and decide to roll everything back.

How To Upgrade

Let’s give it a try and see how it works. I start with an Oracle Database running 19c, and I want to upgrade the time zone file and then revert the operation.

  • The database uses the default time zone file version (32 in Oracle Database 19c):

    SQL> select * from v$timezone_file;
    
    FILENAME                VERSION     CON_ID
    -------------------- ---------- ----------
    timezlrg_32.dat              32 	        0
    
  • I create a user and a table. Also, I insert data related to a time zone change in Yukon, Canada, that comes in with time zone file 35:

    SQL> create user sales no authentication;
    SQL> alter user sales quota unlimited on users;
    SQL> grant create table to sales;
    SQL> create table sales.t1 (
            c1 timestamp with time zone
         );   
    
    SQL> insert into sales.t1 values(TIMESTAMP '2020-03-07 12:00:00.00 America/Whitehorse');
    SQL> insert into sales.t1 values(TIMESTAMP '2020-03-08 12:00:00.00 America/Whitehorse');
    SQL> insert into sales.t1 values(TIMESTAMP '2020-03-09 12:00:00.00 America/Whitehorse');
    SQL> commit;
    
  • I download the time zone patch from Master Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1). Time zone files are cumulative, so I take the latest one, version 38. I apply it using opatch like any other patch to the Oracle Home. This is the result after applying the patch:

    $ ./opatch lsinventory
    Oracle Interim Patch Installer version 12.2.0.1.28
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    ...
    
    Patch  34006614     : applied on Sat Jul 30 12:46:16 CEST 2022
    Unique Patch ID:  24751709
    Patch description:  "RDBMS - DSTV38 UPDATE - TZDATA2022A"
       Created on 27 Apr 2022, 04:16:52 hrs PST8PDT
       Bugs fixed:
         34006614
    
  • So far – no changes have been made to the database. It still runs with the old time zone file.

  • Before performing the time zone file upgrade, I create a restore point:

    SQL> create restore point before_tz_upg guarantee flashback database;
    
    Restore point created.
    
  • I start the time zone file upgrade by running the pre-upgrade check:

    SQL> @utltz_upg_check.sql
    
    INFO: Starting with RDBMS DST update preparation.
    INFO: NO actual RDBMS DST update will be done by this script.
    ...
    A prepare window has been successfully ended.
    INFO: A newer RDBMS DST version than the one currently used is found.
    INFO: Note that NO DST update was yet done.
    INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
    INFO: Note that the utltz_upg_apply.sql script will
    INFO: restart the database 2 times WITHOUT any confirmation or prompt.
    
  • It looks good, so go ahead with the actual upgrade. This step requires a maintenance window:

    SQL> @utltz_upg_apply.sql
    
    INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
    INFO: The database RDBMS DST version will be updated to DSTv38 .
    WARNING: This script will restart the database 2 times
    ...
    An upgrade window has been successfully ended.
    INFO: Your new Server RDBMS DST version is DSTv38 .
    INFO: The RDBMS DST update is successfully finished.
    
  • That’s it. I have upgraded the time zone file in the database.

    SQL> select * from v$timezone_file;
    
    FILENAME		VERSION     CON_ID
    -------------------- ---------- ----------
    timezlrg_38.dat 	     38 	 0
    

How To Fall Back

To answer the original question: Can I use Flashback Database to back out of a time zone file upgrade?

  • I restart the database in mount mode:

    SQL> shutdown immediate
    ...
    ORACLE instance shut down.
    
    SQL> startup mount
    ORACLE instance started.
    ...
    Database mounted.
    
  • I try to use Flashback Database

    SQL> flashback database to restore point before_tz_upg;
    
    Flashback complete.
    
    SQL> alter database open resetlogs;
    
    Database altered.
    
  • It works. The database time zone file is back at version 32:

    SQL> select * from v$timezone_file;
    
    FILENAME		VERSION     CON_ID
    -------------------- ---------- ----------
    timezlrg_32.dat 	     32 	 0
    
  • I can also select my data again:

    SQL> select * from sales.t1;
     
    C1
    -----------------------------------------------
    07-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE
    08-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE
    09-MAR-20 12.00.00.000000 PM AMERICA/WHITEHORSE
    
  • Don’t roll off the time zone patch from the Oracle Home. It makes no difference to the database. Once you have reverted the second step, the database will work like before the upgrade. If you insist on doing so, just ensure that no other database uses the time zone file that came in with the patch.

Conclusion

This proves that you can use Flashback Database to revert an upgrade of the database time zone file. Flashback Database is easy and effective. There is no need to roll off the time zone patch from the Oracle Home. It makes no difference to the database.

Flashback and Data Guard

When you upgrade your Oracle Database, you should also prepare for fallback. Data Guard plays nicely together with Flashback Database. You don’t have to rebuild the standby database following a Flashback Database if you follow the correct procedure.

When To Use Flashback Database

Flashback Database is easy to use, and it is our preferred fallback mechanism. However, Flashback Database also means data loss because the database is rewinded. So, Flashback Database is only useful before you go live on the new release.

A comparison between Flashback Database and downgrade.

Flashback Database Downgrade
Data loss No data loss
Use before go-live Use after go-live
After flashback, database is identical with before-upgrade state After downgrade, database is compatible with before-upgrade state, but not identical
Requires Enterprise Edition Works in all editions
Preferred method

If your Oracle Database is running Standard Edition 2, you are not licensed to use Flashback Database. Instead look at partial offline backup.

General Considerations

It is a requirement that you have not changed the COMPATIBLE parameter. As soon as you change COMPATIBLE after upgrade, you can no longer use Flashback Database. If you have already changed the COMPATIBLE parameter, you must use other fallback methods like Data Pump or RMAN restore.

The old release Oracle Home must still exist on primary and standby hosts. I recommend that you keep them until you are absolutely sure you will not flashback (nor downgrade) your Oracle Database.

When you flashback your database, I recommend that you leave your Grid Infrastructure at the new release. Don’t downgrade Grid Infrastructure as well. Ideally, in a previous maintenance window, you upgraded Grid Infrastructure to the new release in advance. Thus, you know it can handle the old release of the database. Save yourself the added complexity of also downgrading Grid Infrastructure.

Data Guard broker does not support going back to a previous version. You must disable the broker during flashback and afterward create a new configuration or restore broker configuration files from the old release.

The following works for Data Guard configuration with a physical standby database. Other considerations and a different procedure apply if you have a logical standby database.

Flashback

To flashback a database protected by Data Guard the following applies:

  • You must have a restore point on primary and all standby databases.
  • First, create restore points on standby database, then on primary database. The SCN of the restore points on the standby database must be lower than the SCN of the restore point on the primary database.
  • I recommend using guaranteed restore points when upgrading.
  • Don’t rely on restore point propagation. Manually create the restore points on the standby database.
  • The order of the operation is important: First, handle standby databases (order of standby databases is not important), then primary database.

On YouTube, we have a good introduction to using Flashback Database as fallback. You can also flip through the slides.

How To Flashback With AutoUpgrade

The following assumes:

  • You manually created a restore point on the standby database.
  • AutoUpgrade created a restore point on the primary database (default, controlled by restoration).
  • AutoUpgrade was configured to keep the restore point after upgrade (default, controlled by drop_grp_after_upgrade).
  • $NEW_ORACLE_HOME refers to the release you were upgrading to (e.g. 19c) and $OLD_ORACLE_HOME refers to the release you upgraded from (e.g. 12.1.0.2).

The procedure:

  1. The environment in your session is set to the new Oracle Home.
  2. Stop Data Guard broker on all databases:
    alter system set dg_broker_start=false scope=both sid='*'
    
  3. Stop standby database (all instances, if RAC).
  4. Flashback primary database using AutoUpgrade. nn is the AutoUpgrade job id that executed the upgrade:
    java -jar autoupgrade.jar -config ... -restore -jobs nn
    
    AutoUpgrade handles everything on primary database, like
    • /etc/oratab
    • Grid Infrastructure downgrade
    • SPFile
    • Etc.
  5. Start standby database in mount mode (only one instance, if RAC).
  6. Flashback the standby database:
    flashback database to restore point ...
    
  7. Shut down the standby database.
  8. Downgrade the standby database clusterware configuration. Grid Infrastructure will now start the database in the old Oracle Home (in this example it is 12.1.0.2):
    $NEW_ORACLE_HOME/bin/srvctl \
      downgrade database \
      -db $ORACLE_UNQNAME \
      -oraclehome $OLD_ORACLE_HOME \
      -targetversion 12.1.0.2
    
  9. Start the standby database.
  10. Start Data Guard broker on primary and standby database
    • Either restore broker config files from old release and start Data Guard broker.
    • Or, start Data Guard broker and recreate the configuration.
  11. : Ensure that your Data Guard configuration works. Use validate database command in Data Guard broker on all databases and ensure they are ready for switchover.
  12. Optionally, test your Data Guard by doing a switchover.
  13. Remember to drop the guaranteed restore points on all databases.

That’s it!

Demo

Flashback of a CDB running RAC and Data Guard environment:

Other Blog Posts in This Series

Upgrade and Data Guard

You can upgrade your Oracle Database to a new release and keep the Data Guard setup intact. There is no need to rebuild a physical standby database after the upgrade.

When you upgrade the primary database, many changes go into the data dictionary. These changes are recorded in the redo stream and sent to the standby database. When the standby database applies the redo, it is implicitly upgraded.

Prerequisites

If Grid Infrastructure (GI) manages your Oracle Databases, you must upgrade GI first. Check the Grid Infrastructure Installation and Upgrade Guide for your platform.

You can do it in the same maintenance window as the database upgrade, but I recommend that you perform the GI upgrade in an earlier maintenance window. A newer version of GI can run earlier versions of Oracle Database, so you can safely upgrade GI in advance. Doing so will give you time to adapt to the new GI release.

Also, in advance, you should install the new Oracle Home on both primary and standby hosts. The two Oracle Homes must have the same patches applied, and I recommend that you always apply the latest Release Update and have a look at 555.1 for important one-offs.

How To

When upgrading with Data Guard, there are two approaches:

  1. Standby Offline method
  2. Maximum Availability Architecture (MAA) method

Standby Offline Method

Before the upgrade starts on the primary database, you shut down the standby database. You keep it shut down until the upgrade has completed on the primary database and you have finished your tests. When you are sure you will stay on the new release, the standby database is restarted and synchronized with the primary database. It will take some time before you can go live because the standby database must apply all the redo generated during the upgrade.

If you need to fall back, you can use Flashback Database on the primary database. In addition, no matter what happens to the primary database, you still have the standby database immediately ready in the pre-upgrade state.

My team recommends this method. We prefer to sacrifice a little downtime to achieve even better protection.

MAA Method

The standby database is open and applies redo while the primary database is upgraded. This means that the standby database is closely following the primary database. You can go live very soon after the upgrade completes because there is little or very little apply lag.

The downside is when you must fall back. In that case, you have two databases to bring back in time with Flashback Database. In the very unlikely event that something happens during flashback on both databases, you may need to restore your backup.

The MAA team recommends this method as it guarantees the lowest downtime.

Which One To Choose?

If you have two or more standby databases, you can combine the two methods and get the best of both worlds. Otherwise, rest assured that both methods work fine and are supported.

Standby Offline MAA
Maximum protection Minimum downtime
Upgrade team recommendation MAA recommendation
Redo transport deferred Redo transport enabled
Redo apply stopped Redo apply active
Protected by offline standby and guaranteed restore point Protected by guaranteed restore point
AutoUpgrade default

Of course, AutoUpgrade supports both methods. You can check the other blog post in the series for detailed instructions.

Note, the following implication of using the standby offline method. AutoUpgrade will defer redo log transport to all remote archive destinations. Not only standby databases, but also GoldenGate downstream capture and Real-Time Redo Transport feature of Zero Data Loss Recovery Appliance. Most likely this is not a problem, since the database is in a maintenance window during the upgrade. But remember to enable all of them afterward.

What If

Exadata

If you are running Oracle Database on Exadata, you should read the dedicated procedure created by the Maximum Availability Architecture (MAA) team.

Multiple Standby Databases

Not much changes if you have many standby databases in your Data Guard configuration. The procedure is basically the same, except that you must execute commands on all the standby databases. The order of the standby databases does not matter (unless you have cascaded standby databases – see below).

Data Guard Broker

If you have configured your Data Guard setup using Data Guard broker, then you can leave it running during the upgrade. There used to be some problems with Data Guard broker during upgrade to previous releases, but it works fine when you upgrade to Oracle Database 19c.

However, you must disable Fast-Start Failover before the upgrade. After a successful upgrade, you can enable it again.

Cascaded Standby Databases

If you have cascaded standby databases, the following applies according to the documentation:

If there are cascaded standbys in your configuration, then those cascaded standbys must follow the same rules as any other standby, but should be shut down last, and restarted in the new home first.

You must treat cascaded standby databases like any other standby database. However, the order is now important. Imagine this scenario:

  • Primary database: BOSTON
  • Standby database: CHICAGO
  • Cascaded standby database: NEWYORK

When the procedure tells you to stop standby databases: First CHICAGO, then NEWYORK When the procedure tells you to start standby databases: First NEWYORK, then CHICAGO

Far Sync

A far sync database should be treated like any other standby database. Like cascaded standby databases the order of the shutdown is important to ensure that all redo from primary reaches the standby database connected via the far sync.

Logical Standby

When you have logical standby databases in your Data Guard configuration, things are slightly different. In that case, look in the documentation.

Database Services in OCI

You need to follow the documentation for your particular database service. If you have an Exadata Cloud Service, you might find Exadata Cloud Database 19c Rolling Upgrade With DBMS_ROLLING (Doc ID 2832235.1) interesting.

Other Blog Posts in This Series

Do I Need To Disable the Scheduler During Upgrade?

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.

12.46 STARTUP, User’s Guide and Reference 19c