When I changed our Hands-On Lab to Oracle 18c, I encountered one strange issue in case I upgraded the UPGR database to 18.3.0 and wanted to plug it in afterwards. Before plugin I usually do a compatibility check. But this seems to fail. I checked my file, my paths, everything. But I couldn’t find the root cause. And yesterday I received an email from a customer who has a case open for over a week encountering a similar problem. And we both came to the conclusion for Oracle 18.3.0 Multitenant: Compatibility Check does not work under certain circumstances.
Interestingly this applies to Oracle 18c sources only. In lower versions and with sources lower than Oracle 18.3.0 the DBMS_PDB.CHECK_PLUG_COMPATIBILITY
runs fine.
Compatibility Check
First of all, the idea of the compatibility check is to alert you about potential issues before you plugin. Still the procedure does not tell you anything about the issues but populates my favorite view PDB_PLUG_IN_VIOLATIONS with more or less useful information.
You should then fix ERROR
s either before or after the plugin. In the view you’ll find things such as “run noncdb_to_pdb.sql” after plugin.
This is how a compatibility check looks like:
SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/PDB12201.xml', pdb_name => 'PDB12201') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; /
It tells you “YES
” or “NO
” – but in case the procedure tells you “NO
” which happens in >90% of all cases, you will have to find out the reason in PDB_PLUG_IN_VIOLATIONS
.
Oracle 18.3.0 Multitenant: Compatibility Check does not work
Unfortunately this fails as soon as the source is an Oracle 18.x.y database or PDB.
This is a very simple test case – my compatibility_check.sql
contains exactly the check from above with adjusted paths:
SQL> create pluggable database pdb2 admin user pdb2 identified by pdb2 file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2'); Pluggable database created. SQL> alter pluggable database pdb2 open; Pluggable database altered. SQL> alter pluggable database pdb2 close; Pluggable database altered. SQL> alter pluggable database PDB2 unplug into '/home/oracle/pdb2.xml'; Pluggable database altered. SQL> drop pluggable database pdb2 keep datafiles; Pluggable database dropped. SQL> start compatibility_check.sql ERROR: ORA-03114: not connected to ORACLE DECLARE * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 32550 Session ID: 33 Serial number: 50352
Ouch.
Unfortunately the client disconnect usually means that there is a core dump happening. And that’s the case: ORA-07445: exception encountered: core dump [__intel_ssse3_rep_memcpy()+6710] [SIGSEGV] [ADDR:0xFFFFFFFFFFFFFFEF] [PC:0x6E46FE6] [Address not mapped to object] []
The customer who encountered the issue as well sent me an example with an 18.3.0 non-CDB which he wanted to clone directly as PDB into an 18.3.0 CDB. It fails with the same error.
Summary
In most cases you can safely live without the compatibility check with DBMS_PDB.CHECK_PLUG_COMPATIBILITY
. You’ll have to justify and fix the issues anyways. But in the case where you’d like to clone a non-CDB into a PDB the check is actually important to avoid multiple failing attempts.
As far as I see, the issue applies to Oracle 18c sources at least up to Oracle 18.3.0, regardless if you plan to plugin a non-CDB, clone a non-CDB or unplug and plug a PDB. As soon as you have a non-18c source such as an 12.2.0.1 PDB it works flawless.
Issue is under investigation. I will update the blog post once I find out about a patch number and potentially a MOS note.
Further Information
Please find further information here:
- Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c
- MOS Note: 2288129.1 (Known Issues With PDB_PLUG_IN_VIOLATIONS)
- noncdb_to_pdb.sql – Why does it take so long?
Important Update as of Sep 3, 2018
First of all the issue is tracked under BUG 28502403 – ORACLE 18.3.0 MULTITENANT: COMPATIBILITY CHECK DOES NOT WORK. The issue got introduced due to a malformed string. And the fix is included in Oracle 19c but is also available as single patch on Linux for 18.3.0 and 18.2.0. Glad that my Multitenant colleagues did provide a fix so quickly.
And a day after, the customer who alerted me about this issue, confirmed that the patch works fine – and the compatibility check with the patch is working.
–Mike
Would this issue apply to a scenario whereby we upgrade from 11.2.0.4 to Non-CDB 18.3 and try to plug into an existing 18.3 CDB?
Thanks
Yes, it will:
“Unfortunately this fails as soon as the source is an Oracle 18.x.y database or PDB.”
Only if you’d upgrade to 12.2.0.1 or 12.1.0.2 you wouldn’t see the issue. But this wouldn’t make sense.
Better plugin and check PDB_PLUG_IN_VIOLATIONS and fix the issues. Use the COPY option so you keep your source when plugin.
Cheers,
Mike
Nice to read as always
Thanks!
Is the DBMS_PDB.CHECK_PLUG_COMPATIBILITY supposed to check for characterset compatibility ?
Reason for asking is that when I have a 19c CDB with characterset AL32UTF8 and want to plugin a none-cdb with WE8ISO8859P1 the outcome from DBMS_PDB.CHECK_PLUG_COMPATIBILITY is YES.
However, Oracle support states that this combination is not supported and therefore not compatible. In PDB_PLUG_IN_VIOLATIONS there are two warnings also indicading this :
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
PDB not in Unicode (AL32UTF8) character set. PDB character set WE8ISO8859P1.
So shouldn’t the outcome from DBMS_PDB.CHECK_PLUG_COMPATIBILITY have been NO ?
Hi Børge,
another magic answer from Oracle Support 🙁
As long as:
– your CDB is at least at 12.2.0.1 (and 19c is newer)
– COMPATIBLE is equal or higher than 12.2.0
– and your CDB has been created with AL32UTF8
you of course can plugin.
See here:
https://mikedietrichde.com/2017/10/23/can-you-select-a-pdbs-character-set/
So yes, of course, you can do that.
And the WARNING in PDB_PLUG_IN_VIOLATIONS is only a WARNING which has no deeper meaning. Hence, the YES from the compatibility check is correct.
Cheers,
Mike
Many thanks for your prompt reply Mike.