In this part of the series, we will upgrade the Oracle Physical Standby Database from 18c to 19c. This is the fourth part of a 7-part series that includes:
Part 1. Upgrade Oracle Grid Infrastructure on the RAC Cluster from 18c to 19c
Part 2. Upgrading the Oracle Grid Infrastructure on the Physical Standby from 18c to 19c
Part 3. Upgrade Oracle RAC Database from 18c to 19c
Part 4. Upgrade Oracle Physical Standby Database from 18c to 19c
Part 5. Installing the Latest Oracle 19c Update Patches – Opatch/OJVM/GI/DB/JDK on Oracle 19c RAC on Linux
For the purpose of this guide, note the following:
-To make following this guide easier, put all your downloaded software in /usr/software/oracle.
-GI stands for Grid Infrastructure, also known as Oracle Restart for Standalone Databases.
-DB stands for Database.
-DB Home owner is Linux OS user “oracle”.
-OS Version: Oracle Linux Server release 7.5
-Source GI/DB Version: 18.14.0.0
-Target GI/DB Version: 19.3.0.0
-Source 18c DB Home: /oracle/oraBase/oracle/18.0.0/database.
-Target 19c DB Home: /oracle/oraBase/oracle/19.0.0/database.
-GI Home owner is Linux OS user “grid”.
-Source 18c GI Home: /oracle/oraBase/18.0.0/grid.
-Target 19c GI Home: /oracle/oraBase/19.0.0/grid.
-DG stands for Data Guard.
-ALWAYS make a backup of the database and the oracle software before you start.
Primary RAC DB Name: PROD
RAC Node 1: rac1
SID: PROD1
RAC Node 2: rac2
SID: PROD2
—
Standby DB Name: PRODDR
Physical Standby Node: stdby1
SID: PRODDR1
1. Prerequisites and Preparations
1.1 Download the following software:
–oracle-database-preinstall-19c for Linux 7
-The latest OPatch from Oracle Support
-The latest AutoUpgrade Tool (Doc ID 2485457.1)
-The latest 19c Update Patches
-The latest JDK for GI and DB Homes
Note: The JDK is for GI and DB Homes, not to be confused with the JDK that is installed directly on the OS. That is not covered here.
1.2 Create the DB 19c Home Directories.
mkdir -p /oracle/oraBase/oracle/19.0.0/database
chmod -R 775 /oracle/oraBase/oracle/19.0.0
chown -R oracle.oinstall /oracle/oraBase/oracle/19.0.0
1.3 Extract the DB software into the 19c DB Homes.
su - oracle
cd /oracle/oraBase/oracle/19.0.0/database
unzip -oq /usr/software/oracle/LINUX.X64_193000_db_home.zip
1.4 Create a response file in /usr/software/oracle/19c_db_ee.rsp. Here’s my response file. Edit it to reflect your environment.
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/oracle/oraBase/oraInventory
ORACLE_BASE=/oracle/oraBase/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=false
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.ConfigureAsContainerDB=false
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.managementOption=DEFAULT
oracle.install.db.config.starterdb.omsPort=0
oracle.install.db.config.starterdb.enableRecovery=false
1.5 Create a Guaranteed Restore Point (GRP) before we upgrade the Standby.
[oracle@stdby1 oracle]$ srvctl start db -d PRODDR
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> create restore point before_upgrade_rp guarantee flashback database;
Restore point created.
1.6 Stop the Standby Database.
[oracle@stdby1 oracle]$ srvctl status db -d PRODDR
Database is running.
[oracle@stdby1 oracle]$ srvctl stop db -d PRODDR
[oracle@stdby1 oracle]$ srvctl status db -d PRODDR
Database is not running.
2 Install the DB Software as oracle user.
[oracle@stdby1 ~]$ cd /oracle/oraBase/oracle/19.0.0/database
[oracle@stdby1 database]$ ./runInstaller -ignorePrereq -waitforcompletion -silent -responseFile /usr/software/oracle/19c_db_ee.rsp
Launching Oracle Database Setup Wizard...
The response file for this session can be found at:
/oracle/oraBase/oracle/19.0.0/database/install/response/db_2024-06-25_02-42-44PM.rsp
You can find the log of this install session at:
/oracle/oraInventory/logs/InstallActions2024-06-25_02-42-44PM/installActions2024-06-25_02-42-44PM.log
As a root user, execute the following script(s):
1. /oracle/oraBase/oracle/19.0.0/database/root.sh
Execute /oracle/oraBase/oracle/19.0.0/database/root.sh on the following nodes:
[stdby1]
Successfully Setup Software.
2.1 As root user, run root.sh.
[root@stdby1 ~]# /oracle/oraBase/oracle/19.0.0/database/root.sh
Check /oracle/oraBase/oracle/19.0.0/database/install/root_stdby1_2024-06-25_15-16-57-512021832.log for the output of root script
[root@stdby1 ~]# cat /oracle/oraBase/oracle/19.0.0/database/install/root_stdby1_2024-06-25_15-16-57-512021832.log
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/oraBase/oracle/19.0.0/database
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Oracle Trace File Analyzer (TFA) is available at : /oracle/oraBase/oracle/19.0.0/database/bin/tfactl
3 On Primary Node PROD1, enable Data Guard and the redo log transport to standby.
SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.
SQL> alter system set log_archive_dest_state_2='enable' scope=both sid='*';
System altered.
4 Upgrade the database on Standby
4.1 Check the current database configuration.
[oracle@stdby1 admin]$ /oracle/oraBase/oracle/18.0.0/database/bin/srvctl config db -d PRODDR
Database unique name: PRODDR
Database name: PRODDR
Oracle home: /oracle/oraBase/oracle/18.0.0/database
Oracle user: grid
Spfile: +DATA/PRODDR/spfilePRODDR1.ora
Password file:
Domain:
Start options: read only
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: cat,dog,pig
OSDBA group:
OSOPER group:
Database instance: PRODDR1
4.2 Update the /home/oracle/.bash_profile to point to 19c Home.
[oracle@stdby1 ~]$ egrep "18|19" ~/.bash_profile
ORACLE_HOME=$ORACLE_BASE/19.0.0/database; export ORACLE_HOME
[oracle@stdby1 ~]$ . ~/.bash_profile
[oracle@stdby1 ~]$ env|grep ORACLE
ORACLE_SID=PRODDR1
ORACLE_HOSTNAME=stdby1
ORACLE_BASE=/oracle/oraBase/oracle
ORACLE_HOME=/oracle/oraBase/oracle/19.0.0/database
4.3 Upgrade the database.
[oracle@stdby1 admin]$ /oracle/oraBase/oracle/19.0.0/database/bin/srvctl upgrade database -d PRODDR -o /oracle/oraBase/oracle/19.0.0/database
[oracle@stdby1 admin]$ /oracle/oraBase/oracle/19.0.0/database/bin/srvctl modify database -d PRODDR -s "mount" -r PHYSICAL_STANDBY
[oracle@stdby1 admin]$ /oracle/oraBase/oracle/19.0.0/database/bin/srvctl enable database -d PRODDR
[oracle@stdby1 admin]$ /oracle/oraBase/oracle/19.0.0/database/bin/srvctl start database -d PRODDR
SQL> set lines 200 pages 9999
SQL> col name for a15
SQL> col db_unique_name for a15
SQL> col database_role for a35
SQL> col open_mode for a45
SQL> select name, db_unique_name, database_role, open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------------- --------------- ----------------------------------- ---------------------------------------------
PROD PRODDR PHYSICAL STANDBY MOUNTED
4.4 Enable Data Guard.
SQL> alter system set dg_broker_start=true scope=both sid='*';
4.5 When you start the standby database in mount or open read only mode, it automatically tries to start managed recovery. If you need to start it manually, you can run this command.
SQL> alter database recover managed standby database disconnect nodelay;
4.6 Add the 19c Home entry in /etc/oratab if it doesn’t exist. Comment out the 18c Home.
+ASM:/oracle/oraBase/19.0.0/grid:N # line added by Agent
PRODDR:/oracle/oraBase/oracle/19.0.0/database:N # line added by Agent
#PRODDR:/oracle/oraBase/oracle/18.0.0/database:N # line added by Agent
4.7 Copy these files over from the old 18C Homes to the new 19c Homes if they exist.
From /oracle/oraBase/oracle/18.0.0/database/dbs to /oracle/oraBase/oracle/19.0.0/database/dbs/
-Spfile, pfile, Password File, Data Guard Config Files
From /oracle/oraBase/oracle/18.0.0/database/network/admin/ to /oracle/oraBase/oracle/19.0.0/database/network/admin/
-listener.ora, tnsnames.ora, sqlnet.ora
Note: The following files should have been automatically copied over during the grid infrastructure upgrade. They are here for completeness.
From /oracle/oraBase/18.0.0/grid/network/admin/ to /oracle/oraBase/19.0.0/grid/network/admin/
-listener.ora, tnsnames.ora, sqlnet.ora
Check the listener.ora, sqlnet.ora, and tnsnames.ora in both grid and oracle homes and change any references from the old 18c homes to the new 19c homes.
If any changes are made to the listener.ora, restart the listener.
[oracle@stdby1 ~]$ srvctl stop listener
[oracle@stdby1 ~]$ srvctl start listener
5 Put the Standby in open mode so it can automatically start in OPEN READ ONLY WITH APPLY.
[oracle@stdby1 trace]$ /oracle/oraBase/oracle/19.0.0/database/bin/srvctl modify database -d PRODDR -s "read only" -r PHYSICAL_STANDBY
[oracle@stdby1 trace]$ srvctl config database -d PRODDR
Database unique name: PRODDR
Database name: PRODDR
Oracle home: /oracle/oraBase/oracle/19.0.0/database
Oracle user: oracle
Spfile: +DATA/PRODDR/spfilePRODDR1.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: cat,dog,pig
OSDBA group:
OSOPER group:
Database instance: PRODDR1
6 Open the database in READ ONLY mode. Verify OPEN_MODE is READ ONLY WITH APPLY
SQL> alter database open read only;
Database altered.
SQL> set lines 200 pages 9999
SQL> col name for a15
SQL> col db_unique_name for a15
SQL> col database_role for a35
SQL> col open_mode for a45
SQL> select name, db_unique_name, database_role, open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------------- --------------- ----------------------------------- ---------------------------------------------
PROD PRODDR PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> alter pluggable database PROD1PDB open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROD1PDB READ ONLY NO
7 Verify Standby DB is 19c.
[oracle@stdby1 trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 25 17:57:10 2024
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> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
8. Check the timezone.
SQL> set lines 200
SQL> set lines 180
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
------------------------------------------------------------ ---------- ----------
timezlrg_32.dat 32 0
9. Verify the status are all VALID in the registry for all containers.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> set lines 180 pages 9999
SQL> col cid for a20
SQL> col cname for a45
SQL> col version for a20
SQL> col prv_version for a20
SQL> col status for a15
SQL> select r.cid, r.cname, r.prv_version, r.version, d.status from registry$ r, dba_registry d
2 where r.cid=d.comp_id;
CID CNAME PRV_VERSION VERSION STATUS
-------------------- --------------------------------------------- -------------------- -------------------- ---------------
CATALOG Oracle Database Catalog Views 18.0.0.0.0 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 18.0.0.0.0 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 18.0.0.0.0 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 18.0.0.0.0 19.0.0.0.0 VALID
XML Oracle XDK 18.0.0.0.0 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 18.0.0.0.0 19.0.0.0.0 VALID
XDB Oracle XML Database 18.0.0.0.0 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 18.0.0.0.0 19.0.0.0.0 VALID
CONTEXT Oracle Text 18.0.0.0.0 19.0.0.0.0 VALID
9 rows selected.
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> select r.cid, r.cname, r.prv_version, r.version, d.status from registry$ r, dba_registry d
2 where r.cid=d.comp_id;
CID CNAME PRV_VERSION VERSION STATUS
-------------------- --------------------------------------------- -------------------- -------------------- ---------------
CATALOG Oracle Database Catalog Views 18.0.0.0.0 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 18.0.0.0.0 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 18.0.0.0.0 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 18.0.0.0.0 19.0.0.0.0 VALID
XML Oracle XDK 18.0.0.0.0 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 18.0.0.0.0 19.0.0.0.0 VALID
XDB Oracle XML Database 18.0.0.0.0 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 18.0.0.0.0 19.0.0.0.0 VALID
CONTEXT Oracle Text 18.0.0.0.0 19.0.0.0.0 VALID
9 rows selected.
SQL> alter session set container=PROD1PDB;
Session altered.
SQL> select r.cid, r.cname, r.prv_version, r.version, d.status from registry$ r, dba_registry d
2 where r.cid=d.comp_id;
CID CNAME PRV_VERSION VERSION STATUS
-------------------- --------------------------------------------- -------------------- -------------------- ---------------
CATALOG Oracle Database Catalog Views 18.0.0.0.0 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 18.0.0.0.0 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 18.0.0.0.0 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 18.0.0.0.0 19.0.0.0.0 VALID
XML Oracle XDK 18.0.0.0.0 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 18.0.0.0.0 19.0.0.0.0 VALID
XDB Oracle XML Database 18.0.0.0.0 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 18.0.0.0.0 19.0.0.0.0 VALID
CONTEXT Oracle Text 18.0.0.0.0 19.0.0.0.0 VALID
9 rows selected.
10 Drop the restore point once you verified database is successfully upgraded and you are certain that you no longer need to flashback the database to a point prior to the upgrade.
SQL> col name for a55
SQL> select name from v$restore_point;
NAME
-------------------------------------------------------
BEFORE_UPGRADE_RP
SQL> drop restore point BEFORE_UPGRADE_RP;
Restore point dropped.
11 Verify Data Guard is healthy.
DGMGRL> show configuration;
Configuration - BISDB
Protection Mode: MaxPerformance
Members:
PROD - Primary database
PRODDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 24 seconds ago)
DGMGRL> show database 'PROD';
Database - PROD
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD1
PROD2
Database Status:
SUCCESS
DGMGRL> show database 'PRODDR';
Database - PRODDR
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 58.00 KByte/s
Real Time Query: ON
Instance(s):
PRODDR1
Database Status:
SUCCESS
DGMGRL> validate database 'PROD';
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
PROD: YES
DGMGRL> validate database 'PRODDR';
Database Role: Physical standby database
Primary Database: PROD
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Capacity Information:
Database Instances Threads
PROD 2 2
PRODDR 1 2
Warning: the target standby has fewer instances than the
primary database, this may impact application performance
Managed by Clusterware:
PROD : YES
PRODDR : YES
12. Troubleshooting Data Guard Errors after upgrade.
If you are getting any of these errors even though you have verified that listener.ora, tnsnames.ora, and password files are copied from the old homes to the new homes on all Primary and Standby nodes, and they have been modified with the new ORACLE_HOME, then the Data Configuration file might be corrupted. This can be easily fixed by removing and creating a new DG Configuration.
Errors when running from standby node.
DGMGRL> show configuration;
Configuration - BISDB
Protection Mode: MaxPerformance
Members:
PROD - Primary database
Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
PRODDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 0 seconds ago)
DGMGRL> show database 'PROD';
Database - PROD
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD1
PROD2
Database Status:
DGM-17016: failed to retrieve status for database "PROD"
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach member "PROD"
Errors when running from Primary Node.
DGMGRL> show configuration;
Configuration - BISDB
Protection Mode: MaxPerformance
Members:
PROD - Primary database
PRODDR - Physical standby database
Error: ORA-16664: unable to receive the result from a member
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 117 seconds ago)
DGMGRL> show database proddr;
Database - PRODDR
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
PRODDR1
Database Status:
DGM-17016: failed to retrieve status for database "PRODDR"
ORA-16664: unable to receive the result from a member
Solution: Reconfigure Data Guard.
DGMGRL> disable configuration;
Disabled.
DGMGRL> remove configuration;
Removed configuration
DGMGRL> create configuration 'BISDB' as primary database is 'PROD' connect identifier is PROD;
Configuration "BISDB" created with primary database "PROD"
DGMGRL> add database 'PRODDR' as connect identifier is PRODDR maintained as PHYSICAL;
Database "PRODDR" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - BISDB
Protection Mode: MaxPerformance
Members:
PROD - Primary database
PRODDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 6 seconds ago)
DGMGRL> show database 'PRODDR';
Database - PRODDR
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 206.00 KByte/s
Real Time Query: OFF
Instance(s):
PRODDR1
Database Status:
SUCCESS
DGMGRL> show database 'PROD';
Database - PROD
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD1
PROD2
Database Status:
SUCCESS
Congratulation! Your Physical Standby Database is upgraded!