Oracle 12c Pluggable Database – Plug Unplug and Clone

February 25, 2013 by 2 Comments 

My presentation of Oracle 12c Pluggable Database feature at DOAG

My presentation of Oracle 12c Pluggable Database feature at DOAG

Last week I gave a presentation called “12c Pluggable Database Feature Insights”at the German Oracle User Group (DOAG) regional meeting in Nuremberg. Not only the presentation itself but also a later discussion was very interesting and valuable. I encourage my nearby German colleagues joining DOAG and visiting regular Regional DOAG meetings in Nuremberg.

Let’s continue my series of articles on Oracle 12c Pluggable Database feature including SQL code.  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
Oracle 12c Pluggable Database (PDB) feature hands-on experience

This time I’ll show how to plug a Pluggable Database (PDB) into another Container Database (CDB), how to clone PDBs and how to adopt non-CDB as a PDB into a pre-existing CDB. To start with, I will use a Pluggable Database MY_PDB which I unplugged in my previous article and will plug it into another Oracle 12c Container database called cdb2.

Plug My_PDB into cdb2

1. Connect to target Container Database in my case cdb2 located in /u01/app/oracle/oradata/cdb2

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

2. Then, make sure that the to-be-plugged-in PDB is compatible with the new host Container Database

exec DBMS_PDB.Check_Plug_Compatibility(
PDB_Descr_File =>'/u01/app/oracle/oradata/cdb1/my_pdb/my_pdb.xml')

If case of incompatibility issues the procedure will throw an error

3. Now plug it in. The using SQL keyword must be followed by the full path for the PDB manifest, a.k.a. the .xml file, generated by the previous unplug operation.

create pluggable database My_PDB
using '/u01/app/oracle/oradata/cdb1/my_pdb/my_pdb.xml'
move
file_name_convert = ('/cdb1/', '/cdb2/')
/
alter pluggable database My_PDB open
/

Create a PDB as a clone of an unplugged PDB

This example suggests the idea of maintaining a collection of PDB “gold images”, as unplugged PDBs. There are many use cases, for example:

• in a development shop to allow developers and testers rapidly, and repeatedly, to provision a well-known starting state

• to support self-paced learning

• to provide a new way to deliver a brand-new application

To model this, imagine you have already unplugged PDB containing the Sample Schemas, placed on a suitable directory and set to read-only (or less!).
create pluggable database Sample_Schemas_1 as clone
using
'/u01/app/oracle/oradata/gold_pdbs/sample_schemas/sample_schemas.xml'
copy
file_name_convert =
('/gold_pdbs/sample_schemas/', '/cdb1/sample_schemas_1/')
/
alter pluggable database Sample_Schemas_1 open
/

The as clone SQL clause ensures that the new PDB gets a proper globally unique identifier.
After that you can see your GUIDs:

select PDB_Name, GUID
from DBA_PDBs
order by Creation_scn
/

Note: The DBA_PDBs.GUID travels with a PDB as it is unplugged from one CDB and later plugged into another. The server code can enforced uniqueness within a CDB, but not across CDBs.

Adopting a non-CDB as a PDB into a pre-existing CDB

I’ll show you here how you can establish your pre-12.1 database as a PDB. Overall you have several methods to accomplish that:

– Transportable tablespaces/datapump
– Replication
– Upgrade the source non-CDB to 12c and plug it into a 12c CDB

Since first two methods are the standard once, I’ll only describe the last one below.

Notice that there is no single upgrade step that takes you there from here. It’s a two-stage operation: first you upgrade your existing database into a 12.1 non-CDB; then you adopt the non-CDB as a PDB into a pre-existing CDB — simply by plugging it and then doing a single post-plug-in step.

1. As a first step do the upgrade of your pre-12.1 DB to 12c version.

2. Second step, connected to noncdb, is to generate the manifest file, just as you do as part of unplugging a PDB:

shutdown immediate
startup mount

alter database open read only;

begin
DBMS_PDB.Describe(PDB_Descr_File => ‘/u01/app/oracle/oradata/noncdb/noncdb.xml’);
end;
/

shutdown immediate

3. The next step is to connect to the adopting CDB, cdb2 and to plug in the datafiles of noncdb using its manifest file.

create pluggable database ExNonCDB
as clone
using '/u01/app/oracle/oradata/noncdb/noncdb.xml'
source_file_name_convert = none
copy
file_name_convert = ('/noncdb/', '/cdb1/exnoncdb/')
storage unlimited
/

4. Now open it, to finalize the plug-in, close it, and re-open it, setting its Restricted status to YES:

alter pluggable database ExNonCDB open;
alter pluggable database ExNonCDB close;
alter pluggable database ExNonCDB open restricted;

5. Finally, run an Oracle-supplied SQL*Plus script to remove data from what will now be a local data dictionary, because, in the new regime, the metadata that defines the Oracle-system is, held once for the entire CDB.

alter session set container = ExNonCDB;
@?/rdbms/admin/noncdb_to_pdb.sql

6. As a last step, open the newly-adopted, former non-CDB

alter pluggable database ExNonCDB open;

That’s it for now for Oracle 12c Pluggable Database feature… and I expect Oracle database 12c to be released already in March this year.

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

Comments

2 Responses to “Oracle 12c Pluggable Database – Plug Unplug and Clone”
  1. Suppose you have Container Database, Cont_1, which has pluggable database, my_pbd. Following your steps, I am able to disconnect (unplug) from Cont_1. What is the command to plug it back to Container Database, Cont_1

  2. Kirill Loifman says:

    This can be a command you are looking for:
    create pluggable database pdb_plug_nocopy using ‘/u01/app/oracle/oradata/pdb1.xml’
    NOCOPY TEMPFILE REUSE;

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