Skip to content

18c : Datapatch New Features And Changes from 12.1

PURPOSE

The purpose of this article is to help DBA’s / Support Teams to understand the new features of Datapatch

SCOPE

DBA/Support

DETAILS

 Overview:

  In 12.1  we have two different types of patches – Bundle patch /PSU patches and interim patches (non bundles) :

  The versioning scheme up until 12.2.0.1 splits the patch metadata into 2 places :

  1. The XML patch descriptor found under $ORACLE_HOME/sqlpatch/bug id/bug uid/bug id.xml  
  2. bundledata_<series>.xml found under $ORACLE_HOME/rdbms/admin.

datapatch uses the metadata from both locations to determine if a given SQL file should be run for a particular install

They have three important pieces of metadata as described below :

 Metadata Location in Patch Metadata Location in dba_registry_sqlpatch
Bundle Series (i.e. DBBP or DBPSU) bundleSeries  attribute of XML descriptor bundle_series column
Bundle ID (i.e. 170116 or 170416) bundleID  attribute of XML descriptor bundle_id column
Bundles in which a given file has been included bundle and file attributes of bundledata_<series>.xml NA

 

 

 

 

 

 

Bundle patches are marked with ‘B’ in the flags column of dba_registry_sqlpatch.

Change in 18c :

With the new model, there are still 2 main types of patches – release updates/release update revision and interim patches.

Patch Type  Description Update version in v$instance
Interim Patch One-off or MLR on top of a particular release No
Release Update(RU) Cumulative containing security and other fixes Yes,second digit(i.e 18.2.0.0.0)
Release Update Revision(RUR) Cumulative containing security and regressions on top of an RU or prior RUR Yes,third digit (i.e. 18.2.1.0.0)

 

 

 

 

To migrate to a new release update or revision, we will use the patching process (opatch for binary changes, datapatch for SQL changes).

This implies that, like our current bundle patches, a release update or revision must be RAC rolling, and can therefore be installed with zero down time.

The bundle series/bundle ID metadata which we have currently in 12.1.0.2 and 12.2.0.1 will not work for this new model. Hence we will have instead the following new metadata:

From the file : $ORACLE_HOME/sqlpatch/28090523/22329768/28090523.xml

Metadata Location in Patch Metadata Location in dba_registry_sqlpatch
Build timestamp buildTimestamp  attribute of XML  descriptor build_timestamp
Build description buildDescription  attribute  of  XML  descriptor Encoded in target_version
Patch type patchType  attribute of XML descriptor patch_type column
RUs and RURs in which a given file has been included ruData  element of XML descriptor N/A

 

 

 

 

 

 

 

The source_* and target_* columns of dba_registry_sqlpatch will encode the full version information .

bundledata.xml is no longer needed as the XML descriptor includes all the patch specific and RU metadata

 

Complete Version Description:

In order to completely describe a given release update, all 3 components of the version are needed:

  • 5 digit version string (i.e. 18.3.2.0.0). Although this string includes 5 digits, currently only the first 3 will be updated with this model.
  • Patch type (i.e. RU)
  • Release_Update_Revision

Collectively these 3 pieces of metadata are known as a version description.

The oraversion executable can be used to determine the current version description of the binaries in a given oracle home:

$ORACLE_HOME/bin/oraversion -baseVersion
18.0.0.0.0$ORACLE_HOME/bin/oraversion -buildDescription
Release_Update

$ORACLE_HOME/bin/oraversion -h
This program prints release version information.

These are its possible arguments:

-compositeVersion: Print the full version number: a.b.c.d.e.
-baseVersion: Print the base version number: a.0.0.0.0.
-majorVersion: Print the major version number: a.
-buildStamp: Print the date/time associated with the build.
-buildDescription: Print a description of the build.
-help: Print this message.

 

Binary patching can be accomplished with opatch inplace operations, or out of place patching between two homes/containers at different releases. SQL patching may require more than one operation to complete, depending on the binary and SQL versions when datapatch is invoked, as described below.

Scenario SQL Version Binary Version Datapatch Operations
1 18.1.0.0.0 18.3.0.0.0 RU Apply from binary to SQL using 18.3.0.0.0 RU apply script
2 18.2.2.0.0 RUR 18.3.0.0.0 RU Rollback from SQL to common version 18.2.0.0.0 RU using 18.2.2.0.0 RUR rollback script
Apply from common version 18.2.0.0.0 RU to binary using 18.3.0.0.0 RU apply script
3 18.3.2.0.0 RUR 18.2.1.0.0 RU Rollback from SQL to common version 18.2.0.0.0 RU using 18.3.2.0.0 RUR rollback script
Apply from common version 18.3.2.0.0 RUR to 18.2.1.0.0 RU using 18.2.1.0.0 RU apply script

 

 

 

 

 

 

The recommended action for customers is to stay on the RU branch unless they only want security and regression fixes.

SQL Registry Changes :

The structure of registry$sqlpatch and dba_registry_sqlpatch in 18c is as follows. Columns in bold are new in 18c.

