From all my quarterly blog posts you know that I patch usually in-place for space and other reasons. And you may have similar reasons when you patch. But it is not ideal – and just speaking about risk and downtime, you should always patch out-of-place. So let me show you how Patching to Oracle 19.19.0 out-of-place with additional patches works.
Photo by Stefano Ferretti on Unsplash
What do I need?
Actually, this is not complicated at all. And if you never done it this way, give it a try now please.
- Base release Oracle Database 19.3.0 image
- opatch 37 from April 2023
- Release Update 19.19.0 from April 2023
- Data Pump Bundle Patch on top of RU 19.19.0 from April 2023
- OJVM Patch Bundle 19.19.0 from April 2023
- MRP1 on top of 19.19.0 from May 2023
And of course, you can add more fixes. Just make sure they are built on top of 19.19.0. The above list is conflict free to each other. I described the process already here. But today I will add more patches, and then have AutoUpgrade patch my database to the new home.
Where do I start?
Starting point is my new home. I will unpack the base release 19.3.0 into this home. And of course, set your environment correctly before you start.
cd /u01/app/oracle/product mkdir 1919 cd 1919 cp /home/oracle/stage/LINUX.X64_193000_db_home.zip . unzip LINUX.X64_193000_db_home.zip rm LINUX.X64_193000_db_home.zip rm -rf OPatch cp /home/oracle/stage/p6880880_210000_Linux-x86-64.zip . unzip p6880880_210000_Linux-x86-64.zip rm p6880880_210000_Linux-x86-64.zip
I need to remove the existing OPatch subdirectory, and exchange it with the most current one. Always include this step, regardless of what the readmes tell you.
Let’s prepare our patches
It’s key that you separate all additional patches and bundles into separate subdirectories. Otherwise, you will overwrite the PatchSearch.xml file, and patches can’t be accessed and installed.
.
├── dpbp
│ ├── 35261302
│ └── PatchSearch.xml
├── mrp
│ ├── 35333937
│ └── PatchSearch.xml
├── ojvm
│ ├── 35050341
│ └── PatchSearch.xml
└── ru
├── 35042068
└── PatchSearch.xml
We can kick it off
Now let me install everything with just one command. And since the MRP currently is not known to the OUI (aka runInstaller), I need to add every of its subdirectories separately. This will be fixed in a later RU.
├── mrp │ ├── 35333937 │ │ ├── 34340632 │ │ ├── 35012562 │ │ ├── 35037877 │ │ ├── 35116995 │ │ ├── 35225526 │ │ ├── README.html │ │ └── README.txt │ └── PatchSearch.xml
Hence, my string will become a bit lengthy:
./runInstaller -applyRU /home/oracle/stage/ru/35042068 -applyOneOffs /home/oracle/stage/ojvm/35050341,/home/oracle/stage/dpbp/35261302,/home/oracle/stage/mrp/35333937/34340632,/home/oracle/stage/mrp/35333937/35012562,/home/oracle/stage/mrp/35333937/35037877,/home/oracle/stage/mrp/35333937/35116995,/home/oracle/stage/mrp/35333937/35225526
So what am I doing?
I use the command:
./runInstaller -applyRU ... -applyOneOffs ...
where I separate the one-offs (the OJVM and the Data Pump Bundle Patch are technically one-offs as well) by comma. Once Bug 35251760 – ADD SUPPORT FOR “NAPPLYONEOFFS” TO BE ABLE TO INSTALL MULTIPLE ONEOFFS OR MRP USING GRIDSETUP/RUNINSTALLER will be fixed, you’ll be able to pass on the main bug number for the MRP, and it will fetch the subpatches automatically.
And then it installs and applies
Now it will take a bit – but not too long usually.
Once we are here (in my VBox environment on my laptop’s SSD this finishes in 12 minutes):
$ ./runInstaller -applyRU /home/oracle/stage/ru/35042068 -applyOneOffs /home/oracle/stage/ojvm/35050341,/home/oracle/stage/dpbp/35261302,/home/oracle/stage/mrp/35333937/34340632,/home/oracle/stage/mrp/35333937/35012562,/home/oracle/stage/mrp/35333937/35037877,/home/oracle/stage/mrp/35333937/35116995,/home/oracle/stage/mrp/35333937/35225526 Preparing the home to patch... Applying the patch /home/oracle/stage/ru/35042068... Successfully applied the patch. Applying the patch /home/oracle/stage/ojvm/35050341... Successfully applied the patch. Applying the patch /home/oracle/stage/dpbp/35261302... Successfully applied the patch. Applying the patch /home/oracle/stage/mrp/35333937/34340632... Successfully applied the patch. Applying the patch /home/oracle/stage/mrp/35333937/35012562... Successfully applied the patch. Applying the patch /home/oracle/stage/mrp/35333937/35037877... Successfully applied the patch. Applying the patch /home/oracle/stage/mrp/35333937/35116995... Successfully applied the patch. Applying the patch /home/oracle/stage/mrp/35333937/35225526... Successfully applied the patch. The log can be found at: /u01/app/oraInventory/logs/InstallActions2023-05-22_02-57-50PM/installerPatchActions_2023-05-22_02-57-50PM.log Launching Oracle Database Setup Wizard...
I will need to pass in my root command in the OUI’s screen:
And another screen:
And another screen …:
And more … I am silently hoping that there are ways to suppress these screens …
And now the important screen:
And then we are (almost) done
Well, finally after all these clicks, I’m getting to the OUI’s summary page where I can click the “Install” button:
Which I happily do – but just to get to a screen I will never understand:
Didn’t I say before (and confirm by passing on my root credentials) that I want the OUI do this for me? I have no idea why this screen is coming up …
Installation with all patches complete.
Let me do a quick check before we continue:
$ $ORACLE_HOME/OPatch/opatch lspatches 35225526;FRA E18POD EPM | ORA-00942 TABLE OR VIEW DOES NOT EXIST WHEN RUNNING SELECT QUERY 35116995;19.X DATAPATCH FAILS WITH ORA-01422 ,ORA-20004 NO FILE FOUND IN SCRIPT RDBMS/ADMIN/NOTHING.SQL 35037877;EM CODE IS BROKEN IN 19.18 35012562;OR EXPANSION IS NOT PICKED UP IN QUERY WITH CONTAINS OPERATOR AFTER UPGRADE TO 19C 34340632;AQAH SMART MONITORING & RESILIENCY IN QUEUE KGL MEMORY USAGE 35261302;DATAPUMP BUNDLE PATCH 19.19.0.0.0 35050341;OJVM RELEASE UPDATE: 19.19.0.0.230418 (35050341) 35042068;Database Release Update : 19.19.0.0.230418 (35042068) 29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399) OPatch succeeded.
This looks good.
But I am still not done
Final steps is to lift my databases to the new home. You can – of course – do this database-by-database. But we are in a modern IT environment and hence, we will use AutoUpgrade certainly and automate this task.
Let’s have a look at the config file at first:
global.autoupg_log_dir=/home/oracle/upg_logs global.timezone_upg=no global.restoration=no upg1.source_home=/u01/app/oracle/product/19 upg1.target_home=/u01/app/oracle/product/1919 upg1.sid=CDB2
And then I can kickoff AutoUpgrade to lift my database from 19.18.0 to 19.19.0 and execute all the necessary checks and steps.
$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config PATCH.cfg -mode deploy AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM) AutoUpgrade 22.5.221011 launched with default internal options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 CDB(s) plus 1 PDB(s) will be processed Type 'help' to list console commands upg>
And shortly later …
upg> lsj +----+-------+---------+---------+-------+----------+-------+-------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+-------------------+ | 100| CDB2|DBUPGRADE|EXECUTING|RUNNING| 23:04:44|24s ago|0%Patching CDB$ROOT| +----+-------+---------+---------+-------+----------+-------+-------------------+ Total jobs 1
Now AutoUpgrade completed the patching process successfully:
upg> Job 100 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs restored [0] Jobs pending [0]
All set. And you can automate this perfectly well for all your databases.
Are we done now?
Not fully. Once you are fine with the new home, all is working well, you may want to cleanup the previous, old home. And in addition, you may also cleanup the patch leftovers from the previous patch run as I explained in Cleaning up patch artifacts and improving patching performance.
One of the real benefits with out-of-place patching is that you can go back to the previous home easily – while when you patch in-place, you will have a lot of extra work to do rolling out patches in the right order.
Further Links and Information
- Patching Process with MRPs
- MOS Note: 888.1 – Primary Note for Database Proactive Patch Program
- MOS Note: 555.1 – Oracle Database 19c Important Recommended One-off Patches
- Simple database installation with runInstaller -applyRU -applyOneOffs
–Mike
This is a very nice compressed guideline of all we need to do.
Even including the “rm -f *.zip”, which is often disregarded 🙂
Thank you for that, Mike.
And don’t forget to update the listener-config (my own favourite mistake)
Thanks for the hint, Dieter – let me check if AutoUpgrade does update the listener config as well 🙂
But I added your hint to the blog post, too!
Cheers,
Mike
I finally got my answer. we must install MRPs, one by one via applyOneOffs option. it would be nice to use only MRP patch number instead of all single patches. Thanks for explaining Mike.
Hi Mustafa,
I updated the blog post with the bug number the OUI team is supposed to fix soon.
Then you can add the root patch number of the MRP instead of each subdirectory.
Cheers
Mike
Hi Mike!
Some time ago I found amazing thing:
The MRP patch can still be installed with a single command, without having to list all the patches that make it up. I just checked on DBRU version 19.19, GI MRP and OPatch version 12.2.0.1.37 with this command:
./runInstaller -applyRU $HOME/INSTALL/35037840/35042068 -applyOneOffs $HOME/INSTALL/35333960,$HOME/INSTALL/35050341,$HOME/INSTALL/29213893
And the result:
29213893;DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE
35050341;OJVM RELEASE UPDATE: 19.19.0.0.230418 (35050341)
35225526;FRA E18POD EPM | ORA-00942 TABLE OR VIEW DOES NOT EXIST WHEN RUNNING SELECT QUERY
35116995;19.X DATAPATCH FAILS WITH ORA-01422,ORA-20004 NO FILE FOUND IN SCRIPT RDBMS/ADMIN/NOTHING.SQL
35037877;EM CODE IS BROKEN IN 19.18
35012562;OR EXPANSION IS NOT PICKED UP IN QUERY WITH CONTAINS OPERATOR AFTER UPGRADE TO 19C
34340632;AQAH SMART MONITORING & RESILIENCY IN QUEUE KGL MEMORY USAGE
33872599;OCW Interim patch for 33872599
35042068;Database Release Update : 19.19.0.0.230418 (35042068)
So maybe OIU already knows how to put MRP patches?
Regards, Vadim
That is interesting – so you apply the GI MRP to the database home. Is this a Oracle Restart (Single Instance High Availability) environment?
Thanks
Mike
Hi Mike!
No it is not Oracle Restart, just simple DB Home. I use GI MRP, because it contains same patches as DB MRP and OCW patch in addition (I checked it before installing of course). I prefer to install OCW on DB Home, because sometimes I need to convert DB-only instance with datafiles in filesystem to DB + GI with ASM datafiles storage. And my DB will be already ready for GI with OCW patch installed.
Regards, Vadim
Hi Vadim,
got you – I assumed that you do this with a Oracle Restart home. The teams are still evaluating things, and I think you are in contact with Support as well about this.
Cheers,
Mike
it was working for database home too if MRP is installed via opatchauto. when MRPs are installed via opatch napply, then applyOneOffs stopped to work for Database home
To get rid of the GUI dialogs I am doing the command “….runInstaller -silent -responsefile /home/oracle/database.rsp…….” where the rsp is generated from the GUI-installer in advance. By doing this the job can be repeated on many servers through scripting or whatever you like.
Oh thanks – I silently assumed there would be a way – I will give this a try.
But does this work in combination with -applyRU and -applyOneOffs ?
Thanks for sharing!!!
Mike
Seems like it might be more efficient to include the OJVM and data pump patches in the Database release update.
Are there defensible reasons why someone patching the database would want to avoid those two patches?
OJVM is already included in 21c – and will be in 23c.
We are working on having DPBP included as well once it is RAC Rolling – but it isn’t yet.
Cheers
Mike
Hi Mike, may I ask you if autoupgrade does already support RAC (including GI) for the mentioned OOP-switch-step? So far my preferred way for applying RUs on our RACs was using the OOP-patching-feature of opatchauto with the disadvantage that additional one-off-patches have to be installed ‘manually’ afterwards.
Thanks & regards
Axel D.
Hi Alex,
yes, AU supports this – with one important restriction. It does not do the “rolling” approach right now. Thus, it restarts the DB – which is not intended and wanted in many cases. Future version of AU will support the rolling approach as well. And ALWAYS ONLY out-of-place.
Cheers
Mike
Hi Mike ,
What about the datapatch -verbose ,will autoupgrade utility will take care,
what if we have DG setup ? which home we need to switch like this first standby or primary ?
Regards,
Balaji
Sure, it does. Try it out please.
Otherwise, this would be really imperfect 🙂
DG setup:
At the moment, it takes care on the PROD environment only. But we work with high priority on the DG setups as well.
Normally, in a DG environment, you would flip the standby over to the new home already, then start redo apply again (that works nicely), and then you invoke AU on the primary and let it do its work.
Cheers,
Mike
Hi Mike,
I am not sure if this is in your zone. There are some patches like 29259068 or 26749785 etc. These are listed as a warning in orachk report. why they are not added into MRPs?
Very simple – please see the readmes:
https://updates.oracle.com/Orion/Services/download?type=readme&aru=25195443
==> This patch is non-Data Guard Standby-First Installable
https://updates.oracle.com/Orion/Services/download?type=readme&aru=24595385
==> This patch is non-RAC Rolling Installable.
Hence, they both can’t be included.
RUs are always guaranteed RAC Rolling and Standby First.
Cheers,
Mike
many thanks, you said “RUs are always guaranteed RAC Rolling and Standby First” so MRPs are too? I though they can be added into MRPs not RUs.
MRPs are RAC Rolling and Standby First as well.
Cheers
Mike
Hi Mike,
What about read-only Oracle Homes? Would this process work with this configuration?
Thanks,
Steve
Yes!
Cheers
Mike
Hi,
Can I install Combo RU 35058163 in the same manner (one PatchSearh.xml file for both patches) with option -applyRU?
I don’t think so – I think you must point to the individual subdirectories. But I have not tried it …
Can you give me feedback please once you had a chance to try it out?
Cheers
Mike
Hi Mike ,
I will have to patch my env from 19.16 to 19.19 and ive been doing in our test env the following order with Opatch apply (not from the runInstaller) .
The following is the order of my patching the binaries on new ORACLE_HOME (out of place)
LINUX.X64_193000_db_home.zip ==> 19.3
p35058163_190000_Linux-x86-64.zip ==> 19.19 (OJVM + DB Update (35058163))
Patch 35261302 – DATAPUMP BUNDLE PATCH 19.19.0.0.0
Patch 34614081 – AH DIAGNOSTIC : NOT DUMP THE SQL_ID FOR USER QUERIES IN ALERT.LOG (BUG 33118355)
Patch 34832725 – ORA-4031 “KSU:STATS_FREELIST” AND “KGLSESHTTABLE” ERRORS IN 19C
Patch 35012562 – OR EXPANSION IS NOT PICKED UP IN QUERY WITH CONTAINS OPERATOR AFTER UPGRADE TO 19C
Patch 35095748 – NO SEMI JOIN IF ONLY THE COLUMNS USED IN THE GROUP BY CLAUSE ARE PROJECTED
When iam trying to apply the patch 35095748 ,while performing the “opatch prereq CheckConflictAgainstOHWithDetail -ph ./” , i got the below conflict
Invoking prereq “checkconflictagainstohwithdetail”
ZOP-40: The patch(es) has conflicts with other patches installed in the Oracle Home (or) among themselves.
Prereq “checkConflictAgainstOHWithDetail” failed.
Summary of Conflict Analysis:
There are no patches that can be applied now.
Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
35012562, 35095748
Conflicts/Supersets for each patch are:
Patch : 35095748
Conflict with 35012562
Conflict details:
/u01/app/oracle/product/19.19.0/db_1/lib/libserver19.a:kko.o
OPatch succeeded.
Please suggest/advise.
Thanks,
Kumar
Hi Kumar,
the message tells it already:
Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
35012562, 35095748
Please open an SR and request a merge.
Cheers
Mike