How to speed up datapatch – and much more information

Ok, I promised to write more about patching speed. And this blog post will be just the root post targeting datapatch speed. I will link also to blog posts I’ve written before but also share knowledge which may not be obvious at first stage. The overall topic will be How to speed up datapatch – and much more information.

.

What is datapatch

Let me start at the ground level and explain what datapatch is and why it exists.

When you are still on Oracle Database 11g, you may wonder what datapatch is all about. Certainly, I hope that you are not on 11g anymore. Hence, most readers will have used datapatch before already. It basically came in to satisfy software/database patching in a Multitenant environment where you need to adjust not only a single database. Even in a single tenant environment with only one PDB you need to apply SQL and PL/SQL changes to CDB$ROOT but also to the PDB and the – read-only – PDB$SEED.

datapatch is the patching vehicle which applies SQL and PL/SQL changes to all your databases and/or containers and updates the data dictionary.

You can find datapatch in your $ORACLE_HOME/OPatch directory. A new version may be introduced with a Release Update but not with opatch since there is only a PERL wrapper deployed with opatch.

But have you ever opened datapatch in an editor? It is fairly short and only basically does this:

$ORACLE_HOME/sqlpatch/sqlpatch $@

Now when you open sqlpatch, it may not wonder you anymore that it will just call another “buddy” to do actual work:

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/sqlpatch -I$ORACLE_HOME/rdbms/admin -I$ORACLE_HOME/sqlpatch/lib $ORACLE_HOME/sqlpatch/sqlpatch.pl $@

So now we come closer. It is actually a perl script. And with all these steps, the right environment and patching parameters will be set, it will do the logging and clean up afterwards.

But at the bottom line it utilizes SQL*Plus to execute scripts and statements.

 

Tracking patches

One of the central components of datapatch is that it tracks patching within the database. On this blog you can find several scripts to monitor the datapatch actions and patching history within your database.

This is how it looks in one of my environments:

   CON_ID    ACTION_TIME    PATCH_ID    PATCH_TYPE      ACTION                                              DESCRIPTION    SOURCE_VERSION    TARGET_VERSION 
