Another surprise: DBMS_PDB in Oracle 11.2.0.4

I think I should start a blog post series of surprises. At least, this time it’s not harmful at all. But it still a surprise. And ACE Director Rodrigo Jorge reported about it and did ask me for more details. So yes, here we have Another surprise: DBMS_PDB in Oracle 11.2.0.4.

DBMS_PDB

You use the package DBMS_PDB normally to create the so called XML manifest file which describes the physical layout of a PDB or future PDB. And it adds some markers. You can find the package since Oracle Database 12.1.0.1 – the release when Oracle Multitenant got introduced.

The structure is (sort of) documented – find it here:

When you compare the definitions, you realize immediately that there were a lot of subroutines added since its introduction. And most of them unfortunately are not even documented anywhere.

  • Oracle Database 12.1.0.2
    SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 17 00:36:15 2020
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> desc dbms_pdb
    
    FUNCTION CHECK_PLUG_COMPATIBILITY RETURNS BOOLEAN
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_DESCR_FILE 		VARCHAR2		IN
     PDB_NAME			VARCHAR2		IN     DEFAULT
    
    FUNCTION CLEANUP_TASK RETURNS NUMBER
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     TASK_ID			NUMBER			IN
    
    PROCEDURE CREATEX$PERMANENTTABLES
    
    PROCEDURE DESCRIBE
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_DESCR_FILE 		VARCHAR2		IN
     PDB_NAME			VARCHAR2		IN     DEFAULT
    
    PROCEDURE DROPX$PERMANENTTABLES
    
    PROCEDURE EXEC_AS_ORACLE_SCRIPT
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SQL_STMT			VARCHAR2		IN
    
    PROCEDURE NONCDB_TO_PDB
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PHASE				NUMBER			IN
    
    PROCEDURE POPULATESYNCTABLE
    
    PROCEDURE RECOVER
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_DESCR_FILE 		VARCHAR2		IN
     PDB_NAME			VARCHAR2		IN
     FILENAMES			VARCHAR2		IN
    
    PROCEDURE SYNC_PDB
    
    FUNCTION UPDATE_CDBVW_STATS RETURNS NUMBER
    
    FUNCTION UPDATE_COMDATA_STATS RETURNS NUMBER
    
    FUNCTION UPDATE_OBJLINK_STATS RETURNS NUMBER
    
    PROCEDURE UPDATE_VERSION
    
  • Oracle Database 19c
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.8.0.0.0
    
    SQL> desc dbms_pdb
    FUNCTION CHECK_LOCKDOWN RETURNS BOOLEAN
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     RULE_TYPE			NUMBER			IN
     RULE_ID			NUMBER			IN
     RAISE_ERROR			BOOLEAN 		IN
     INIT_PARAMETER 		NUMBER			IN
     EVENTS 			BOOLEAN 		IN
    
    PROCEDURE CHECK_NFT
    
    FUNCTION CHECK_PLUG_COMPATIBILITY RETURNS BOOLEAN
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_DESCR_FILE 		VARCHAR2		IN
     PDB_NAME			VARCHAR2		IN     DEFAULT
     FATAL_ONLY			BOOLEAN 		IN     DEFAULT
    
    FUNCTION CLEANUP_TASK RETURNS NUMBER
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     TASK_ID			NUMBER			IN
    
    PROCEDURE CLEAR_PLUGIN_VIOLATIONS
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_NAME			VARCHAR2		IN     DEFAULT
    
    PROCEDURE CONVERT_TO_LOCAL
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SCHEMA_NAME			VARCHAR2		IN
     OBJECT_NAME			VARCHAR2		IN
     NAMESPACE			NUMBER			IN
     OBJECT_SUBNAME 		VARCHAR2		IN     DEFAULT
     SIG_MISMATCH			BOOLEAN 		IN     DEFAULT
    
    PROCEDURE CREATEX$PERMANENTTABLES
    
    PROCEDURE DESCRIBE
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_DESCR_FILE 		VARCHAR2		IN
     PDB_NAME			VARCHAR2		IN     DEFAULT
    
    PROCEDURE DROPX$PERMANENTTABLES
    
    PROCEDURE EXEC_AS_ORACLE_SCRIPT
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SQL_STMT			VARCHAR2		IN
    
    PROCEDURE EXPORTRMANBACKUP
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_NAME			VARCHAR2		IN     DEFAULT
    
    FUNCTION IS_VALID_PATH RETURNS BOOLEAN
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PATH_NAME			VARCHAR2		IN
    
    PROCEDURE NONCDB_TO_PDB
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PHASE				NUMBER			IN
    
    PROCEDURE POPULATESYNCTABLE
    
    PROCEDURE RECOVER
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PDB_DESCR_FILE 		VARCHAR2		IN
     PDB_NAME			VARCHAR2		IN
     FILENAMES			VARCHAR2		IN
    
    PROCEDURE SET_DATA_LINKED
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SCHEMA_NAME			VARCHAR2		IN
     OBJECT_NAME			VARCHAR2		IN
     NAMESPACE			NUMBER			IN
     EDITION_NAME			VARCHAR2		IN     DEFAULT
    
    PROCEDURE SET_EXT_DATA_LINKED
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SCHEMA_NAME			VARCHAR2		IN
     OBJECT_NAME			VARCHAR2		IN
     NAMESPACE			NUMBER			IN
     EDITION_NAME			VARCHAR2		IN     DEFAULT
    
    PROCEDURE SET_METADATA_LINKED
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SCHEMA_NAME			VARCHAR2		IN
     OBJECT_NAME			VARCHAR2		IN
     NAMESPACE			NUMBER			IN
     EDITION_NAME			VARCHAR2		IN     DEFAULT
    
    PROCEDURE SET_PROFILE_EXPLICIT
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PROFILE_NAME			VARCHAR2		IN
    
    PROCEDURE SET_ROLE_EXPLICIT
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     ROLE_NAME			VARCHAR2		IN
    
    PROCEDURE SET_SHARING_NONE
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SCHEMA_NAME			VARCHAR2		IN
     OBJECT_NAME			VARCHAR2		IN
     NAMESPACE			NUMBER			IN
     EDITION_NAME			VARCHAR2		IN     DEFAULT
    
    PROCEDURE SET_USER_EXPLICIT
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     USER_NAME			VARCHAR2		IN
    
    PROCEDURE SYNC_PDB
    
    FUNCTION UPDATE_CDBVW_STATS RETURNS NUMBER
    
    FUNCTION UPDATE_COMDATA_STATS RETURNS NUMBER
    
    FUNCTION UPDATE_DATALINK_STATS RETURNS NUMBER
    
    PROCEDURE UPDATE_VERSION
    

