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
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 :
- The XML patch descriptor found under $ORACLE_HOME/sqlpatch/bug id/bug uid/bug id.xml
- 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:
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>
WikiDBA 