Tuesday, July 9, 2013

Pluggable Database Part (3)

Since Pluggable Database is New Topic to post about , I Cannot post Everything in One Post so i decide to do it as tutorial and this one part (3) , every Topics uploaded Here on my blog or on my Account on Shareslide here.

In This topic :

  • How to unplug database.
  • Plugging Database to another Container Database.

Let's Start :

SQL> select name, con_id from v$active_services ;

NAME          CON_ID
--------------------------------- ----------
new2   4
new   3

db12cXDB   1
db12c   1
SYS$BACKGROUND   1
SYS$USERS   1
as you see i already create two pluggable database new,new2 and now :

SQL> alter pluggable database new close immediate ;
Pluggable database altered.
SQL> alter pluggable database new2 close immediate ;

Pluggable database altered.
Unplug database included with xml file :

SQL> alter pluggable database new unplug into '/u01/app/oracle/oradata/new.xml';Pluggable database altered.

SQL> alter pluggable database new2 unplug into '/u01/app/oracle/oradata/new_2.xml';Pluggable database altered.
Drop Database :

SQL> drop pluggable database new keep datafiles ;Pluggable database dropped.

SQL> drop pluggable database new2 keep datafiles ;Pluggable database dropped.
make sure you drop database :

SQL> select pdb_name, status from cdb_pdbs ;

PDB_NAME STATUS
----------- -------------
PDB$SEED NORMAL

 Before Plug Database to any Container you need to make sure from compatibility , there's PL/SQL code written by Oracle to check compatibility.

SQL > DECLARE
   compatible BOOLEAN := FALSE;
BEGIN
   compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
        pdb_descr_file => '/u01/app/oracle/oradata/new.xml');
   if compatible then
      DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
   else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
   end if;
END;
/
Is pluggable PDB1 compatible? YES
PL/SQL procedure successfully completed.
 The Same will be for new2.xml

Now Let's Create Database Using With Two Way :

SQL> create pluggable database newdb_plug using '/u01/app/oracle/oradata/new.xml' nocopy tempfile reuse ;
Pluggable database created.

SQL> select pdb_name, status from cdb_pdbs; 
PDB_NAME STATUS
------------------ -------------
NEWDB_PLUG NEW
PDB$SEED NORMAL 

 Another method to plug database :

SQL> create pluggable database new_plug_copy using '/u01/app/oracle/oradata/new_2.xml'
  2  copy
  3  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/New2','/u01/app/oracle/oradata/new_plug_copy');
Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
NEWDB_PLUG         MOUNTED
NEW_PLUG_COPY MOUNTED
 Therefore 
  • Copy Clause : 
    • if you want the files listed in the XML file to be copied to the new location and used for the new PDB.
  • Nocopy Clause
    • if you want the files for the PDB to remain in their current locations.

But what if i want to move all datafiles and create new pluggable database,oracle 12c provide you with new clause to do this which is "move" check below :

SQL> create pluggable database new_plug_move using '/u01/app/oracle/oradata/new_2.xml'
  2  move
  3  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/New2','/u01/app/oracle/oradata/move');
create pluggable database new_plug_move using '/u01/app/oracle/oradata/new_2.xml'
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.


The above error is normal because i already create  pluggable database using new_2.xml to solve it

SQL> create pluggable database new_plug_move as clone using '/u01/app/oracle/oradata/new_2.xml'
  2  move
  3  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/New2','/u01/app/oracle/oradata/move');
Pluggable database created.
SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME STATUS
------------------- -------------
NEWDB_PLUG NEW
PDB$SEED NORMAL
NEW_PLUG_COPY NEW
NEW_PLUG_MOVE NEW

You can open any database now and work on them.
This article uploaded to Slide Share here.

Reference
1-Oracle Documentation here


Thank you
Osama Mustafa

No comments:

Post a Comment