datapatch stores rollback patching files in the database

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.

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 fixed.
Bad news: It is not included right now as part of an RU.

Hence, you please must request a one-off patch if you need this fix. 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.

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.

 

Further Links and Information

–Mike

Share this: