DBMS_PDB.DESCRIBE – Something to be aware of

When I wrote up my long blog post series about how to migrate to Multitenant, I came across an interesting behavior. I could create the XML manifest file to plugin without putting the database in read-only mode. But I learned afterwards that there are is a pitfall. Hence, this blog post will talk about DBMS_PDB.DESCRIBE – Something to be aware of.

DBMS_PDB.DESCRIBE – why and when?

You will use the DBMS_PDB.DESCRIBE call for the sole purpose to create the so called XML manifest file. This file contains information about the non-CDB to plugin afterwards. You either generate it check the compatibility, or to tell the receiving CDB where the files of the non-CDB  are located. That’s a simplified explanation – but feel free to have a look at the XML file.

The call is simple:

exec DBMS_PDB.DESCRIBE('/home/oracle/DB12.xml');

In Oracle Database 12.1.0.1 and 12.1.0.2, executing this call required to start the database in read-only mode. But during all the tests for my blog post series about how to migrate to Multitenant, I came across the fact that read-only is not required anymore. I took this for granted and happily changed my notes and write-up.

DBMS_PDB.DESCRIBE – what if?

I sent the feedback to my colleagues for the AutoUpgrade. Some weeks later, my team mate Ben contacted me asking if this has really worked for me. He gets this error sequence during his tests:

SQL> create pluggable database "db19200" using '/home/oracle/autoupg/db19200/temp/db19200.xml' NOCOPY tempfile reuse
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file
/databases/oradata/DB19200/system01.dbf for value of fcpsb (2028174 in the plug XML file, 2030170 in the data file)

I tried it again and in my environment, and for me it worked fine.

Very strange.

So we did ask the Multitenant team whether there had been a change, and if yes, what the reason may be.

DBMS_PDB.DESCRIBE – a small change

Ben and I did some research, and it was easy to reproduce the behavior. An ALTER SYSTEM FLUSH SHARED_POOL, or simpler, a ALTER SYSTEM CHECKPOINT led to this (mis)behavior,

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 25 00:21:37 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> exec dbms_pdb.describe('/home/oracle/hugo.xml');

PL/SQL procedure successfully completed.

SQL> alter system checkpoint;

System altered.

SQL> shutdown immediate

When you try to plugin this non-CDB, the error happens:

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 25 00:26:15 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> create pluggable database hugo using '/home/oracle/hugo.xml' nocopy;
create pluggable database hugo using '/home/oracle/hugo.xml' nocopy
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file
/u02/oradata/HUGO/system01.dbf for value of fcpsb (595566 in the plug XML file,
598650 in the data file)

I could fix this by editing the XML file manually and exchanged 595566 with 598650 everywhere it occurred. Simple fix of course just in case you face this issue. Hence, not critical.

Why?

The question remained why this change had been introduced silently. And the Multitenant team shed some light. There were several cases – which I can see as a very valid case, too – to generate the XML file to check plugin compatibility. But you may not want to take their database into read-only mode just to check whether you can plugin with seeing any issues.

set serveroutput on

DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/DB12.xml', pdb_name => 'FTEX') WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Is the future PDB compatible?  ==>  ' || compatible);
END;
/

So that’s good. Even though I’d preferred an option on the call which allows you to specify explicitly to create the file in read-write mode, Data Pump for instance does something for the “transportable trial” mode where you can generate the TTS metadata for testing without the need for read-only mode of your tablespaces (19c feature).

Summary

Since Oracle Database 12.2.0.1 you can execute the DBMS_PDB.DESCRIBE call without the need to take your database into read-only mode. But this is meant only for the compatibility check portion. If you want to plugin this database later on, bring it into read-only mode, generate the XML file and shut it down and plug it in afterwards.

–Mike