More about cleaning up datapatch patching files

One of the reasons why I love my job: We have the smartest customers out there. Incredible. When I blogged the other day (after a longer pause) about Cleanup datapatch patching files in your database some of you commented the other day with either success messages, or tried out to rollback – or simply missed steps in the README. And since we have also smart developers at Oracle, I received some additional facts from the datapatch architect which I let him explain below.

Photo by Brian Yurasits on Unsplash

The README file

Well, Devin commented on the previous blog post. He simply missed the steps to activate the cleanup patch. And in fact, I’d been trapped by the same experience as well. When Santosh told me that the patch is ready, and the first customers who pushed for this (thank you, Jannes!!) sent me feedback, I planned to try it out as soon as time allows. Which I did a week ago before I wrote the blog post.

And my first check was the README. But READMEs are generic files about how to apply and how to rollback a patch. READMEs don’t contain information about how to “activate” or “use” a patch.

In this case, the cleanup on the underlying tables sys.registry$sqlpatch_ru_info (RUs) and sys.registry$sqlpatch (one-offs) happens when you invoke datapatch after applying the patch. And there is nothing else to do.

The Rollback process

You can easily rollback any patch – and the README describes not only the apply but also the rollback process. So far, so good. But Johann tried this out, and realized: The patch is still active, even after a rollback.

This is Johann’s finding in brief:

1. Applied the patch and ran datapatch -verbose.
   The BLOB shrunk from 1.2 GB to 176 MB. Success!!
2. Rolled back the patch from the Oracle Home, restored the database from “before“, and invoked datapatch -verbose again.
   The BLOB shrunk again from 1.2 GB to 176 MB – but this wasn’t expected since the patch had been rolled back from the home.

Now Johann asked whether this is a bug or a feature?!

And here, Santosh, our datapatch architect comes into play since he explained this to me right away as “expected” and “it has to be this way”.

Mike Dietrich and Santosh Pulavarthi (datapatch architect) at YATRA2024 in Bangalore, India

Datapatch code is marked as OneWayCopy. That means, if datapatch code is updated, it will stay intact until it is updated again to newer version. Rolling back a ‘patch containing a datapatch code fix’ doesn’t have any affect on the datapatch code. The patch is marked as rollback success, all other content apart from datapatch’s code will be rolled back to previous versions, except for datapatch’s own code.  So, in essence, a user can only update ‘datapatch code’, but never “rollback”). Hence, the name “OneWayCopy”. This is the process for  “in-place” patching. Here, OPatch itself takes care of it.

The reason for this OneWayCopy design is to address any issues when executing rollback SQLs.

For e.g., take this scenario.

Patch 12345:  (following files changed in this patch)

• • abc_apply.sql         #   (this runs only when we are applying the patch)
  • sqlpatch.pm           #  (datapatch code fix which addresses an issue happening when running abc_rollback.sql)
  • abc_rollback.sql      #  (this runs only when we are rolling back the patch)

1. 1. Now 12345 is applied (both in OH and in DB), and all went well
   2. Later customer found some issues with “abc” and wanted to rollback the patch
   3. User rolls back the patch 12345 from OH. This will bring “abc_apply.sql” and “abc_rollback.sql” back to earlier, pre-patched versions. But, NOT the sqlpatch.pm.
   4. Now, user has to run datapatch to rollback the patch from DB, i.e, datapatch has to run “abc_rollback.sql”
   5. Here, datapatch will successfully run “abc_rollback.sql” but “datapatch code fix which contains the fix to run abc_rollback.sql successfully” is NOT rolled back. datapatch is still the latest version.
   6. Imagine when “datapatch code fix” also would have been rolled back from OH in “Step 3”: Then – when user runs datapatch now – it would fail when executing “abc_rollback.sql”.

In consequence, datapatch code always has to remain the latest in an Oracle Home to address any potential issues with rollback. Even though you have “rolled back” the patch, “datapatch code fix“ will remain at the latest change, and doesn’t get rolled back to a previous version.

Coming back to Johann’s finding, this is why datapatch was able to shrink the LOB segments even after the inventory confirmed “patch rollback”.

As a final confirmation or check, you could check if sqlpatch.pm. It will remain the same, before and after the patch rollback.

Thanks Santosh for the extra explanation 🙂

One additional thing to mention

In case the cleanup hangs, it could be that you have a smallfile SYSTEM tablespace, and there is not enough space to cleanup. Please see Vegard’s blog post:

• Datapatch seems to hang

We are looking into it, and see whether we can check such cases upfront to prevent you from a hanging datapatch run. No harm is done here – once you added a file you can invoke datapatch again. And thanks to Vegard for sharing this with us.

Further Links and Information

• Final blog post about datapatch cleanup and purge
  • Find the final fix in this blog post, too: 37352486
• 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
• Vegard Kåsa’s Blog Post: Datapatch seems to hang

–Mike