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/icacdb/icapdb2

$sqlplus
Enter User:/ as sysdba
SQL>

Give the following command

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

Cloning an Existing Pluggable Database

Let us clone the local pluggable database icapdb1 to icapdb3.

First, create the directory to hold icapdb3 datafiles

$mkdir /u02/oracle/icacdb/icapdb3

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 icapdb1 close;

Pluggable database altered.

SQL> alter pluggable database icapdb1 open read only;

Pluggable database altered.

Now give the following command

SQL>CREATE PLUGGABLE DATABASE icapdb3 FROM icapdb1
FILE_NAME_CONVERT = ('/u02/oracle/icacdb/icapdb1/',
'/u02/oracle/icacdb/icapdb3/')
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 icapdb3 in ICACDB database it’s files are located in ‘/u02/oracle/icacdb/icapdb3’ directory.

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

Step 1:- Connect to ICACDB database

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

Step2:- Close the pluggable database icapdb3

SQL> alter pluggable database icapdb3 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 icapdb3 unplug into '/u02/oracle/icapdb3.xml';

Pluggable database altered.

Step 4:- Create target directory

$mkdir /u01/oracle/icapdb_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 icapdb_moved
USING '/u02/oracle/icapdb3.xml' MOVE
FILE_NAME_CONVERT = ('/u02/oracle/icacdb/icapdb3/',
'/u01/oracle/icapdb_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      ICAPDB_MOVED                    MOUNTED

Step 8:- Open the pluggable database ICAPDB_MOVED

SQL> alter pluggable database icapdb_moved open;

Pluggable database altered.