How to create a pluggable database by unplugging an existing PDB and plugging it into another container

Tweet

So I want to create a new pluggable database into the CDBTEST container located into vsi08devpom machine (@vsi08devpom in this post) and I want the exact copy of PDB003 pluggable database currently contained into CDB001 located into my local machine (@localhost in this post).

Why not simply unplug it from my local machine and plug it into the target machine ?

When you want to disconnect a pluggable database from a multitenant container you can unplug it, specifying a file containing its metadata information formatted in XML, used eventually if you want to plug it in another container like we want to do.

To successfully unplug a pdb it must be put first in MOUNTED mode otherwise the following error is thrown:

SQL@localhost> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ WRITE
PDB002          READ WRITE
PDB003          READ WRITE

SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';
alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml'
*
ERROR at line 1:
ORA-65025: Pluggable database PDB003 is not closed on all instances.

So before proceeding with the unplug operation you have to close the pluggable database

SQL@localhost> alter pluggable database pdb003 close immediate;

Pluggable database altered.

SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';

Pluggable database altered.

Even if the previous operation is not destructive, the container knows that a pluggable database was available, currently the database administrator no longer want to use it, you of course cannot open an unplugged database.

SQL@localhost> select name, open_mode from V$PDBS;

NAME         OPEN_MODE
-------------   ----------
PDB$SEED READ ONLY
PDB001         READ WRITE
PDB002         READ WRITE
PDB003         MOUNTED

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

Where you can have information about the plugged or unplugged status of your pluggable databases ?
Query the CDB_PDBS view: it displays information about the PDBs associated with the CDB, including the status of each PDB.

SQL@localhost> select PDB_NAME, STATUS from CDB_PDBS where PDB_NAME = 'PDB003';

PDB_NAME   STATUS
---------- -------------
PDB003    UNPLUGGED

To create a new pluggable database on vsi08devpom machine I have to copy the datafiles of the unplugged database to the target machine.

[oracle@localhost PDB003]$ ll
total 921688
-rw-r-----. 1 oracle oinstall   5251072 Aug  1 14:37 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Aug  1 14:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Aug  1 14:37 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Aug  1 14:30 temp01.dbf
[oracle@localhost PDB003]$ scp -r ../PDB003 vsi08devpom.mydomain.it:/opt/app/oracle/oradata/CDBTEST/PDB003
oracle@vsi08devpom.mydomain.it's password: 
temp01.dbf                                               100%   20MB 620.9KB/s   00:33    
system01.dbf                                             100%  260MB 543.4KB/s   08:10    
PDB001_users01.dbf                                       100% 5128KB   1.0MB/s   00:05    
sysaux01.dbf                                             100%  635MB 545.1KB/s   19:53    
[oracle@localhost PDB003]$ scp /app/oracle/oradata/pdb003.xml vsi08devpom.mydomain.it:/opt/app/oracle/oradata
oracle@vsi08devpom.mydomain.it's password: 
pdb003.xml                                               100% 4003     3.9KB/s   00:00   

Log into the target machine.
My current multitenant container CDBTEST located on vsi08devpom is formed by three pluggable databases:

[oracle@vsi08devpom CDBTEST]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom CDBTEST]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 15:32:55 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@vsi08devpom> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE

SQL@vsi08devpom> select pdb_name, status from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDBTEST1   NORMAL
PDBTEST2   NORMAL
PDBTEST3   NORMAL

Now it's time to plug into CDBTEST container on vsi08devpom machine the unplugged database coming from CDB container (running on my local machine). The source location of the datafiles contained into the XML file is different from the target destination, so I cannot use just the following command, because the error "ORA-27041: unable to open file" is thrown as you can see.

SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse;
create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse
*
ERROR at line 1:
ORA-19505: failed to identify file
"/app/oracle/oradata/CDB001/PDB003/system01.dbf"
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9

I need to appropriately use the SOURCE_FILE_NAME_CONVERT clause in the command:

SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' source_file_name_convert=('/app/oracle/oradata/CDB001','/opt/app/oracle/oradata/CDBTEST') nocopy tempfile reuse;

Pluggable database created.

The new PDB003 pluggable database on vsi08devpom machine has the same DBID of that one...

SQL@vsi08devpom> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;

PDB_NAME   STATUS        DBID    CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
PDB$SEED   NORMAL  4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8
PDBTEST1   NORMAL  3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC
PDBTEST2   NORMAL  2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671
PDBTEST3   NORMAL  2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7
PDB003    NEW   1448206714 1448206714 E2B9BE56B8B936CEE045000000000001

... unplugged from my local machine

SQL@localhost> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;

PDB_NAME   STATUS        DBID    CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
PDB$SEED   NORMAL  4063489868 4063489868 E19363E52C005C9AE045000000000001
PDB001    NORMAL  1701599811 1701599811 E1F26215682E1142E045000000000001
PDB002    NORMAL  1497027100 1497027100 E1F329ECE0F411E6E045000000000001
PDB003    UNPLUGGED  1448206714 1448206714 E2B9BE56B8B936CEE045000000000001

The new plugged database is in MOUNTED mode so I need to open it first:

SQL@vsi08devpom> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          MOUNTED

SQL@vsi08devpom> alter pluggable database PDB003 open;

Pluggable database altered.

My data is all there as expected!!!

SQL@vsi08devpom> alter session set container=PDB003;

Session altered.

SQL@vsi08devpom> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

That's all.