Wednesday, August 7, 2013

How to unplug a PDB and plug it back into the same CDB

You have just unplugged a PDB and want to know how to eventually plug it back into the same container.
Let's assume I have a pluggable database named PDB003 and I am just able to unplug it using the following commands:
SQL> alter pluggable database pdb003 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';

Pluggable database altered.
If you query the CDB_PDBS view you can receive useful information about all PDBs contained: id, name, unique identifiers, SCN at the time of creation and the status.
The STATUS column has several values:
  • NEW, the pdb has been created but never opened 
  • NORMAL, pdb available to be used 
  • UNPLUGGED, the pdb has been unplugged 
  • NEEDS UPGRADE, the pdb must be upgraded 
  • CONVERTING, a non-CDB is going to be converted into a pdb 
  • UNUSABLE, the pdb cannot be used (creation in progress) 
In my case the PDB003 pluggable database is in an UNPLUGGED status as expected:
SQL> select * from CDB_PDBS;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063489868 4063489868 E19363E52C005C9AE045000000000001 NORMAL      233   1
  3 PDB001     1701599811 1701599811 E1F26215682E1142E045000000000001 NORMAL  1821405   1
  4 PDB002     1497027100 1497027100 E1F329ECE0F411E6E045000000000001 NORMAL  1844390   1
  5 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 UNPLUGGED  1997215   1
To plug again the pluggable database PDB003 inside the original container (CDB001) you cannot use a syntax like: ALTER PLUGGABLE DATABASE ... PLUG (it doesn't exist in the "SQL Language Reference" manual a PLUG clause) ...
SQL> alter pluggable database PDB003 plug;
alter pluggable database PDB003 plug
                                *
ERROR at line 1:
ORA-00922: missing or invalid option
... neither you can simply use CREATE PLUGGABLE DATABASE ... USING ...
SQL> create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse;
create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse
*
ERROR at line 1:
ORA-65012: Pluggable database PDB003 already exists.
You need first to drop the pluggable database (DROP PLUGGABLE DATABASE command) and then create it again (CREATE PLUGGABLE DATABASE command).
In this way the pluggable database is plugged again into the same CDB and using the same PDB name.
Have a look at the following commands:
SQL> drop pluggable database PDB003;

Pluggable database dropped.

SQL> create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy;               

Pluggable database created.

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS  
---------- ------------- 
PDB$SEED   NORMAL 
PDB001     NORMAL 
PDB002     NORMAL 
PDB003     NEW  

SQL> alter session set container=PDB003;

Session altered.

SQL> alter database open;

Database altered.

SQL> select count(*) from marcov.T1;

  COUNT(*)
----------
       100
Have a look again at the CDB_PDBS view and note the differences compared with the previous results.
All the unique identifiers of PDB003 have been recreated.
SQL> select * from CDB_PDBS;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063489868 4063489868 E19363E52C005C9AE045000000000001 NORMAL      233   1
  3 PDB001     1701599811 1701599811 E1F26215682E1142E045000000000001 NORMAL  1821405   1
  4 PDB002     1497027100 1497027100 E1F329ECE0F411E6E045000000000001 NORMAL  1844390   1
  5 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 NORMAL  3110422   1

That's all.

4 comments:

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose