Two of the best key features of the multitenant container databases are unplugging PDB from CDB and plugging PDB to the same or another CDB respectively. Let me explain how the unplugging actually works: There are two methods for unplugging PDB from CDB. The first and the older method which has been introduced in Oracle database 12.1 is unplugging PDB from the XML description file and the newer method that has been introduced in Oracle database 12.2 is unplugging PDB to the archive file. I am going to explain the first method, i.e., “using XML description file”. To migrate from a pre-12.1 non-CDB database to CDB please take a look here. Suppose after the unplugging PDB, the XML description file is lost or corrupted. Now, we are going to answer some questions as follows:
How to unplug a PDB from a CDB?
How to recover an XML description file?
How to plug a PDB into another CDB?
Unplug a PDB from a CDB:
Before attempting to unplug PDB we review the current CDB setup
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@oel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select name, cdb from v$database; NAME CDB --------- --- DB12C YES SQL> col name for a20; SQL> select con_id, name, open_mode, restricted from v$pdbs; CON_ID NAME OPEN_MODE RES ---------- -------------------- ---------- --- 2 PDB$SEED READ ONLY NO 3 MYPDB READ WRITE NO 4 MAS12C READ WRITE NO |
With the following commands we will unplug and then will drop PDB:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | SQL> alter pluggable database mypdb close; MYPDB(3):JIT: pid 40960 requesting stop Pluggable database MYPDB closed Completed: alter pluggable database mypdb close Pluggable database altered. SQL> alter pluggable database mypdb unplug into '/u01/mypdbfile.xml'; alter pluggable database mypdb unplug into '/u01/mypdbfile.xml' MYPDB(3):Autotune of undo reSQL> alter pluggable database mypdb close; MYPDB(3):JIT: pid 40960 requesting stop Pluggable database MYPDB closed Completed: alter pluggable database mypdb close Pluggable database altered. SQL> alter pluggable database mypdb unplug into '/u01/mypdbfile.xml'; alter pluggable database mypdb unplug into '/u01/mypdbfile.xml' MYPDB(3):Autotune of undo retention is turned on. MYPDB(3):Endian type of dictionary set to little MYPDB(3):[40960] Successfully onlined Undo Tablespace 2. MYPDB(3):Undo initialization finished serial:0 start:95543809 end:95543858 diff:49 ms (0.0 seconds) MYPDB(3):Database Characterset for MYPDB is AL32UTF8 MYPDB(3):Opatch XML is skipped for PDB MYPDB (conid=3) MYPDB(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated MYPDB(3):JIT: pid 40960 requesting stop MYPDB(3):Autotune of undo retention is turned on. MYPDB(3):Endian type of dictionary set to little MYPDB(3):Undo initialization finished serial:0 start:95546119 end:95546119 diff:0 ms (0.0 seconds) MYPDB(3):Database Characterset for MYPDB is AL32UTF8 MYPDB(3):Opatch XML is skipped for PDB MYPDB (conid=3) MYPDB(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated MYPDB(3):JIT: pid 40960 requesting stop Completed: alter pluggable database mypdb unplug into '/u01/mypdbfile.xml' Pluggable database altered. SQL> drop pluggable database mypdb; drop pluggable database mypdb Completed: drop pluggable database mypdb Pluggable database dropped. SQL> select con_id, name, open_mode, restricted from v$pdbs; CON_ID NAME OPEN_MODE RES ---------- -------------------- ---------- --- 2 PDB$SEED READ ONLY NO 4 MAS12C READ WRITE NO |
As I mentioned before, the supposition is that XML is lost or corrupted. Thus we rename the XML description file
1 2 3 4 5 | [oracle@oel7 ~]$ mv /u01/mypdbfile.xml /u01/mypdbfile.xml.old [oracle@oel7 ~]$ ll /u01/ total 8 drwxrwxr-x. 4 oracle oinstall 38 Jan 13 11:58 app -rw-r--r--. 1 oracle oinstall 6957 Jan 31 15:11 mypdbfile.xml.old |
recover an XML description file:
Let us review the destination CDB and belonging PDBs:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [oracle@oel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select name, cdb from v$database; NAME CDB --------- --- MYCDB YES SQL> col name for a20; SQL> select con_id, name, open_mode, restricted from v$pdbs; CON_ID NAME OPEN_MODE RES ---------- -------------------- ---------- --- 2 PDB$SEED READ ONLY NO 3 NIMA READ WRITE NO |
Now I am going to recover the XML description file. There is a DBMS Procedure (DBMS_PDB.RECOVER) which may help us to create an XML metadata file. The XML file Describes the database options and other configurations of PDB. This procedure generates an XML file that describes a pluggable database using data files belonging to the pluggable database (PDB). We can see syntax and procedure parameters as below:
1 2 3 4 5 6 7 8 9 10 11 12 13 | DBMS_PDB.RECOVER ( pdb_descr_file IN VARCHAR2, pdb_name IN VARCHAR2, filenames IN VARCHAR2); pdb_descr_file: Path of the XML file that contains description of a pluggable database pdb_name: Name of a pluggable database filenames: Comma-separated list of datafile paths and/or directories containing datafiles for the pluggable database |
For more information please click here. Let us begin recovering the XML description file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [oracle@oel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> begin dbms_pdb.recover( pdb_descr_file => '/home/oracle/mypdb.xml', pdb_name => 'mypdb', filenames => '/u01/app/oracle/oradata/db12c/mypdb' ); end; / PL/SQL procedure successfully completed. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@oel7 ~]$ ls -l mypdb.xml -rw-r--r--. 1 oracle oinstall 4184 Jan 31 16:20 mypdb.xml |
plug a PDB into another CDB:
The XML file has been recovered now, It’s time to plug our PDB to the CDB. One of the optional phases is to check PDB compatibility with CDB destination. For this check we use DBMS_PDB.CHECK_PLUG_COMPATIBILITY.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | [oracle@oel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> set serverout on; SQL> DECLARE theResult BOOLEAN; BEGIN l_result := DBMS_PDB.check_plug_compatibility( pdb_descr_file => '/home/oracle/mypdb.xml', pdb_name => 'mypdb'); IF theResult THEN DBMS_OUTPUT.PUT_LINE('pdb is compatible'); ELSE DBMS_OUTPUT.PUT_LINE('dpb is not incompatible'); END IF; END; / pdb is compatible PL/SQL procedure successfully completed. |
If the PDB is not compatible, Then violations are listed in the PDB_PLUG_IN_VIOLATIONS view. In the final step, we plug the PDB to the CDB.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SQL> create pluggable database mypdb using '/home/oracle/mypdb.xml' 2 file_name_convert=('/u01/app/oracle/oradata/db12c/mypdb','/u01/app/oracle/oradata/mycdb/mypdb') 3 tempfile reuse; Pluggable database created. SQL> alter pluggable database mypdb open; Pluggable database altered. SQL> col name for a20; SQL> select con_id, name, open_mode, restricted from v$pdbs; CON_ID NAME OPEN_MODE RES ---------- -------------------- ---------- --- 2 PDB$SEED READ ONLY NO 3 NIMA READ WRITE NO 4 MYPDB READ WRITE NO SQL> select name from v$datafile where con_id=4; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/mycdb/mypdb/undotbs01.dbf /u01/app/oracle/oradata/mycdb/mypdb/system01.dbf /u01/app/oracle/oradata/mycdb/mypdb/sysaux01.dbf /u01/app/oracle/oradata/mycdb/mypdb/users01.dbf |
We successfully moved the PDB to a different CDB. This is the common way to upgrade a database from version 12.1 to 12.2.
Thanks for your attention.
2 comments On How to unplug PDB, recover a XML description file and plug a PDB to another CDB
this is a wonderful post. i had lost all my hopes after my pdb was screwed, and then i found this page. it saved me from missing an important deadline. thank you so much.
I am happy that the post has helped you.