Column Type Description
action VARCHAR2(15) APPLY, ROLLBACK
status VARCHAR2(25) BEGIN, END, SUCCESS, WITH ERRORS, WITH ERRORS (RETRYABLE)
target_build_description VARCHAR2(80) Build description after the patch is installed
source_build_description VARCHAR2(80) Build description on which the patch will be installed
target_build_timestamp TIMESTAMP Build timestamp after the patch is installed
source_build_timestamp TIMESTAMP Build timestamp on which the patch will be installed
target_version VARCHAR2(15) Five digit version after the patch is installed
source_version VARCHAR2(15) Five digit version on which the patch will be installed
logfile VARCHAR2(500) Full path to logfile
ru_logfile VARCHAR2(500) Full path to logfile for RU specific operations
patch_type VARCHAR2(10) INTERIM, RU, RUR
flags VARCHAR2(10) One or more of the following characters. Either N or U will always be present.N: Normal mode

U: Upgrade mode

J: Patch updates Java (and hence should be run first)

A: Application patch

R: Patch installation attempt retried

M: Patch installation was done at the same time as another installation

description VARCHAR2(100) Patch description (typically the same as the bug subject)
patch_id NUMBER Patch ID (typically the same as the bug ID)
patch_uid NUMBER Patch UID (universal patch identifier)
action_time TIMESTAMP Time of installation attempt
install_id NUMBER Unique ID per datapatch invocation
patch_descriptor XMLType XML descriptor for this patch
patch_directory BLOB Zipped contents of patch directory

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Note the following about this table structure:

  • The bundle_series, bundle_id, and bundle_descriptor columns (present in 12.2) have been removed.
  • As in 12.2 and earlier, each row in the SQL registry represents one install attempt for one patch.
  • As in 12.2 and earlier, each PDB will have its own SQL registry.
  • The source_* and target_* columns, along with the patch_type column, encode the complete version information for the environment on which the patch is installed, as well as the environment after patch is (successfully) installed.
  • The build description may be empty. Since interim patches do not change the version information, source_* and target_* will both have the same value, namely the version on which the patch is applied

We introduce a second SQL registry table registry$sqlpatch_ru_info, with associated view dba_registry_sqlpatch_ru_info, with the following structure:

Column Type Description
ru_build_description VARCHAR2(80) Build description for this RU patch
ru_build_timestamp TIMESTAMP Build timestamp for this RU patch
ru_version VARCHAR2(15) Five digit version for this RU patch
patch_id NUMBER Patch ID (typically the same as the bug ID)
patch_uid NUMBER Patch UID (universal patch identifier)
patch_descriptor XMLType XML descriptor for this patch
patch_directory BLOB Zipped contents of patch directory

 

 

 

 

 

 

 

 

Each row in dba_registry_sqlpatch_ru_info will contain the metadata associated with an RU patch.

Following files used by Datapatch :

File or Directory Description
sqlpatch/sqlpatch Shell wrapper script for datapatch
sqlpatch/sqlpatch.bat Windows wrapper script for datapatch
sqlpatch/sqlpatch.pl Entry datapatch Perl script
sqlpatch/sqlpatch.pm Main datapatch Perl script
sqlpatch/sqlpatch_bootstrap.sql Datapatch bootstrap script
sqlpatch/lib Directory with datapatch Perl libraries
rdbms/admin/dbmssqlpatch.sql dbms_sqlpatch package header
rdbms/admin/prvtsqlpatch.plb dbms_sqlpatch package body
rdbms/admin/dbmsqopi.sql dbms_qopatch package header
rdbms/admin/prvtqopi.plb dbms_qopatch package body
rdbms/admin/catconst.pm Perl version constants
rdbms/admin/dbms_registry_basic.sql dbms_registry constants
rdbms/admin/preupgrade.jar pre upgrade utility

 

 

 

 

 

 

 

 

 

 

 

 

Simpler call to patch_initialize:

Currently the call to patch_initialize in the apply or rollback script looks as follows:

BEGIN
dbms_sqlpatch.patch_initialize(p_patch_id => patch ID,
p_patch_uid => patch UID,
p_flags => ‘&flags’,
p_description => ‘&description’,
p_action => ‘APPLY’,
p_logfile => ‘&full_logfile’,
p_bundle_series => ‘&bundle_series’);
END;
/

The flags, description, action, and bundle series parameters are no longer needed, so the call starting in 18.1 should look as follows:

BEGIN
dbms_sqlpatch.patch_initialize(p_patch_id => patch ID,
p_patch_uid => patch UID,
p_logfile => ‘&full_logfile’);
END;
/

 

Procedure Changes :

The following table summarizes the changes to each of the main routines within datapatch.

Routine Changes
initialize Removal of obsolete command line arguments such as -bundle_series
bootstrap No change
get_current_patches XML descriptor is required, read and store new metadata into the patch descriptors
add_to_queue Support new queue definition and perform logic to determine starting and ending RU version
install_patches Support new queue definition and new install script format
validate_logfiles No change

 

 

 

 

 

 

 

 

 

Addional Logfile :

sqlpatch_debug.log  === > Starting from 18c this logfile will be created for each invocation of datapatch .

Location : $ORACLE_BASE/cfgtoollogs/sqlpatch_pid_<timestamp>

No comments yet

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

%d bloggers like this: