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;

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;