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.
Photo by Kira auf der Heide on Unsplash
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:
- MOS Note: 2664876.1 – Critical Patch Update (CPU) Program Jul 2020 Patch Availability Document (PAD)
- Quarterly Database Patch for Exadata BP 11.2.0.4.200714 Patch 31220011 for UNIX
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:
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
- Blog Post by Rodrigo Jorge: DBMS_PDB arriving in Oracle 11.2.0.4
- Oracle Documentation: DBMS_PDB in Oracle 12.1.0.2
- Oracle Documentation: DBMS_PDB in Oracle 19c
- DBMS_PDB.DESCRIBE – Something to be aware of
- Patching all my environments with the July 2020 Patch Bundles
- Migration from non-CDB to PDB – Upgrade/Plugin/Convert
- MOS Note 2650115.1 – How to Plug-in 11.2.0.4 non-CDB (Without Upgrading) to 19c)
–Mike
I guess, the details of this procedure is document in the following ID
How to Plug-in 11.2.0.4 non-CDB (Without Upgrading) to 19c (Doc ID 2650115.1)
Thanks – good advice.
But as I documented, it fails already when I try to open the PDB in UPGRADE mode 😉
And furthermore, I see zero benefit in using this but only disadvantages 😉
Kind regards,
Mike