Here I am going to show three scenarios to illustrate when datapatch utility is required to be executed.
In my simulation the source CDB environment version is 19.3 , the destination CDB environment is 19.9 (patched with October 2020 RU).
Scenario 1: moving PDB from a lower version (source CDB) to a higher destination CDB
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
SQL> ALTER PLUGGABLE DATABASE PDB2 CLOSE;
SQL> ALTER PLUGGABLE DATABASE PDB2 UNPLUG INTO ‘D:\19c_vm\OracleDatabase\19.3.0\PDB2_UNLPLUG.xml’;
Then I will drop the database from the source CDB:
SQL> DROP PLUGGABLE DATABASE PDB2;
After that copy the datafiles and .XML file to the destination server hosting the destination CDB:
SQL> CREATE PLUGGABLE DATABASE NEW_PDB
USING ‘/opt/oracle/product/19c/dbhome_1/dbs/PDB2_UNLPLUG.xml’ MOVE
SOURCE_FILE_NAME_CONVERT= (‘D:\19C_BASE\ORADATA\ORACLE19C\PDB2\’, ‘/opt/oracle/oradata/ORCLCDB/NEW_PDB/’);
// open the database and check the dictionary:
alter pluggable database NEW_PDB open;
alter session set container=NEW_PDB;
select * from dba_registry_sqlpatch;
There are NO rows resulted from dba_registry_sqlpatch , this means we will need to run datapatch to update the dictionary of the newly plugged database (always download the latest OPatch utility version):
cd $ORACLE_HOME/OPatch
[oracle@oracle-19c]$ ./datapatch -verbose
SQL Patching tool version 19.9.0.0.0 Production on Mon Nov 30 15:40:32 2020
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_9900_2020_11_30_15_40_32/sqlpatch_invocation.log
Connecting to database…OK
Gathering database info…done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions…done
Determining current state…done
Current state of interim SQL patches:
Interim patch 31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882)):
Binary registry: Installed
PDB CDB$ROOT: Applied successfully on 06-NOV-20 08.17.57.037468 PM
PDB NEW_PDB: Not installed
PDB PDB$SEED: Applied successfully on 06-NOV-20 08.17.58.718557 PM
PDB PDB1: Applied successfully on 06-NOV-20 08.18.00.702663 PM
PDB PDB2: Applied successfully on 06-NOV-20 08.18.02.342712 PM
Current state of release update SQL patches:
Binary registry:
19.9.0.0.0 Release_Update 200930183249: Installed
PDB CDB$ROOT:
Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 06-NOV-20 08.59.54.481954 PM
PDB NEW_PDB:
No release update patches installed
After that check dba_registry_sqlpatch view ……the metadata is updated reflecting the patch version and SQL code modifications.
Scenario 2: creating a new pluggable database in a patched CDB
SQL> CREATE PLUGGABLE DATABASE “PDB33” ADMIN USER “admin” IDENTIFIED BY “admin_123”
FILE_NAME_CONVERT=( ‘/opt/oracle/oradata/ORCLCDB/’, ‘/opt/oracle/oradata/ORCLCDB/PDB33/’ )
STORAGE UNLIMITED TEMPFILE REUSE;
So, NO need to run datapatch for any “newly” created PDB in CDB container !
The pluggable database created is based on the template database PDB$SEED
Scenario 3: migrating a PDB to a lower version CDB
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER PLUGGABLE DATABASE NEW_PDB CLOSE;
ALTER PLUGGABLE DATABASE NEW_PDB UNPLUG INTO ‘/vagrant/NEW_PDB_UNLPLUG.xml’;
Then I will drop the database from the source CDB:
DROP PLUGGABLE DATABASE NEW_PDB;
// I will plug the database now in the destination CDB
alter session set container=CDB$ROOT;
CREATE PLUGGABLE DATABASE NEW_PDB
USING ‘D:\19c_base\oradata\ORACLE19C\NEW_PDB\NEW_PDB_UNLPLUG.xml’ MOVE
SOURCE_FILE_NAME_CONVERT= (‘/opt/oracle/oradata/ORCLCDB/ORCLCDB/1D59E472CA974170BFC1A471E133E3C9/datafile/’, ‘D:\19c_base\oradata\ORACLE19C\NEW_PDB\’);
After successfully plugging the database….now checking the registry:
alter session set container=NEW_PDB;
select PATCH_ID,DESCRIPTION from dba_registry_sqlpatch;
Dictionary is reflecting that the PDB is patched with 19.9 patches
What will happen if I run “datapatch -verbose” command ?
The patches will be rolled back (as expected) !
The datapatch perl script executes the package DBMS_QOPATCH that compares the patch level between the binaries and the database system metadata.
select action,action_time,description from dba_registry_sqlpatch;
Geodata Master 