Friday, September 13, 2013

Drop Pluggable Database

In this demo I show how to drop a pluggable database using DBCA or command line.


Environment

CDB- TESTDBS
PDB- TSTDB1 and TSTDB3

Drop Pluggable Database using Command Line

We will drop pluggable database TSTDB1 using command line.

1. Connect to container root and check current pluggable databases.
$sqlplus / as sysdba
SQL> show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;











2. Using RMAN do a report schema to see datafile location. You of course can also do this by selecting from the dictionary tables v$datafile and v$tempfile.
$rman target /
RMAN>report schema












4. Close the pluggable database you want to drop.
SQL>alter pluggable database tstdb1 close immediate;




5. Connected to the container drop the pluggable database.
$sqlplus / as sysdba
SQL>drop pluggable database tstdb1 including datafiles;

Since the option "including datafiles" was used we can see the the drop pluggable database command automatically removed all datafiles and tempfiles.















NOTE: It is not recommended to drop a pluggable database with out including the datafiles since it will require manually cleaning the datafiles using OS utilities which could lead to dropping the wrong datafiles.

You may have a requirement to move the pluggable database so that could be a reason to issue the drop pluggable database with out including the "including datafile" option.



Drop Pluggable Database using DBCA

We will drop pluggable database TSTDB3 using DBCA.

1. Connect to container root and check current pluggable databases.
$sqlplus / as sysdba
SQL> show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;











2. Execute DBCA
$ORACLE_HOME/bin/dbca

Select Manager Pluggable Databases click Next.


























3. Select Delete a Pluggable Database click Next.


























4. Select the container database from where you will drop the pluggable database and click Next.


5. Select the pluggable database you want to drop in the case I will select TSTDB3 and click Next.


























6. Review summary and click Finish.


7. The drop process will begin you can click the activity or alert log to review details of the drop process.


8. Once the pluggable database has been dropped click OK to close the DBCA session.

4 comments:

  1. Thanks for the post.

    I have a very unique issue. we dropped the pdb on primary server but not on standby.
    Now standby cdb says "redo apply stopped" and we cannot drop the pluggable database on standby side.
    when we use dbca , we delete it but the database again comes back.

    ReplyDelete
    Replies
    1. Most likely the issue with the standby is because the standby is not in an open state. Since the primary does not exist anymore try to put the standby into open mode then use DBCA to delete the PDB.

      Delete
  2. Thanks for the Post,

    We have a problem when we drop a pluggable database in Oracle for windows, a file lock is obtained on the underneat file for that pluggable. So we can't recreate the pluggable with the same name... only way is to restart the Oracle service.. is this a known issue and is there a workaround without restarting the service?

    Thanks

    ReplyDelete

About Me

My photo

Senior DBA with over 16 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).