_________ ______________ ___________ _____________ ___________ ________________________________________________________ _________________ _________________ 
        1 2019-04-28        29517242 RU            APPLY       Database Release Update : 19.3.0.0.190416 (29517242)     19.1.0.0.0        19.3.0.0.0        
        1 2019-10-16        30125133 RU            APPLY       Database Release Update : 19.5.0.0.191015 (30125133)     19.3.0.0.0        19.5.0.0.0        
        1 2020-01-21        30557433 RU            APPLY       Database Release Update : 19.6.0.0.200114 (30557433)     19.5.0.0.0        19.6.0.0.0        
        1 2020-04-15        30869156 RU            APPLY       Database Release Update : 19.7.0.0.200414 (30869156)     19.6.0.0.0        19.7.0.0.0        
        1 2020-07-15        31281355 RU            APPLY       Database Release Update : 19.8.0.0.200714 (31281355)     19.7.0.0.0        19.8.0.0.0        
        1 2020-10-21        31771877 RU            APPLY       Database Release Update : 19.9.0.0.201020 (31771877)     19.8.0.0.0        19.9.0.0.0        
        1 2021-01-20        32218454 RU            APPLY       Database Release Update : 19.10.0.0.210119 (32218454)    19.9.0.0.0        19.10.0.0.0       
        1 2021-04-21        32545013 RU            APPLY       Database Release Update : 19.11.0.0.210420 (32545013)    19.10.0.0.0       19.11.0.0.0       
        1 2021-08-09        32904851 RU            APPLY       Database Release Update : 19.12.0.0.210720 (32904851)    19.11.0.0.0       19.12.0.0.0       
        1 2021-12-15        33192793 RU            APPLY       Database Release Update : 19.13.0.0.211019 (33192793)    19.12.0.0.0       19.13.0.0.0       
        1 2021-12-16        33192694 INTERIM       APPLY       OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)         19.13.0.0.0       19.13.0.0.0       
        1 2022-01-19        33192694 INTERIM       ROLLBACK    OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)         19.14.0.0.0       19.14.0.0.0       
        1 2022-01-19        33515361 RU            APPLY       Database Release Update : 19.14.0.0.220118 (33515361)    19.13.0.0.0       19.14.0.0.0       
        1 2022-01-19        33561310 INTERIM       APPLY       OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)         19.14.0.0.0       19.14.0.0.0       
        2 2019-04-28        29517242 RU            APPLY       Database Release Update : 19.3.0.0.190416 (29517242)     19.1.0.0.0        19.3.0.0.0        
        2 2019-10-16        30125133 RU            APPLY       Database Release Update : 19.5.0.0.191015 (30125133)     19.3.0.0.0        19.5.0.0.0        
        2 2020-01-21        30557433 RU            APPLY       Database Release Update : 19.6.0.0.200114 (30557433)     19.5.0.0.0        19.6.0.0.0        
        2 2020-04-15        30869156 RU            APPLY       Database Release Update : 19.7.0.0.200414 (30869156)     19.6.0.0.0        19.7.0.0.0        
        2 2020-07-15        31281355 RU            APPLY       Database Release Update : 19.8.0.0.200714 (31281355)     19.7.0.0.0        19.8.0.0.0        
        2 2020-10-21        31771877 RU            APPLY       Database Release Update : 19.9.0.0.201020 (31771877)     19.8.0.0.0        19.9.0.0.0        
        2 2021-01-20        32218454 RU            APPLY       Database Release Update : 19.10.0.0.210119 (32218454)    19.9.0.0.0        19.10.0.0.0       
        2 2021-04-21        32545013 RU            APPLY       Database Release Update : 19.11.0.0.210420 (32545013)    19.10.0.0.0       19.11.0.0.0       
        2 2021-08-09        32904851 RU            APPLY       Database Release Update : 19.12.0.0.210720 (32904851)    19.11.0.0.0       19.12.0.0.0       
        2 2021-12-15        33192793 RU            APPLY       Database Release Update : 19.13.0.0.211019 (33192793)    19.12.0.0.0       19.13.0.0.0       
        2 2021-12-16        33192694 INTERIM       APPLY       OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)         19.13.0.0.0       19.13.0.0.0       
        2 2022-01-19        33192694 INTERIM       ROLLBACK    OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)         19.14.0.0.0       19.14.0.0.0       
        2 2022-01-19        33515361 RU            APPLY       Database Release Update : 19.14.0.0.220118 (33515361)    19.13.0.0.0       19.14.0.0.0       
        2 2022-01-19        33561310 INTERIM       APPLY       OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)         19.14.0.0.0       19.14.0.0.0       

I don’t want to go into the details of the output but you easily recognize that the view CDB_REGISTRY_SQLPATCH tracks all changes, not only RUs but also interim (one-off) patches.

Please do always two things:

  1. Don’t access DBA_REGISTRY_SQLPATCH but instead CDB_REGISTRY_SQLPATCH instead since your scripts will then display the correct information in an CDB environment, too
  2. Always set alter session set "_exclude_seed_cdb_view"=FALSE; since otherwise you will miss PDB$SEED – and this could end in a true nightmare. It does not harm to use this underscore even in non-CDB environments. And if you wonder why it is set to FALSE by default, please open an SR and refer any of the open bugs with this default parameter setting.

You may also potentially add a delimiter on ACTION_TIME to the above script to see only the patch actions maybe from the past 6 months, especially since 19c will have a longer life most likely. And be aware that the list is sorted by CON_ID at first. You may also prefer a sorting on the ACTION_TIME instead.

In summary, your database knows when it has been massaged by datapatch.

 

What if you forgot to run datapatch?

