Tag: datapatch

Oracle PDB and when is DAPTATCH required to be executed

arcsdegeoLeave a comment

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” ?
Advertisement

What does datapatch do ?

arcsdegeoLeave a comment

Oracle releases patches every quarter (Oracle CPU Critical Patch Updates), these patches are either PSU (Patche-Set-Updates) for 12cR1,11gR4(11.2.0.4), or RU (Release Updates) RUR (Release Update Revisions) for 12cR2,18c,19c…etc.

 

We download the patch and we follow the procedure by first applying the patches on the “database binaries”, and then we perform/execute “datapatch” when the database is up……have you ever wondered what actually is happening when you execute datapatch ?

 

The following is my personal interpretation of the internals of datapatch execution:

The datapatch will cross reference and check wither you have “applied” or “rolled” back the patches on the binaries using dbms_qopatch package….based on that the datapach will either will apply SQL modifications or Roll-Back these modifications, then the package dbms_sqlpatch will be invoked to update dba_registry_sqlpatch with patch information:

CREATE OR REPLACE PACKAGE dbms_sqlpatch AS

— Performs any initialization necessary for the given patch, including

— the initial insert to the SQL registry.  Parameters:

— patch_id/patch_uid: patch ID/universal patch id

 

USERNAME                       OPERATION

—————————— ——————————–

TIMESTAMP           TABLE_NAME                         SESSION#    SERIAL#

——————- ——————————– ———- ———-

SQL_REDO

——————————————————————————–

— flags: patch flags

— description: patch description as recorded in binary registry

— action: APPLY or ROLLBACK

— logfile: Full path to logfile

— bndl_series: For a bundle patch, the bundle series.

— bnld_xml: For a bundle patch, the full path to the bundledata.xml

 

In 18c, a new SQL registry table registry$sqlpatch_ru_info, with associated view dba_registry_sqlpatch_ru_info:

 

SQL> select * from dba_registry_sqlpatch_ru_info;

PICCCC1

In addition, datapatch will change the SQL code of Oracle built in-packages, to illustrate the following SYS packages were changed in Oracle 12cR1 (12.1.0.2) after applying Oracle CPU July 2019 patches:

The following 4 packages under SYS where changed after the patch:

DBMS_BACKUP_RESTORE

DBMS_PREUP

DBMS_RCVMAN

DBMS_REGXDB

For DBMS_BACKUP_RESTORE package is changed after patching…. The following function is newly introduced:

 

FUNCTION KRBI_CREATE_XML RETURN VARCHAR2;

PRAGMA TIMESTAMP(‘2001-10-17:13:28:00’);

PRAGMA INTERFACE (C, KRBI_CREATE_XML);

FUNCTION CREATETEMPXMLFILE  RETURN VARCHAR2 IS

FNAME VARCHAR2(1024);

BEGIN

BEGIN

CHECK_VERSION(11,1,0, ‘dbms_backup_restore’);

ICDSTART(214);

GACTION := KRBI_READ_ACTION();

FNAME := KRBI_CREATE_XML;

GTRACEENABLED := KRBITRC();

ICDFINISH;

RETURN FNAME;

EXCEPTION

WHEN OTHERS THEN ICDFINISH;

RAISE;

END;

PRAGMA TIMESTAMP(‘2001-10-17:13:28:00’);

BEGIN

CHECK_VERSION(11,1,0, ‘dbms_backup_restore’);

GACTION := KRBI_READ_ACTION();

GTRACEENABLED := KRBITRC();

For DBMS_REGXDB package is changed after patching…. The following SQL statement is added:

EXECUTE IMMEDIATE ‘grant execute on XQSequenceFromXMLType to public’;

** Also, The code before patch:

DBMS_OUTPUT.PUT_LINE(‘Warning: XDB now invalid, could not find xdbconfig‘);

SYS.DBMS_REGISTRY.INVALID(‘XDB’);

** The code After patch:

DBMS_OUTPUT.PUT_LINE(‘Warning: XDB now invalid’);

SYS.DBMS_REGISTRY.INVALID(‘XDB’);

RAISE;