Oracle 12c Pluggable Database – Plug Unplug and Clone
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
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
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
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
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
- 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:
alter database open read only;
DBMS_PDB.Describe(PDB_Descr_File => ‘/u01/app/oracle/oradata/noncdb/noncdb.xml’);
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
source_file_name_convert = none
file_name_convert = ('/noncdb/', '/cdb1/exnoncdb/')
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;
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.