This is a deprecated blog post. It is kept for reference only. Please visit the updated blog post series.
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.
The process:
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.
Before Upgrade
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
If you don’t use Data Guard Broker, you can skip this chapter and go to the chapter Stop Data Guard. Connect to the broker and disable Fast Start Failover:
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
Upgrade
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.
After Upgrade
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/18.0.0.0/dbhome_1
[oracle@bm2]$ export NEW_HOME=/u01/app/oracle/product/19.0.0.0/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 modify database -db $ORACLE_UNQNAME -startoption MOUNT -role PHYSICAL_STANDBY
[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/19.0.0.0/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/19.0.0.0/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
Test
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.
Conclusion
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.
I made a video on YouTube that shows the procedure. And while you are there, I suggest that you subscribe to our channel.
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).
Further Reading
-
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
Databases Are Fun 
Thank you! I’ve been looking for exactly this information – nice clear walkthrough and got my data guard upgrade going in no time.
LikeLiked by 1 person
Hi Charlotte,
I am glad that you liked it and found it useful. And very well done on upgrading the data guard environment. Hopefully, to Oracle Database 19c.
Regards,
Daniel
LikeLike
Thank you!!! This was very helpful!
LikeLike
Hi Eric,
I am glad you found it helpful. Happy upgrading! 🙂
Regards,
Daniel
LikeLike
Daniel, Thank you, very helpful!
I moved the Observer to a new host and I noticed that the Observer still display the old host name “A”.
I am running Oracle Active Data Guard 12.1.0.on Windows, if I want to move the Observer to a new host, from Host “A” to host “B”, do I need to re-Create the Dataguard Broker?
These were the steps I took to run the Observer from the new host “B”:
1. Stopped Observer from old host “A”
2. Shutdown old host “A”
3. Create a schedule Tasks job on new host “B” like this
E:\oracle\product\12.1.0\dbhome_1\BIN\dgmgrl -logfile E:\oracle\admin\observer\orcl1\orcl1_obs.log -silent sys/my_password@orcl1_prim “start observer file=’E:\oracle\admin\observer\orcl1\fsfo_orcl1.dat'”
4. Started the Observer from new host “B” (Observer started – no issues)
5. I connected to DGMGRL
6. Ran show database verbose orcl1; show configuration verbose; show fast_start failover; and I noticed that the Observer display the old host name “A”
Fast-Start Failover: ENABLED
Target: orcl1_stby
Observer: old_host-“A” <===
I tested a switchover and no issues. I shuted down the primary server and failed over to the standby no issues, so the Observer on the new host “B” is monitoring the database, but still show the old host “A” even that is down
Any ideas? how can I tell the Observer that the host changed?
LikeLike
Hi Jorge,
That does sound like a bug to me. I haven’t heard of that before, however, I don’t touch Data Guard issues that much. I quickly did a search and nothing came up.
Have you tried “show observer” command? Otherwise, you might need to recreate the Data Guard broker config:
Step By Step How to Recreate Dataguard Broker Configuration (Doc ID 808783.1)
But first, create a SR with support.
Regards,
Daniel
LikeLike
It looks like I have to recreate the broker. very interesting!
Thanks
LikeLike
I will be interested to know the rollback for after the Primary and DR had been upgraded.
LikeLike
Hi Edwin,
We have a webinar coming up on November 11 where we cover all sorts of fallback situations, including rolling back an upgrade on a RAC database with Data Guard. You should attend the webinar. 🙂
https://dohdatabase.com/webinars/
Regards,
Daniel
LikeLike