Creating Pluggable Databases – Part I
This article speaks about the different ways of creating Pluggable databases in 12c. Well, there are many ways to create a PDB of which some are listed:
Here, a PDB is created in a CDB using the files of the CDB. The datafiles of the seed (System and Sysaux) will be used by copying them to the location of the datafiles that would be hosted by the PDB.
2. Creating a PDB from another PDB or non-CDB (Cloning a PDB or non-CDB):
This method allows you to create a PDB by copying the files from another PDB or from another non-CDB. This process is called as “Cloning an existing PDB or non-CDB”. The PDB from which you would be cloning can be a PDB in the local CDB or a PDB in a remote CDB.
3. Creating a PDB by plugging in a an unplugged PDB:
Here, the PDB will be created by plugging an unplugged PDB. The unplugged PDB can be from any other CDB. This technique uses an XML file which describes the metadata of the unplugged PDB.
4. Creating PDB from a non-CDB:
This allows us to create a PDB by moving the non-CDB as a PDB of an existing CDB. This process uses the method of describing the non-CDB into an XML file using the DBMS_PDB package and then plugging this into a CDB based on the xml file details.
Let’s kick off with the creation of PDBS using each of these techniques.
Environment:
Hostname: ora12c-1 CDB Name: oracdb Database version: 12.1.0.1
Here I’m creating a PDB named “orapdb2” using the SEED. Before creating the PDB, make sure the directory to hold the datafiles of the new PDB is created.
[oracle@ora12c-1 ~]$ mkdir -p /u01/app/oracle/oradata/oracdb/orapdb2
Now, as connected to the ROOT container, run the “CREATE PLUGGABLE DATABASE” command. You’ll have to provide the local administrator name for this PDB with the create statement.
SQL> select status,instance_name from v$database,v$instance; STATUS INSTANCE_NAME ------------ ---------------- OPEN oracdb SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
SQL> create pluggable database orapdb2 admin user orapdb2dba identified by oracle
2 file_name_convert=('/u01/app/oracle/oradata/oracdb/pdbseed/','/u01/app/oracle/oradata/oracdb/orapdb2/');
Pluggable database created.
The FILE_NAME_CONVERT clause is used to specify the datafile locations of the SEED and the new PDB.
The newly created PDB will be in mounted state and hsa to be opened in READ WRITE mode.
SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------- ---------------- ---------- 2 PDB$SEED READ ONLY 3 ORAPDB1 READ WRITE 4 ORAPDB2 MOUNTED
SQL> alter pluggable database orapdb2 open; Pluggable database altered.
SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------- -------------- 2 PDB$SEED READ ONLY 3 ORAPDB1 READ WRITE 4 ORAPDB2 READ WRITE
You can use some of the below relevant clauses in the CREATE PLUGGABLE DATABASE statement.
STORAGE – you can specify the storage maximum value that each of the tablespaces of this newly created PDB will have.
DEFAULT TABLESPACE – you can create a tablespace and assign it as the default permanent tablespace of the PDB.
ROLES – if there are any predefined oracle roles, then those can be assigned to the PDB_DBA role locally to that PDB.
An example on creating pluggable database using other options.
SQL> create pluggable database orapdb2 admin user orapdb2dba identified by oracle
2 storage (MAXSIZE 2G)
3 default tablespace myts
4 datafile '/u01/app/oracle/oradata/oracdb/orapdb2/myts01.dbf'
5 file_name_convert=('/u01/app/oracle/oradata/oracdb/pdbseed/','/u01/app/oracle/oradata/oracdb/orapdb2/');
Pluggable database created.
Now let’s move on to the 2nd option of creating a PDB by cloning.
2. Creating a PDB from another PDB or non-CDB (Cloning a PDB or non-CDB):
As said above, a PDB can be cloned from local CDB or from remote CDB. First let’s check out cloning a PDB from a local CDB.
2.1 Clone a local PDB :
Environment:
Hostname: ora12c-1 CDB Name: oracdb Database version: 12.1.0.1 Source PDB : orapdb1 Target PDB : orapdb3
Let’s get the details of the datafiles which orapdb1 is using.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select name,con_id,open_mode from v$pdbs; NAME CON_ID OPEN_MODE ---------- ----------- --------------- PDB$SEED 2 READ ONLY ORAPDB1 3 READ WRITE ORAPDB2 4 READ WRITE
SQL> alter session set container=orapdb1; Session altered. SQL> select file_name from cdb_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/oracdb/orapdb1/system01.dbf /u01/app/oracle/oradata/oracdb/orapdb1/sysaux01.dbf /u01/app/oracle/oradata/oracdb/orapdb1/SAMPLE_SCHEMA_users01.dbf /u01/app/oracle/oradata/oracdb/orapdb1/example01.dbf
Create the necessary directory for the datafiles of the to be created PDB (orapdb3)
[oracle@ora12c-1 ~]$ mkdir -p /u01/app/oracle/oradata/oracdb/orapdb3
Now, login to the root container and clone the pdb orapdb1. But before proceeding, the source PDB should be in READ ONLY mode.
Here’s is a snippet.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter pluggable database orapdb1 close immediate; Pluggable database altered. SQL> alter pluggable database orapdb1 open READ ONLY; Pluggable database altered.
If you proceed without opening the Source PDB in READ ONLY mode, then oracle would throw the below error.
SQL> create pluggable database orapdb3 from orapdb1
2 file_name_convert=('/u01/app/oracle/oradata/oracdb/orapdb1/','/u01/app/oracle/oradata/oracdb/orapdb3');
create pluggable database orapdb3 from orapdb1
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode
Once the source PDB is opened in read only mode, proceed with it’s cloning by making use of “CREATE PLUGGABLE DATABASE FROM” statement.
SQL> create pluggable database orapdb3 from orapdb1
2 file_name_convert=('/u01/app/oracle/oradata/oracdb/orapdb1/','/u01/app/oracle/oradata/oracdb/orapdb3/');
Pluggable database created.
As explained earlier, the clause file_name_convert is used to map the location of the datafiles of source and the target PDBs. Also, you can use STORAGE clause which was explainer earlier.
Open the newly created PDB. Also, do remember to open the source PDB back in READ WRITE mode.
SQL> alter pluggable database orapdb3 open; Pluggable database altered. SQL> alter pluggable database orapdb1 close immediate; Pluggable database altered. SQL> alter pluggable database orapdb1 open; Pluggable database altered.
The list of PDBs now that are plugged into CDB oracdb.
SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID ---------- --------------- ---------- PDB$SEED READ ONLY 2 ORAPDB1 READ WRITE 3 ORAPDB2 READ WRITE 4 ORAPDB3 READ WRITE 5
You can query the list of datafiles for the newly created PDB.
SQL> alter session set container=orapdb3; Session altered. SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/oracdb/orapdb3/system01.dbf /u01/app/oracle/oradata/oracdb/orapdb3/sysaux01.dbf /u01/app/oracle/oradata/oracdb/orapdb3/SAMPLE_SCHEMA_users01.dbf /u01/app/oracle/oradata/oracdb/orapdb3/example01.dbf
2.2 Clone a remote PDB (PDB that is plugged in another CDB) or from a non-CDB
This feature is supported from 12.1.0.2 and not on 12.1.0.1. Trying to use this on 12.1.0.1 would hit the bug 15931910
Snippet on the error that Oracle would throw if a remote PDB cloning is tired out 12.1.0.1.
SQL> create pluggable database orapdb5 from testpdb1@testlink
2 file_name_convert=('/u01/app/oracle/oradata/oraprim/testpdb1/','/u01/app/oracle/oradata/oracdb/orapdb5/');
create pluggable database orapdb5 from testpdb1@testlink
*
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
SQL> select sysdate from dual@testlink;
SYSDATE
---------
23-SEP-15
A separate post on the remote cloning of PDB in 12.1.0.2 would be coming up soon on this site.
The remaining two methods of creating PDB shall be described in my next article “Creating Pluggable Databases – Part 2”
See you there !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 
Leave a Reply