How to Drop Pluggable Database Manually

Contents


1. Environment
2. Pre-requisites
3. Backup PDB Before Drop
4. Close Pluggable database
5. Drop Pluggable database
6. Verify



1. Environment

Platform   : Linuxx86_64
Server Name: TESTHOST
DB Version : Oracle 12.2.0.1.0, File system: Normal
CDB Name   : CONCDB
PDB Name   : ORAPDB
Oracle Home: /u01/app/oracle/product/12.2.0.1


2. Pre-requisites

[oracle@TESTHOST]$ . oraenv
ORACLE_SID = [oracle] ? CONCDB
The Oracle base has been set to /u01/app/oracle
[oracle@TESTHOST]$
[oracle@TESTHOST]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 12 09:37:14 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> col name for a20
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         2 PDB$SEED             READ ONLY
         3 ORAPDB                 READ WRITE  <----

SQL>
SQL> col file_name for a50
SQL> set lines 180
SQL> select con_id,FILE_NAME,TABLESPACE_NAME from cdb_data_files where con_id=3;

    CON_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
         3 /u01/app/oracle/oradata/CONCDB/ORAPDB/system01.dbf     SYSTEM
         3 /u01/app/oracle/oradata/CONCDB/ORAPDB/sysaux01.dbf     SYSAUX
         3 /u01/app/oracle/oradata/CONCDB/ORAPDB/undotbs01.dbf    UNDOTBS1
         3 /u01/app/oracle/oradata/CONCDB/ORAPDB/users01.dbf      USERS

SQL>

SQL> select CON_ID,FILE_NAME,TABLESPACE_NAME from cdb_temp_files  where con_id=3;

    CON_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
         3 /u01/app/oracle/oradata/CONCDB/ORAPDB/temp01.dbf       TEMP

SQL>


3. Take backup before Drop

[oracle@TESTHOST]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 12 10:06:02 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CONCDB (DBID=931007404) <----

RMAN> BACKUP PLUGGABLE DATABASE ORAPDB TAG 'ORAPDB_BACKUP_BEFORE_DROP';

Starting backup at 12-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/undotbs01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-18
channel ORA_DISK_1: finished piece 1 at 12-JUL-18
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/01t7qack_1_1 tag=ORAPDB_BACKUP_BEFORE_DROP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 12-JUL-18

Starting Control File and SPFILE Autobackup at 12-JUL-18
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-931007404-20180712-00 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-18

RMAN>

4. Close PDB

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         2 PDB$SEED             READ ONLY
         3 ORAPDB                 READ WRITE <----

SQL>
SQL> alter pluggable database ORAPDB close immediate;  

Pluggable database altered.

SQL>
SQL> col name for a20
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         2 PDB$SEED             READ ONLY
         3 ORAPDB                 MOUNTED  <------

SQL>

5. Drop PDB

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> drop pluggable database ORAPDB including datafiles;

Pluggable database dropped.

SQL>

6. Verify

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         2 PDB$SEED             READ ONLY

SQL>
SQL> select con_id,FILE_NAME,TABLESPACE_NAME from cdb_data_files where con_id=3;

no rows selected  <----

SQL> select CON_ID,FILE_NAME,TABLESPACE_NAME from cdb_temp_files  where con_id=3;

no rows selected <----

SQL>

[oracle@TESTHOST]$ cd /u01/app/oracle/oradata/CONCDB/ORAPDB
[oracle@rac2 ORAPDB]$ ls -ltr
total 0 <----
[oracle@rac2 ORAPDB]$