Database 12c: Plug and Unplug PDB


To unplug pdb the pdb must be close. So you can close the original pdb or clone and unplug the database. The basic unplug and plug mechanism I’m going to describe in this article:

1. Prepare to unplug

[bash]$ mkdir /opt/oracle/unplug
[bash]$ set ORACLE_SID=CDB12C1
[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 12 23:58:10 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

2. Now unplug database

SQL> alter pluggable database pdb1 unplug into '/opt/oracle/unplug/pdb1.xml';

Pluggable database altered.

Take a look into the new generated XML file

cat /opt/oracle/unplug/pdb1.xml 
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
 <pdbname>PDB1</pdbname>
 <cid>3</cid>
 <byteorder>1</byteorder>
 <vsn>202375168</vsn>
 <dbid>3328312323</dbid>
 <cdbid>4018805978</cdbid>
 <guid>E157C0F3C35907C7E0430100007F6C6A</guid>
 <uscnbas>2082581</uscnbas>
 <uscnwrp>0</uscnwrp>
 <rdba>4194824</rdba>
 <tablespace>
 <name>SYSTEM</name>
 <type>0</type>
 <tsn>0</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0vf3kn_.dbf</path>
 <afn>51</afn>
 <rfn>1</rfn>
 <createscnbas>2077841</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>33280</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>3328312323</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>2082579</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>820192925</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>SYSAUX</name>
 <type>0</type>
 <tsn>1</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_sysaux_8y0vf56k_.dbf</path>
 <afn>52</afn>
 <rfn>4</rfn>
 <createscnbas>2077844</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>81920</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>3328312323</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>2082579</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>820192925</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>TEMP</name>
 <type>1</type>
 <tsn>2</tsn>
 <status>1</status>
 <issft>0</issft>
 <bmunitsize>128</bmunitsize>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_temp_8y0vf96l_.dbf</path>
 <afn>3</afn>
 <rfn>1</rfn>
 <createscnbas>2077842</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>0</status>
 <fileblocks>12800</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <autoext>1</autoext>
 <maxsize>262144</maxsize>
 <incsize>6400</incsize>
 </file>
 </tablespace>
 <optional>
 <csid>873</csid>
 <ncsid>2000</ncsid>
 <options>
 <option>APS=12.1.0.1.0</option>
 <option>CATALOG=12.1.0.1.0</option>
 <option>CATJAVA=12.1.0.1.0</option>
 <option>CATPROC=12.1.0.1.0</option>
 <option>CONTEXT=12.1.0.1.0</option>
 <option>DV=12.1.0.1.0</option>
 <option>JAVAVM=12.1.0.1.0</option>
 <option>OLS=12.1.0.1.0</option>
 <option>ORDIM=12.1.0.1.0</option>
 <option>OWM=12.1.0.1.0</option>
 <option>SDO=12.1.0.1.0</option>
 <option>XDB=12.1.0.1.0</option>
 <option>XML=12.1.0.1.0</option>
 <option>XOQ=12.1.0.1.0</option>
 </options>
 <olsoid>0</olsoid>
 <dv>0</dv>
 <ncdb2pdb>0</ncdb2pdb>
 <APEX>4.2.0.00.27:1</APEX>
 <parameters>
 <parameter>processes=300</parameter>
 <parameter>sga_target=1979711488</parameter>
 <parameter>db_block_size=8192</parameter>
 <parameter>compatible=12.1.0.0.0</parameter>
 <parameter>open_cursors=300</parameter>
 <parameter>pga_aggregate_target=655360000</parameter>
 <parameter>enable_pluggable_database=TRUE</parameter>
 </parameters>
 <tzvers>
 <tzver>primary version:18</tzver>
 <tzver>secondary version:0</tzver>
 </tzvers>
 <walletkey>0</walletkey>
 </optional>
</PDB>

As you can see is the unplug an plug process only an XML? No normally you should clone the pdb or restore the datafiles to an alternativ host and/or location. In my example I’m going to leave the datafiles on the same location.

3. Check state of PDB in source CDB

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
         2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED                       READ ONLY  NO  12-JUL-13 11.14.26.658 PM                                                      1720729  283115520
         3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB1                           MOUNTED        12-JUL-13 11.58.34.430 PM                                                      2077841          0

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

SQL> drop pluggable database pdb1;

Pluggable database dropped.

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
         2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED                       READ ONLY  NO  12-JUL-13 11.14.26.658 PM                                                      1720729  283115520

4. Now plug-in the PDB in another CDB

[bash]$ set ORACLE_SID=CDB12C2
[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 12 23:58:10 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create pluggable database pdb2 using '/opt/oracle/unplug/pdb1.xml';

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select * from v$pdbs;

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
 2 4063213762 4063213762 E157DF67E3A80D46E0430100007F0AFD PDB$SEED READ ONLY NO 12-JUL-13 11.12.26.653 PM 1720729 283115520
 3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB2 READ WRITE NO 13-JUL-13 12.04.45.481 AM 2083522 283115520

SQL>

If the database plug-in has been done all the Datafiles will be copied:

SQL> select con_id,name from v$datafile order by 1;

 CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_system_8y0vsncx_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_sysaux_8y0vr78k_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_undotbs1_8y0vv2ls_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_users_8y0vv1ht_.dbf
 2 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_system_8y0vvx50_.dbf
 2 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_sysaux_8y0vvx4q_.dbf
 3 /opt/oracle/app/oradata/CDB12C2/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0zcmfn_.dbf
 3 /opt/oracle/app/oradata/CDB12C2/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_sysaux_8y0zcmfc_.dbf

8 rows selected.

To compare the datafile of the SYSTEM tablespace from source:

/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0vf3kn_.dbf

Notice: The GUID of the PDB will be the same in the new CDB!

Side effect, replug

An interesting side effect is, that you are able to replug the database after it has been dropped:

SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL> drop pluggable database pdb2;

Pluggable database dropped.

SQL> create pluggable database pdb2 using '/opt/oracle/unplug/pdb1.xml';

Pluggable database created.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select * from v$pdbs;

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
 2 4063213762 4063213762 E157DF67E3A80D46E0430100007F0AFD PDB$SEED READ ONLY NO 12-JUL-13 11.12.26.653 PM 1720729 283115520
 3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB2 READ WRITE NO 13-JUL-13 12.04.45.481 AM 2083522 283115520

SQL>

If you are unintentionally try to plugin the same PDB again an error will be raised:

SQL> create pluggable database pdb3 using '/opt/oracle/unplug/pdb1.xml';
create pluggable database pdb3 using '/opt/oracle/unplug/pdb1.xml'
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s