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.
Photo by National Cancer Institute on Unsplash
.
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. And with each new version of opatch you may also get a new version of datapatch.
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:
- 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
- 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
- Patch monitor script for Oracle 19c
- Do you need STARTUP UPGRADE when you patch?
- MOS Note: 2680521.1 – Datapatch User Guide
- What does datapatch do when you have different components in your containers?
- Binary patching is slow because of the inventory
–Mike
Hi Mike , Thanks for sharing this fantastic blog . Datapatch is something which is crucial to be applied post RU .Few Queries :
1. Is it recommended / mandatory to apply the datapatch post apply the quarterly RU ? Or I just apply the RU for now and since datapatch takes a bit of more time to apply and I have a strict downtime window and I can apply the datapatch next day or some other time as it is an online operation and will not have any impact on my ongoing db OLTP production operations ?
2. Does datapatch apply time varies depending on the DB size ? Eg a applying datapatch on DB of 5TB will take more time than 250GB database or how busy is my DB decides the time .
Hi Mayur,
you must execute datapatch after patching. If you don’t do it, you’ll get in trouble when you patch the next time. Then you must run it.
But datapatch gets run when the database is up and running. Hence, it should not cause any additional downtime.
And the run time does have to do with many factors (number of changes, impact of changes, how many patches in an RU require a script to be run, patch history, number of components, number of PDBs) but not with the size of the database.
Cheers,
Mike
There is something nobody mentioned. What actually happends when you run “datapatch”. One of the action is to store the patch in the meta data of the container/pluggable database.
Realy ? Yes try it. Take 2 CDB’s with for example one with 19.12 and one with 19.14. Unplug a PDB from the 19.14 CDB and plug it into the 19.12. Run datapatch. You will see the message that is is rolling back the RU 19.14 before applying the 19.12. How can datapatch rolll back a 19.14 RU in a 19.12 installation ? Because the 19.14 RU install/remove scripts are included in the PDB.
Hi “P”,
yes, we explained this in our Virtual Classroom Seminar #1 (Release Strategy and Patching).
See slide 94.
Rollback or reapply if necessary
==> Automatically keeps zip of necessary files in database
=======> (e.g., for unplug-plug into a CDB) since Oracle 18c
=============> BLOB column: PATCH_DIRECTORY in CDB_REGISTRY_SQLPATCH
This column contains the zip archive with the rollback script. When needed, datapatch will unzip it and apply the rollback,
In the old days you’d have to copy around the required directory by yourself (a nasty workaround).
Thanks.
Mike
nice
Nice article, very useful information,
My site is https://oracleappsdba.com
Hi Mike, just since I’m currently patching our RACs: do you know why opatchauto is executing datapatch on both the nodes and why datapatch takes much longer on the second node?
Thx
Axel
Hi Axel,
a more recent version of opatchauto is supposed to have this fixed.
Cheers,
Mike
Hi Mike,
it’s not clear for me, why If you want to speed up patching with datapatch, we need to recompile beforehand.
What is the technical reason and explanation of that?
Thanks in advance.
Hi David,
datapatch has an embedded default threshold which you are able to adjust. If there are less than 300 objects needing compilation, it will ignore it. But if there are more than 300, it will attempt recompilation. Now, this number includes user objects as well. Hence, you better check and compile before datapatch does it since this will add extra time to your patching run.
Cheers
Mike
Hi Mike,
During the rolling patching of a RAC with GI RU + OJVM,, is it best practice
* to call datapatch after GI RU and before OJVM patching (and one more time after OJVM) or
* to call ‘final’ datapatch after both binary patchings
?
Best regards
Stéphane
Hi Stephane,
GI does not require OJVM patching.
https://mikedietrichde.com/2020/01/24/do-you-need-to-apply-ojvm-patches-to-grid-infrastructure/
Cheers
Mike
Hi Mike,
Sorry, my question was misleading. I am using the GI patch in order to patch both the GI and the DBs at at once in a rolling fashion on RAC.
In the meantime I noticed that opatchauto is applying the datapatch both on SIHA and on RAC when the last node is binary patched.
So my question is obsolete.
Always a pleasure to read your blog.
Best regards
Stéphane
Thanks Stephane – this was an enhancement we fought for, and which got added I think with the 31 version of opatch “secretly”.
Cheers
Mike
Is it okay to run datapatch on multiple command window in Windows environment?
Hi Wilson,
you mean against different databases? Sure, it is.
Cheers
Mike
Hi Mike,
In the article under the heading “STARTUP UPGRADE?” you say
“Of course, in RAC environments you may realize this with the resource setting.”
I’d like clarification on this please. Do you mean the services created for PDB (which are crs resources)?
These articles tell us not to use PDB saved state with RAC, but to use services:
Doc ID 2833029.1
Doc ID 1933511.1
Thanks
Robyn
Hi Robyn,
this is what I was referring to.
Thanks
Mike