Oracle PDB and when is DAPTATCH required to be executed

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;

This blog post was inspired from youtube video by Connor McDonald titled “Do I need to run DataPatch” ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s