I count 1 FUNCTION and 10 PROCEDUREs in the 19c documentation. But the packaged contains actually 6 FUNCTIONs and 19 PROCEDUREs.

A while ago I blogged about a secret behavior change which allows you to create the manifest file in read-write mode – and almost certainly will lead to issues.

When you issue the DESCRIBE in Oracle Database 11.2.0.4, you will receive this message:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dbms_pdb.describe
ERROR:
ORA-04043: object dbms_pdb.describe does not exist

That’s expected.

Another surprise: DBMS_PDB in Oracle 11.2.0.4

But Rodrigo checks all differences between patch bundles. And he apparently discovered DBMS_PDB in Oracle 11.2.0.4.

How could this happen? And why didn’t I have it, even though I patch all my environments with the most recent patch bundles?

Applying the July 2020 Bundle Patch on Oracle 11.2.0.4

Now I am curious. I’ve had the impression that it may be included only in the Exadata Bundle Patch. So I download the July 2020 Bundle Patch which I quickly found with:

I tried to check whether it gives any indication on this surprise-package:

But unfortunately, this note does contain no section or link for “Bugs fixed with this patch”. Hm …

DBMS_PDB only if …

In my first test, I created a database with no components. And I’ve ended up with no DBMS_PDB. Then I started looking at the scripts – and with the July 2020 Exadata Bundle Patch there is this new dbmspdb.sql script in ?/rdbms/admin.

