How to create a pluggable database by cloning an existing local PDB

Using the CREATE PLUGGABLE DATABASE ... FROM command you can clone an existing pluggable database (the source pdb) to create a new pdb (the clone pdb).
The source pdb could be in the current local container or it can be located in a remote container (in a next post): during a clone of a remote pdb you need to use a database link referencing the remote container in the FROM clause.

Let's start cloning a local pluggable database. Be sure the current container is the root

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

Here are my current pluggable databases and I want to clone PROSPDB1

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PROSPDB1          READ WRITE
PROSPDB2          READ WRITE

SQL> alter session set container=PROSPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PROSPDB1

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Here is the content of my tnsnames.ora file

CDB$ROOT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.146.187)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB001)
    )
  )

PROSPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.146.187)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROSPDB1)
    )
  )

To clone the source pluggable database PROSPDB1 I need to open it in READ ONLY mode

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PROSPDB1          READ WRITE
PROSPDB2          READ WRITE

SQL> alter pluggable database PROSPDB1 close immediate;

Pluggable database altered.


SQL> alter pluggable database PROSPDB1 open read only;

Pluggable database altered.

On my file system I have the following directories and files.

[oracle@localhost CDB001]$ pwd
/app/oracle/oradata/CDB001
[oracle@localhost CDB001]$ ll
total 2163224
-rw-r-----. 1 oracle oinstall  17973248 Jul 30 13:12 control01.ctl
drwxr-x---. 2 oracle oinstall      4096 Jul 20 15:29 PROSPDB1
drwxr-x---. 2 oracle oinstall      4096 Jul 20 16:22 PROSPDB2
drwxr-x---. 2 oracle oinstall      4096 Jul 15 22:07 pdbseed
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 13:12 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 29 22:06 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 02:32 redo03.log
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:11 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:06 system01.dbf
-rw-r-----. 1 oracle oinstall  76554240 Jul 30 13:11 temp01.dbf
-rw-r-----. 1 oracle oinstall 325066752 Jul 30 13:10 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 02:37 users01.dbf

Here is the content of PROSPDB1 directory.

[oracle@localhost CDB001]$ ll PROSPDB1/
total 922516
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 13:08 PROSPDB1_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Jul 30 13:08 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Jul 30 13:08 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jul 21 17:51 temp01.dbf

###########################################################################################################
to create a new pluggable database called PDB003, cloning PROSPDB1. The command to clone a pluggable database locally is the following:

SQL> create pluggable database PDB003 from PROSPDB1 file_name_convert=('/app/oracle/oradata/CDB001/PROSPDB1','/app/oracle/oradata/CDB001/PDB003');

Pluggable database created.
###########################################################################################################


###########################################################################################################
If files are in different directory use below method.

SQL>Alter system set DB_FILE_CREATE_DEST='/oradata2/PROSPDB2';
SQL>create pluggable database PDB003 from PROSPDB1;
Pluggable database created.
###########################################################################################################

New files and directories are created. Note also the same name of the datafile PROSPDB1_users01.dbf used as default tablespace for PDB003.

[oracle@localhost CDB001]$ ll
total 2163228
-rw-r-----. 1 oracle oinstall  17973248 Jul 30 13:19 control01.ctl
drwxr-x---. 2 oracle oinstall      4096 Jul 20 15:29 PROSPDB1
drwxr-x---. 2 oracle oinstall      4096 Jul 20 16:22 PROSPDB2
drwxr-x---. 2 oracle oinstall      4096 Jul 30 13:17 PDB003
drwxr-x---. 2 oracle oinstall      4096 Jul 15 22:07 pdbseed
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 13:18 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 29 22:06 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 02:32 redo03.log
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:18 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:17 system01.dbf
-rw-r-----. 1 oracle oinstall  76554240 Jul 30 13:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 325066752 Jul 30 13:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 02:37 users01.dbf
[oracle@localhost CDB001]$ ll PDB003
total 921688
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 13:18 PROSPDB1_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Jul 30 13:18 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Jul 30 13:18 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jul 30 13:17 temp01.dbf

After the command is sucessfully completed the status and the open mode of the new pluggable database are NEW and MOUNTED.

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PROSPDB1    NORMAL
PROSPDB2    NORMAL
PDB003    NEW

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PROSPDB1          READ ONLY
PROSPDB2          READ WRITE
PDB003          MOUNTED

A new service is created too.

SQL> select name, pdb from V$SERVICES order by creation_date;

NAME       PDB
-------------------- ------------------------------
CDB001XDB      CDB$ROOT
SYS$BACKGROUND      CDB$ROOT
CDB$ROOT       CDB$ROOT
SYS$USERS      CDB$ROOT
PROSPDB1       PROSPDB1
PROSPDB2       PROSPDB2
pdb003       PDB003

You can now open both pluggable databases with one simply command (have a look at this post for more information about the syntax and examples)

SQL> alter pluggable database PROSPDB1,PDB003 open READ WRITE FORCE;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME       OPEN_MODE
-------------------- ----------
PDB$SEED      READ ONLY
PROSPDB1       READ WRITE
PROSPDB2       READ WRITE
PDB003       READ WRITE

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PROSPDB1    NORMAL
PROSPDB2    NORMAL
PDB003    NORMAL

Add the following entry on the tnsnames.ora file.

PDB003 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.146.187)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB003)
    )
  )

Let's see my data on PDB003 and on PROSPDB1

SQL> connect marcov/marcov@PDB003
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB003
SQL> select count(*) from marcov.T1;

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

SQL> connect marcov/marcov@PROSPDB1
Connected.
SQL> show con_name

CON_NAME
------------------------------
PROSPDB1
SQL> select count(*) from marcov.T1;

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

It is not possible to create a new pdb cloning a local or a remote seed: to create a new pdb from the seed you have to follow this post. If you try to clone from the seed template you will receive the ORA-65000 error, described below:

SQL> create pluggable database PDB004 from PDB$SEED file_name_convert=('/app/oracle/oradata/CDB001/pdbseed','/app/oracle/oradata/CDB001/PDB004');
create pluggable database PDB004 from PDB$SEED file_name_convert=('/app/oracle/oradata/CDB001/pdbseed','/app/oracle/oradata/CDB001/PDB004')
                          *
ERROR at line 1:
ORA-65000: missing or invalid pluggable database name

[oracle@localhost pdbseed]$ oerr ora 65000
65000, 00000, "missing or invalid pluggable database name"
// *Cause:  A valid pluggable database name was not present where required
//     by the syntax of a CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE
//          DATABASE or DROP PLUGGABLE DATABASE statement.
// *Action: Reissue the statement with a valid pluggable database name.