Oracle

When you are a sensible person, you patch regularly your Oracle database. After a few years, maybe you noticed that the $ORACLE_HOME increased size from new ~7 GB to …. 26 GB!

$ du -hs /u00/app/oracle/product/19.18.0
26G     /u00/app/oracle/product/19.18.0

Looking better, you find the culprit is the hidden directory $ORACLE_HOME/.patch_storage :

$ du -hs /u00/app/oracle/product/19.18.0/.patch_storage
17G     /u00/app/oracle/product/19.18.0/.patch_storage

Today I show how a new option from OPatch allowed me to regain 11 GB of disk space in a couple of minutes. In a supported way.

Last year I had already presented other options to recover space from Oracle Patching. Below I remind them, but todays post is about the latest and improved way that OPatch tool has to keep only the usual needed patch backups.

[a new solution is available as of 2023. Read here about the new OPatch util DeleteInactivePatches option.]

I love database patching and apart of the tiring coordination work or the applications that keep to not automatically reconnect to the database, all is usually perfect and issue free. Well, almost. The most common error are space issues.

You can try to follow the Oracle guidelines and have a 100 GB partition for the $ORACLE_HOME(s). Initially it only uses 7 or 8 GB per home, but after few years you are fighting against the space pressure.

There are several strategies to prevent or act against this space problem when patching:

Solution 1 – Recreate separate Oracle Home from scratch

It is a clean solution, when you make really from scratch, meaning no home cloning, no opatchauto apply -outofplace, and then apply only the latest patch there. This solution is quite easy to do for DB home. However when you have Grid Home this is a bit more hassle.

Solution 2 – Use the opatch hidden “archive” feature

This feature allows to move out from .patch_storage folder some patches in a zip format. It was “documented” by Mike Dietrich in his blog. Unfortunately to have a common archive between different Oracle Homes you need to do some hand work: archive on one Oracle Home, delete the patches from remaining homes and copy the $ORACLE_HOME/OPatch/.patch_storage/.patch_archive_mapping.xml file to the other homes. Of course this works when all Homes have exactly the same features installed and patches. Keep in mind that before rollback you need to use the “unarchive” option and that the rollback procedure will restore the files that were changed, and this can vary depending on the state of the oracle home at the moment of patch. Use opatch util archive -help for more info.

Solution 3 – Move OPatch folder to another partition and soft link it

Here we are not saving any space, just moving the problem away. The other partition can be a remote slower location, but the patching will also then be slower, as it will need to copy files there. Use: TARGET=<partition>/patch_storage ; mv $ORACLE_HOME/.patch_storage $TARGET ; ln -s $TARGET $ORACLE_HOME/.patch_storage

Solution 4 – Remove unneeded patches from .patch_storage

In $ORACLE_HOME/.patch_storage the whole history of patches you applied is kept. You can rollback one after the other and bring the Oracle Home several steps behind. However, the most of the cases you are ok to just be able to move one step backward. The older history of the home is past. If that is you case, then there is this nice Python script clean_patch_storage.py which is based on the premise of Oracle Doc ID 550522.1 which states you can “remove all the sub-directories from $ORACLE_HOME/.patch_storage that are not present in the list of installed patches”. The list directories you can delete is exactly what the script do.

Maybe you have other solutions or tricks, please share in the comments.

Yesterday on a simple 19.13 patching session, got a nice error during datapatch: ORA-01691: usable to extend lob segment in Tablespace SYSTEM ! Got a bit scared, but simple added more space to the tablespace, run datapatch again and all was ok. Maybe patch/datapatch should check also for free space in the SYSTEM Tablespace ?

Here the error:

DBD::Oracle::st execute failed: ORA-01691: unable to extend lob segment SYS.SYS_LOB0000323098C00008$$ by 128 in tablespace SYSTEM
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
           INSERT INTO sys.dba_registry_sqlpatch_ru_info
             (patch_id,
              patch_uid,
              patch_descriptor,
              ru_version,
              ru_build_description,
              ru_build_timestamp,
              patch_directory)
           VALUES
             (:patch_id,
              :patch_uid,
              :patch_descriptor,
              :ru_version,
              :ru_build_description,
              TO_TIMESTAMP(:ru_build_timestamp, 'YYMMDDHH24MISS'),
              :patch_directory);
           COMMIT;
         END;" with ParamValues: :patch_descriptor=OCIXMLTypePtr=SCALAR(0x468b0a0), :patch_directory='PK........▒LOS▒.
▒. ..▒[..
...33192793.xml▒][.۸.~..
▒.v▒▒▒.x.▒▒q*.o▒▒*.'s▒▒▒(.-qL▒l.ji▒▒/@Im        8 .$.I▒<▒0j▒.>\.▒.▒~.▒▒▒K▒vE▒▒▒.~K..▒6▒▒b▒▒▒a▒?=F.▒..▒]▒\▒5▒▒▒▒.?|▒▒y4f▒▒{X▒▒▒..▒"▒▒|?d.▒..I▒.E.▒▒▒▒=▒▒▒▒▒..▒▒▒.▒▒5.Wg▒w.ۺ▒..▒v}Q%}.▒..▒▒.▒▒.-▒▒▒▒▒\%▒▒׿▒vE.▒▒xsU▒▒)%ħa@.r▒▒.▒Kۢ▒.▒}..y▒▒.~j2▒▒â.=▒▒▒.▒▒▒O.▒▒<▒wm▒▒▒d▒.▒vw▒▒▒▒....▒o޼.▒}▒▒▒.I.▒x▒.▒▒.▒▒▒▒.▒▒.▒x▒o▒)▒▒wo.vxۿ{|\▒,.i]5▒6▒▒..▒▒.b.g▒b▒▒7Ew▒▒▒▒./▒.▒▒o.▒/▒.▒.▒▒6▒#▒.▒.▒...▒▒..▒▒▒▒ߵ▒l▒▒▒▒...▒▒.▒.▒▒.a(▒.▒?|▒.▒
X▒▒{+▒▒v▒=.▒{▒▒▒▒9Íui▒▒.H.▒▒▒▒▒▒▒▒S.▒^▒|▒9.▒%▒,▒▒▒SU▒.▒. zZ.▒..c;.▒vحY▒B...▒▒▒▒/▒▒.▒▒▒▒.▒▒▒..H{.7}f.▒..▒j.▒mۿ▒.▒S.▒7▒.:§.▒+p▒%▒..w▒.▒.▒{7▒.▒k▒..▒6▒6▒▒>v▒bwMU▒..▒Y_▒+▒IG▒<.|4▒▒\▒4.▒i▒*▒▒;G▒-V.ε▒▒.p.▒▒▒▒
G̍1▒.▒]▒▒▒.1O▒v▒^.▒▒▒.▒:.Wy._q7▒▒.*▒&▒▒`/▒I߹...pW.
▒▒▒▒C.v▒j▒[▒▒.▒▒~▒▒.▒.bo▒▒.U▒..▒V▒▒
.`n▒▒N7▒.`nyup▒▒.w▒.]▒▒B▒.▒▒5D7▒.w.#w.▒.▒m▒▒ܬ▒.p▒.3p.▒▒.▒.l▒.▒▒Z;▒0.\bm▒J▒...', :patch_id="33192793", :patch_uid="24462514", :ru_build_description="Release_Update", :ru_build_timestamp="211004165050", :ru_version="19.13.0.0.0"] at /u01/oracle/db19s/sqlpatch/sqlpatch.pm line 5876, <LOGFILE> line 110.