One of the benefits of the new Oracle multi-tenant database option is to create a kind of cloned environment through simple statements. This post will focus on the creation of pluggable database through a database link. The reason of this topic is that I am currently developing an advanced RMAN cloning procedure for 11g and this new 12c feature might make our life much easier in the future release. The second reason of choosing this topic is that I was confronted with some issues during my beta tests, so I would like to verify again if everything works now.

Multi-tenant database overview

CDB_layout

It is not the scope of this post to explain the multi-tenant database concept, it will be presented on lots of other dedicated posts. Furthermore it has been largely introduced by Oracle on several conferences even before the 12c launch.

Just to give a brief overview, multi-tenant databases (pluggable databases) allow to consolidate several databases (applications) under one single “container database” (CDB). This allows to isolate applications in separate databases (containers) having their own tablespaces, users, dictionary but sharing the same instance, the same redo log files and other shared components. We benefit from better isolation than schema consolidation, indeed pluggable databases can be easily moved, restored, aso … without affecting other pluggable databases. The current Oracle 12c release has however some limits and issues, see (http://dbi-services.com/blog/oracle-12c-pluggable-databases-not-that-isolated/). Since the PDBs share the same memory and process structures the CPU and memory consumption will be reduced allowing to run several (pluggable) databases (PDBs) on the same server (compared to the classical database/instance architecture).

There are several ways to create a pluggable database :

  • Copy from a seed (integrated template)
  • Copy from an existing PDB (local or remote)
  • By unplugging/plugging a pluggable database from one Container Database to another

The exhibit below resumes these possibilities :

PDB_creation_tree

To be complete it is also important to mention that PDBs can also be duplicated with RMAN.

PDB creation through a DB link

This post will focus on the creation of a pluggable database (PDB) through a database link. The goal might be to “transport” a PDB from one Container Database (CDB) to another, eventually located on a different physical server.

For this purpose, some conditions must be full filled :

  • Remote (Source) DB must be opened in read-only mode
  • Link must exist to the remote CDB
  • Current user must have the “CREATE PLUGGABLE DATABASE” privilege on the destination CDB as the user used by the DB link
  • Source and destination CDB must have:
    • The same endianness
    • Compatible database options installed
    • Compatible (national) character sets

As stated previously I was confronted with some issues while using this feature during the beta tests and Oracle opened and worked on several bugs. Let’s check if it works now 🙂 ?

First of all, create the source PDB (PDB1) on the source container (CDB121) :

SQL> CREATE PLUGGABLE DATABASE PDB1
  2        ADMIN USER pdb1_admin IDENTIFIED BY manager
  3        DEFAULT TABLESPACE APPLICATION_DATA
  4        DATAFILE '/u01/oradata/PDB1/application_data_01.dbf'
  5        SIZE 100M AUTOEXTEND ON NEXT 32M
  6        PATH_PREFIX = '/u01/oradata/PDB1'
  7        FILE_NAME_CONVERT = ('/u01/oradata/CDB121/pdbseed',
  8        '/u01/oradata/PDB1');
Pluggable database created.

 

We are currently connected to the CDB :

SQL> show con_idCON_ID
 ------------------------------
 1
 
SQL> SELECT CON_ID,DBID,NAME,OPEN_MODE FROM V$containers;
CON_ID     DBID       NAME                   OPEN_MODE
 ---------- ---------- ---------------------- ----------
 1 3781127169     CDB$ROOT               READ WRITE
 2 4063553988     PDB$SEED               READ ONLY
 3 3328657606     PDB1                   MOUNTED

 

We swich to the created PDB (PDB1) :

SQL> alter session set container = PDB1;
Session altered.

 

We open it (in READ ONLY as stated by the documentation) :

SQL> alter pluggable database open read only;
Pluggable database altered.
 
SQL> SELECT CON_ID,DBID,NAME,OPEN_MODE FROM V$containers;
    CON_ID       DBID NAME                  OPEN_MODE
 ---------- ---------- --------------------- ----------
 3 3328657606 PDB1                  READ ONLY
1 row selected.

 

On the destination container (NCDB121), create a database link to the source container (CDB121) :

SQL> CREATE PUBLIC DATABASE LINK CDB121
 2  CONNECT TO SYSTEM IDENTIFIED BY manager
 3  USING 'CDB121.IT.DBI-SERVICES.COM'
 4  /
Database link created.

 

Now create the NPDB1 PDB in NCDB121 through the database link. Of course we have to change the path of the NPDB1 datafiles, they will be located under “/u01/oradata/NPDB1” instead of “/u01/oradata/PDB1”, this is supposed to be managed by the FILE_NAME_CONVERT parameter :

SQL> CREATE PLUGGABLE DATABASE NPDB1
 2 FROM PDB1@CDB121
 3 FILE_NAME_CONVERT = ('PDB1','NPDB1')
 4/
FROM PDB1@CDB121
 *
 ERROR at line 2:
 ORA-17628: Oracle error 19505 returned by remote Oracle server
 ORA-19505: failed to identify file ""

 

As you can see, unfortunately it still fails, Oracle is still investigating this issue. The same tests failed also with the following values for FILE_NAME_CONVERT : (‘/u01/oradata/PDB1′,’/u01/oradata/NPDB1’). We are still in contact with Oracle on this topic and hope we will be able to quickly provide successful results about this feature.

This feature has also been tested with the session parameter PDB_FILE_NAME_CONVERT without success.