I held back this blog post for a longer while. A customer (Thanks Jannes!) reported this to me many months ago but the fix was not as trivial as we thought initially, and there were several unlucky circumstances delaying it multiple times. But now it is time to tell you how datapatch stores rollback patching files in the database. And of course, how to cleanup.
Photo by Pawel Czerwinski on Unsplash
Why is this important?
At first, you may ask yourself: Why should I care? And you are right. This is normally nothing you should care about. Things just happen in the background, and as we keep saying, datapatch does it all right.
Let me explain at first what datapatch does, especially since I received this question multiple times on the blog. In our seminars we talk about it. And we even have a slide on it.
In brief, when you invoke datapatch to patch to a new Release Update (RU), then it stores the rollback files needed for a potential revert to a previous RU as a zip file inside your database in your SYSTEM tablespace. This is the solution to the riddle what will happen when you downgrade from one patch level to another, and you invoke datapatch then again to adjust the database to the lower version’s target. In the past, you had to copy around files to make this work.
In addition, it tracks all your patching attempts including the rollback in REGISTRY$SQLPATCH. When you use the check_patches_19.sql script from the blog, you can easily see what it does.
This is how the output looks in my Hands-On Lab environment:
SQL> start /home/oracle/scripts/check_patches_19
Session altered.
CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSI TARGET_VERSI
---------- -------------------- ---------- ---------- ---------- ------------------------------------------------------ ------------ ------------
[Over 90 lines were cut out to shorten the output]
3 2023-01-26 34411846 INTERIM ROLLBACK OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846) 19.18.0.0.0 19.18.0.0.0
3 2023-01-26 34734035 INTERIM ROLLBACK MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 19.18.0.0.0 19.18.0.0.0
24338134 25143018 26565187
3 2023-01-26 34765931 RU APPLY Database Release Update : 19.18.0.0.230117 (34765931) 19.17.0.0.0 19.18.0.0.0
3 2023-01-26 34786990 INTERIM APPLY OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990) 19.17.0.0.0 19.17.0.0.0
3 2023-01-26 34972375 INTERIM APPLY DATAPUMP BUNDLE PATCH 19.18.0.0.0 19.17.0.0.0 19.17.0.0.0
3 2023-05-22 34861493 INTERIM APPLY RESYNC CATALOG FAILED IN ZDLRA CATALOG AFTER PROTECTED 19.18.0.0.0 19.18.0.0.0
DATABASE PATCHED TO 19.17
3 2023-05-22 35160800 INTERIM APPLY GG IE FAILS WITH ORA-14400 AT SYSTEM.LOGMNRC_USER AFTE 19.18.0.0.0 19.18.0.0.0
R ORACLE DB UPGRADE TO 19.18DBRU
3 2024-01-10 34786990 INTERIM ROLLBACK OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990) 19.21.0.0.0 19.21.0.0.0
3 2024-01-10 34861493 INTERIM ROLLBACK RESYNC CATALOG FAILED IN ZDLRA CATALOG AFTER PROTECTED 19.21.0.0.0 19.21.0.0.0
DATABASE PATCHED TO 19.17
3 2024-01-10 34972375 INTERIM ROLLBACK DATAPUMP BUNDLE PATCH 19.18.0.0.0 19.21.0.0.0 19.21.0.0.0
3 2024-01-10 35160800 INTERIM ROLLBACK GG IE FAILS WITH ORA-14400 AT SYSTEM.LOGMNRC_USER AFTE 19.21.0.0.0 19.21.0.0.0
R ORACLE DB UPGRADE TO 19.18DBRU
3 2024-01-10 35643107 RU APPLY Database Release Update : 19.21.0.0.231017 (35643107) 19.18.0.0.0 19.21.0.0.0
3 2024-01-10 35648110 INTERIM APPLY OJVM RELEASE UPDATE: 19.21.0.0.231017 (35648110) 19.18.0.0.0 19.18.0.0.0
3 2024-01-10 35787077 INTERIM APPLY DATAPUMP BUNDLE PATCH 19.21.0.0.0 19.18.0.0.0 19.18.0.0.0
3 2024-01-10 35965044 INTERIM APPLY PROVIDING RDBMS API FOR ONLINE DST UPGRADE 19.18.0.0.0 19.18.0.0.0
108 rows selected.
I’ve had to shorten the output – but you get the idea quickly by looking at the above listing.
Still, there is more to care about as you could assume from my comment above: The apply and rollback scripts. You will find those in your $ORACLE_HOME:
- $ORACLE_HOME/sqlpatch/…/nnn_apply.sql
- $ORACLE_HOME/sqlpatch/…/nnn_rollback.sql
The interesting fact is that you can find them also inside your database in the SYSTEM tablespace in the BLOB column named PATCH_DIRECTORY in two tables, REGISTRY$SQLPATCH and in REGISTRY$SQLPATCH_RU_INFO. These tables exists in every container, every PDB including the PDB$SEED and CDB$ROOT.
As mentioned above, the scripts are stored as archive files inside these two tables.
What is stored in your database?
Now it is time to have a closer look and run a query to check the size being consumed by these zip files in my own database. It has gotten a lot of patches over the past years. Keep in mind that Oracle Database 19c is available since April 2019. I applied more than 80% of the RUs plus OJVM, MRPs and the Data Pump Bundle Patch occasionally.
I am using this script check_patches_size.sql which I added to the scripts section of the blog to check the RU content. There is another script below but the one-offs and merge patches are less interesting size-wise as you will see later:
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;
And the output looks interesting:
PATCH_ID RU_VERSION RU_BUILD_TS LOB_SIZE_MB
----------- --------------- -------------------- -----------
29517242 19.3.0.0.0 04/10/2019 12:27:20 4
30125133 19.5.0.0.0 09/09/2019 18:05:49 18
30557433 19.6.0.0.0 12/17/2019 15:50:04 24
30869156 19.7.0.0.0 04/04/2020 03:50:18 30
31281355 19.8.0.0.0 07/03/2020 03:15:01 37
31771877 19.9.0.0.0 09/30/2020 18:32:49 43
32218454 19.10.0.0.0 01/08/2021 18:50:17 51
32545013 19.11.0.0.0 04/13/2021 00:40:09 59
32904851 19.12.0.0.0 07/16/2021 14:18:10 67
33192793 19.13.0.0.0 10/04/2021 16:50:50 75
33515361 19.14.0.0.0 12/25/2021 12:21:23 83
34133642 19.16.0.0.0 07/03/2022 02:22:23 99
34419443 19.17.0.0.0 09/24/2022 22:40:51 108
34765931 19.18.0.0.0 01/11/2023 17:17:38 116
35643107 19.21.0.0.0 09/30/2023 15:19:51 141
15 rows selected.
CON_ID TOTAL_LOB_SIZE_MB
---------- ------------------
1 953
2 953
3 953
So, at first you get an idea which RUs I applied, and which ones I skipped.
But it tells you also that there is a new zip file stored in REGISTRY$SQLPATCH_RU_INFO for every “datapatch -verbose” run for every RU.
Now I need to clarify something. I patch in-place in my lab (simply for space reasons in a tiny lab, and downtime is not an issue, plus, I want to see issues you never see with out-of-place patching). The result of the above listing won’t be as impressive when you patch out-of-place.
The above list looks as if the content from the previous ones is included in the most recent additions, too. I will verify this below.
Well, it reads to me that almost 3GB are used for patch rollback files in one of my databases. That is quite an amount. Feel free to use the scripts and check by yourself, and if you want, report the size accumulated in the comments section of the blog.
Same check for one-offs and merges
For completeness, let me add the script and check for one-off patches as well.
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;
You will easily see why this part doesn’t matter that much when you look at the output and the sum:
PATCH_ID PATCH_DESCRIPTION PATCH_BUILD_TS LOB_SIZE_MB
----------- ---------------------------------------- ------------------- -----------
33192694 OJVM RELEASE UPDATE: 19.13.0.0.211019 (3 10/04/2021 16:50:50 0
33192694 OJVM RELEASE UPDATE: 19.13.0.0.211019 (3 12/25/2021 12:21:23 0
33561310 OJVM RELEASE UPDATE: 19.14.0.0.220118 (3 12/25/2021 12:21:23 0
33561310 OJVM RELEASE UPDATE: 19.14.0.0.220118 (3 07/03/2022 02:22:23 0
34086870 OJVM RELEASE UPDATE: 19.16.0.0.220719 (3 07/03/2022 02:22:23 0
34086870 OJVM RELEASE UPDATE: 19.16.0.0.220719 (3 09/24/2022 22:40:51 0
34411846 OJVM RELEASE UPDATE: 19.17.0.0.221018 (3 07/03/2022 02:22:23 0
34734035 MERGE ON DATABASE RU 19.17.0.0.0 OF 3465 07/03/2022 02:22:23 1
34411846 OJVM RELEASE UPDATE: 19.17.0.0.221018 (3 01/11/2023 17:17:38 0
34786990 OJVM RELEASE UPDATE: 19.18.0.0.230117 (3 09/24/2022 22:40:51 0
34734035 MERGE ON DATABASE RU 19.17.0.0.0 OF 3465 01/11/2023 17:17:38 1
34972375 DATAPUMP BUNDLE PATCH 19.18.0.0.0 09/24/2022 22:40:51 1
34861493 RESYNC CATALOG FAILED IN ZDLRA CATALOG A 01/11/2023 17:17:38 0
35160800 GG IE FAILS WITH ORA-14400 AT SYSTEM.LOG 01/11/2023 17:17:38 0
34786990 OJVM RELEASE UPDATE: 19.18.0.0.230117 (3 09/30/2023 15:19:51 0
35648110 OJVM RELEASE UPDATE: 19.21.0.0.231017 (3 01/11/2023 17:17:38 0
34861493 RESYNC CATALOG FAILED IN ZDLRA CATALOG A 09/30/2023 15:19:51 0
34972375 DATAPUMP BUNDLE PATCH 19.18.0.0.0 09/30/2023 15:19:51 1
35160800 GG IE FAILS WITH ORA-14400 AT SYSTEM.LOG 09/30/2023 15:19:51 0
35787077 DATAPUMP BUNDLE PATCH 19.21.0.0.0 01/11/2023 17:17:38 1
35965044 PROVIDING RDBMS API FOR ONLINE DST UPGRA 01/11/2023 17:17:38 0
21 rows selected.
CON_ID TOTAL_LOB_SIZE_MB
---------- ------------------
1 6
2 6
3 6
That’s 6MB per CON_ID. And many of the rollback scripts are small, therefore I rather neglect them for now.
What is in such a file?
Let me extract the RU ones, and compare them to each other. I used unload_blob_from_table.sql which I slightly adjusted from the example shared here. As a result, I receive this:
[CDB3] oracle@hol:~/zip $ ls -lrt total 976408 -rw-r--r--. 1 oracle dba 3842294 Jan 16 17:51 19.3.0.0.0.zip -rw-r--r--. 1 oracle dba 19124771 Jan 16 17:52 19.5.0.0.0.zip -rw-r--r--. 1 oracle dba 24998381 Jan 16 17:52 19.6.0.0.0.zip -rw-r--r--. 1 oracle dba 31858188 Jan 16 17:52 19.7.0.0.0.zip -rw-r--r--. 1 oracle dba 38332143 Jan 16 17:52 19.8.0.0.0.zip -rw-r--r--. 1 oracle dba 45166414 Jan 16 17:52 19.9.0.0.0.zip -rw-r--r--. 1 oracle dba 53104344 Jan 16 17:52 19.10.0.0.0.zip -rw-r--r--. 1 oracle dba 61888310 Jan 16 17:52 19.11.0.0.0.zip -rw-r--r--. 1 oracle dba 69914460 Jan 16 17:52 19.12.0.0.0.zip -rw-r--r--. 1 oracle dba 78384565 Jan 16 17:52 19.13.0.0.0.zip -rw-r--r--. 1 oracle dba 86703241 Jan 16 17:52 19.14.0.0.0.zip -rw-r--r--. 1 oracle dba 104101225 Jan 16 17:52 19.16.0.0.0.zip -rw-r--r--. 1 oracle dba 112843678 Jan 16 17:52 19.17.0.0.0.zip -rw-r--r--. 1 oracle dba 121574801 Jan 16 17:52 19.18.0.0.0.zip -rw-r--r--. 1 oracle dba 147976621 Jan 16 17:52 19.21.0.0.0.zip
And my suspicion is unfortunately correct: Each newer zip contains everything from the previous one. And here I needed to update my blog post (thanks again, Jannes!). In fact, there seems to be no difference between in-place and out-of-place patching. All RUs since 19.6.0 seem to contain all the rollback files for all previous RUs, regardless of whether you installed the RU or not.
But let me at first copy/paste only a small excerpt of the contents of the zip I unpacked:
... ./1918/rollback_files/19.15.0.0.0-RU-Release_Update-220331125408/... ... ./1918/rollback_files/19.16.0.0.0-RU-Release_Update-220703022223/... ... ./1918/rollback_files/19.17.0.0.0-RU-Release_Update-220924224051/... ... ./1918/rollback_files/19.18.0.0.0-RU-Release_Update-230111171738/... ...
There are thousands of files – and the reason why every newer zip gets bigger than the previous one is simply that it contains ALL the previous rollback files and adds the ones for the newest RU to it.
And then let me examine one of the older RUs since the content is shorter. I am using 19.10.0 in my home. The rollback files are stored in:
/u01/app/oracle/product/19/sqlpatch/32218454
There you will find a subdirectory called rollback_files under the patch-ID’s subdirectory. And when I climb further down I get this:
/u01/app/oracle/product/19/sqlpatch/32218454/24018797/rollback_files drwxr-xr-x. 6 oracle dba 52 Jan 20 2021 19.1.0.0.0 drwxr-xr-x. 4 oracle dba 29 Jan 20 2021 19.3.0.0.0-RU-Release_Update-190407062351 drwxr-xr-x. 4 oracle dba 29 Jan 20 2021 19.3.0.0.0-RU-Release_Update-190410122720 drwxr-xr-x. 4 oracle dba 29 Jan 20 2021 19.4.0.0.0-RU-Release_Update-190626171128 drwxr-xr-x. 4 oracle dba 29 Jan 20 2021 19.5.0.0.0-RU-Release_Update-190909180549 drwxr-xr-x. 4 oracle dba 29 Jan 20 2021 19.6.0.0.0-RU-Release_Update-191217155004 drwxr-xr-x. 6 oracle dba 52 Jan 20 2021 19.7.0.0.0-RU-Release_Update-200404035018 drwxr-xr-x. 6 oracle dba 52 Jan 20 2021 19.8.0.0.0-RU-Release_Update-200703031501 drwxr-xr-x. 6 oracle dba 52 Jan 20 2021 19.9.0.0.0-RU-Release_Update-200930183249 drwxr-xr-x. 6 oracle dba 52 Jan 20 2021 19.10.0.0.0-RU-Release_Update-210108185017
Now when you compare to my above listings you will see that I did never apply 19.4.0 to my home (and I skipped some more recent RUs in this installation as well). But this subdirectory contains all the rollback files, even for RUs I never applied. I can see why that is there but it makes me less understanding why the files need to be in the database as well. Let me clarify this separately.
But as a matter of fact, this explains why the zip archive gets bigger and bigger since every new RU will also contain all the previous rollback files from all RUs. And this happens regardless whether you patch in-place or out-of-place.
And this is the reason why we need a cleanup.
How can you cleanup?
At first, let me emphasize that this way of storing information is not an idea of the current datapatch owner. When datapatch got created for Oracle 12c a long while ago – mainly to manage the patching of multiple containers with one call – the design was taken this way, potentially not thinking about what happens when a release lives many years and gets patched quite often. Hence, the current datapatch team is not to blame for this.
What has happened as a result of this discussion?
We filed a bug a while ago: Bug 34674756 – PURGE OLD PATCH METADATA ZIP BLOB FROM DATAPATCH INVENTORY, IF NOT NEEDED
This is a non-public bug, and you won’t find much information about it.
Good news: The bug is was fixed.
Bad news: It is not included right now as part of an RU – but should be there soon.
More news: The fix had to undergo several revisions until a real fix got produced. See Final blog post about datapatch cleanup and purge.
Hence, you please must request a one-off patch if you need this fix. Please see my blog post Cleanup datapatch patching files in your database for more information.
Since I couldn’t try out the backport on top of 19.21.o (bug 36164546) yet by myself I simply assume that the cleanup will be part of a standard datapatch run. I did not see any additional parameters required to trigger the cleanup. But as soon as the one-off is available, I will update the blog post.
Clarification July 26, 2026
Since somebody asked, and I replied very late:
The extraction happens automatically when needed. So, for instance, when you go from 19.31 back to a 19.29 home, and you invoke datapatch, it would unpack the necessary rollback files automatically. No action needed from your side.
Further Links and Information
- Final blog post about datapatch cleanup and purge
- 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
–Mike
About RU rollback. Brief test case: read-only Oracle home e.g. 19.15, patching done out-of-place via deploying a new read-only Oracle Home 19.22 as a software image (./runInstaller, datapatch and all things included). Issues in 19.22 are found, rollback is needed… Question: is it possible to deploy a software image 19.21 in do a rollback to a previous RU directly from 19.22 to 19.21 (database was never on version 19.21!) or a complete rollback to version 19.15 is needed? I tried this scenario in my lab environment (database created as 19.19 -> RU to 19.21 -> rollback to 19.20) and it worked. Some issues regarding timezone files are possible but I found a Doc ID that covers that. Is this scenario considered »valid«?
Yes, you can – see the update on my blog post. All necessary rollback files are always present.
Cheers
Mike
My output (non-CDB):
PATCH_ID RU_VERSION RU_BUILD_TS LOB_SIZE_MB
———– ————— ——————– ———–
29517242 19.3.0.0.0 04/10/2019 12:27:20 4
32904851 19.12.0.0.0 07/16/2021 14:18:10 67
33515361 19.14.0.0.0 12/25/2021 12:21:23 83
34133642 19.16.0.0.0 07/03/2022 02:22:23 13
34419443 19.17.0.0.0 09/24/2022 22:40:51 108
34765931 19.18.0.0.0 01/11/2023 17:17:38 116
35042068 19.19.0.0.0 03/22/2023 02:04:06 124
35320081 19.20.0.0.0 07/15/2023 02:28:00 133
35643107 19.21.0.0.0 09/30/2023 15:19:51 141
CON_ID TOTAL_LOB_SIZE_MBA
———- ——————
0 788
PATCH_ID PATCH_DESCRIPTION PATCH_BUILD_TS LOB_SIZE_MB
———– —————————————————————————————————————————————————————- ——————- ———–
33561310 OJVM RELEASE UPDATE: 19.14.0.0.220118 (3 0
33588396 MERGE ON DATABASE RU 19.14.0.0.0 OF 3342 1
33561310 OJVM RELEASE UPDATE: 19.14.0.0.220118 (3 12/25/2021 12:21:23 0
34086870 OJVM RELEASE UPDATE: 19.16.0.0.220719 (3 12/25/2021 12:21:23 0
33588396 MERGE ON DATABASE RU 19.14.0.0.0 OF 3342 12/25/2021 12:21:23 1
34086870 OJVM RELEASE UPDATE: 19.16.0.0.220719 (3 03/22/2023 02:04:06 0
35050341 OJVM RELEASE UPDATE: 19.19.0.0.230418 (3 07/03/2022 02:22:23 0
32781163 TRACKING BUG FOR RTI 24059230 KGL X LO 07/03/2022 02:22:23 0
35050341 OJVM RELEASE UPDATE: 19.19.0.0.230418 (3 03/22/2023 02:04:06 0
35354406 OJVM RELEASE UPDATE: 19.20.0.0.230718 (3 03/22/2023 02:04:06 0
32781163 TRACKING BUG FOR RTI 24059230 KGL X LO 07/15/2023 02:28:00 0
35354406 OJVM RELEASE UPDATE: 19.20.0.0.230718 (3 07/15/2023 02:28:00 0
35648110 OJVM RELEASE UPDATE: 19.21.0.0.231017 (3 07/15/2023 02:28:00 0
CON_ID TOTAL_LOB_SIZE_MBA
———- ——————
0 2
Thank you!!
Cheers,
Mike
Hi Mike,
please update us once it is clear how the cleanup works. In an multitenant DB with 30+ pdbs I get 14,5 GB of that rollback data with only 19.3, 19.16 and 19.21 in the history.
Thanks,
Robert
Hi Robert,
the team is still working on a fix. Unfortunately, you only claim back space when you rebuild the table (aka “truncate” and move back again).
Once the fix is out, I will let you know for sure.
Cheers,
Mike
Years++ ago I had an SR about moving the PL/SQL tracing tables out of SYSTEM to SYSAUX, and eventually I got approval to do so specifically because those tables aren’t required for system startup.
Is it possible to consider moving this data over to SYSAUX? Or some other system-level tablespace, since I guess we’re creating more of those in recent years.
Are these considered critical for system startup? If your database won’t start and you need to revert something, you might perhaps decide that the answer is “yes”, but…
Hi Jason,
nope, it is not allowed to move them to SYSAUX.
But we’ll get a viable solution soon. The first drop did not release the space but the team is working on a better way.
Thanks
Mike
The latest quarterly patches include an optional step to run ./datapatch -sanity_checks before running ./datapatch -verbose. No official doco appears to be available on it yet. Can you shed some light on precisely what the command does (beyond “it checks if your system is in shape to run datapatch”)? Thanks.
Hi Tim,
please see:
Oracle Support Document 2975965.1 (Datapatch – Sanity Checks Reference)
Cheers.
Mike
Hi Mike,
You state : “The one on-top-of 19.21. on Linux is in the making right now. I will link it from here as soon as it is available for download from MOS. In addition, we try to make sure to have it included in the next potential RU.” Current RU is 22. Is this now included? Thanks
Hi Mike
This was a new one to me.
But just one question:
If I get You right, the rollback packages are stored in the database, but are not extracted automaticly
if I issue an rollback?
This would be a nice one
Chears
Christian
Hi Christian,
they are extracted automatically when needed. Let me add this to the blog post.
Cheers,
Mike
Hi Mike,
So if the bug is resolved with a future patch, the old patches in $ORACLE_HOME/sqlpatch will be deleted too?
I have a problem where the $ORACLE_HOME/sqlpatch directory is getting large and in some cases almost 4GB, is this the side effect of this bug? 34674756
Hi Piero,
the fix should clean up the rollback scripts in your database. I think the old entries in sqlpatch can be removed manually. Let me investigate this further.
Cheers,
Mike
Thank you for this article. SYSTEM used to be a stable TABLESPACE. The Oracle Guides tell you to make it 2GB and it rarely grows. Now it not only grows, but it grows exponentially as each patch is the sum of all other patches. REGISTRY$SQLPATCH_RU_INFO is as big as everything else in SYSTEM. At this rate, by 2028 it will be 16G.
We’ll request the fix before the next RU.
Hi Andy,
it will be actually two fixes – and hopefully in the coming week I can give an update. One fix will prevent it from growing but the other will also reclaim space again.
Thanks,
Mike
Hi Mike,
Were these two patches included in the latest DBRU or will it still be?
Hi Piero,
sorry for the late reply – and please see my two recent blog posts:
https://mikedietrichde.com/2024/09/23/cleanup-datapatch-patching-files-in-your-database/
https://mikedietrichde.com/2024/09/30/more-about-cleaning-up-datapatch-patching-files/
Cheers,
Mike
I requested and got a patch for 19.23
Any update on this please ?
Here we go:
https://mikedietrichde.com/2024/09/23/cleanup-datapatch-patching-files-in-your-database/
Needed to try it out at first and do some due-diligence.
Cheers,
Mike
Hi Mike,
it looks as if Patch 34674756: PURGE OLD PATCH METADATA ZIP BLOB FROM DATAPATCH INVENTORY, IF NOT NEEDED is available for 19.23 and 19.24.
Have you already applied that?
And how will it work? Will it cleanup the datapatch inventory immediately when it is applied?
Regards,
Rüdiger
Hi Rüdiger,
here we go:
https://mikedietrichde.com/2024/09/23/cleanup-datapatch-patching-files-in-your-database/
Cheers,
Mike
Hi Mike,
Great article as usual. Any update on this bug? It’s been a while since your last update. Unfortunately we’re running on Windows so I’m sure we’ll be last to get the fix. Thanks!
Please see here:
https://mikedietrichde.com/2024/09/23/cleanup-datapatch-patching-files-in-your-database/
Cheers,
Mike
Hi Mike,
Do we have any practical means to determine whether an installed patch in the inventory is rolling or standby-first installable by querying a dictionary view? Or maybe by checking a config. file in inventory?
This info can be crucial when you need to rollback.
Regards.
Hi Atilla,
that is a great question – I don’t think so but let me ask one of my guys 🙂
If I don’t reply again, then there is no such way. If there is one, I will let you know.
Cheers,
Mike