When patching Oracle database, some patches need to update database objects. A SQL patch is a patch that contains SQL scripts which need to be run after OPatch completes.

In Oracle 12c, Oracle has introduced a new tool named “datapatch” which enables automation of post-patch SQL actions for RDBMS patches. It’s called by OPatchAuto usually.

To check SQL Patch history, the view DBA_REGISTRY_SQLPATCH can be used.

SELECT TO_CHAR(ACTION_TIME, 'DD-MON-YYYY HH24:MI:SS') AS ACTION_TIME, PATCH_TYPE,
ACTION,STATUS,DESCRIPTION, SOURCE_VERSION,TARGET_VERSION, PATCH_ID 
FROM DBA_REGISTRY_SQLPATCH ORDER BY ACTION_TIME DESC;

ACTION_TIME           PATCH_TYPE ACTION   STATUS     DESCRIPTION                                              SOURCE_VERSION   TARGET_VERSION PATCH_ID
--------------------- ---------- -------- ---------- -------------------------------------------------------- --------------- --------------- ----------
12-NOV-2021 19:38:49  INTERIM    APPLY    SUCCESS    OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)         19.1.0.0.0       19.1.0.0.0     32876380
12-NOV-2021 19:38:49  RU         APPLY    SUCCESS    Database Release Update : 19.12.0.0.210720 (32904851)    19.1.0.0.0       19.12.0.0.0    32904851

A while ago we did an Oracle Linux OS upgrade from OL7 to OL8 by moving the 19c database (19.12.0.0.0) from one VM running OL7 to another VM running OL8. At the same time, we have a newer version of 19c (19.15.0.0.0) on the OL8 — it meant the database were patched on the new OL8 VM.

We thoguht we had copied everything over — conrtrol files, data files, password file, spfile, listener files. However when running SQL Patch using “datapatch -verbose“, we got the following errors:

$ORACLE_HOME/OPatch/datapatch -verbose

Error: prereq checks failed!
  patch 32876380: Archived patch directory is empty
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_8668_2022_04_06_20_34_10/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Wed April  6 20:34:34 2022

What happened was SQL Patch would put the all necessary files under $ORACLE_HOME/sqlpatch and later if there is a need to rollback a SQL Patch, the tool will read from the corresponding patch files saved there.

In our case, the patch 32876380 is about OJVM coming with 19.12.0.0.0. With 19.15.0.0.0, a newer version of OJVM is being installed and Oracle needs to rollback the previous one (32876380) and apply the new one. We didn’t copy stuff from $ORACLE_HOME/sqlpatch, so the subdirectory 32876380 was missing and the datapatch tool could not find 32876380 to rollback and failed.

[oracle@joetest ~]$ ls -lart $ORACLE_HOME/sqlpatch
total 444
-rw-r--r--.  1 oracle oinstall   1578 Nov  5  2018 sqlpatch_app_create.sql
-rw-r--r--.  1 oracle oinstall   2080 Nov  5  2018 sqlpatch_app_begin.sql
drwxr-xr-x.  8 oracle oinstall     92 Apr 17  2019 lib
drwxr-xr-x.  4 oracle oinstall     38 Apr 18  2019 29517242
drwxr-x---.  3 oracle oinstall     22 Sep 17  2021 32545013
drwxr-xr-x.  3 oracle oinstall     22 Sep 17  2021 32399816
drwxr-x---.  3 oracle oinstall     22 Sep 22  2021 32067171
drwxr-xr-x.  3 oracle oinstall     22 Nov  8  2021 32904851
drwxr-xr-x.  3 oracle oinstall     22 Nov  8  2021 32876380
-rw-r--r--.  1 oracle oinstall 390978 Jan 13 00:13 sqlpatch.pm
-rw-r--r--.  1 oracle oinstall   3132 Jan 13 00:13 sqlpatch_app_end.sql
-rwxr-x---.  1 oracle oinstall  11310 Jan 13 00:13 copy_sqlpatch_files.pl
-rw-r--r--.  1 oracle oinstall   7969 Jan 13 00:13 sqlpatch.pl
-rw-r--r--.  1 oracle oinstall  11041 Jan 13 00:13 sqlpatch_bootstrap.sql
-rw-r--r--.  1 oracle oinstall   1484 Jan 13 00:13 sqlpatch.bat
-rwxr-x---.  1 oracle oinstall   2732 Jan 13 00:13 sqlpatch
drwxr-xr-x.  3 oracle oinstall     22 Apr 24 03:29 33515361
drwxr-xr-x.  3 oracle oinstall     22 Apr 24 03:33 33561310
drwxr-xr-x. 80 oracle oinstall   4096 Apr 24 03:34 ..
drwxr-xr-x. 11 oracle oinstall   4096 May  6 19:46 .

Once we copied the contents under $ORACLE_HOME/sqlpatch over to OL8 from OL7, datapatch succeeded.

References:

1. Datapatch: Database 12c or later Post Patch SQL Automation (Doc ID 1585822.1)
2. datapatch -verbose Fails with Error :” Patch xxxxxx: Archived Patch Directory Is Empty” (Doc ID 2235541.1)
3. Troubleshooting Assistant:12c Datapatch Issues (Doc ID 2335899.2)

• Oracle