Create Pluggable Database(PDB) from Different Methods in Oracle 12c

Create Pluggable Database(PDB) from Different Methods in Oracle 12c

In this blog, we see different method of creating a PDB database and basic command to use them. In following example we try to cover first two method for creating PDB databases.

PDB Creating Methods
1. Creating PDB from scratch or seed database
2. Creating PDB from cloning a PDB from local or remotely . Remotely from pdb or non cdb
3. Creating PDB from relocation
4. Creating PDB from plugging in from unplug PDB or NON-CDB
5. Refresing as a proxy pdb

Following are clauses used for Cloning and moving in syntax of CREATE PLUGGABLE DATABASE:
Clauses:
Storage Limit: clause specifies that the storage used by all tablespaces in PDB. you can use unlimited as STORAGE (MAXSIZE UNLIMITED)
Default Tablespace: clause to separate the data for multiple schemas into different PDBs. like noncdb have multiple schema which transfer to PDB.
PATH_PREFIX: clause ensures that all directory object paths associated with the PDB are restricted to the specified directory or its subdirectories
FILE_NAME_CONVERT: clause is the names of the PDB files after the PDB is created.
CREATE_FILE_DEST: clause specifies the default Oracle Managed Files file system directory or Oracle ASM disk group for the PDB’s files.
DB_CREATE_FILE_DEST: specifies the default location for Oracle Managed Files for the CDB
PDB_FILE_NAME_CONVERT: initialization parameter maps names of existing files to new file names
SERVICE_NAME_CONVERT : clause used to convert the service name used for connectivity.

Creation of a PDB from a Seed
A seed PDB that act as a template for create an another PDB.

--Creating a PDB from SEED PDB
CREATE PLUGGABLE DATABASE pdb_salesdb ADMIN USER pdb_user1 IDENTIFIED BY password;

--Creating a PDB with role defined
CREATE PLUGGABLE DATABASE pdb_salesdb ADMIN USER pdb_user1 IDENTIFIED BY password ROLES=(DBA);

--Creating a PDB with all options
CREATE PLUGGABLE DATABASE pdb_salesdb
ADMIN USER pdb_user1 IDENTIFIED BY password
STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE sales
DATAFILE '/disk1/oracle/dbs/pdb_salesdb/sales01.dbf' SIZE 250M AUTOEXTEND ON
PATH_PREFIX = '/disk1/oracle/dbs/pdb_salesdb/'
FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/pdb_salesdb/');

Creation of a PDB by Cloning a PDB

-- cloning of PDB with same
CREATE PLUGGABLE DATABASE salespdb FROM hrpdb;

--cloning with all clauses
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
PATH_PREFIX = '/disk2/oracle/pdb2/'
FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')
SERVICE_NAME_CONVERT = ('salesrep_ca','salesrep_or','orders_ca','orders_or')
NOLOGGING;

--cloning with other clauses
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')
STORAGE (MAXSIZE 2G)
SERVICE_NAME_CONVERT = ('salesrep_ca','salesrep_or','orders_ca','orders_or');

--Clone with No Data Clause it will donot export data of tables
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA;

--Cloning from remote PDB means from another CDB of same version through db link.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;

--Creating a refreshable clone of PDB
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link REFRESH MODE EVERY 60 MINUTES;

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.