Managing Oracle 12c CDB’s and PDB’s – Plugging and Unpluggging PDB’s – Part B

One of the main features of the Oracle Database 12c is the portable nature of the pluggable databases (PDBs). You can easily unplug a PDB from a CDB and then plug it into a different Container database (CDB). This ease of Plugging in and Unplugging PDBs makes the 12c database truly Cloud ready. If you have just downloaded the Oracle Database software you may want to read previous article.

Oracle 12c PDB Multitenent Database

Let’s look at how to accomplish this task of plugging and unplugging PDB’s. There are two ways you can do that and essentially they are based on whether you want to move the datafiles from one location to the other or not. We will look at both of these methods here.

Using the NoCopy Method

The first method uses the NoCopy option which implies that you want to unplug the PDB from one CDB to another without moving the actual datafiles. Obviously this can only be done when you want to unplug a PDB from one CDB to another CDB, on the same server.

In our examples here, we will use two CDBs named CDB12c and CDBNEW. The CDB12c container database has one PDB database named PDB3 and the CDBNEW container database has one PDB database named PDB10. The SQL commands below confirm the databases in the current environment.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdb12c
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB3 READ WRITE
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdbnew
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB10 READ WRITE

First lets move the PDB10 from the CDBNEW to CDB12c. Lets now unplug CDB10 from CDBNEW .
SQL> alter pluggable database pdb10 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb10 unplug into '/u01/app/oracle/oradata/pdb10_unplug.xml';
Pluggable database altered.
SQL> drop pluggable database pdb10 keep datafiles;
Pluggable database dropped.

Just three commands and the database has been unplugged and ready to be moved. First we closed the database, then the second command generates an XML manifest file and the third command drops the PDB from the current CDB it is attached to. Please note the “Keep datafiles” clause which make sure that data files are not deleted.

To confirm that PDB10 is no longer part of CDBNEW issue the following command.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY

The next step is to plug the database into our second CDB i.e. CDB12c. Log into CDB12c and run the following command to plug PDB10 into PDB12c container database.
SQL> create pluggable database pdb10_nocopy using '/u01/app/oracle/oradata/pdb10_unplug.xml'
2 nocopy
3 tempfile reuse;
Pluggable database created.
Note the use of XML manifest file and also the NoCopy clause. The NoCopy clause basically makes sure that data files retain their location. The Temp File reuse clause is required to re-initiate the temporary files.

You can query the v$PDBS to confirm plugging of database.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB3 READ WRITE
PDB10_NOCOPY MOUNTED

The newly plugged database is in the mount state. You can now open it for normal use.
SQL> alter pluggable database pdb10_nocopy open;
Pluggable database altered.

Using Copy Method

The second method uses the Copy clause to copy the datafiles from the old location to new. Almost all steps are the same, except for the actual command which plugs the database to new CDB container database. In this example we will move PDB3 from CDB12c to CDBNEW. Before we move onto unplugging the PDB3 database from CDB12c first check the location of datafiles. This is to make sure that Copy clause can indeed move data files.
SQL> select file_name from cdb_data_files where con_id=3;
FILE_NAME
----------------------------------------------------------------------
/u02/app/oracle/oradata/cdb12c/pdbtest/system01.dbf
/u02/app/oracle/oradata/cdb12c/pdbtest/sysaux01.dbf
(We have used CDB_DATA_Files view which is new in 12c. Any view starting with CDB will only be part of CDBs. They do not exist in PDBs. They show information from all PDBs attached to CDB and also information about CDB itself. For example CDB_DATA_FILES will have information about all datafiles. Whether they belong to CDB itself or they belong to some PDB attached to it. You can filter information using the CON_ID column as shown above. The DBA_DATA_FILES is still there in PDBs and in CDBs and will only show information pertinent to datafiles of the current database only.)

Now that we have the current location of data files, lets move onto to the unplugging phase.
SQL> alter pluggable database pdb3 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb3 unplug into '/u01/app/oracle/oradata/pdb3_unplug.xml';
Pluggable database altered.
SQL> drop pluggable database pdb3 keep datafiles;
Pluggable database dropped.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB10_NOCOPY READ WRITE


The unplugging part is almost the same. First we close the database, create the manifest file and then drop the database from CDB while keeping datafiles intact. Querying the V$PDBS confirms the process.
To plug PDB3 into our other CDB i.e. CDBNEW, log into CDBNEW and use the following command.
SQL> create pluggable database pdb3_copy using '/u01/app/oracle/oradata/pdb3_unplug.xml'
2 copy
3 tempfile reuse;
Pluggable database created.

SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB3_COPY MOUNTED

SQL> alter pluggable database pdb3_copy open;
Pluggable database altered.

The command to plug it is similar to the method above except that this time around we used the copy clause. The Copy clause will move the files to the new default location of the CDBNEW container database. The subsequent commands shows that PDB_COPY has been created in CDBNEW. To verify the new location of data files use the following command.
SQL> select file_name from cdb_data_files where con_id=3;
FILE_NAME
----------------------------------------------------------------------
/u02/app/oracle/oradata/CDBNEW/E03A6382B68D4162E0450000FF00020F/datafile/
o1_mf_system_8wzvmf7n_.dbf
/u02/app/oracle/oradata/CDBNEW/E03A6382B68D4162E0450000FF00020F/datafile/
o1_mf_sysaux_8wzvmktl_.dbf

As you can see that the datafiles have been moved into the new default location of the CDNBEW container. The names are not what you may be expecting and that is because we did not provided any convention. You can use the FILE_NAME_CONVERT clause to properly specify the custom location where data files should be copied.
File_Name_Reuse(,)

Using As Clone … Move Method

The last scenario of plugging and unplugging pdb’s is when you want to plug back a database into the same CDB from where it was removed. Suppose we have the following pdbs in our CDB.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE

Let’s unplug the PDB1 database.
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 unplug into '/u01/app/oracle/unplug_pdb1.xml';
Pluggable database altered.

The PDB1 database has been unplugged from the CDB container. Hence it cannot be used anymore. If you try to open it, you will get the error message.
SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

What if you want to get it back up and running again? Here the “As Clone … Move method” will come in handy. You will be able to plug the database back in again, with a single command after renaming the database name.
SQL> create pluggable database pdb1_plug_move
2 as clone using '/u01/app/oracle/unplug_pdb1.xml'
3 move
4 file_name_convert=('pdb1','pdb1_plug_move');
Pluggable database created.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDB1_PLUG_MOVE MOUNTED

The new database PDB1_PLUG_MOVE has been created as an exact replica of the PDB1 database. You can go ahead and open that database and drop the original PDB1.
SQL> alter pluggable database pdb1_plug_move open;
Pluggable database altered.
SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1_PLUG_MOVE READ WRITE