We can clone a new pdb into the same location(where source pdb's datafile resides) or into a different location than the source pdb database. Here I used OMF for configuring my
CDB database.In this demonstration I am considering two case,
1.Clone a pdb from
the local pdb into the same mount point/disk group as that of source PDB.
[oracle@mydb01]:[TDREC] $ . oraenv
ORACLE_SID = [TDREC] ? TDCDB
The Oracle base remains unchanged with value
/u01/app/oracle
[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12
19:10:13 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Real Application Clusters, Oracle
Label Security, OLAP, Advanced Analytics and Real Application Testing options
SQL> select NAME,CON_ID,OPEN_MODE from v$containers;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
CDB$ROOT 1 READ WRITE
PDB$SEED 2 READ ONLY
PDB1 3 READ
WRITE
PDB2 4 READ
WRITE
SQL>
I will create one user in my source PDB.
SQL> alter session set container=PDB2;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> create user mahi identified by mahi;
User created.
SQL> grant dba to mahi;
Grant succeeded.
SQL> conn mahi/mahi
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> exit
Basically you can’t use connect clause within a PDB. So I will connect to
PDB2 using tns entry and will create one table on it.
[oracle@mydb01]:[TDCDB] $ sqlplus mahi/mahi@PDB2
SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12
19:18:48 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Real Application Clusters, OLAP,
Advanced Analytics and Real Application Testing options
SQL> create table ta (id number );
Table created.
SQL> insert into ta values (11);
1 row created.
SQL> insert into ta values (12);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ta;
ID
----------
11
12
SQL> alter session set container=cdb$root;
ERROR:
ORA-01031: insufficient privileges
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12
19:20:00 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Real Application Clusters, Oracle
Label Security, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter pluggable database
pdb2 close immediate;
Pluggable database altered.
SQL> select
NAME,CON_ID,OPEN_MODE from v$containers;
NAME
CON_ID OPEN_MODE
------------------------------ ---------- ----------
CDB$ROOT 1 READ WRITE
PDB$SEED 2 READ ONLY
PDB1 3 READ
WRITE
PDB2 4 MOUNTED
SQL> alter pluggable database
pdb2 open read only;
Pluggable database altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- --------
db_create_file_dest string /data3
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> create pluggable database
pdb2_clone from pdb2;
Pluggable database created.
SQL> alter pluggable database
pdb2_clone open;
Pluggable database altered.
SQL> set lines 222
col file_name for a81
col con_name for a15
select a.name "file_name",a.CON_ID,b.NAME
"con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order
by a.CON_ID;
file_name CON_ID
con_name
---------------------------------------------------------------------------------
---------- ---------------
/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1
CDB$ROOT
/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1
CDB$ROOT
/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1
CDB$ROOT
/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1
CDB$ROOT
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1
/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2
/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2
/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_system_czvwqlcv_.dbf 5 PDB2_CLONE
/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_sysaux_czvwqld1_.dbf 5 PDB2_CLONE
13 rows selected.
You could see both datafiles are created within the
mountpont(/data3) as that of my source PDB. Login to the new pluggable database
and verify the user and tables.
SQL> alter session set container=PDB2_CLONE;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB2_CLONE
SQL> sho user;
USER is "SYS"
SQL> select * from mahi.ta;
ID
----------
11
12
SQL> select NAME,CON_ID,OPEN_MODE from v$containers;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB2_CLONE 5 READ WRITE
Lets open our source database(PDB2) back to normal read-write
mode.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select NAME,CON_ID,OPEN_MODE from v$containers;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
CDB$ROOT 1 READ WRITE
PDB$SEED
2 READ ONLY
PDB1 3 READ
WRITE
PDB2 4 READ ONLY
PDB2_CLONE 5 READ WRITE
SQL> alter pluggable database pdb2 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL> select
NAME,CON_ID,OPEN_MODE from v$containers;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
CDB$ROOT 1 READ WRITE
PDB$SEED 2 READ ONLY
PDB1 3 READ
WRITE
PDB2 4 READ
WRITE
PDB2_CLONE 5 READ WRITE
2.Clone a PDB from
the local PDB into a different mountpoint/diskgroup than the source PDB.
Set the new OMF path from root container – note that new
OMF path sould be set from the root container cdb$root, and not from the source
pdb database.
[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12
19:45:47 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Real Application Clusters, Oracle
Label Security, OLAP,
Advanced Analytics and Real Application Testing options
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter create ;
NAME TYPE VALUE
------------------------------------ -----------
-----------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string /data3
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_file_dest='/data1';
System altered.
SQL> show parameter create ;
NAME TYPE
VALUE
------------------------------------ -----------
-----------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string /data1
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 open read only;
Pluggable database altered.
SQL> select
NAME,CON_ID,OPEN_MODE from v$containers;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
CDB$ROOT 1 READ WRITE
PDB$SEED 2 READ ONLY
PDB1 3 READ ONLY
PDB2 4 READ
WRITE
PDB2_CLONE 5 READ WRITE
SQL> create pluggable database pdb1_clone from pdb1;
Pluggable database created.
SQL> alter pluggable database pdb1_clone open;
Pluggable database altered.
SQL> set lines 222
col file_name for a81
col con_name for a15
select a.name "file_name",a.CON_ID,b.NAME
"con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order
by a.CON_ID;
file_name
CON_ID con_name
---------------------------------------------------------------------------------
---------- ---------------
/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1
CDB$ROOT
/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1
CDB$ROOT
/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1
CDB$ROOT
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1
/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2
/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2
file_name
CON_ID con_name
---------------------------------------------------------------------------------
---------- ---------------
/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_system_czvwqlcv_.dbf 5 PDB2_CLONE
/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_sysaux_czvwqld1_.dbf 5 PDB2_CLONE
/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_system_czvy5pjw_.dbf 6 PDB1_CLONE
/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_sysaux_czvy5pjy_.dbf 6 PDB1_CLONE
/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_users_czvy5pk0_.dbf 6 PDB1_CLONE
16 rows selected.
You could see the datafiles of new PDB created within /data1 mount point instead of /data3 mount point.
Lets drop all
pluggable database that we cloned.
[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12
19:49:44 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Real Application Clusters, Oracle
Label Security, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> alter pluggable database pdb2_clone close
immediate;
Pluggable database altered.
SQL> drop pluggable database pdb2_clone including
datafiles;
Pluggable database dropped.
SQL> alter pluggable database pdb1_clone close
immediate;
Pluggable database altered.
SQL> drop pluggable database pdb1_clone including
datafiles;
Pluggable database dropped.
SQL> select NAME,CON_ID,OPEN_MODE from v$containers;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
CDB$ROOT 1 READ WRITE
PDB$SEED 2 READ ONLY
PDB1 3 READ
WRITE
PDB2 4 READ
WRITE
SQL>
No comments:
Post a Comment