In 12c
container database we can unplug a PDB from its root container and can plug
it back into the same/different CDB according to the requirement.In case if you
are pluging into a differnet CDB on a
remote machine you may use SOURCE_FILE_NAME_CONVERT
parameter while creating the PDB. This time I’ll show how to plug a Pluggable
Database (PDB) into another Container Database (CDB) within the same host.
In this
demonstration PDB1 is my source PDB within the CDB – TDCDB, I will plug this
PDB into a different CDB named MYCDB1 within the same machine.
Login
to source PDB and create some ojects
[oracle@mydb01]:[TDCDB]
$ sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Oct 18 15:55:46 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>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 TAPDBCLONE READ WRITE NO
SQL>
alter session set container=PDB1;
Session
altered.
SQL>
show con_name
CON_NAME
------------------------------
PDB1
SQL>
create user mahi identified by mahi;
User
created.
SQL>
grant dba to mahi;
Grant
succeeded.
SQL>
!hostname
mydb01
SQL>
conn mahi/mahi@mydb01:1521/PDB1
Connected.
SQL>
show user;
USER is
"MAHI"
SQL>
create table t1 as select * from dba_users;
Table
created.
SQL>
create table t2 as select * from dba_tables;
Table
created.
SQL>
select count(1) from t1;
COUNT(1)
----------
40
SQL>
select count(1) from t2;
COUNT(1)
----------
2339
SQL>
select NAME,CON_ID from v$datafile where
CON_ID=3;
NAME CON_ID
----------------------------------------------------------------------------------------
----------
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf
3
SQL>
exit
Disconnected
from 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
Close and unplug the source PDB
Before
attempting to unplug a PDB, you must make sure it is closed. To unplug the
database use the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause
to specify the location of the XML metadata file.
[oracle@mydb01]:[TDCDB]
$ sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Oct 18 15:55:46 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>
alter pluggable database pdb1 close immediate;
Pluggable
database altered.
SQL>shopdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 READ WRITE NO
5 TAPDBCLONE READ WRITE NO
SQL>alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';
Pluggable
database altered.
SQL> selectpdb_name, status from cdb_pdbs;
PDB_NAME STATUS
---------------
---------
PDB$SEED NORMAL
PDB1 UNPLUGGED
PDB2 NORMAL
TAPDBCLONE NORMAL
You
could see the location of datafiles , tablespace name, configured database
component etc from the xml file.
[oracle@mydb01]:[TDCDB]
$ grep dbf /tmp/pdb1.xml
<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf</path>
<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf</path>
<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_temp_cz9kdysy_.dbf</path>
<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf</path>
[oracle@mydb01]:[TDCDB]
$ grep option /tmp/pdb1.xml
<optional>
<options>
<option>APS=12.1.0.2.0</option>
<option>CATALOG=12.1.0.2.0</option>
<option>CATJAVA=12.1.0.2.0</option>
<option>CATPROC=12.1.0.2.0</option>
<option>CONTEXT=12.1.0.2.0</option>
<option>DV=12.1.0.2.0</option>
<option>JAVAVM=12.1.0.2.0</option>
<option>OLS=12.1.0.2.0</option>
<option>ORDIM=12.1.0.2.0</option>
<option>OWM=12.1.0.2.0</option>
<option>RAC=12.1.0.2.0</option>
<option>SDO=12.1.0.2.0</option>
<option>XDB=12.1.0.2.0</option>
<option>XML=12.1.0.2.0</option>
<option>XOQ=12.1.0.2.0</option>
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]
$
Checking the Compatibility of the
Unplugged PDB with the Host CDB
First
check the PBD is compatible with the target CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in
the XML metadata file and the name of the PDB you want to create using it.
[oracle@mydb01]:[TDCDB]
$ .oraenv
ORACLE_SID
= [TDCDB] ? MYCDB1
The
Oracle base remains unchanged with value /u01/app/oracle
[oracle@mydb01]:[MYCDB1]
$ sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Mon Oct 17 18:47:15 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>
SET SERVEROUTPUT ON
DECLARE
compatible
CONSTANT VARCHAR2(3) :=
CASE
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file
=>'/tmp/pdb1.xml')
WHEN
TRUE THEN 'YES'
ELSE
'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/SQL> 2
3 4 5
6 7 8
9 10 11
YES
PL/SQL
procedure successfully completed.
SQL>
If you get an output ‘NO’, then PDB is not
compatible and check the view PDB_PLUG_IN_VIOLATIONS for
Errors.
Pre-requisites
for plugin to a remote CDB
·
The
Target CDB must have the same endianness as source CDB.
·
The
CDB must have the same set of options installed.
·
The
source CDB and the target CDB must have compatible character sets and national
character sets.
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TAPDB READ WRITE NO
SQL>
Here
I am using OMF, so I don’t have to use file_name_convert clause while creating
new PDB. If you want to configure a new OMF path for PDBs while creating PDBs use
the create_file_dest parameter,other wise it will inherit
the value(of OMF) from the root container.
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>
SQL>create pluggable database pdb1_tdcdb using '/tmp/pdb1.xml'
copy ;
Pluggable
database created.
SQL>
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TAPDB READ WRITE NO
4 PDB1_TDCDB MOUNTED
SQL>
alter pluggable database PDB1_TDCDB open;
Pluggable
database altered.
SQL>
alter pluggable database PDB1_TDCDB save state;
Pluggable
database altered.
SQL> showpdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TAPDB READ WRITE NO
4 PDB1_TDCDB READ WRITE NO
SQL>
SQL>
alter session set container=PDB1_TDCDB;
Session
altered.
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
4 PDB1_TDCDB READ WRITE NO
SQL>
Verify
the data that we created on the source PDB
SQL>
!hostname
mydb01
SQL>
conn mahi/mahi@mydb01:1521/PDB1_TDCDB
Connected.
SQL>sho
user;
USER is
"MAHI"
SQL> select count(1) from t1;
COUNT(1)
----------
40
SQL>
select count(1) from t2;
COUNT(1)
----------
2339
SQL>
exit
Disconnected
from 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
We can
use different methods while cloning-
COPY: Specify COPY if you want the files listed in the XML file to
be copied to the new location and used for the new PDB. This is the default and
should be useful if you are cloning the new PDB in the same host.
NOCOPY: If you do not want Oracle to copy the files and would like to
have the current location of data files of source PDB as the location for the
new PDB, then make use of this option. Normally we may use this option after
droping a pdb while keeping its datafiles like,
SQL>drop pluggable database pdb1 keep datafiles;
MOVE: Specify MOVE if you want the files listed in the XML file to
be moved, rather than copied, to the new location and used for the new PDB.
FILE_NAME_CONVERT=When using a copy clause, you need
to specify the source and destination file location and this can be done using
the FILE_NAME_CONVERT clause.
AS CLONE:Specify this clause only if the target CDB already contains a
PDB that was created using the same set of data files. The source files remain
as an unplugged PDB and can be used again. Specifying AS CLONE also ensures
that Oracle Database generates new identifiers, such as DBID and GUID, for the
new PDB.
SOURCE_FILE_NAME_CONVERT: If the location of the datafiles
in the XML are different from that what you have for the source files, then
this caluse is to be used. Let’s say that you have the source PDB on host1 and
it’sdatafiles are at location “/u01/sourcePDB/” and the same is recorded in the
XML file. On the target server, you copy these files manually to say
“/u03/targetPDB”. But when you use the XML file to create the PDB, the location
of the files in the XML do not match or are not accurate as the files still
point to location “/u01/sourcePDB/”. In such cases, you can use the
SOURCE_FILE_NAME_CONVERT clause which is used mainly for the source PDB files
if the XML file does not describe the exact current location of the source PDB
files.For example,
SQL> create pluggable database CLONEPDB
using '/tmp/pdb1.xml’
source_file_name_convert=('/u01/sourcePDB’,'/u03/targetPDB')
move;
AS CLONE Clause
Specify this clause only if the target CDB already contains a
PDB that was created using the same set of data files. The source files remain
as an unplugged PDB and can be used again. Specifying
AS CLONE also ensures that Oracle Database generates new
identifiers, such as DBID and GUID, for the new PDB. Let me demonstrate this
with an example.
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TAPDB READ WRITE NO
4 PDB1_TDCDB READ WRITE NO
SQL>
SQL>
create pluggable database pdb2_tdcdb using '/tmp/pdb1.xml' copy ;
create
pluggable database pdb2_tdcdb using '/tmp/pdb1.xml' copy
*
ERROR at
line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an
existing
container.
SQL>
create pluggable database pdb2_tdcdb as clone using '/tmp/pdb1.xml' copy ;
Pluggable
database created.
SQL> showpdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TAPDB READ WRITE NO
4 PDB1_TDCDB READ WRITE NO
5 PDB2_TDCDB MOUNTED
SQL>
alter pluggable database PDB2_TDCDB open;
Pluggable
database altered.
SQL>
alter pluggable database PDB2_TDCDB save state;
Pluggable
database altered.
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TAPDB READ WRITE NO
4 PDB1_TDCDB READ WRITE NO
5 PDB2_TDCDB READ WRITE NO
SQL>
