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.
Photo by Pramod Tiwari on Unsplash
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
- MOS Note: 2680521.1 – Datapatch User Guide
- Moving non-CDB to PDB: The Patch Level Pitfall
- PDB_PLUG_IN_VIOLATIONS – The View with the Special Name
–Mike
Hi Mike,
Some questions came to mind after reading this post:
a) From the path of the XML file, it looks like the compatibility check was performed on Windows. If the PDB was unplugged from Windows and going to be plugged into Linux, shouldn’t the compatibility check be performed on Linux?
b) If target is at 19.17, can’t we rollback the source to say 19.16 or do we need to rollback all?
c) Does datapatch need files from .patch_storage to rollback the SQL part of patch?
Thanks,
Arun
Hi Arun,
you can’t just rollback to 19.16.0. Or maybe you could, but it wouldn’t a relief in this case. And no, datapatch since 18c does not need any files in .patch_storage. It carries the necessary files in the database or PDB. Watch out for a blog post coming soon.
Reg the comp check, you are right – and I may have received only the first check. But I know that the customer did the check on the target as well. The below output is from the target. Hence, I guess it got executed twice. I removed the path to avoid any sort of puzzling here. But very well spotted – thanks!
Mike