Rolling upgrade with Transient Logical Standby is known as a MAA (Maximum Availability Architecture) technique, to minimize downtime during upgrade of Oracle database.
Approaches:
There are several methods which can be used:
- Manually from command line
- Manually using phsyru.sh script which is available since 11g (Doc ID 949322.1)
- Use DBMS_ROLLING package which is available since 12.1.0.1 (Doc ID 2086512.1)
Note: for both manual methods Data Guard Broker needs to be turned off. For DBMS_ROLLING approach you can leave it running and drive the rolling upgrade process semi-automatic.
In this post I will use DBMS_ROLLING approach with Data Broker setup.
This is the graphical representation of the whole process:
On a high level, these are the steps depicted from the above graph:
- Create the guaranteed restore point
- Build the logminer dictionary
- Convert the physical standby to a logical standby
- Upgrade the logical standby
- Start the apply again and let it recover
- Switchover the primary database to the Logical standby, at that point, you are upgraded already!
- The old primary is now a logical standby, so it needs to be flashed back
- Then converted to a physical standby.
- Due to that conversion, the redo (with the upgrade info in) is sent to the old primary, the new physical standby
- Finally, a switchover again to put the primary back in place
DBMS_ROLLING package will do the following actions:
- Prerequisite Phase
- init_plan
- build_plan
- start_plan
- upgrade (or do what you want with the Transient logical standby)
- switchover
- Restart the standby
- finish_plan
I have the following setup prepared for this demo:
The dataguard configuration is is MAXIMUM AVAILABILITY mode
[[email protected] ~]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 21 07:26:57 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "orcl_dga" Connected as SYSDG. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxAvailability Members: orcl_dga - Primary database orcl_dgb - Physical standby database orcl_dgc - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 52 seconds ago) DGMGRL>
This is an out of place upgrade which means I will do the upgrade on a different ORACLE_HOME (but on the same server). This is the plan for the upgrade for all 3 nodes:
NO DOWNTIME:
- On all 3 nodes: create the necessary folder for the the ORACLE_HOME which will be used for this upgrade
- On primary: clone the existing ORACLE_HOME using golden image way
- On primary: unzip the golden image into the new ORACLE_HOME
- On primary: install the golden image
- On primary: upgrade the new ORACLE_HOME
- On primary: clone the new ORACLE_HOME to be ready to ship to the standby nodes
- On primary: take a full backup of the database
- On primary: Ship from PRIMARY node to both standby nodes the new clone
- On standbys: unzip the new clone in the new ORACLE_HOME folder
- On standbys: install the golden image
- Start DBMS_ROLLING upgrade procedure:
- Prerequisite Phase
- init_plan
- build_plan
- start_plan
- upgrade (or do what you want with the Transient logical standby)
DOWNTIME
- switchover
NO DOWNTIME
- Restart the standby
- finish_plan
As you can see, there is very little downtime using this transient logical standby method.
There are 2 patches that I will deploy on the new ORACLE_HOME:
- p32545013_190000_Linux-x86-64.zip (19.11.0.0)
- p6880880_190000_Linux-x86-64.zip (latest 19.0.0.0 Opatch version)
Let’s start with the upgrade:
Step 1:
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_2
Step 2, 3 ,4 :
All these steps are explained in detail in this post: How to clone ORACLE_HOME in 19c
Step 5:
cd $ORACLE_HOME mv OPatch OPatch_BCK unzip p32545013_190000_Linux-x86-64.zip -d $ORACLE_HOME cd /patch_location/ unzip p32545013_190000_Linux-x86-64.zip cd 32545013 $ORACLE_HOME/OPatch/opatch apply
Check the patch version now:
[[email protected] scripts]$ $ORACLE_HOME/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.25 Copyright (c) 2021, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_2 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.0.0/dbhome_2/oraInst.loc OPatch version : 12.2.0.1.25 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/opatch2021-06-21_08-21-48AM_1.log Lsinventory Output file location : /u01/app/oracle/product/19.0.0/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2021-06-21_08-21-48AM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: ol8-19-dg1.local ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 19c 19.0.0.0.0 There are 1 products installed in this Oracle Home. Interim patches (2) : Patch 32545013 : applied on Thu Jun 17 09:12:39 UTC 2021 Unique Patch ID: 24175065 Patch description: "Database Release Update : 19.11.0.0.210420 (32545013)"
Step 6
All these steps are explained in detail in this post: How to clone ORACLE_HOME in 19c
Step 7
I’m using the following basic script:
#!/bin/bash
INST=ORCL
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
LEVEL=0
TODAY=`date "+%Y%m%d"`
RMAN_LOCATION=/u01/oracle_db_backups
RMAN_LOCATION=$RMAN_LOCATION"/"$INST_$TODAY
mkdir -p $RMAN_LOCATION
mkdir -p $RMAN_LOCATION/LOG
export PATH=$ORACLE_HOME/bin:$PATH
fullBackup () {
rman log=$RMAN_LOCATION/LOG/RMANFULLincr.log << EOF
connect target /
set echo on;
run {
ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch02 DEVICE TYPE DISK;
configure controlfile autobackup format for device type DISK to '${RMAN_LOCATION}/${INST}_CONTROLFILE_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_orcl.f';
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
#CONFIGURE ENCRYPTION FOR DATABASE OFF;
backup
incremental level 0 check logical database
filesperset 1
tag 'orcl_lvl_0_${TODAY}'
format '${RMAN_LOCATION}/${INST}_${TODAY}_df_%s_%p_%t';
release channel ch01;
release channel ch02;
}
run {
ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch02 DEVICE TYPE DISK;
crosscheck archivelog all;
backup check logical
filesperset 1
tag 'ARCH_${INST}_${TODAY}'
format '${RMAN_LOCATION}/${INST}_${TODAY}_al_%s_%p_%t'
archivelog all not backed up 1 times;
backup check logical
filesperset 1
tag 'ARCH_${INST}_${TODAY}'
format '${RMAN_LOCATION}/${INST}_${TODAY}_al_%s_%p_%t'
archivelog all not backed up 1 times;
release channel ch01;
release channel ch02;
}
sql 'alter system archive log current';
exit
EOF
}
# Main
fullBackup
Step 8
scp /u01/19.3_goldimage/db_home_2021-06-17_10-01-12AM.zip ol8-19-dg2.local:/u01/19.3_goldimage/ scp /u01/19.3_goldimage/db_home_2021-06-17_10-01-12AM.zip ol8-19-dg3.local:/u01/19.3_goldimage/
Step 9
cd /u01/app/oracle/product/19.0.0/dbhome_2 unzip -oq /u01/19.3_goldimage/db_home_2021-06-17_10-01-12AM.zip
Step 10
${ORACLE_HOME}/runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=${ORA_INVENTORY} \
SELECTED_LANGUAGES=${ORA_LANGUAGES} \
ORACLE_HOME=${ORACLE_HOME} \
ORACLE_BASE=${ORACLE_BASE} \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
Step 11 Start of the rolling upgrade procedure
Step 12
SQL> select * from DBA_ROLLING_UNSUPPORTED; no rows selected SQL>
Step 13
SQL> exec dbms_rolling.init_plan('orcl_dgb');
PL/SQL procedure successfully completed.
SQL> set lin 400
col name format a50
col scope format a20
col curval format a10
select scope, name, curval from dba_rolling_parameters order by scope, name;
Step 14
SQL> exec dbms_rolling.build_plan; PL/SQL procedure successfully completed. SQL> SQL> set lin 300 col instid format 999 col target format a20 col phase format a30 col description format a80 SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN;
Step 15
SQL> set timing on exec dbms_rolling.start_planSQL> PL/SQL procedure successfully completed. Elapsed: 00:05:58.75 SQL>
This is how the dgbroker is reporting the status:
DGMGRL> show configuration;
Configuration - my_dg_config
Protection Mode: MaxAvailability
Members:
orcl_dga - Primary database
orcl_dgb - Transient logical standby database
orcl_dgc - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
ROLLING DATABASE MAINTENANCE IN PROGRESS
DGMGRL>
Step 16
Let’s start with the upgrade. Since the upgrade of the binaries were done in the beginning of our process, we only have to proceed with datapatch.
First we stop the listener and the logical standby database:
[[email protected] scripts]$ lsnrctl stop LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-JUN-2021 11:58:39 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol8-19-dg2.local)(PORT=1521))) The command completed successfully [[email protected] scripts]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 21 11:58:44 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit
We source the profile for the new oracle home and start back the listener and oracle instance
[[email protected] scripts]$ source setEnv_19-11.sh [[email protected] scripts]$ [[email protected] scripts]$ [[email protected] scripts]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-JUN-2021 12:02:00 Copyright (c) 1991, 2021, Oracle. All rights reserved. Starting /u01/app/oracle/product/19.0.0/dbhome_2/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19.0.0/dbhome_2/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/ol8-19-dg2/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol8-19-dg2.local)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol8-19-dg2.local)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 21-JUN-2021 12:02:00 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_2/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ol8-19-dg2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol8-19-dg2.local)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl_dgb" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "orcl_dgb.world" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "orcl_dgb_DGMGRL.world" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [[email protected] scripts]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 21 12:02:05 2021 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1610609192 bytes Fixed Size 8897064 bytes Variable Size 385875968 bytes Database Buffers 1207959552 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- LOGICAL STANDBY READ WRITE SQL>
We need to be sure that the logical standby is in SYNC with PRIMARY
SQL> alter database start logical standby apply immediate; Database altered. SQL>SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered. COL LATEST_TIME FOR A30 set lin 400 COL APPLIED_TIME FOR A30 COL MINING_TIME FOR A30 SELECT LATEST_TIME, APPLIED_TIME, MINING_TIME, (LATEST_TIME-APPLIED_TIME)*24*3600 GAP_IN_SECOND FROM V$LOGSTDBY_PROGRESS; LATEST_TIME APPLIED_TIME MINING_TIME GAP_IN_SECOND ------------------------------ ------------------------------ ------------------------------ ------------- 21-JUN-2021 12:10:01 21-JUN-2021 12:10:00 21-JUN-2021 12:10:00 1 SQL>
It’s in SYNC. Let’s continue with datapatch
[[email protected] scripts]$ cd $ORACLE_HOME/OPatch [[email protected] OPatch]$ ./datapatch -verbose
As the LOGICAL STANDBY has been patched, we need to make this LOGICAL STANDBY to become PRIMARY in order to upgrade this instance as well
SQL> exec dbms_rolling.switchover; PL/SQL procedure successfully completed. SQL> SQL>
Now my previous PRIMARY became LOGICAL STANDBY and it’s open READ/WRITE mode. We need to mount it and finish the plan.
[[email protected] ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 22 10:22:06 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> startup mount force; ORACLE instance started. Total System Global Area 1610609200 bytes Fixed Size 8897072 bytes Variable Size 385875968 bytes Database Buffers 1207959552 bytes Redo Buffers 7876608 bytes Database mounted. SQL>
Now execute finish plan from the NEW primary database:
exec dbms_rolling.finish_plan;
Now we need to stop both STANDBYs and change the ORACLE_HOME from the old to the new one and mount back the standbys and that’s pretty much it:
DGMGRL> show configuration;
Configuration - my_dg_config
Protection Mode: MaxAvailability
Members:
orcl_dgb - Primary database
orcl_dga - Physical standby database
orcl_dgc - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 57 seconds ago)
DGMGRL>