I executed the script manually – and it threw a lot of errors with XML functions. XML was missing in my database:

Another surprise: DBMS_PDB in Oracle 11.2.0.4

So I added it.

With the Exadata BP, didn’t even had to execute the script – DBMS_PDB existed now:

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 16 23:53:45 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dbms_pdb

PROCEDURE DESCRIBE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DIRNAME			VARCHAR2		IN
 FILENAME			VARCHAR2		IN

FUNCTION GETRELEASELEVEL RETURNS NUMBER

But in my other database with the PSU, I had to execute the dbmspdb.sql manually:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dbms_pdb
ERROR:
ORA-04043: object dbms_pdb does not exist


SQL> start ?/rdbms/admin/dbmspdb

no rows selected


Package created.

No errors.

Package body created.

No errors.

Of course I was wrong when I assumed that this is part of the Bundle Patch only. It was on disk with my 11.2.0.4 PSU as well. But as I’ve had no XML component configured, the package wasn’t in my database:

[FTEX] oracle@hol:/u01/app/oracle/product/11.2.0.4/rdbms/admin
$ ls -lrt dbmspdb*
-rw-r--r--. 1 oracle dba 71928 Jun 15 19:18 dbmspdb.sql

This is from my environment with the July 2020 Oracle 11.2.0.4 PSU. The file dates from June 15 – so it must have been included into the patch bundle pretty late.

Quite interesting is the header:

Rem $Header: rdbms/admin/dbmspdb.sql /st_rdbms_11.2.0.4.0dbpsu/1 2020/04/25 12:57:03 apfwkr Exp $
Rem
Rem dbmspdb.sql
Rem
Rem Copyright (c) 2018, 2020, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem    NAME
Rem      dbmspdb.sql - 11.2.0.4 implementation of selected procedures/functions
Rem                     from the dbms_pdb package
Rem
Rem    DESCRIPTION
Rem      This file defines dbms_pdb package to be used to facilitate plugging
Rem      pre-12 non-CDBs into CDBs

But the script log shows that it dates from December 2018.

How useful is it?

From my personal point of view, I don’t see a reason why I would miss DBMS_PDB in 11.2.0.4.

We highly recommend to upgrade first before plugging in for the pure and simple reason of a proven and simple fallback (which does not exist when you plugin at first), I doubt that I will have use for it.

Furthermore, the package is not documented anywhere in the 11.2.0.4 documentation. Hence, following the Oracle philosophy, not documented is internal-only, and hence it is not intended for customer usage.

And finally, as Rodrigo pointed out, it does not seem to work. Well … let me do a quick test.

The GETRELEASELVEL function returns in my case:

declare
   result number;
begin
   result := DBMS_PDB.GETRELEASELEVEL;
   DBMS_OUTPUT.PUT_LINE(result);
end;
/

this value:

186647552

Whatever this is …

Thanks to my colleague Rui Correia, I learned that the number is the HEX representation of 112040 – if you use DBMS_OUTPUT.PUT_LINE(to_char(result,’XXXXXXXXXX’)); in the above procedure, you’ll receive: B200400

The DESCRIBE procedure seems to work:

SQL> exec DBMS_PDB.DESCRIBE('DATA_PUMP_DIR','dbmspdb.xml');

PL/SQL procedure successfully completed.

It requires a directory – and generates an XML file. I started my DB in READ ONLY mode to execute the above action.

Does the Plugin work?

Next step I was curious about: Does the plugin work? Hence, I tried to plugin my 11.2.0.4 non-CDB into a 19c CDB.

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 17 00:21:35 2020
Version 19.8.0.0.0

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


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

SQL> create pluggable database DBMSPDB using '/u02/oradata/DBMSPDB/dbmspdb.xml' nocopy tempfile reuse;

Pluggable database created.

This succeeded.

But there is an upgrade to run – and a convert operation. My “new” PDB is an 11.2.0.4 PDB – and I plugged it into a 19c CDB.

Remember one important rule? When you plugin, you should to be at the same version.

Now, in this particular case an upgrade is needed.

