Introduction:
Prior to 12c, the post patch steps as part of CPU/PSU/Bug fixes are required to run manually on the database to update the patch registry when the RDBMS patch is applied on an Oracle Home.
Where as in 12c, we have a new Post Patch SQL Automation tool called as “Datapatch” provided by Oracle to perform post patch executions. This tool is helpful in executing the necessary scripts to load modified SQL files into the database registry and are viewed from registry$sqlpatch , DBA_REGISTRY_SQLPATCH, DBA_REGISTRY_HISTORY.
In this blog, let’s see how to access and use datapatch to apply and rollback SQL portion of patches.
Description:
Latest version of datapatch comes with the latest OPatch utility installed and can be found under Oracle Home/OPatch directory of your environment.
For example,
cd $ORACLE_HOME/OPatch/datapatch
./datapatch -version
SQL Patching tool version 12.1.0.2.0 Production on Wed Oct X hh:mm:ss 2020
To view patches installed in the ORACLE HOME
$ORACLE_HOME/OPatch/datapatch -oh $ORACLE_HOME
To apply a database post patch:
Note: Start the database in upgrade mode preferably (because few patches may require upgrade mode startup of database which will prompt during datapatch pre-req check). Otherwise, in general normal startup of database should work.
SQL>startup upgrade
$ORACLE_HOME/OPatch/datapatch -verbose
To rollback a specified database patch:
$ORACLE_HOME/OPatch/datapatch -rollback -id <PatchNumber>
$ORACLE_HOME/OPatch/datapatch -rollback -id <PatchNumber1> , <PatchNumberN>
On a multitenant environment:
Datapatch will automatically apply patches to all existing container and pluggable databases.
Note: In multitenant , datapatch actions are applied only to the ROOT, SEED and any open PDBs.
SQL> startup
SQL> alter pluggable database all open;
$ORACLE_HOME/OPatch/datapatch -verbose
To verify patches:
SQL> SELECT patch_id, version, status, bundle_id, bundle_series FROM dba_registry_sqlpatch;
Bottom line:
For further reference on 12c datapatch and known issues to troubleshoot , please refer : Doc ID 1585822.1, Doc ID 2335899.2
