We have gone through features of 12c Multitenant database.
Now we will have discussion on create pluggable databases (PDB). There are
different ways to create pluggable database
We will discuss on below methods
1. Using SEED pdb
2. Using XML file
3. By Cloning
a. Local PDB
b. Remote PDB
Using
seed PDB PDB$SEED
Below are
steps to create PDB using PDB$SEED, first Check the PDB's available in
container database
SQL> show pdbs
CON_ID CON_NAME
OPEN MODE
RESTRICTED
---------- ------------------------------ ----------
----------
2 PDB$SEED
READ
ONLY NO
3 PDB_1
READ WRITE NO
Now
create PDB using below command, note that we have provided options to create
admin user, default tablespaces and roles. FILE_NAME_CONVERT is required in
case mount point or diskgroups are different, for understanding purpose we used
in below example though diskgroup is same.
SQL>
create pluggable database pdb_techon admin user techon_admin identified by
techon#123 roles = (DBA)
default
tablespace techon_tbs datafile '+DATA01' size 100M
FILE_NAME_CONVERT=('+DATA01','+DATA01');
2 3
Pluggable
database created.
We can
see PDB is default created in MOUNTED state. we need to open the same with
alter pluggable database command as shown below
SQL>
show pdbs
CON_ID CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED
READ ONLY NO
3 PDB_1
READ WRITE NO
4 PDB_TECHON
MOUNTED
SQL>
SQL>
alter pluggable database PDB_TECHON open;
Pluggable
database altered.
SQL>
show pdbs
CON_ID CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED
READ ONLY NO
3 PDB_1
READ WRITE NO
4 PDB_TECHON
READ WRITE NO
We can check datafile created for tablespace techon_tbs
SQL> select name
from v$datafile where name like '%tech%';
NAME
---------------------------------------------------------------
+DATA01/DEV12C1/TECHON_PDB/DATAFILE/techon_tbs.363.994818875
Using
XML file
For carrying out this
activity, I have created .xml file of our previous PDB, techno_pdb and dropped
it using keep datafile option and generated xml file for the same
SQL>
show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3 PDB_1
READ WRITE NO
4
PDB_TECHON
READ WRITE NO
SQL>
alter pluggable database PDB_TECHON close;
Pluggable
database altered.
SQL>
show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3 PDB_1
READ WRITE NO
4
PDB_TECHON
MOUNTED
SQL>
alter pluggable database PDB_TECHON unplug into
'/u01/pdb_backup/pdb_techon.xml';
Pluggable
database altered.
SQL>
drop pluggable database PDB_TECHON keep datafiles;
Pluggable
database dropped.
SQL>
show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3 PDB_1
READ WRITE NO
SQL>
Now lets see command to
create PDB from xml file, as we have kept datafiles as it is during drop no
file_name_convert or transfer of any datafile is required
SQL>
Create pluggable database PDB_TECHON using '/u01/pdb_backup/pdb_techon.xml’;
Pluggable
database created.
SQL>
show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED READ
ONLY NO
3
PDB_1 READ
WRITE NO
4
PDB_TECHON MOUNTED
SQL>
SQL>
alter pluggable database PDB_TECHON open;
Pluggable
database altered.
SQL>
show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED READ
ONLY NO
3
PDB_1
READ WRITE NO
4
PDB_TECHON READ
WRITE NO
Using clone from local or remote PDB
Using Local PDB
Now we will discuss to clone PDB from
local PDB, Below are the steps discussed,
Open source PDB in read only mode
using below method
SQL> show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED READ
ONLY NO
3
PDB_1 READ WRITE
NO
4
PDB_TECHON READ WRITE
NO
SQL> alter pluggable database PDB_TECHON close;
Pluggable database altered.
SQL> alter pluggable database PDB_TECHON open
read only;
Pluggable database altered.
SQL> show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED READ ONLY NO
3
PDB_1 READ WRITE
NO
4
PDB_TECHON READ
ONLY NO
Below are commands to create PDB from local PDB, we have created PDB techon_new and open both PDB's
SQL>
create pluggable database techon_new from PDB_TECHON FILE_NAME_CONVERT=(
'+DATA01', '+DATA01');
Pluggable
database created.
SQL>
show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED READ
ONLY NO
3
PDB_1 READ
WRITE NO
4
PDB_TECHON READ
ONLY NO
5
TECHON_NEW MOUNTED
SQL>
alter pluggable database PDB_TECHON close;
Pluggable
database altered.
SQL>
alter pluggable database PDB_TECHON open;
Pluggable
database altered.
SQL>
alter pluggable database TECHON_NEW open;
Pluggable
database altered.
SQL>
show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED READ
ONLY NO
3
PDB_1
READ
WRITE NO
4
PDB_TECHON READ
WRITE NO
5
TECHON_NEW READ
WRITE NO
Using Remote PDB
For cloning remote follow the steps mentioned here Clone Remote PDB using DB link
You can also read (12c Features )
0 comments:
Post a Comment