This is a common question we hear from time to time – and actually we see sometimes out there. You patched nicely with opatch, but somebody forgot to run datapatch.

At first, the fact that somebody forgot to run datapatch is bad. It means that your database operates with binary patches but misses the SQL and PL/SQL parts of a patch. Certainly, there are patches which have no SQL or PL/SQL representation. Usually optimizer fixes are such an examples. The fix for an issue is in the binary, and there is no adjustment in the dictionary necessary. The opposite is Data Pump. There usually you have a counter part in the SQL and PL/SQL APIs.

So please make sure to run:

$ORACLE_HOME/OPatch/datapatch -verbose

as a standard action when you applied the binary patches to your systems.

But see what happens if you did run it accidentally another time for no reason. No worries at all, it recognizes that it has nothing to do.

Current state of interim SQL patches:
Interim patch 33192694 (OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)):
  Binary registry: Not installed
  PDB CDB$ROOT: Rolled back successfully on 19-JAN-22 10.14.44.327180 PM
  PDB PDB$SEED: Rolled back successfully on 19-JAN-22 10.14.44.347566 PM
Interim patch 33561310 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 19-JAN-22 10.14.44.331372 PM
  PDB PDB$SEED: Applied successfully on 19-JAN-22 10.14.44.350816 PM

Current state of release update SQL patches:
  Binary registry:
    19.14.0.0.0 Release_Update 211225122123: Installed
  PDB CDB$ROOT:
    Applied 19.14.0.0.0 Release_Update 211225122123 successfully on 19-JAN-22 09.48.19.540225 PM
  PDB PDB$SEED:
    Applied 19.14.0.0.0 Release_Update 211225122123 successfully on 19-JAN-22 09.48.19.756424 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied

SQL Patching tool complete on Mon May 30 11:35:36 2022

And then it stops successfully with no treatment done to your database(s) and container(s).

 

Rolling or non-rolling, that’s the question

I need to mention one very important additional piece of information.

You may have wondered why some patches, for instance Data Pump patches, often are not included in Release Update (RU) patch bundles. And the simple explanation is that a patch in an RU is supposed to be “RAC rolling and standby-first“. But as soon as a patch has a SQL and/or PL/SQL part and adjust an API, as long as there is no way to block access to the API for all instances, there is a risk involved. Hence, such patches are excluded from patch bundles.

Hence, it also does not matter on which node of your cluster you invoke datapatch. But you need to invoke it only on one node, not on every node, since you patch the dictionary of your database.

 

STARTUP UPGRADE?

This is one of the oldest myths.

Your database or PDB does not have to be in STARTUP UPGRADE mode. Instead, a normal STARTUP is fine and sufficient. But it is very important that your PDBs are open. A closed PDB can’t be adjusted.

This is why we always recommend to use the SAVE STATE command to ensure that your PDBs are opened automatically after you took down the CDB. Of course, in RAC environments you may realize this with the resource setting.

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)

A while ago I wrote a blog post about whether a STARTUP UPGRADE is needed or not.

And repeating myself, it is not needed. We worked together with the patching team to correct also the OJVM READMEs.

 

Logging information

Now when you dig deeper you may recognize that datapatch logs quite a bit. And even though the logging is not perfect yet and lacks some things due to the nature of the underlying PERL libraries, there is a lot of good information in:

$ORACLE_BASE/cfgtoollogs/sqlpatch

So the first important part is that the logs aren’t in your actual $ORACLE_HOME.

What will you find in this directory? At first, you may wonder about the kind of naming schema of the logs. It has the process ID in the name rather then the container name or ID. Hence, it is a bit hard to navigate around.

