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.
Latest version of datapatch comes with the latest OPatch utility installed and can be found under Oracle Home/OPatch directory of your environment.
SQL Patching tool version 22.214.171.124.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.
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> alter pluggable database all open;
To verify patches:
SQL> SELECT patch_id, version, status, bundle_id, bundle_series FROM dba_registry_sqlpatch;
For further reference on 12c datapatch and known issues to troubleshoot , please refer : Doc ID 1585822.1, Doc ID 2335899.2