Creating and Managing Pluggable Databases

Creating and Managing Pluggable Databases using SQL Statements

We can create pluggable databases by either one of the following methods

1. Creating Pluggable Database from Seed database
2. Cloning an Existing Pluggable Database
3. Unplugging and Plugging a database from one CDB to another CDB

Creating Pluggable Database from Seed.

Let’s create a pluggable database icapdb2. We will place the datafiles in the /u02/oracle/icacdb/icapdb2 directory

Create the directory

$mkdir /u02/oracle/cdb1/pdb1

$sqlplus
Enter User:/ as sysdba
SQL>

Give the following command

SQL>CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1adm IDENTIFIED BY tiger
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE users
DATAFILE '/u02/oracle/cdb1/pdb1/users01.dbf'
SIZE 250M AUTOEXTEND ON
FILE_NAME_CONVERT=('/u02/oracle/cdb1/pdbseed/','/u02/oracle/cdb1/pdb1/')

Cloning an Existing Pluggable Database

Let us clone the local pluggable database pdb1 to  pdb3.

First, create the directory to hold icapdb3 datafiles

$mkdir /u02/oracle/cdb1/pdb3

Start SQL Plus and connect to root

$ sqlplus
Enter Username: / as sysdba
SQL>

First we need to close the source pluggable database icapdb1 and open it in Read Only mode.

SQL> alter pluggable database  pdb1 close;

Pluggable database altered.

SQL> alter pluggable database  pdb1 open read only;

Pluggable database altered.

Now give the following command

SQL>CREATE PLUGGABLE DATABASE  pdb3 FROM  pdb1
FILE_NAME_CONVERT = ('/u02/oracle/cdb1/pdb1/',
'/u02/oracle/cdb/pdb3/')
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

Pluggable database created.

Unplugging and Plugging a database from one CDB to another CDB

Let’s us see an example of unplugging a database from CDB and plugging it into another CDB.

Let’s assume we have a Pluggable database pdb3 in ICACDB database it’s files are located in ‘/u02/oracle/cdb1/pdb3’ directory.

Now we want to unplug this database from cdb1 database and plug it into another CDB whose SID is ‘orcl’ .
We want to move the files from ‘/u02/oracle/cdb1/pdb3’ to ‘/u01/oracle/pdb_moved’ directory.

Step 1:- Connect to ICACDB database

$ export ORACLE_SID=cdb1
$ sqlplus
Enter Username: / as sysdba
SQL>

Step2:- Close the pluggable database icapdb3

SQL> alter pluggable database  pdb3 close;

Pluggable database altered.

Step 3:- Unplug the database by specifying XML file. This XML file will be used to  plug this database into the target CDB

SQL> alter pluggable database  pdb3 unplug into '/u02/oracle/pdb3.xml';

Pluggable database altered.

Step 4:- Create target directory

$mkdir /u01/oracle/pdb_moved

Step 5:- Connect to Target CDB ‘orcl’

$ export ORACLE_SID=orcl
$ sqlplus
Enter User: / as sysdba

Step 6:- Start the target CDB

SQL> startup

Step 7:- Give the following command to plug the database

 CREATE PLUGGABLE DATABASE pdb_moved
USING '/u02/oracle/pdb3.xml' MOVE
FILE_NAME_CONVERT = ('/u02/oracle/cdb1/pdb3/',
'/u01/oracle/pdb_moved/')
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE RESTRICTED
------ ------------------------------ ---------- ----------
2      PDB$SEED                        READ ONLY NO
3      MYSALESPDB                      MOUNTED
4      PDB_MOVED                       MOUNTED

Step 8:- Open the pluggable database ICAPDB_MOVED

SQL> alter pluggable database pdb_moved open;

Pluggable database altered.