Rolling Database Upgrade from 19.3.0.0 to 19.11.0.0 Using Transient Logical Standby

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:

  1. Create the guaranteed restore point
  2. Build the logminer dictionary
  3. Convert the physical standby to a logical standby
  4. Upgrade the logical standby
  5. Start the apply again and let it recover
  6. Switchover the primary database to the Logical standby, at that point, you are upgraded already!
  7. The old primary is now a logical standby, so it needs to be flashed back 
  8. Then converted to a physical standby.
  9. Due to that conversion, the redo (with the upgrade info in) is sent to the old primary, the new physical standby
  10. Finally, a switchover again to put the primary back in place 

DBMS_ROLLING package will do the following actions:

  1. Prerequisite Phase
  2. init_plan
  3. build_plan
  4. start_plan
  5. upgrade (or do what you want with the Transient logical standby)
  6. switchover
  7. Restart the standby
  8. 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:

  1. On all 3 nodes: create the necessary folder for the the ORACLE_HOME which will be used for this upgrade
  2. On primary: clone the existing ORACLE_HOME using golden image way
  3. On primary: unzip the golden image into the new ORACLE_HOME
  4. On primary: install the golden image
  5. On primary: upgrade the new ORACLE_HOME
  6. On primary: clone the new ORACLE_HOME to be ready to ship to the standby nodes
  7. On primary: take a full backup of the database
  8. On primary: Ship from PRIMARY node to both standby nodes the new clone
  9. On standbys: unzip the new clone in the new ORACLE_HOME folder
  10. On standbys: install the golden image
  11. Start DBMS_ROLLING upgrade procedure:
  12. Prerequisite Phase
  13. init_plan
  14. build_plan
  15. start_plan
  16. upgrade (or do what you want with the Transient logical standby)

DOWNTIME

  1. switchover

NO DOWNTIME

  1. Restart the standby
  2. 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:

  1. p32545013_190000_Linux-x86-64.zip (19.11.0.0)
  2. 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>

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.