Advertisements
Skip to content
September 25, 2015 / Shivananda Rao P

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:

 

1. Creating a PDB using Seed

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.

 

1. Creating a PDB using Seed:

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, 2013, 2014, 2015. 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: