Looking for something? Try here..

Wednesday, February 28, 2018

Dropping pluggable database

This is a small post on how to drop a pluggable database if you are on multi tenant architecture of the Oracle 12c database.
Commands are as below and the explanation is provided after the command executions.
[oracle@xxxx ~]$ dblogin

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 20 01:56:45 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Enter value for container:
old   1: alter session set container=&container
new   1: alter session set container=
alter session set container=
                           *
ERROR at line 1:
ORA-65015: missing or invalid container name


SQL> select name, TOTAL_MB, FREE_MB, CON_ID from  v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB     CON_ID
------------------------------ ---------- ---------- ----------
RECOD1                           59731200   53564384          0
DBFS_DG                           1030816    1012720          0
DATAD1                          209018880   99355460          0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  ..
  ..
        16 QADB26                         READ WRITE NO
        17 PTETFSA                        READ WRITE NO
        18 DVBSODB                        READ WRITE NO
  ..
  ..
  
SQL> alter pluggable database PTETFSA close;
^Calter pluggable database PTETFSA close
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL>  alter pluggable database PTETFSA close immediate;

Pluggable database altered.

SQL> DROP PLUGGABLE DATABASE ptetfsa INCLUDING DATAFILES;

Pluggable database dropped.

SQL> select name, TOTAL_MB, FREE_MB, CON_ID from  v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB     CON_ID
------------------------------ ---------- ---------- ----------
RECOD1                           59731200   53564384          0
DBFS_DG                           1030816    1012720          0
DATAD1                          209018880  101575024          0

SQL>

Explanations are as below.

Line 1: dblogin is an alias used in my profile file defined as below.
alias dblogin='sqlplus sys/***** as sysdba @set_con'

set_con is a sql script with the following line
alter session set container=&container;

You don't pass a pdb name, the login defaults to root container.

Line 22: Checking for space information in the ASM diskgroup

Line 48: Close the pluggable database on both the instances as this is a RAC setup

Line 52: INCLUDING DATAFILES clause is required to delete any files associated with the pluggable database. The default is KEEP DATAFILES

Reference: Click here

Happy dropping!!