In this post, I am sharing my notes on upgrading Oracle Standby database. Following are the steps I preformed on standby database while upgrading from 12.1 to 19.5 but these can be used for upgrading any standby database version.
I hope you find this helpful and good luck with your upgrade. 😃
At high level, you don’t upgrade standby database. You just need to start it in mount state with 19.5 software. And if you have data guard broker configured, then you need to disable it for upgrade. The standby database will upgrade as it receives redo logs from primary database.
On standby database copy following files from 12.1 Oracle home to 19.
- password file
- data guard broker files
- sqlnet.ora file
- tnsnames.ora file
cp /oracle/product/12.1.0/dbhome_1/dbs/spfilestdby.ora /oracle/product/19/dbhome_1/dbs/ cp /oracle/product/12.1.0/dbhome_1/dbs/orapwstdby /oracle/product/19/dbhome_1/dbs/ cp /oracle/product/12.1.0/dbhome_1/dbs/dr1stdby.dat /oracle/product/19/dbhome_1/dbs/ cp /oracle/product/12.1.0/dbhome_1/dbs/dr2stdby.dat /oracle/product/19/dbhome_1/dbs/ cp /oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora /oracle/product/19/dbhome_1/network/admin/ cp /oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora /oracle/product/19/dbhome_1/network/admin/
Disable Data Guard Broker
Now we need to disable data guard broker (if configured)
dgmgrl sys@prmy show configuration; DISABLE CONFIGURATION;
Run the following alter statement on both, primary and standby database.
ALTER SYSTEM SET DG_BROKER_START=FALSE;
On primary database, remove the net_timeout setting from log_archive_dest_2 and verify the primary database has specified db_unique_name of the standby database in the log_archive_dest_n parameter setting as follows:
SYS@CDB$ROOT>select name, value from v$parameter where name in ('log_archive_dest_2'); NAME VALUE _____________________ ____________________________________________________________________________________________________________________________________________________________________________________________________ log_archive_dest_2 service="stdby", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="stdby" net_timeout=30, valid_for=(online_logfile,all_roles) 1 rows selected. --set the parameter on PRIMARY DATABASE without net_timeout alter system set log_archive_dest_2='service="stdby", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="stdby", valid_for=(online_logfile,all_roles)' scope=both;
Next, flush all redo to the standby database using the following command. Monitor the alert.log of the standby database to verify for the ‘End-of-Redo‘ message.
--Startup Primary in MOUNT startup mount; alter system flush redo to 'stdby'; --In the Standby Alert log: Standby switchover readiness check: Checking whether recovery applied all redo.. Physical Standby applied all the redo from the primary.
Start Standby database from 19c home in mount state
srvctl stop database -db stdby Set env to 19c Home --Update the OCR configuration for the standby database by running the 'srvctl upgrade' command from the new database home as follow: cd /oracle/product/19/dbhome_1/bin srvctl upgrade database -d stdby -o /oracle/product/19/dbhome_1 --Start the standby as follows (add -o mount option for database running Active Data Guard): srvctl start database -d stdby -o mount --Start MRP (Managed Recovery Process): ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Upgrade Primary Database
Now we can upgrade primary database. I will not be listing details of upgrading primary database as this is not the scope of this blog.
Active Data Guard
After successful upgrade of primary database, standby database will also get upgraded. If you have ADG license, then wait until primary and standby database redo are in sync before opening the standby database in read-only mode.
alter database open read only;
Enable Data Guard Broker
As a last step, enable DG broker. Run the following on both primary and standby databases
ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;
Enable data guard broker.
dgmgrl sys@prmy show configuration; enable configuration;