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$ROOTHere are my current pluggable databases and I want to clone PDB001 which contains the user MARCOV and the table T1 (with 100 rows)
SQL> select name, open_mode from V$PDBS; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB001 READ WRITE PDB002 READ WRITE SQL> alter session set container=PDB001; Session altered. SQL> create user marcov identified by marcov quota unlimited on users; User created. SQL> grant create session to marcov; Grant succeeded. SQL> grant create table to marcov; Grant succeeded. SQL> connect marcov/marcov@PDB001 Connected. SQL> show user USER is "MARCOV" SQL> show con_name CON_NAME ------------------------------ PDB001 SQL> create table T1 (a number); Table created. SQL> create table T1 (a number); Table created. SQL> insert into T1 select level from dual connect by level < 101; 100 rows created. SQL> commit; Commit complete. SQL> connect / as sysdba Connected. SQL> show user USER is "SYS" SQL> show con_name CON_NAME ------------------------------ CDB$ROOTHere is the content of my tnsnames.ora file
CDB001 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDB001)
)
)
PDB001 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB001)
)
)
To clone the source pluggable database PDB001 I need to open it in READ ONLY mode
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB001 READ WRITE PDB002 READ WRITE SQL> alter pluggable database PDB001 open read only force; 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 PDB001 drwxr-x---. 2 oracle oinstall 4096 Jul 20 16:22 PDB002 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.dbfHere is the content of PDB001 directory.
[oracle@localhost CDB001]$ ll PDB001/ total 922516 -rw-r-----. 1 oracle oinstall 5251072 Jul 30 13:08 PDB001_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.dbfI'm going to create a new pluggable database called PDB003, cloning PDB001. The command to clone a pluggable database locally is the following:
SQL> create pluggable database PDB003 from PDB001 file_name_convert=('/app/oracle/oradata/CDB001/PDB001','/app/oracle/oradata/CDB001/PDB003');
Pluggable database created.
New files and directories are created. Note also the same name of the datafile PDB001_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 PDB001 drwxr-x---. 2 oracle oinstall 4096 Jul 20 16:22 PDB002 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 PDB001_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.dbfAfter 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 PDB001 NORMAL PDB002 NORMAL PDB003 NEW SQL> select name, open_mode from V$PDBS; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB001 READ ONLY PDB002 READ WRITE PDB003 MOUNTEDA 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 CDB001 CDB$ROOT SYS$USERS CDB$ROOT pdb001 PDB001 pdb002 PDB002 pdb003 PDB003You 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 PDB001,PDB003 open READ WRITE FORCE; Pluggable database altered. SQL> select name, open_mode from V$PDBS; NAME OPEN_MODE -------------------- ---------- PDB$SEED READ ONLY PDB001 READ WRITE PDB002 READ WRITE PDB003 READ WRITE SQL> select PDB_NAME, STATUS from CDB_PDBS; PDB_NAME STATUS ---------- ------------- PDB$SEED NORMAL PDB001 NORMAL PDB002 NORMAL PDB003 NORMALAdd the following entry on the tnsnames.ora file.
PDB003 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB003)
)
)
Let's see my data on PDB003 and on PDB001
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@PDB001
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB001
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.
That's all.
