Cleanup datapatch patching files in your database

A while back I wrote about datapatch stores rollback patching files in the database, and the amount of space it may consume over time, especially if you patch frequently. I am fully aware that it took a bit longer, but finally since a few months, there is a relief. You can now Cleanup datapatch patching files in your database, and reclaim the space as well.

Photo by JESHOOTS.COM on Unsplash

A quick recap

In my blog post from January 2024 I shared queries to check the space consumed by previous patches in your database. In brief, to allow seamless rollback of patches especially in Multitenant environments when you unplug/plug PDBs but also for other reasons, the database stored the rollback scripts for each patch run in a compressed zip format. This is good and useful, but unfortunately, it happened with every patch run – and it kept all the previous zip files as well.

As a consequence of this finding we filed a tracking bug, Bug 34674756 – PURGE OLD PATCH METADATA ZIP BLOB FROM DATAPATCH INVENTORY, IF NOT NEEDED, and the datapatch team fixed it – which wasn’t as trivial as one may think.

The fix is available

==> You may want to skip to The Final Blog Post about the topic at this point.

Currently, the fix is available as a one-off patch for 19.24.0. It was scheduled to be included into 19.25.0 Release Update but had got pulled because of a performance topic. It is now scheduled to be included into the following RU. This means, if you want to the fix on top of 19.24.0, you simply download it from MOS. In case it isn’t available on top of your desired Release Update, you need to open a service request (SR), and request a backport on top of your RU for Bug 34674756. Please make sure to include your “opatch lsinventory” with this SR as well.

The performance topic is fixed already as Bug 37055793 – and it is supposed to be included soon.

How to use the fix?

==> You may want to skip to The Final Blog Post about the topic at this point.

If you wonder how the fix gets used, this is very simple and straight forward. You apply the fix, in my case on top of 19.24.0 RU, then you open all your PDBs. As soon as you invoke datapatch -verbose, the associated scripts run, and once the scripts for the fix gets executed, the cleanup will start.

Please be prepared that – depending on the size – it may take a little bit. But since your database can be open and accessible already, this shouldn’t be much of an issue when you apply the fix just by itself.

In my case, all my containers had almost 1GB consumed by the zip files in sys.registry$sqlpatch_ru_info (RUs) and sys.registry$sqlpatch (one-offs).

After datapatch had finished its duties, the cleanup completed its work as well. Only one single ZIP file stayed in my sys.registry$sqlpatch_ru_info – all the other rows were still there but without the zips, hence, not using any space anymore. The same happened for sys.registry$sqlpatch where all the space got released since the patches aren’t even present anymore in my current environment.

Only the runtime may need some improvement – but I know that we are working on this already.

Scripts to check patch consumption within your database

I am sharing the scripts from my previous blog post here again:

Release Updates and Bundles

column patch_id format 9999999999
column ru_version format a15
column lob_size_md format 9999
COLUMN ru_build_ts FORMAT A20


set linesize 100
set pagesize 300

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'MM/DD/YY HH24:MI';
ALTER SESSION SET "_EXCLUDE_SEED_CDB_VIEW" = FALSE;

SELECT
    patch_id,
    ru_version,
    TO_CHAR(ru_build_timestamp, 'MM/DD/YYYY HH24:MI:SS') AS ru_build_ts,
    round(dbms_lob.getlength(patch_directory) / 1024 / 1024) lob_size_mb
FROM
    sys.registry$sqlpatch_ru_info;


SELECT
   con_id, round(sum(dbms_lob.getlength(patch_directory) / 1024 / 1024)) total_lob_size_mba
FROM
   containers(sys.registry$sqlpatch_ru_info)
GROUP BY
   con_id
ORDER BY
   con_id;

One-Off Patches

column patch_id format 9999999999
column lob_size_md format 9999
COLUMN SUBSTR(description,1,40) FORMAT A40

set linesize 100
set pagesize 300

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'MM/DD/YY HH24:MI';
ALTER SESSION SET "_EXCLUDE_SEED_CDB_VIEW" = FALSE;

SELECT
    patch_id,
    SUBSTR(description,1,40) PATCH_DESCRIPTION,
    TO_CHAR(source_build_timestamp, 'MM/DD/YYYY HH24:MI:SS') AS patch_build_ts,
    round(dbms_lob.getlength(patch_directory) / 1024 / 1024) lob_size_mb
FROM
    sys.registry$sqlpatch
WHERE
    patch_type<>'RU';

SELECT
   con_id, round(sum(dbms_lob.getlength(patch_directory) / 1024 / 1024)) total_lob_size_mba
FROM
   containers(sys.registry$sqlpatch)
GROUP BY
   con_id
ORDER BY
   con_id;

Further Links and Information

• The Final Blog Post about this topic
• Blog Post: Cleanup Datapatch Patching Files In Your Database
• Blog Post: More about cleaning up datapatch patching files
• Datapatch User Guide (Doc ID 2680521.1)
• Script: check_patches_19.sql
• Script: unload_blob_from_table.sql
• Script: check_patches_size.sql
• Bug 34674756 – PURGE OLD PATCH METADATA ZIP BLOB FROM DATAPATCH INVENTORY, IF NOT NEEDED 
• Correct patch to download: 37352486

–Mike