Here we will be seeing how to upgrade you standby database from 12c to 19c without rebuilding the standby database. 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.
Primary Database Details:
DB_NAME=MYDB, DB_UNIQUE_NAME=MYDB1, ServerName=ed-olraclin1
Standby Database Details:
DB_NMAE=MYDB, DB_UNIQUE_NAME=MYDB2, ServerName=ed-olraclin2
1. Install the new Oracle Home on both primary and standby host. The two Oracle Homes should have the same patches applied with the latest Release Update RU.
2. Before Upgrade on the Standby database (MYDB2), run the preupgrade.jar and the preupgrade fixup SQL script
SYNTAX USAGE: 12c_release_Oracle_home/jdk/bin/java -jar $19c_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]
#java -jar /u01/app/oracle/product/19.9.0.0/mydb_home/rdbms/admin/preupgrade.jar TERMINAL TEXT
3. Disable Dataguard broker.
If you are not using the broker then use the manual method to disable the dataguard log shipping from primary to standby. Also disable the broker configuration.
DGMGRL SYS@MYDB1> disable fast_start failover;
DGMGRL SYS@MYDB1> 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:
MYDB1 SQL> alter system set dg_broker_start=false scope=both;
MYDB1 SQL> select 'host cp ' || value || ' /tmp' as cmd from v$parameter where name like 'dg_broker_config_file%';
MYDB1 SQL> --Now, execute the commands
MYDB1 SQL> host ls /tmp/dr*.dat
Now Stop Data Guard: On the primary database, defer the redo log transport to the standby database. This is not necessary, but can be done. Be sure to verify that log_archive_dest_state_2 is the actual archive destination for your standby database
MYDB1 SQL> show parameter log_archive_dest_2
MYDB1 SQL> alter system set log_archive_dest_state_2='defer' scope=both;
Next, you cancel redo apply on the standby database:
MYDB2 SQL> alter database recover managed standby database cancel;
Finally, you shut down the database:
MYDB2 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 not must but can be done.
[oracle@ed-olraclin2]$ $ORACLE_HOME/bin/srvctl stop database -d MYDB2
[oracle@ed-olraclin2]$ $ORACLE_HOME/bin/srvctl disable database -d MYDB2
Upgrade the primary db to 19c:
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 and simply undo the before upgrade stegps (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@ed-olraclin2]$ $GRID_HOME/bin/lsnrctl status
[grid@ed-olraclin2]$ vi $GRID_HOME/network/admin/listener.ora
[grid@ed-olraclin2]$ $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@ed-olraclin2]$ export OLD_HOME=/u01/app/oracle/MYDBuct/18.0.0.0/dbhome_1
[oracle@ed-olraclin2]$ export NEW_HOME=/u01/app/oracle/MYDBuct/19.0.0.0/dbhome_1
[oracle@ed-olraclin2]$ export ORACLE_HOME=$NEW_HOME
[oracle@ed-olraclin2]$ export ORACLE_SID=MYDB
[oracle@ed-olraclin2]$ #Set ORACLE_UNQNAME to DB_UNIQUE_NAME
[oracle@ed-olraclin2]$ export ORACLE_UNQNAME=MYDB2
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@ed-olraclin2]$ #Back up files
[oracle@ed-olraclin2]$ cp $NEW_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin/sqlnet.ora.backup
[oracle@ed-olraclin2]$ cp $NEW_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin/tnsnames.ora.backup
[oracle@ed-olraclin2]$ #Copy from old to new home
[oracle@ed-olraclin2]$ cp $OLD_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin
[oracle@ed-olraclin2]$ 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@ed-olraclin2]$ vi /etc/oratab
Copy SPFile and password file to the new Oracle Home:
[oracle@ed-olraclin2]$ cp $OLD_HOME/dbs/orapw$ORACLE_SID $ORACLE_HOME/dbs
[oracle@ed-olraclin2]$ 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@ed-olraclin2]$ $ORACLE_HOME/bin/srvctl upgrade database -db $ORACLE_UNQNAME -oraclehome $ORACLE_HOME
[oracle@ed-olraclin2]$ $ORACLE_HOME/bin/srvctl modify database -db $ORACLE_UNQNAME -startoption MOUNT -role PHYSICAL_STANDBY
[oracle@ed-olraclin2]$ $ORACLE_HOME/bin/srvctl enable database -d $ORACLE_UNQNAME
[oracle@ed-olraclin2]$ $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME
Or, if you are not using GI, simply start the database:
MYDB2 SQL> startup mount
Re-enable Redo Log Transport and Apply
On the primary database re-enable redo log transport to standby database:
MYDB1 SQL> alter system set log_archive_dest_state_2='enable' scope=both;
On the standby database restart redo apply
MYDB2 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@ed-olraclin1]$ export ORACLE_HOME=/u01/app/oracle/MYDBuct/19.0.0.0/dbhome_1
[oracle@ed-olraclin1]$ export ORACLE_UNQNAME=MYDB1
[oracle@ed-olraclin1]$ cp /tmp/dr1$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
[oracle@ed-olraclin1]$ cp /tmp/dr2$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
Do the same on the standby database host:
[oracle@ed-olraclin2]$ export ORACLE_HOME=/u01/app/oracle/MYDBuct/19.0.0.0/dbhome_1
[oracle@ed-olraclin2]$ export ORACLE_UNQNAME=MYDB2
[oracle@ed-olraclin2]$ cp /tmp/dr1$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
[oracle@ed-olraclin2]$ cp /tmp/dr2$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
Now, you can restart the Data Guard Broker on both primary and standby database:
MYDB1 SQL> alter system set dg_broker_start=true scope=both;
MYDB2 SQL> alter system set dg_broker_start=true scope=both;
Finally, enable the broker configuration and fast start failover:
DGMGRL SYS@MYDB1> show configuration
DGMGRL SYS@MYDB1> enable configuration
DGMGRL SYS@MYDB1> enable fast_start failover
Test: Use the broker to ensure everything is fine:
DGMGRL SYS@MYDB1> show configuration
DGMGRL SYS@MYDB1> show database MYDB1
DGMGRL SYS@MYDB1> show database MYDB2
You should have SUCCESS listed for both databases.
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 throughout the process.
