Oracle 12c Pluggable Database (PDB) feature hands-on experience

February 1, 2013 by Leave a comment 

I strongly recommend first reading my previous articles on this topic to understand the rest of the material:
Oracle 12c Pluggable Database is a fundamental architectural change
Oracle 12c Pluggable Database feature insights

As I wrote previously Pluggable Databases is a brand-new capability in Oracle Database 12c that will be released soon. In this article I continue giving more practical insights of an Oracle 12c Pluggable Database future including SQL statement examples. The SQL code I mention below is applicable to Oracle database 12.1 Beta2.

Let’s repeat the step of connecting to a Container database (CDB) called cdb1 from my last article.

sqlplus sys/pass@localhost:1521/cdb1 as sysdba

Create and open a new Oracle 12c Pluggable database (PDB)

Now we will create and open a new Pluggable database (PDB) called My_PDB.
Every CDB has a standard template of a PDB whose name is PDB$Seed. We actually create a new PDB by cloning this Seed. See below a SQL example :
create pluggable database My_PDB
admin user App_Admin identified by pass
file_name_convert = ('/pdbseed/', '/my_pdb/')

The file_name_convert clause specifies how names for the new files are derived from those of the seed using the same scheme as you know from RMAN. During the PDB creation Oracle copies just 2 database files for System and Sysaux tablespaces to the new location of our PDB. The rest of database files for undo, redo, and so on is global for the whole CDB and belongs to the special container called CDB$Root.

The admin user clause is mandatory. In an extended form, it lets you grant privileges and roles to the new user. This user is able to start a new session only within My_PDB.

On completion of a create pluggable database command, the new PDB will be in MOUNTED mode. Before you can start a session in the new PDB, it must be open. So, let’s open our PDB using below SQL :

alter pluggable database My_PDB open

Check Container Database (CDB) and Pluggable database (PDB) files

select con_id, tablespace_name, file_name
from cdb_data_files
where file_Name like '%/cdb1/pdbseed/%'
or file_Name like '%/cdb1/my_pdb/%'
order by 1, 2

CON_ID Tablespace_Name File_Name
------ ------------ -------------------------------------------------
2 SYSAUX /opt/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
2 SYSTEM /opt/oracle/oradata/cdb1/pdbseed/system01.dbf
3 SYSAUX /opt/oracle/oradata/cdb1/My_PDB/sysaux01.dbf
3 SYSTEM /opt/oracle/oradata/cdb1/My_PDB/system01.dbf

Open all Oracle 12c Pluggable Databases (PDB)

Each PDB within a CDB has its own Open_Mode and Restricted status in each instance of a RAC. The possible values Open_Mode are MOUNTED, READ ONLY, and READ WRITE; and the possible values for the Restricted status are YES and NO when the PDB is open and otherwise null.

Starting an instance (which opens the entire CDB) does not cause PDBs to be opened. The alter pluggable database statement is used to set the Open_Mode. You give in SQL either the name of a particular PDB or use the keyword all, thus:

alter pluggable database all open

Close all Oracle 12c Pluggable Databases (PDB) in CDB

This SQL statement closes all the PDBs in the CDB:

alter pluggable database all close

Clone of an existing Oracle 12c PDB in the same CDB

As a next step we will clone of an existing PDB in the same CDB. For that, you must close it and then open it in READ ONLY mode before you can clone it:

alter pluggable database My_PDB close
alter pluggable database My_PDB open read only
create pluggable database My_Clone
from My_PDB
file_name_convert = ('/my_pdb', '/my_clone')
alter pluggable database My_PDB close
alter pluggable database My_PDB open
alter pluggable database My_Clone open

Unplug Pluggable database (PDB) from Container Database (CDB)

As last step in this post, I’ll show you how to Unplug My_PDB from cdb1. The into keyword must be followed by the full path for a description of the PDB, in XML, generated by the operation:

alter pluggable database My_PDB close
alter pluggable database My_PDB
unplug into '/u01/app/oracle/oradata/cdb1/my_pdb/my_pdb.xml'

The my_pdb.xml  file specifies information like the names and the full paths of the datafiles. The information is used by the plug-in operation. Notice that PDB is still a part of the CDB from which it was unplugged, but it now has status UNPLUGGED.

The unplug operation actually makes some changes in the PDB’s datafiles to record, for example, that the PDB was properly and successfully unplugged. Because it is still part of the CDB, you can do an ad hoc RMAN backup of it. This provides a convenient way to archive the unplugged PDB.

Once you’ve backed it up, you then remove it from the CDB’s catalog — but, of course, you must preserve the datafiles for the subsequent plug-in operation.

drop pluggable database My_PDB keep datafiles

Read all atricles about Oracle 12 Pluggable database feature

Oracle 12c Pluggable Database is a fundamental architectural change
Oracle 12c Pluggable Database feature insights

Enjoyed this article? Please share it with others using the social site of your choice:

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email