Cloning an existing local PDB is similar to creating a new PDB from the seed PDB, except now we are using non-seed PDB as the source, which we have to identify using the FROM clause. Make sure the source PDB is open in READ ONLY mode.
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ ONLY;
CREATE PLUGGABLE DATABASE pdb4 FROM pdb3
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb4/');
ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;
-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
-----------------------------------------------------------------------------------------------------------
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
view plainprint?
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
Here are my current pluggable databases and I want to clone PDB001 which contains the user MARCOV and the table T1 (with 100 rows)
view plainprint?
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$ROOT
Here is the content of my tnsnames.ora file
view plainprint?
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
view plainprint?
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.
view plainprint?
[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.dbf
Here is the content of PDB001 directory.
view plainprint?
[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.dbf
I'm going to create a new pluggable database called PDB003, cloning PDB001. The command to clone a pluggable database locally is the following:
view plainprint?
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.
view plainprint?
[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.dbf
After the command is sucessfully completed the status and the open mode of the new pluggable database are NEW and MOUNTED.
view plainprint?
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 MOUNTED
A new service is created too.
view plainprint?
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 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)
view plainprint?
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 NORMAL
Add the following entry on the tnsnames.ora file.
view plainprint?
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
view plainprint?
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:
view plainprint?
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.
==================================================================
No comments:
Post a Comment