But what types of logs are there? I’m stealing this from MOS Note: 2680521.1  – Datapatch User Guide since it lists the various logs:

 
sqlpatch_invocation.log A detailed log of the processing done by datapatch
sqlpatch_debug.log With –debug , contains additional information useful for troubleshooting
Bootstrap*.sql Script created by datapatch to run the bootstrap processing in the database.
bootstrap*.log The log files from running the bootstrap*.sql script in the database (per container).
Install*.sql Script created by datapatch to run the apply and/or rollback scripts for the patches.  For CDBs, this script is passed to catcon.pl to run in each container.
sqlpatch_catcon_*.log The log files created by catcon as it processes the install<n> script for each container.  (The individual apply/rollback logs are captured for each patch ID in the $ORACLE_BASE/cfgtoollogs/sqlpatch/patch_id directories).
sqlpatch_catcon__catcon_<action>.sql Scripts generated by catcon to perform specific actions in each container of a CDB.
sqlpatch_catcon__catcon_<PID>.lst catcon startup log file; identifies catcon log file locations

So the “invocation” log is the place where usually I start at when I want to analyze a patching run.

What we miss right now but have an enhancement open for is a patch_summary.log as we have it for the upgrade which shows you an overview at first glance.

If you want to see what can be done with such logs, what can be read and what can’t, you may read this blog post:

How does this look now in my environment? There is at first a log part which is aligned with the patch number, for instance:

├── 33515361
│   └── 24589353
│       ├── 33515361_apply_CDB2_CDBROOT_2022Jan19_21_47_29.log
│       ├── 33515361_apply_CDB2_PDBSEED_2022Jan19_21_47_54.log
│       ├── 33515361_apply_UP19_2022Jan19_23_17_14.log
│       ├── 33515361_ru_apply_CDB2_CDBROOT_2022Jan19_21_47_28.log
│       ├── 33515361_ru_apply_CDB2_PDBSEED_2022Jan19_21_47_53.log
│       └── 33515361_ru_apply_UP19_2022Jan19_23_17_13.log

This is the information for a specific patch. You see that it got applied not only to my CDB2 but also to another database, UP19.

And there is another tree with the sqlpatch information including the sqlpatch_invocation.log:

├── sqlpatch_29286_2022_01_19_21_46_51
│   ├── bootstrap1_CDB2_CDBROOT.log
│   ├── bootstrap1_CDB2_PDBSEED.log
│   ├── bootstrap1.sql
│   ├── install1.sql
│   ├── sqlpatch_autorecomp_CDBROOT.log
│   ├── sqlpatch_autorecomp_PDBSEED.log
│   ├── sqlpatch_catcon_0.log
│   ├── sqlpatch_catcon__catcon_29286.lst
│   ├── sqlpatch_debug.log
│   ├── sqlpatch_invocation.log
│   ├── sqlpatch_progress.json
│   └── sqlpatch_summary.json

Now be very aware that the root directory is badly ordered due the naming – and even more important, you will find ALL patch runs for ALL your homes using this $ORACLE_BASE within this directory.

This makes it sometimes a lovely exercise when I download a 3GB zip file from MOS looking for an issue for a specific database. Especially with many containers/PDBs it is great fun.

 

How to speed up datapatch – Rule no.1?

Now I wrote a lot of stuff about datapatch – but you may miss the information how to speed it up.

Of course there is a lot of things to write and discuss – and I don’t want this blog post to become too lengthy, especially since it will be hard to find the correct topic.

Still, one general rule needs to be told, especially for Multitenant environments.

Rule no.1

If you want to speed up patching with datapatch, recompile beforehand.

You will especially feel and see the difference in larger Multitenant environments. Make sure your PDBs are all open, and then invoke the recompilation:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql

 

How do we continue?

I hope you aren’t too disappointed at this point. Just a tiny little recommendation but many of you will certainly now state that this doesn’t fix your current problem. But I wanted to give a broad overview at first, and then dig deeper and go step-by-step. The following blog post will be much shorter but also cover an area we seem more and more happening.

So stay tuned 🙂

 

Further Links and Information

–Mike

 

Share this: