Rolling back or removing all patch SQL changes

This is quite an interesting case I’ve had to scratch my head at first as well. Magnus mailed me the other day asking for a client since the PDB compatibility check errored out with a patch conflict. We discussed the topic but the most easiest workaround to me was Rolling back or removing all patch SQL changes from the PDB before unplug. And since I had to look up the command I’d rather put it on the blog in case you or I need it again one day.

 

What is the case?

I may discuss this in another blog post since this is interesting as well. A PDB is supposed to be unplugged from 19.17.0 on MS Windows, and plugged (or migrated) into a 19.17.0 CDB on Linux. This sounds straight forward but may offer you some challenges as well, especially because of the different directories.

The warning Magnus saw came up during the compatibility check:

declare
  c boolean;
  begin
   c:=dbms_pdb.check_plug_compatibility('mypdb.xml','MYPDB');
    if (c) then dbms_output.put_line('Result ==> True');
    else dbms_output.put_line('Result ==> False');
   end if;
  end;
/

Result ==> False

And when he checked my most “favorite” view, PDB_PLUG_IN_VIOLATIONS, he found this:

TIME             NAME         TYPE      CAUSE       STATUS      CON_ID MESSAGE
---------------- ------------ --------- ----------- --------- -------- ------------------------------------------------------------
2023-01-18 10:58 MYPDB        ERROR     SQL Patch   0 PENDING        1 '19.17.0.0.0 Release_Update 2210270221' is installed in the
                                                                        CDB but '19.17.0.0.0 Release_Update 2209242240' is installed
                                                                        in the PDB

That’s quite interesting. At first sight, this looks like a simple issue with the different release dates of 19.17.0 on Windows and Linux since source and target both are on the same patch release.

As mentioned before, I will try to investigate this in another blog post. But here we were looking for a quick workaround.

I wrote up a bit about this 3 years ago in Moving non-CDB to PDB: The Patch Level Pitfall since this error scenario is not unusual. What I didn’t specify in more detail in this older blog post is a handy workaround.

 

How can we remove or roll back the SQL patch?

My first or second thought was: Let us remove the patch information from the source. Then we don’t have to struggle with 19.17.0 being in here and there but on different OS platforms with different time stamps.

And this is how you can enforce datapatch to roll out all the patch information:

  • datapatch -rollback all -force

Please note that the above command will rollback all patches from the REGISTRY within the database from all containers. So if you execute the above command in a container database environment, it will rollback patches from CDB$ROOT, PDB$SEED and all open PDBs.

In case you target only a single PDB you plan to plugout and plugin, then use this instead:

  • datapatch -pdbs MYPDB -rollback all -force

You can add more PDBs as a comma-separated list.

And this is what it does to my environment.

$ $OH19/OPatch/datapatch -rollback all -force
SQL Patching tool version 19.17.0.0.0 Production on Wed Jan 18 12:11:52 2023
Copyright (c) 2012, 2022, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_23912_2023_01_18_12_11_52/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 33192694 (OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)):
Binary registry: Unknown as -force or -noqi specified
PDB CDB$ROOT: Rolled back successfully on 19-JAN-22 10.14.44.327180 PM
PDB PDB$SEED: Rolled back successfully on 19-JAN-22 10.14.44.347566 PM
PDB PDB2: Rolled back successfully on 19-JAN-22 10.14.44.347566 PM
Interim patch 33561310 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)):
Binary registry: Unknown as -force or -noqi specified
PDB CDB$ROOT: Rolled back successfully on 20-JUL-22 09.17.02.767339 PM
PDB PDB$SEED: Rolled back successfully on 20-JUL-22 09.17.02.812896 PM
PDB PDB2: Rolled back successfully on 20-JUL-22 09.17.02.812896 PM
Interim patch 34086870 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)):
Binary registry: Unknown as -force or -noqi specified
PDB CDB$ROOT: Rolled back successfully on 08-NOV-22 11.05.34.788571 PM
PDB PDB$SEED: Rolled back successfully on 08-NOV-22 11.05.36.745638 PM
PDB PDB2: Rolled back successfully on 08-NOV-22 11.05.36.745638 PM
Interim patch 34411846 (OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)):
Binary registry: Unknown as -force or -noqi specified
PDB CDB$ROOT: Applied successfully on 08-NOV-22 11.05.35.742058 PM
PDB PDB$SEED: Applied successfully on 08-NOV-22 11.05.37.696607 PM
PDB PDB2: Applied successfully on 08-NOV-22 11.05.37.696607 PM
Interim patch 34734035 (MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 24338134 25143018 26565187):
Binary registry: Unknown as -force or -noqi specified
PDB CDB$ROOT: Applied successfully on 08-NOV-22 11.05.36.721787 PM
PDB PDB$SEED: Applied successfully on 08-NOV-22 11.05.38.671338 PM
PDB PDB2: Applied successfully on 08-NOV-22 11.05.38.671338 PM

Current state of release update SQL patches:
Binary registry:
Unknown as -force or -noqi specified
PDB CDB$ROOT:
Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 08-NOV-22 11.05.35.738500 PM
PDB PDB$SEED:
Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 08-NOV-22 11.05.37.692881 PM
PDB PDB2:
Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 08-NOV-22 11.05.37.692881 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB2
The following interim patches will be rolled back:
34411846 (OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846))
34734035 (MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 24338134 25143018 26565187)
Patch 34419443 (Database Release Update : 19.17.0.0.221018 (34419443)):
Rollback from 19.17.0.0.0 Release_Update 220924224051 to 19.1.0.0.0 Feature Release
No interim patches need to be applied

Installing patches...
Patch installation complete. Total patches installed: 9

Validating logfiles...done
Patch 34411846 rollback (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB2_CDBROOT_2023Jan18_12_12_00.log (no errors)
Patch 34734035 rollback (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34734035/24992341/34734035_rollback_CDB2_CDBROOT_2023Jan18_12_12_01.log (no errors)
Patch 34419443 rollback (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34419443/24972075/34419443_rollback_CDB2_CDBROOT_2023Jan18_12_12_22.log (no errors)
Patch 34411846 rollback (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB2_PDBSEED_2023Jan18_12_15_26.log (no errors)
Patch 34734035 rollback (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34734035/24992341/34734035_rollback_CDB2_PDBSEED_2023Jan18_12_15_26.log (no errors)
Patch 34419443 rollback (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34419443/24972075/34419443_rollback_CDB2_PDBSEED_2023Jan18_12_18_53.log (no errors)
Patch 34411846 rollback (pdb PDB2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB2_PDB2_2023Jan18_12_15_26.log (no errors)
Patch 34734035 rollback (pdb PDB2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34734035/24992341/34734035_rollback_CDB2_PDB2_2023Jan18_12_15_26.log (no errors)
Patch 34419443 rollback (pdb PDB2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34419443/24972075/34419443_rollback_CDB2_PDB2_2023Jan18_12_18_52.log (no errors)

Automatic recompilation incomplete; run utlrp.sql to revalidate.
PDBs: PDB2 PDB$SEED

SQL Patching tool complete on Wed Jan 18 12:20:53 2023

So it rolls out the entire SQL and PLSQL changes from my containers. Please note as well that I still run with 19.17.0 executables. So I’d rather either use a different executable at this point, or I’ll ask datapatch to apply the required changes again.

Please see MOS Note: 2680521.1 – Datapatch User Guide for more information and insights into datapatch.

 

Further Links and Information

 

–Mike

Share this: