You can upgrade your database to a new release, and keep the data guard setup intact. The standby database(s) can be upgraded implicitly via the redo from the primary database, and there is no need to rebuild the standby database after upgrade.
In the following I will be using this setup:
In advance, you should install the new Oracle Home on both primary and standby host. The two Oracle Homes should have the same patches applied, and I recommend that you always apply the latest Release Update.
The procedure starts right before you run the preupgrade fixups. Downtime has started and users are no logged connected to the database.
Disable Data Guard Broker
DGMGRL SYS@PROD1> disable fast_start failover
Next, you disable the broker configuration:
DGMGRL SYS@PROD1> disable configuration
Then, you can shut down the broker in the primary. Make a copy of the broker configuration files. Use the below SQL to generate commands to copy the files. Remember to execute the commands generated:
PROD1 SQL> alter system set dg_broker_start=false scope=both; PROD1 SQL> select 'host cp ' || value || ' /tmp' as cmd from v$parameter where name like 'dg_broker_config_file%'; PROD1 SQL> --Now, execute the commands PROD1 SQL> host ls /tmp/dr*.dat
Finally, you do the same for the standby database:
PROD2 SQL> alter system set dg_broker_start=false scope=both; PROD2 SQL> select 'host cp ' || value || ' /tmp' as cmd from v$parameter where name like 'dg_broker_config_file%'; PROD1 SQL> --Now, execute the commands PROD2 SQL> host ls /tmp/dr*.dat
Stop Data Guard
On the primary database, defer the redo log transport to the standby database. Strictly speaking, this is not necessary, but I do it from a "better-safe-than-sorry" aspect. Be sure to verify that log_archive_dest_state_2 is the actual archive destination for your standby database:
PROD1 SQL> show parameter log_archive_dest_2 PROD1 SQL> alter system set log_archive_dest_state_2='defer' scope=both;
Next, you cancel redo apply on the standby database:
PROD2 SQL> alter database recover managed standby database cancel;
Finally, you shut down the database:
PROD2 SQL> shutdown immediate
If you are using Grid Infrastructure (GI) to manage the database, you should stop and disable the database. Disabling the database is strictly speaking not necessary, but again a "better-safe-than-sorry" approach:
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl stop database -d PROD2 [oracle@bm2]$ $ORACLE_HOME/bin/srvctl disable database -d PROD2
Now you can upgrade the primary database using the method you prefer. Complete all the post-upgrade tasks and perform the necessary tests to validate the new database release.
If something happens during upgrade and you want to revert, you can flash back the database (or restore on Standard Edition) and simply undo the before upgrade steps (start by enabling database, starting database, starting redo apply and so forth).
Remember that the standby databases was left behind before we started touching anything, so if all other fails, simply restart the standby database, and connect your users to it.
Restart Data Guard
When you are happy with the upgrade, and your tests validate the new database release, you can proceed.
Update the listener on the standby host. Be sure to update the Oracle Home information in the listener.ora entry. Note, that your listener.ora might be stored in a non-default location, so use
lsnrctl status to get the location. Finally, reload the listener:
[grid@bm2]$ $GRID_HOME/bin/lsnrctl status [grid@bm2]$ vi $GRID_HOME/network/admin/listener.ora [grid@bm2]$ $GRID_HOME/bin/lsnrctl reload
For the next commands, I will be used the same prompt, and I will need the following environment variables:
[oracle@bm2]$ export OLD_HOME=/u01/app/oracle/product/126.96.36.199/dbhome_1 [oracle@bm2]$ export NEW_HOME=/u01/app/oracle/product/188.8.131.52/dbhome_1 [oracle@bm2]$ export ORACLE_HOME=$NEW_HOME [oracle@bm2]$ export ORACLE_SID=PROD [oracle@bm2]$ #Set ORACLE_UNQNAME to DB_UNIQUE_NAME [oracle@bm2]$ export ORACLE_UNQNAME=PROD2
Next, if the standby database is using TNS_ADMIN in the default location ($ORACLE_HOME/network/admin), then be sure to copy the relevant TNS aliases into the new tnsnames.ora. There should be TNS aliases to the primary and standby database. Or, if there are no other databases in the same Oracle Home, you can simply copy the files:
[oracle@bm2]$ #Back up files [oracle@bm2]$ cp $NEW_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin/sqlnet.ora.backup [oracle@bm2]$ cp $NEW_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin/tnsnames.ora.backup [oracle@bm2]$ #Copy from old to new home [oracle@bm2]$ cp $OLD_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin [oracle@bm2]$ cp $OLD_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin
Now, you can edit /etc/oratab and update the information about the Oracle Home to match the new Oracle Home:
[oracle@bm2]$ vi /etc/oratab
Copy SPFile and password file to the new Oracle Home:
[oracle@bm2]$ cp $OLD_HOME/dbs/orapw$ORACLE_SID $ORACLE_HOME/dbs [oracle@bm2]$ cp $OLD_HOME/dbs/spfile$ORACLE_SID.ora $ORACLE_HOME/dbs
If you are using GI to manage the database, you must upgrade the database, meaning updating the Oracle Home information, so GI will start the database in the correct Oracle Home. Next, re-enable and start the database:
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl upgrade database -db $ORACLE_UNQNAME -oraclehome $ORACLE_HOME [oracle@bm2]$ $ORACLE_HOME/bin/srvctl enable database -d $ORACLE_UNQNAME [oracle@bm2]$ $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME
Or, if you are not using GI, simply start the database:
PROD2 SQL> startup mount
Re-enable Redo Log Transport and Apply
On the primary database re-enable redo log transport to standby database:
PROD1 SQL> alter system set log_archive_dest_state_2='enable' scope=both;
On the standby database restart redo apply
PROD2 SQL> alter database recover managed standby database disconnect from session;
Re-enable Data Guard Broker
First, we need to copy the broker config files into the new Oracle Home. If you store your broker config files outside of the Oracle Home this might not be necessary to you:
[oracle@bm1]$ export ORACLE_HOME=/u01/app/oracle/product/184.108.40.206/dbhome_1 [oracle@bm1]$ export ORACLE_UNQNAME=PROD1 [oracle@bm1]$ cp /tmp/dr1$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs [oracle@bm1]$ cp /tmp/dr2$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
Do the same on the standby database host:
[oracle@bm2]$ export ORACLE_HOME=/u01/app/oracle/product/220.127.116.11/dbhome_1 [oracle@bm2]$ export ORACLE_UNQNAME=PROD2 [oracle@bm2]$ cp /tmp/dr1$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs [oracle@bm2]$ cp /tmp/dr2$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
Now, you can restart the Data Guard Broker on both primary and standby database:
PROD1 SQL> alter system set dg_broker_start=true scope=both; PROD2 SQL> alter system set dg_broker_start=true scope=both;
Finally, enable the broker configuration and fast start failover:
DGMGRL SYS@PROD1> show configuration DGMGRL SYS@PROD1> enable configuration DGMGRL SYS@PROD1> enable fast_start failover
Use the broker to ensure everything is fine:
DGMGRL SYS@PROD1> show configuration DGMGRL SYS@PROD1> show database prod1 DGMGRL SYS@PROD1> show database prod2
You should have
SUCCESS listed for both databases
Let’s try to make a switchover:
DGMGRL SYS@PROD1> switchover to prod2
If you don’t use Data Guard Broker, you use regular SQLs and SQLPlus to verify the data guard environment.
It is actually not that complicated to upgrade your database, even if it is part of a data guard setup. A little extra legwork is needed to take care of the standby database. But the good thing is that your DR setup is maintained althroughout the process.
Keep an eye out for coming versions of AutoUpgrade. At time of writing our developers are working on streamlining the process. We want upgrade with data guard to be 100 % automated (or as close to as possible).
Patching, Upgrading, and Downgrading Databases in an Oracle Data Guard Configuration – Oracle Database 19c Data Guard Concepts and Administration
Oracle Data Guard Broker Upgrading and Downgrading – Oracle Database 19c Data Guard Broker
When you upgrade, disable the Data Guard Broker – Mike Dietrich blog post
Oracle Data Guard Command-Line Interface (DGMGRL) Reference – Oracle Database 19c Data Guard Broker