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. 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:
- 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
Hi Mike,
the necessity of applying datapatch was of course underscored, but asked if he could apply net day or week. You replied that you must as next patch will cause issue, but we know patches come quarterly, that is 3 months, so how about delay of one 1 or weeks?
Technical note: 1635056.1, says you can run datapatch multiple times if it fails, fix issue and re-run. To me that is obvious running next day and week should be ok, as long as next patch should not run until you have completed this one else run into trouble, s you said
Hi Nazir,
again, to me, running datapatch is directly connected to the patch apply, and not an action I would ever postpone.
You don’t service your car with new tires and tighten the screws a week after (hopefully).
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
Hi Mike,
thx for sharing all the informations.
I have a question about the parallel execution of datapatch.
We have more then one Oracle Home (RAC, 19.19) with 2 Database, can i run the datapatch parallel in each database?
Many Thx in advance
Martin
Hi Martin,
you can start multiple instances of datapatch, either from the same or from different homes.
The issue that a certain file gets locked has been removed years ago. Hence, you should not see any problems.
Cheers,
Mike
Hi Mike Thx for your quick reply!
That sounds good,
Can i do this also with 2 instances in one DBHOME ?
Cheers
Martin
Yes, you can 🙂
Cheers
Mike
Hi Mike,
Oracle claims in different articles, and README that datapatch can be run while users connected, and rolling upgrade has no outage at all, as autopatch will run datapatch on last primary server..but then I recently had session skilled and search also confirmed this possibility.
Is datapatch online running is misleading?
Can I run datapatch when users are connected?
The automation tooling executes Datapatch while users are connected to the database. Of course, one might run into the same issues described above. But Oracle have automation to handle the situation. If necessary, the database KILLS any sessions blocking Datapatch.
/home/oracle > cat /opt/oracle//cfgtoollogs/sqlpatch/sqlpatch_57782_2024_08_19_18_18_21/sqlpatch_catcon__catcon_kill_sess_57782_ALL.sql
ALTER SYSTEM KILL SESSION ‘1768,38014’ force timeout 0 — process 63689
/
ALTER SYSTEM KILL SESSION ‘4916,12902’ force timeout 0 — process 63706
/
ALTER SYSTEM KILL SESSION ‘5490,49897’ force timeout 0 — process 63719
/
Hi Nazir,
when you get issues with a datapatch run, you should file and SR and have Oracle Support analyze it.
For instance, it happened to me recently that a customer complained. But we saw that they included the ORA-20000 DBMS_STATS patch into their gold image – and this fix isn’t RAC rolling, and hence will cause issues. But I know from other customers that especially AQ could cause trouble with datapatch.
Thanks,
Mike
Regarding the database not having to be in upgrade mode – is that only true for OJVM patches? I tried it out when applying 19.28 and got errors because the pdbs were not in upgrade mode.
Thanks.
Hi Tricia,
neither with or without OJVM PDBs are supposed to be in upgrade mode for patching.
Can you elaborate this a bit more?
Thanks
Mike
Here are the errors I got from datapatch
Error: prereq checks failed!
patch 38010839: The pluggable databases that need to be patched must be in upgrade mode
patch 38270838: The pluggable databases that need to be patched must be in upgrade mode
Prereq check failed, exiting without installing any patches.
I was applying 19.28 on AIX (patches 37952382, 28090604, 37738908, 38270838)
Sorry, I submitted a response but it doesn’t look like it got posted.
I applied the 19.28 RU and associated patches and when I ran datapatch I got the following errors:
Error: prereq checks failed!
patch 38010839: The pluggable databases that need to be patched must be in upgrade mode
patch 38270838: The pluggable databases that need to be patched must be in upgrade mode
Prereq check failed, exiting without installing any patches.
Sorry Tricia,
there was a problem on my side with pending comments – and I guess this is not current anymore.
But it looks as if the patches you chose require UPGRADE mode? Strange, though. Unusual.
Is this still an issue in newer RUs?
Mike
I have gone back to setting upgrade mode because I was consistently seeing it. I will try again on the next patch and see if it still happens. It does seem to be only for container dbs – I have a couple of single instance dbs that work fine without upgrade mode.
Thanks Tricia!
Cheers,
Mike
I just applied 19.32 on a Windows server with one container db and it didn’t require upgrade mode. I also have AIX servers, I will try it there too and update you if it doesn’t work.
Thanks.
Thanks for letting me know!
thanks
Mike
I applied 19.32 on an AIX server and it required upgrade mode.
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED DBA05DV
The following interim patches will be rolled back:
38523609 (OJVM RELEASE UPDATE: 19.30.0.0.260120 (38523609))
28090604 (DBMS_STATS FAIL ORA-12899 VALUE TOO LARGE FOR COLUMN SYS.WRI$_OPTSTAT_OPR .JOB_)
39047206 (MERGE ON DATABASE RU 19.30.0.0.0 OF 38844733 33053101)
Patch 39472050 (Database Release Update : 19.32.0.0.260721 (39472050)):
Apply from 19.30.0.0.0 Release_Update 260122161835 to 19.32.0.0.0 Release_Update 260729223009
The following interim patches will be applied:
39222882 (OJVM RELEASE UPDATE: 19.32.0.0.260721 (39222882))
28090604 (DBMS_STATS FAIL ORA-12899 VALUE TOO LARGE FOR COLUMN SYS.WRI$_OPTSTAT_OPR .JOB_)
39657094 (DATAPUMP BUNDLE PATCH 19.32.0.0.0)
Error: prereq checks failed!
patch 39047206: The pluggable databases that need to be patched must be in upgrade mode
Prereq check failed, exiting without installing any patches.
Maybe because 39047206 was a merge patch?
Hi Tricia,
at first, I personally would not mix the 19.30 OJVM with the 19.32 Database RU. Plus, you have another MERGE:
39047206 (MERGE ON DATABASE RU 19.30.0.0.0 OF 38844733 33053101)
which is visibly for 19.30 and can’t be applied to 19.32 either.
Therefore, you please need to clarify your install queue and make everything 19.32.
Then, the next step is to find out why STARTUP UPGRADE should be required.
I doubt that this is because of OJVM or RU but rather these ones:
28090604 (DBMS_STATS FAIL ORA-12899 VALUE TOO LARGE FOR COLUMN SYS.WRI$_OPTSTAT_OPR .JOB_)
39657094 (DATAPUMP BUNDLE PATCH 19.32.0.0.0)
DBMS_STATS and DATAPUMP fixes are excluded from “rolling”.
For Data Pump, you have control. But for DBMS_STATS, you can try – still, it is likely that datapatch will not complete. But it is worth a trial, and you can always re-run datapatch again without harm.
Cheers,
Mike
The 19.30 patches – 38523609, 28090604 (19.30), and 39047206 were being rolled back.
I am not sure what you mean by excluded from rolling.
Are you suggesting applying the patches and running datapatch for each indvidually to see which one is requiring upgrade mode? I can see where that would be nice to know but once I figured it out, I would have to run patching twice each time I applied patches – once with upgrade mode and once without.
It seems the general answer is that if you have patches other than the RU, you will likely need upgrade mode on AIX.
Thanks.
Hi Tricia,
with “rolling” I referred to: These patches I listed aren’t RAC Rolling. Therefore, you can’t patch with the database staying up and running. You have to take it down anyway. Still, you are right, no proof or argument that STARTUP UPGRADE would be required.
And no, you need to apply them all together – but into a new home, and not into the existing home. Out-of-place patching is a must (meaning: New fresh home).
Then configure your resources to point to the new home, and run datapatch.
If this requires STARTUP UPGRADE, then something is wrong. I would like to see the watch included which requires this (see the READMEs).
Cheers,
Mike