Visit Counter

Thursday, November 14, 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 PDB1 and I am just able to unplug it using the following commands: 


Container Database:   CDB1
Pluggable Databases: PDB1 , PDB2


$ ORACLE_HOME='/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin
$ export ORACLE_HOME

$ ORACLE_SID=cdb1
$ export ORACLE_SID


SQL>  alter pluggable database pdb1 close immediate; 

Pluggable database altered.

SQL> alter pluggable database PDB1 unplug into '/ora/oracle/app/oracle/oradata/cdb1/pdb1.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 PDB1 pluggable database is in an UNPLUGGED status as expected: 


SQL> select * from CDB_PDBS;  

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 PDB2     1701599811 1701599811 E1F26215682E1142E045000000000001 NORMAL  1821405   1  

  4 PDB1     1497027100 1497027100 E1F329ECE0F411E6E045000000000001 UNPLUGGED  1844390   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 pdb1 plug;
alter plugable database pdb1 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
 '/ora/oracle/app/oracle/oradata/cdb1/pdb1.xml' nocopy tempfile  reuse;

SQL> create pluggable database PDB003 using
          '/ora/oracle/app/oracle/oradata/cdb1/pdb1.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 PDB1 using 
           '/ora/oracle/app/oracle/oradata/cdb1/pdb1.xml' nocopy;                   

Pluggable database created.    

SQL> select PDB_NAME, STATUS from CDB_PDBS;    

PDB_NAME   STATUS   
---------- -------------   
PDB$SEED   NORMAL   
PDB2       NORMAL   
PDB1       NEW    
  
SQL> alter session set container=PDB1;   

Session altered.    

SQL> alter database open;   

Database altered.    

SQL> select count(*) from emp.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. 



Open another terminal & run the command.


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 PDB1     1701599811 1701599811 E1F26215682E1142E045000000000001   NORMAL  1821405   1    4 PDB2     1497027100 1497027100 E1F329ECE0F411E6E045000000000001   NORMAL  1844390   1    




No comments:

Post a Comment