Oracle DBCS DB Home In-Place Patch Upgrade from 19.X to 19.X via DBCLI with Post-Patching Validation
In this blog post, I walk through the process of performing an in-place patch upgrade of Oracle Database 19c from version 19.15.0.0.0 to 19.20.0.0.0 on an Oracle Database Appliance (ODA). This includes precheck validation, applying the patch via dbcli, enabling the OLAP component, and recompiling invalid objects post-patch.
Environment Details
-
Host: <Db hostname>
-
Current DB Version: 19.15.0.0.0
-
Target DB Version: 19.20.0.0.0
-
Platform: Oracle Database Appliance
-
Tools Used:
dbcli,SQL*Plus,srvctl,chopt,utlrp.sql - In this exercise, we're only upgrading the Oracle Home. However, the Grid Infrastructure Home must be upgraded beforehand.
Step 1 : Login and Environment Preparation
Authenticating with public key "imported-openssh-key"
Last login: Tue Oct 31 12:28:29 2023 from 171.40.40.162
[opc@omdevdb ~]$ sudo -su root
[root@omdevdb opc]# cd
[root@omdevdb ~]# sudo -su oracle
[oracle@omdevdb root]$ cd
==========================================================================Step 2 : Log in to the SQL Plus from the SSH Console
[oracle@omdevdb ~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 6 00:27:49 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3.0602E+10 bytes
Fixed Size 13873640 bytes
Variable Size 1.5301E+10 bytes
Database Buffers 1.5099E+10 bytes
Redo Buffers 187449344 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
[oracle@omdevdb ~]$ exit
exit
==============================================================
Step 3 : Check for Available Patches and Oracle Home Details
[root@omdevdb ~]# cd /opt
[root@omdevdb opt]# ls
cerberus clamav io lost+found opc oracle ORCLfmap os-updater rh unified-monitoring-agent
[root@omdevdb opt]# cd oracle
[root@omdevdb oracle]# ls
bmc dcs dms extapi mysql_compact oak olite rp stig
[root@omdevdb oracle]# cd dcs
[root@omdevdb dcs]# ls
agent auth bin commonstore conf configuredcs.pl dbaasca dcsadmin-stderr.log dcsadmin-stdout.log dcsagent_wallet dcscli fpca invctl Inventory java log rdbaas repo sample thirdparty upload
[root@omdevdb dcs]# cd bin
[root@omdevdb bin]# ls
[root@omdevdb bin]# ./dbcli describe-latestpatch
componentType availableVersion
--------------- --------------------
gi 12.2.0.1.230718
gi 18.16.0.0.0
gi 19.21.0.0.0
gi 21.12.0.0.0
gi 23.3.0.23.09
db 11.2.0.4.231017
db 12.2.0.1.231017
db 12.1.0.2.231017
db 18.16.0.0.0
db 19.21.0.0.0
db 21.12.0.0.0
[root@omdevdb bin]# ./dbcli describe-component
System Version
---------------
23.3.2.0.0
Component Installed Version Available Version
---------------------------------------- -------------------- --------------------
GI 19.15.0.0.0 19.21.0.0
DB 19.15.0.0.0 19.21.0.0
With this command we are able to get what is the current installed version of the Grid DB and what is the highest available version to upgrade
Note: Before stating the process we need to check whether the dbcli is up to date if it is not up to date then the below command will update the dbcli binary to the compatible version
[root@omdevdb bin]# ./cliadm update-dbcli
dbcli is already up-to-date. Skipping...
In this case the dbcli is up-to date no no upgrade to the latest binary is required else it will be upgraded to the latest binary
Step 4: Perform Pre-Check for Server Patching
Run the precheck for both Grid Infrastructure (GI) and Oracle Home using dbcli. Once initiated, the process runs in the background as a job. You can use the dbcli list-jobs command to view all currently running jobs, and use dbcli describe-job -i <job-id> to get detailed information about a specific job and monitor its progress.
[root@omdevdb bin]# ./dbcli update-server --precheck -v 19.20.0.0.0 --precheck
{
"jobId" : "4203c11d-213a-4872-a192-1c1bf00aec47",
"status" : "Created",
"message" : null,
"errorCode" : "",
"reports" : [ ],
"createTimestamp" : "November 06, 2023 00:46:52 AM GST",
"resourceList" : [ ],
"description" : "Server Patching Prechecks",
"updatedTime" : "November 06, 2023 00:46:52 AM GST",
"percentageProgress" : "0%",
"cause" : null,
"action" : null
}
check the Job to know the status
[root@omdevdb bin]# ./dbcli describe-job -i 4203c11d-213a-4872-a192-1c1bf00aec47
Job details
----------------------------------------------------------------
ID: 4203c11d-213a-4872-a192-1c1bf00aec47
Description: Server Patching Prechecks
Status: Running
Created: November 6, 2023 at 12:46:52 AM GST
Progress: 0%
Message:
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching Prechecks November 6, 2023 at 12:46:52 AM GST November 6, 2023 at 12:46:52 AM GST Running
Once the Job is completed the Progress will be changed to 100%
[root@omdevdb bin]# ./dbcli describe-job -i 4203c11d-213a-4872-a192-1c1bf00aec47
Job details
----------------------------------------------------------------
ID: 4203c11d-213a-4872-a192-1c1bf00aec47
Description: Server Patching Prechecks
Status: Success
Created: November 6, 2023 at 12:46:52 AM GST
Progress: 100%
Message:
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching Prechecks November 6, 2023 at 12:46:52 AM GST November 6, 2023 at 12:49:02 AM GST Success
Server Patching Prechecks November 6, 2023 at 12:49:02 AM GST November 6, 2023 at 12:49:47 AM GST Success
Install object store swift module November 6, 2023 at 12:49:08 AM GST November 6, 2023 at 12:49:38 AM GST Success
[root@omdevdb bin]# ./dbcli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
f6bd8244-cd8b-439b-a4a5-5c6080a3f67a OraDB19000_home1 19.15.0.0.0 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured
Run rm -rf /tmp/datapatchoutput* before patching to remove any stale or leftover datapatch output files, ensuring a clean environment and preventing conflicts or errors during the patching process.
[root@omdevdb bin]# rm -rf /tmp/datapatchoutput*
[root@omdevdb bin]# ./dbcli update-dbhome -i f6bd8244-cd8b-439b-a4a5-5c6080a3f67a -v 19.20.0.0.0
{
"jobId" : "bf6c920c-a773-47c2-ad38-5eb543dd4982",
"status" : "Created",
"message" : null,
"errorCode" : "",
"reports" : [ ],
"createTimestamp" : "November 06, 2023 00:53:11 AM GST",
"resourceList" : [ ],
"description" : "Database inplace image patching with dbhomeId : f6bd8244-cd8b-439b-a4a5-5c6080a3f67a",
"updatedTime" : "November 06, 2023 00:53:13 AM GST",
"percentageProgress" : "0%",
"cause" : null,
"action" : null
}
[root@omdevdb bin]# ./dbcli describe-job -i bf6c920c-a773-47c2-ad38-5eb543dd4982
Job details
----------------------------------------------------------------
ID: bf6c920c-a773-47c2-ad38-5eb543dd4982
Description: Database inplace image patching with dbhomeId : f6bd8244-cd8b-439b-a4a5-5c6080a3f67a
Status: Running
Created: November 6, 2023 at 12:53:11 AM GST
Progress: 0%
Message:
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Precheck DBHome patching tasks November 6, 2023 at 12:53:13 AM GST November 6, 2023 at 12:53:13 AM GST Running
==========================================================
[root@omdevdb bin]# ./dbcli describe-job -i bf6c920c-a773-47c2-ad38-5eb543dd4982
Job details
----------------------------------------------------------------
ID: bf6c920c-a773-47c2-ad38-5eb543dd4982
Description: Database inplace image patching with dbhomeId : f6bd8244-cd8b-439b-a4a5-5c6080a3f67a
Status: Running
Created: November 6, 2023 at 12:53:11 AM GST
Progress: 8%
Message:
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Precheck DBHome patching tasks November 6, 2023 at 12:53:13 AM GST November 6, 2023 at 12:53:13 AM GST Running
===============================================================
[root@omdevdb bin]# ./dbcli describe-job -i bf6c920c-a773-47c2-ad38-5eb543dd4982
Job details
----------------------------------------------------------------
ID: bf6c920c-a773-47c2-ad38-5eb543dd4982
Description: Database inplace image patching with dbhomeId : f6bd8244-cd8b-439b-a4a5-5c6080a3f67a
Status: Running
Created: November 6, 2023 at 12:53:11 AM GST
Progress: 18%
Message: WARNING::Patch bundle conflicts with: [35320081]. Missing Patches in target DBHome are: [34059654]
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Precheck DBHome patching tasks November 6, 2023 at 12:53:13 AM GST November 6, 2023 at 1:00:02 AM GST Success
DBHome patching November 6, 2023 at 1:00:02 AM GST November 6, 2023 at 1:00:02 AM GST Running
======================================================================
[root@omdevdb bin]# ./dbcli describe-job -i bf6c920c-a773-47c2-ad38-5eb543dd4982
Job details
----------------------------------------------------------------
ID: bf6c920c-a773-47c2-ad38-5eb543dd4982
Description: Database inplace image patching with dbhomeId : f6bd8244-cd8b-439b-a4a5-5c6080a3f67a
Status: SuccessWithWarning
Created: November 6, 2023 at 12:53:11 AM GST
Progress: 100%
Message: WARNING::Patch bundle conflicts with: [35320081]. Missing Patches in target DBHome are: [34059654]
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Precheck DBHome patching tasks November 6, 2023 at 12:53:13 AM GST November 6, 2023 at 1:00:02 AM GST Success
DBHome patching November 6, 2023 at 1:00:02 AM GST November 6, 2023 at 1:24:12 AM GST Success
Post DBHome patching tasks November 6, 2023 at 1:24:12 AM GST November 6, 2023 at 2:04:27 AM GST Success
Install object store swift module November 6, 2023 at 2:04:09 AM GST November 6, 2023 at 2:04:27 AM GST Success
In this case the patch had been completed with warnings but the patch is successful
=====================================================================
[root@omdevdb bin]# sudo -su oracle
Step 6: check the database components
in case of upgrade completed with the warnings we need to check the logs and fix the warnings in this case we are checking the list of database options that are available in the instance
[oracle@omdevdb bin]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 6 07:22:20 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
SQL> select COMP_NAME, VERSION, STATUS from dba_registry ORDER BY 3;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ---------------------------------
OLAP Analytic Workspace
19.0.0.0.0 OPTION OFF
Oracle OLAP API
19.0.0.0.0 OPTION OFF
Oracle Real Application Clusters
19.0.0.0.0 OPTION OFF
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ---------------------------------
Oracle XDK
19.0.0.0.0 VALID
Oracle Database Java Packages
19.0.0.0.0 VALID
Oracle XML Database
19.0.0.0.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ---------------------------------
Oracle Workspace Manager
19.0.0.0.0 VALID
Oracle Text
19.0.0.0.0 VALID
Oracle Multimedia
19.0.0.0.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ---------------------------------
Spatial
19.0.0.0.0 VALID
Oracle Label Security
19.0.0.0.0 VALID
JServer JAVA Virtual Machine
19.0.0.0.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ---------------------------------
Oracle Database Vault
19.0.0.0.0 VALID
Oracle Database Catalog Views
19.0.0.0.0 VALID
Oracle Database Packages and Types
19.0.0.0.0 VALID
15 rows selected.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
In this case the OLAP is off so we need to se the CHOPT to change the status of the options that are available in the database
[oracle@omdevdb bin]$ srvctl stop database -d <db unique name>
Step 7 use CHOPT to enable or disable the database option as per the requirement
chopt (short for "change option") is a command-line utility provided by Oracle to enable or disable optional database features in an Oracle Home without reinstalling the software.
chopt enable/disable database options
Important Notes
-
Must be run from the Oracle Home
bindirectory. -
Requires database to be stopped before enabling/disabling features.
-
Logs can be found in:
$ORACLE_HOME/install/enable_<option>_<timestamp>.log Log location :/u01/app/oracle/product/19.0.0.0/dbhome_1/install/enable_olap_2023-11-06_07-30-54AM.log
[oracle@omdevdb bin]$ cd $ORACLE_HOME/bin ; chopt enable olap
Writing to /u01/app/oracle/product/19.0.0.0/dbhome_1/install/enable_olap_2023-11-06_07-30-54AM.log...
/usr/bin/make -f /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/lib/ins_rdbms.mk olap_on ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
/usr/bin/make -f /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@omdevdb bin]$ vi /u01/app/oracle/product/19.0.0.0/dbhome_1/install/enable_olap_2023-11-06_07-30-54AM.log...
[oracle@omdevdb bin]$ vi/u01/app/oracle/product/19.0.0.0/dbhome_1/install/enable_olap_2023-11-06_07-30-54AM.log^C
[oracle@omdevdb bin]$ vi /u01/app/oracle/product/19.0.0.0/dbhome_1/install/enable_olap_2023-11-06_07-30-54AM.log
Once the Chopt is completed start the database and and compile the invalid objects
[oracle@omdevdb bin]$ srvctl start database -d OMDEV_OIC
Step 8: Compile Invalid objects
[oracle@omdevdb bin]$ sqlplus "/ as sysdba" @?/rdbms/admin/utlrp.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 6 07:33:08 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2023-11-06 07:33:09
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2023-11-06 07:33:11
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
1
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
Step 9 :Check the status of the Component that is off and make sure that it had been valid
SQL> col comp_name format a40
SQL> col VERSION format a20
SQL> col status format a10
SQL> set pages 100
SQL> select COMP_NAME, VERSION, STATUS from dba_registry ORDER BY 3;
COMP_NAME VERSION STATUS
---------------------------------------- -------------------- ----------
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle Database Vault 19.0.0.0.0 VALID
JServer JAVA Virtual Machine 19.0.0.0.0 VALID
Oracle XDK 19.0.0.0.0 VALID
Oracle Database Java Packages 19.0.0.0.0 VALID
OLAP Analytic Workspace 19.0.0.0.0 VALID
Oracle XML Database 19.0.0.0.0 VALID
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle Text 19.0.0.0.0 VALID
Oracle Multimedia 19.0.0.0.0 VALID
Spatial 19.0.0.0.0 VALID
Oracle OLAP API 19.0.0.0.0 VALID
Oracle Label Security 19.0.0.0.0 VALID
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
15 rows selected.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
Conclusion
This blog walked through the end-to-end process of performing an in-place patch upgrade of Oracle Database 19c from version 19.15.0.0.0 to 19.20.0.0.0 using dbcli on DBCS running in Base database service in OCI. Key steps included running prechecks, applying the patch to the Oracle Home, enabling optional components like OLAP, and performing post-patch validation with utlrp.sql.
The upgrade completed successfully, with all core components in a VALID state and the database running on the new patched version. This approach ensures minimal downtime while keeping the environment consistent and secure with the latest updates and fixes.
Following a structured and clean patching process—combined with checks like clearing old datapatch outputs and validating job statuses—helps maintain Oracle environments efficiently and reduces the risk of post-upgrade issues.
