Nothing is certain in this life, except death, taxes and... patching.
We all agree that patching is an activity that should be performed on a regular basis, as it has the following benefits:
- Resolve bugs that affect performance and/or quality of results
- Resolve bugs affecting security
- Include enhancements / new features
On the other hand, we must consider that patches come in various forms:
| Release Updates (RU) | Contains hundreds of patches that resolve known issues. It is generated quarterly. |
| Monthly Recommended Patches (MRP) | It contains the patches for the bugs registered in the note 555.1. It is generated monthly for each RU. |
| Interim patch | Also known as one-off. They are used to solve specific problems, usually in a reactive manner. |
| Merge patch | It is generated to overcome conflicts between one-offs and RU/MRPs. |
In-place patching
This name is given to the traditional way of applying patches, since it implies working exclusively on the Oracle Home (hereinafter OH) that contains the executables in use by the database.
Let’s see how a patching cycle is developed under this modality.
Initial situation
We have a server called server1, in which Oracle Database Server 19c is installed, in an OH called DBHome1, which supports a single instance database.
This OH has the following patches applied:
| 34765931 database release update 19.18.0.0.0 |
| 34786990 ojvm release update 19.18.0.0.0 |
| 34777391 jdk bundle patch 19.0.0.0.230117 |
| 35573556 database mrp 19.18.0.0.230718 |
| 34972375 datapump bundle patch 19.18.0.0.0 |
$ opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
31222103;STRESS RAC ATPD FAN EVENTS ARE NOT GETTING PROCESSED WITH 21C GI AND 19.4 DB
32727143;TRANSACTION-LEVEL CONTENT ISOLATION FOR TRANSACTION-DURATION GLOBAL TEMPORARY TABLES
33973908;DBWR NOT PICKING UP WRITES FOR SOME TIME
34340632;AQAH SMART MONITORING & RESILIENCY IN QUEUE KGL MEMORY USAGE
34557500;CTWR CAUSED MULTIPLE INSTANCES IN HUNG STATE ON THE RAC STANDBY DATABASE
34632426;FADBRWT STRESS FA HITTING MULTIPLE INCIDENTS OF ORA-12850 IN ALERT LOG EVEN WITHOUT EXPLICITLY SETTING 12850 ERROR EVENT IN 19.17RU
34765931;DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)
34777391;JDK BUNDLE PATCH 19.0.0.0.230117
34783802;PARALLEL QUERY ON PARTITIONED TABLE RETURNS WRONG RESULT
34786990;OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)
34793099;STRESS FA CDB CREATION FAILS ON 19.17 WITH THE ORA-00704 BOOTSTRAP PROCESS FAILURE WHILE OPENING PDB$SEED
34809715;SQL WITH CASE EXPRESSION MAY FAIL WITH ORA-00932 IN 19.17 AND ABOVE
34810252;SPIN OFF FOR BUG 34808861 [ORA-00600 INTERNAL ERROR CODE, ARGUMENTS [KFDS_GETSEGREUSEENQ01] TERMINATED ALL DB INSTANCES
34832725;ORA-4031 KSU STATS_FREELIST AND KGLSESHTTABLE ERRORS IN 19C
34847038;ORA-16856 TRANSPORT LAG COULD NOT BE DETERMINED | TRANSPORT LAG (UNKNOWN) | NO LAG | 19.16.0.0
34861493;RESYNC CATALOG FAILED IN ZDLRA CATALOG AFTER PROTECTED DATABASE PATCHED TO 19.17
34879016;ALL SESSIONS HANG DUE TO INST_RCV BUFFER IS NOT GETTING WRITE PERMISSION
34972375;DATAPUMP BUNDLE PATCH 19.18.0.0.0
34988484;ORA-600 [KTUGETTEMPRSP NO TSO] EVEN WITH PATCH 34615905 APPLIED
35156936;ORA-7445 [KFFBNEW()+351] AFTER CONVERT TO ASM FLEX DISKGROUP
35160800;GG IE FAILS WITH ORA-14400 AT SYSTEM.LOGMNRC_USER AFTER ORACLE DB UPGRADE TO 19.18DBRU
35162446;NEED BEHAVIOR CHANGE TO BE SWITCHED OFF
35213579;MERGE ON DATABASE RU 19.18.0.0.0 OF 35037877 35046819
35246710;HIGH DIRECT PATH READ AFTER 19.18 DBRU PATCHING
35320424;MRP INTERMITTENT HANG ON TWO STANDBY DATABASES REGR_FIX
35386601;MERGE ON DATABASE RU 19.18.0.0.0 OF 34997479 35242133
35471762;KZTDE KZTSMTKI ENCRYPTION WALLET LOCATION MESSAGES FLOODING ALERT LOG POST 19.18
35556847;MERGE ON DATABASE RU 19.18.0.0.0 OF 35527937 35412607
1
2
Suspension of service
Before OH can be patched, the associated database must be shutdown.
SQL> shutdown immediate;
Oracle Home patching
The following patches are applied:
| 36233263 database release update 19.23.0.0.0 |
| 36199232 ojvm release update 19.23.0.0.0 |
| 36195566 jdk bundle patch 19.0.0.0.240416 |
| 36701173 database mrp 19.23.0.0.240618 |
| 36420641 datapump bundle patch 19.23.0.0.0 |
$ opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
34982944;COMPRESS ADVANCED VALUES NOT CHANGED AFTER EXCHANGE PARTITION WITH INDEXES REFERENCES TO BUG 33829857
35077128;AIM ORA-600 [15709] - KXFPQSRLS DUE TO CPURM AND DEAD SESSIONS
35149201;DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES FAILS LRG FILTERING
36065162;ORA-7445 [QKAUAL_GET_SELEXP] WITH PIPELINED FUNCTION IN UNION ALL IN 19.20
36079489;Fix for Bug 36079489
36103892;Fix for Bug 36103892
36158909;DB HUNG WATING FOR LOG FILE SWITCH (CHECKPOINT INCOMPLETE)
36195566;JDK BUNDLE PATCH 19.0.0.0.240416
36199232;OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232)
36233263;Database Release Update : 19.23.0.0.240416 (36233263)
36261036;SPIN OFF -RCA-DATABASE HAS CURSOR PIN S WAIT ON X CONTENTION AND CAUSED OUTAGE
36285197;ORA-04021 TIMEOUT OCCURRED WHILE WAITING TO LOCK OBJECT
36319353;WRONG FIX CONTROL ADDED BY JIAKLI_BUG-29413205
36343856;Fix for Bug 36343856
36391004;REVERT NZWLT_COMPAT_V11 FLAG TO CREATE 3DES BASED WALLET
36391038;ADD OPTION TO CREATE 3DES WALLET IN 19C AFTER CHANGE IN DEFAULT ENCRYPTION ALGORITHM
36420641;DATAPUMP BUNDLE PATCH 19.23.0.0.0
36480774;RECOVERY SIDE REVIEW OPENING THE DATABASE IS VERY SLOW
36530005;TRACKING BUG TO FIX LOST CHANGES FOR BUG 32872839 DUE TO CI TRANSACTION OF BUG 32195815
36587533;RESULT CACHE GLOBAL FLUSH SHOULD ALWAYS CLEAR BYPASS FLAG EVEN IF THE RESULT CACHE IS UNINITIALIZED3
4
Database patching
We now proceed to update the database catalog, using the datapatch utility.
SQL> startup;
$ datapatch -verboseFinal situation
5
Disadvantages of in-place patching.
1. Long time without service
Since applying a patch on an Oracle Home requires a database shutdown, the time it takes to apply the patch affects the availability of the services.
First of all you have to run the conflict check, then run a rollback of the patches that generate conflicts, and then proceed to apply the new patch.
If something goes wrong you have to invest time in troubleshooting or in starting the full rollback procedure of the patch, which may require restoring the entire Oracle Home from a backup generated in advance.
opatch prereq CheckConflictAgainstOHWithDetail
opatch nrollback -id 34810252,35213579,34557500
opatch apply /stage/RU1923
opatch napply /stage/MRU
opatch napply /stage/OneOffsOn top of that, as time goes by and more and more patches are applied to Oracle Home, there is an increase in the time it takes to apply each new patch.
Below is the time I recorded in my test environment, where I only applied the Release Updates/RU (no additional Monthly Recommended Patches/MRP or one-offs); both the time it takes to check for conflicts (prereq) and the actual application time (apply) are shown:
It is evident that time is increasing almost exponentially with the application of a new RU.
In real life, in installations where not only RUs but also MRPs and one-offs are periodically applied, applying a new patch can take hours! and Oracle Support recognizes this:
While performing patching on 19c homes , opatch apply and opatch rollback commands are getting slower if the number of oneoff or RU patches is higher.
It may take beyond 2 hours as well to perform a single RU apply command .
2. A lot of space consumed
For each patch applied, Oracle must obtain a backup of all modified objects, allowing it to run a rollback in the future, if required.
Below is the space consumption I recorded in my test environment, where I only applied the Release Updates/RU (no additional Monthly Recommended Patches/MRP or one-offs):
The space consumed is proportional to the number of patches that are applied, going from the original 7GB with 19.3 to almost 32GB with 19.23.
Conclusions
Taking as a reference the survey conducted by Tim Hall via Twitter, and without being an official or reliable source, it can be assumed that a good part of the patching performed by Oracle DBAs is in-place.
Although it is perhaps the modality most used by DBAs, in-place patching is by far inefficient, subject to errors, takes up a lot of disk space, and what is worse: it presents the highest service downtime.
