"opatch" applies updates to the library and binary files to the ORACLE_HOME at the OS level.
"datapatch" then applies the corresponding changes to the data dictionary in the database. Note that if you have multiple databases running from the same ORACLE_HOME, you must run "datapatch" against each database. Also, if you later create a new database [most of us use a template / script to do so], you must run "datapatch" against the new database as well.
The instructions for both "opatch" and "datapatch" (including the pre-requisite checks) are included in the README file that accompanies each patch. See Mike Dietrich's blog post on how to apply the July 2021 Release Update Patch
Here is a quick demo of the commands to identify patches that are applied.
oracle19c>$ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.25
OPatch succeeded.
oracle19c>
oracle19c>$ORACLE_HOME/OPatch/opatch lspatches
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
oracle19c>
oracle19c>$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 : /opt/oracle/product/19c/dbhome_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/19c/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.25
OUI version : 12.2.0.7.0
Log file location : /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/opatch2021-08-29_21-20-33PM_1.log
Lsinventory Output file location : /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2021-08-29_21-20-33PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: ora19cs1
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 (3) :
Patch 32876380 : applied on Mon Aug 09 22:59:39 GMT+08:00 2021
Unique Patch ID: 24269510
Patch description: "OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)"
Created on 5 Jul 2021, 04:53:50 hrs UTC
Bugs fixed:
29445548, 29254623, 29540327, 29774362, 30134746, 30160625, 30534662
29512125, 29942275, 30855101, 31306261, 31359215, 30895577, 29224710
26716835, 31668872, 32165759, 32069696, 32032733, 30889443, 30674373
32167592, 32523206, 29415774, 28777073, 32124570, 31247838, 29540831
32892883, 31776121
Patch 32904851 : applied on Mon Aug 09 21:44:24 GMT+08:00 2021
Unique Patch ID: 24343243
Patch description: "Database Release Update : 19.12.0.0.210720 (32904851)"
Created on 20 Jul 2021, 09:21:24 hrs UTC
Bugs fixed:
7391838, 8460502, 8476681, 14570574, 14735102, 15931756, 16662822
16664572, 16750494, 17275499, 17395507, 17428816, 17468475, 17777718
18534283, 18697534, 19080742, 19138896, 19697993, 20007421, 20083476
20313356, 20319830, 20479545, 20867658, 20922160, 21119541, 21232786
21245711, 21374587, 21528318, 21629064, 21639146, 21888352, 21965541
22066547, 22252368, 22325312, 22387320, 22580355, 22725871, 22729345
22748979, 23020668, 23094775, 23125587, 23294761, 23296836, 23311885
23606241, 23645975, 23734075, 23763462, 24336782, 24356932, 24561942
24596874, 24669730, 24687075, 24833686, 24957575, 24971456, 25030027
25092651, 25093917, 25148135, 25404117, 25416731, 25560538, 25562258
25607406, 25607716, 25756945, 25792962, 25804387, 25804908, 25806201
...
... cut short a VERY LONG LIST of Bug Numbers
...
32677702, 32686850, 32697781, 32698569, 32700989, 32704765, 32711741
32712220, 32716726, 32718316, 32720458, 32725484, 32728984, 32738356
32739966, 32740503, 32754845, 32758096, 32765738, 32784393, 32784403
32786309, 32795712, 32810668, 32811069, 32811131, 32816003, 32817950
32818019, 32858446, 32874995, 32881853, 32889434, 32895105, 32900208
32902635, 32936961, 32941509, 32996071, 33034103, 33048277, 33127032
32490416
Patch 29585399 : applied on Thu Apr 18 15:21:33 GMT+08:00 2019
Unique Patch ID: 22840393
Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
Bugs fixed:
27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250
28925460, 28935956, 28940472, 3, 28942694, 28951332, 28963036, 28968779
28980448, 28995287, 29003207, 29003617, 29016294, 29018680, 29024876
29026154, 29027933, 29047127, 29052850, 29058476, 29111631, 29112455
29117337, 29123444, 29125708, 29125786, 29129476, 29131772, 29132456
29139727, 29146157, 29147849, 29149170, 29152603, 29152752, 29154631
29154636, 29154829, 29159216, 29159661, 29160462, 29161923, 29169540
29169739, 29170717, 29173618, 29181568, 29182920, 29183298, 29186091
29191827, 29201143, 29201695, 29209545, 29210577, 29210610, 29210624
29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29235934
29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
29413360, 29457319, 29465047
--------------------------------------------------------------------------------
OPatch succeeded.
oracle19c>
The first command "opatch version" shows the current version
The second command "opatch lspatches" provides a high level listing of the patches that have been applied.
The third command "opatch lsinventory" provides a detailed listing of the patches that includes the dates when they were applied by the DBA, the dates they were actually created and released by Oracle Development / Support and each individual Bug# numbers fixed in the patches.
Release Updates in 12.2 and higher are cumulative. Here, "19.12.0.0.210720" indicates that I have applied the 19.12 Release Update of July 2021 ("2107" from the patch identifier)
Another method, using sql is the DBMS_QOPATCH package which has been implemented and substantially improved in recent releases. (Oracle Support Document "How to Find PSU/One-off Patches is Applied Using QOPatch(DBMS_QOPATCH) (Doc ID 2169610.1)" is a good starting point :
SQL> set pages50000 SQL> set long 100000 SQL> spool dbms_qopatch_lsinventory.TXT SQL> select dbms_qopatch.get_opatch_lsinventory from dual; ... ... SQL> spool off SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0 oracle19c>ls -l dbms_qopatch_lsinventory.TXT -rw-r--r--. 1 oracle oinstall 237880 Aug 29 21:50 dbms_qopatch_lsinventory.TXT oracle19c> oracle19c>wc -l dbms_qopatch_lsinventory.TXT 1792 dbms_qopatch_lsinventory.TXT
DBMS_QOPATCH is very useful. You can query to check if a certain Patch has been installed (Patch 32904851 is the July 2021 Release Update Patch 19.12 that I installed on 09-Aug-2021).
SQL> set pages5000
SQL> set pages50000
SQL> set long 1000000
SQL> set pages50000
SQL> set long 1000000
SQL> select xmltransform(dbms_qopatch.is_patch_installed('32904851'),dbms_qopatch.get_opatch_xslt) from dual;
XMLTRANSFORM(DBMS_QOPATCH.IS_PATCH_INSTALLED('32904851'),DBMS_QOPATCH.GET_OPATCH_XSLT)
------------------------------------------------------------------------------------------------------------------------------------
Patch Information:
32904851: applied on 2021-08-09T21:44:24+08:00
SQL>
SQL>
"opatch" only lists patches that are applied to the ORACLE_HOME. However, you must also confirm if the database data dictionary has also been updated -- which would be so if "datapatch" has been executed. (side note : "datapatch" also has a number of command-line arguments which you might want to explore)
This information is in the dba_registry and dba_registry_sqlpatch views in the database
SQL> set linesize 132
SQL> select * from dba_registry_history order by action_time;
ACTION_TIME ACTION
--------------------------------------------------------------------------- ------------------------------
NAMESPACE VERSION ID
------------------------------ ------------------------------ ----------
COMMENTS
------------------------------------------------------------------------------------------------------------------------------------
BUNDLE_SERIES
------------------------------
04-MAY-19 11.30.32.133846 PM RU_APPLY
SERVER 19.0.0.0.0
Patch applied on 19.3.0.0.0: Release_Update - 190410122720
09-AUG-21 10.13.42.105236 PM RU_APPLY
SERVER 19.0.0.0.0
Patch applied from 19.3.0.0.0 to 19.12.0.0.0: Release_Update - 210716141810
09-AUG-21 11.10.15.544636 PM jvmpsu.sql
SERVER 19.12.0.0.210720OJVMRU 0
RAN jvmpsu.sql
09-AUG-21 11.10.15.722597 PM APPLY
SERVER 19.12.0.0.210720OJVMRU 0
OJVM RU post-install
BOOTSTRAP
DATAPATCH 19
RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
SQL>
SQL> select * from dba_registry_sqlpatch order by action_time;
INSTALL_ID PATCH_ID PATCH_UID PATCH_TYPE ACTION STATUS
---------- ---------- ---------- ---------- --------------- -------------------------
ACTION_TIME
---------------------------------------------------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
LOGFILE
------------------------------------------------------------------------------------------------------------------------------------
RU_LOGFILE
------------------------------------------------------------------------------------------------------------------------------------
FLAGS
----------
PATCH_DESCRIPTOR
------------------------------------------------------------------------------------------------------------------------------------
PATCH_DIRECTORY
------------------------------------------------------------------------------------------------------------------------------------
SOURCE_VERSION SOURCE_BUILD_DESCRIPTION
--------------- --------------------------------------------------------------------------------
SOURCE_BUILD_TIMESTAMP TARGET_VERSION
--------------------------------------------------------------------------- ---------------
TARGET_BUILD_DESCRIPTION
--------------------------------------------------------------------------------
TARGET_BUILD_TIMESTAMP
---------------------------------------------------------------------------
1 29517242 22862832 RU APPLY SUCCESS
04-MAY-19 11.31.01.355942 PM
Database Release Update : 19.3.0.0.190416 (29517242)
/opt/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_ORCLCDB_CDBROOT_2019May04_23_23_03.log
/opt/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_ru_apply_ORCLCDB_CDBROOT_2019May04_23_22_59.log
N
sqlPatch ID="29517242" uniquePatchID=
19.1.0.0.0 Feature Release
19.3.0.0.0
Release_Update
10-APR-19 12.27.20.000000 PM
2 32904851 24343243 RU APPLY SUCCESS
09-AUG-21 10.35.37.696526 PM
Database Release Update : 19.12.0.0.210720 (32904851)
/opt/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_ORCLCDB_CDBROOT_2021Aug09_22_04_05.log
/opt/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_ru_apply_ORCLCDB_CDBROOT_2021Aug09_22_03_59.log
N
sqlPatch ID="32904851" uniquePatchID=
19.3.0.0.0 Release_Update
10-APR-19 12.27.20.000000 PM 19.12.0.0.0
Release_Update
16-JUL-21 02.18.10.000000 PM
3 32876380 24269510 INTERIM APPLY SUCCESS
09-AUG-21 11.12.07.815060 PM
OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
/opt/oracle/cfgtoollogs/sqlpatch/32876380/24269510/32876380_apply_ORCLCDB_CDBROOT_2021Aug09_23_06_42.log
NJ
sqlPatch ID="32876380" uniquePatchID=
504B03041400000008007A77E552F6AD657DF40100006C0400000C00000033323837363338302E786D6CB5534D73DA3014BCFB57BCEAD41EC01F044232B6339EE076
C804C81092999E3A8A2D374A6559
19.12.0.0.0 Release_Update
16-JUL-21 02.18.10.000000 PM 19.12.0.0.0
Release_Update
16-JUL-21 02.18.10.000000 PM
SQL>
The first query shows that "datapatch" was executed on 09-Aug-21 and the second query shows that it executed the changes for both Patches 32904851 (Database Release Update) and 32876380 (OJVM Release Update)