Blog - comments

Hello David,I think Oracle is behaving differently with 12.1.0.2.x. At least in my case I was able t...
David D'Acquisto

Bravo, il fallait y penser Mery Christmas

Eric

Great !! Thank you very much.

SEK

Great !! Thank you very much.

SEK
Franck, thank you for the thorough explanation on diagnosing these types issues. It showed me exac...
TimL
Blog Yann Neuhaus Oracle Database 12c: PDBs not fully transportable yet

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Oracle Database 12c: PDBs not fully transportable yet

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://www.dbi-services.com/index.php/blog/entry/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.

Rate this blog entry:
2

Yann Neuhaus is Chief Executive Officer (CEO) and Region Manager at dbi services. He has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies, cluster computing and high availability solutions (Oracle Real Application Clusters RAC, Oracle Data Guard, and Oracle Clusterware). His expertise also includes Oracle Grid Control as well as several open source solutions (MySQL, Unix/Linux, etc.). Yann Neuhaus is Oracle Certified Expert (OCE) Cluster 11g, Oracle RAC Certified Expert, MySQL 5.0 DBA certified, and Red Hat Certified Engineer. Prior to joining dbi services, Yann Neuhaus was Principal Consultant at Trivadis in Basel, where he was also responsible for the delivery and quality management of large infrastructure projects. He also worked as IT Database / Network Administrator for IFS France. Yann Neuhaus holds an Engineer's Degree in Computer Science from the University of Technology of Belfort-Montbéliard (F) as well as a Master in Business Engineering of the EM Strasbourg Business School (F). His branch-related experience covers Financial Services / Banking, Chemicals & Pharmaceuticals, Transport & Logistics, Retail, Food, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Saturday, 20 December 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter