Sunday, June 30, 2013

Oracle 12c New Features - Plugging and Unplugging Databases

In a previous blog post I demonstrated creating Pluggable Databases (PDB) in an Oracle 12c Container Database (CDB).  In this test I will demonstrate how easy it is to unplug a PDB from a CDB, and then plugin again.

We currently have one PDB with name PDB1.  We will shutdown, unplug it, and drop it.

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
PDB1

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1 keep datafiles;

Pluggable database dropped.

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED

We can now backup the database to tape for later restore, or copy the datafiles and xml file for the pluggable database to another CDB on another server and plugin.  In this example I will just plug the database back into the original CDB.  

Before we plugin we first need to run DBMS_PDB.CHECK_PLUG_COMPATIBILITY to check that the PDB is compatible with the new CDB.

SQL> set serveroutput on
SQL> DECLARE
   compatible BOOLEAN := FALSE;
  2    3  BEGIN
  4     compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  5          pdb_descr_file => '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml');
  6     if compatible then
  7        DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? YES');
  8     else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? NO');
  9     end if;
 10  END;
 11  /
Is pluggable PDB2 compatible? YES

PL/SQL procedure successfully completed.

As the PDB is compatible with the CDB we can proceed to plug it in. 

SQL> create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

SQL> alter session set container=PDB1;

Session altered.

SQL> alter database open;

Database altered.

SQL> connect test/test@pdb1;
Connected.


SQL> show con_name



CON_NAME

------------------------------
PDB1

We are now able to login to the plugged in database.

The Alert log entries for these operations are as follows :-

Mon Jul 01 11:14:31 2013
alter pluggable database pdb1 close immediate
Mon Jul 01 11:14:31 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close immediate
alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml'
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Completed: alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml'
drop pluggable database pdb1 keep datafiles
Mon Jul 01 11:15:02 2013
Deleted file /u01/app/oracle/oradata/T12/pdb1/pdbseed_temp01.dbf
Completed: drop pluggable database pdb1 keep datafiles
create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE
Mon Jul 01 11:20:45 2013
****************************************************************
Pluggable Database PDB1 with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#10 from file$
Deleting old file#11 from file$
Adding new file#12 to file$(old file#10)
Adding new file#13 to file$(old file#11)
Successfully created internal service pdb1 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************

Post plug operations are now complete.
Pluggable database PDB1 with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE
Mon Jul 01 11:29:00 2013
alter database open
Mon Jul 01 11:29:00 2013
Pluggable database PDB1 dictionary check beginning
Pluggable Database PDB1 Dictionary check complete
Opening pdb PDB1 (3) with no Resource Manager plan active

XDB installed.

XDB initialized.
Pluggable database PDB1 opened read write
Completed: alter database open