Unfortunately, I couldn’t even start the new “PDB” (it is not really a PDB at this point) in UPGRADE mode:

SQL> startup upgrade
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Basically the same error, Rodrigo ran into when he tried to kick off noncdb_to_pdb.sql.

Behind the scenes …

Ah … this blog post gets much longer than I intended.

I check the alert.log:

DBMSPDB(3):ALTER PLUGGABLE DATABASE  OPEN UPGRADE
DBMSPDB(3):Autotune of undo retention is turned on.
2020-09-17T00:26:35.144805+02:00
DBMSPDB(3):Endian type of dictionary set to little
DBMSPDB(3):Undo initialization recovery: err:0 start: 450086 end: 450106 diff: 20 ms (0.0 seconds)
DBMSPDB(3):[4659] Successfully onlined Undo Tablespace 2.
DBMSPDB(3):Undo initialization online undo segments: err:0 start: 450106 end: 450114 diff: 8 ms (0.0 seconds)
DBMSPDB(3):Caching undo dictionary info: used hint for object index=3
DBMSPDB(3):Caching undo dictionary info: used hint for object index=4
DBMSPDB(3):Undo initialization finished serial:0 start:450086 end:450119 diff:33 ms (0.0 seconds)
DBMSPDB(3):Deleting old file#8 from file$
DBMSPDB(3):Deleting old file#9 from file$
DBMSPDB(3):Deleting old file#10 from file$
DBMSPDB(3):Deleting old file#11 from file$
DBMSPDB(3):Adding new file#8 to file$(old file#8).             fopr-0, newblks-89600, oldblks-89600
DBMSPDB(3):Adding new file#9 to file$(old file#9).             fopr-0, newblks-76800, oldblks-76800
DBMSPDB(3):Adding new file#10 to file$(old file#10).             fopr-0, newblks-25600, oldblks-25600
DBMSPDB(3):Adding new file#11 to file$(old file#11).             fopr-0, newblks-640, oldblks-640
Pdb DBMSPDB hit error 604 during open read write (1) and will be closed.
2020-09-17T00:26:35.234825+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_4659.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
DBMSPDB(3):JIT: pid 4659 requesting stop
DBMSPDB(3):Buffer Cache flush started: 3
DBMSPDB(3):Buffer Cache flush finished: 3
DBMSPDB(3):ORA-604 signalled during: ALTER PLUGGABLE DATABASE  OPEN UPGRADE...

The trace tells me:

*** 2020-09-17T00:26:35.213332+02:00 (DBMSPDB(3))
Caching undo dictionary info: used hint for object index=3
Caching undo dictionary info: used hint for object index=4

*** 2020-09-17T00:26:35.234499+02:00 (CDB$ROOT(1))
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x7ffcb0b6e6b8 placed kpdba.c@13479
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

*** 2020-09-17T00:26:35.235107+02:00 (DBMSPDB(3))
JIT: pid 4659 requesting stop

So this won’t work. And I stopped digging deeper at this point.

Now … is it useful?

I can’t see any benefit from it. I always upgrade at first as I have a proven fallback. For instance, I can flashback to a GRP.

When I plugin at first, I have no fallback for the upgrade. And my process gets proven correct here as I plugin the 11g database, and then I’m fully stuck. I can’t go forward, I can’t go back. It is broken.

Actually the above test I’ve did shows high right we are. I plugig using the NOCOPY option – and I can’t even start the PDB in upgrade mode to upgrade it.

I can only restore my backup.

That’s it. I’m lost.

And, as the package is not documented for 11g, consider this blog post as a test and check – the procedure does not really exist officially.

Please use this procedure instead – it is well proven, AutoUpgrade does it for you all in one pass – and it works if you avoid the pitfalls.

I just learned from a customer that there is a MOS Note 2650115.1 – How to Plug-in 11.2.0.4 non-CDB (Without Upgrading) to 19c) which has a complete absurd title. “Without Upgrading” is funny as the note uses the ancient call directly to catctl.pl instead of using dbupgrade which is much shorter – and of course, does an upgrade.

More Information and Links

–Mike

Share this: