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
[oracle@ol8-19-dg1 ~]$ 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:
[oracle@ol8-19-dg1 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:
[oracle@ol8-19-dg2 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
[oracle@ol8-19-dg2 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
[oracle@ol8-19-dg2 scripts]$ source setEnv_19-11.sh
[oracle@ol8-19-dg2 scripts]$
[oracle@ol8-19-dg2 scripts]$
[oracle@ol8-19-dg2 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
[oracle@ol8-19-dg2 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
[oracle@ol8-19-dg2 scripts]$ cd $ORACLE_HOME/OPatch
[oracle@ol8-19-dg2 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.
[oracle@ol8-19-dg1 ~]$ 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>
Another Oracle blog 