Tag Archives: drop pluggable database

DROP PDB

How to Drop Pluggable Database Manually

Contents
___________________________________________________________________________________________________

1. Environment
2. Pre-requisites
3. Close Pluggable database
4. Drop Pluggable database
5. Verify
___________________________________________________________________________________________________


1. Environment

Platform   : Linuxx86_64
Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version : Oracle 12.2.0.1.0, File system: Normal
CDB Name   : CDB1
PDB Name   : PDB1
Oracle Home: /u01/app/oracle/product/12.2.0.1



2. Pre-requisites

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? CDB1
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$
[oracle@rac2 ~]$ 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 PDB1                 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/CDB1/PDB1/system01.dbf     SYSTEM
         3 /u01/app/oracle/oradata/CDB1/PDB1/sysaux01.dbf     SYSAUX
         3 /u01/app/oracle/oradata/CDB1/PDB1/undotbs01.dbf    UNDOTBS1
         3 /u01/app/oracle/oradata/CDB1/PDB1/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/CDB1/PDB1/temp01.dbf       TEMP

SQL>

[oracle@rac2 ~]$ 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: CDB1 (DBID=931007404) <----

RMAN> BACKUP PLUGGABLE DATABASE PDB1 TAG 'PDB1_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/CDB1/PDB1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/CDB1/PDB1/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/CDB1/PDB1/undotbs01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/CDB1/PDB1/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=PDB1_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>


3. Close PDB

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

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

SQL>
SQL> alter pluggable database PDB1 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 PDB1                 MOUNTED  <------

SQL>

4. Drop PDB

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> drop pluggable database PDB1 including contents and datafiles;
drop pluggable database PDB1 including contents and datafiles
                                       *
ERROR at line 1:
ORA-02000: missing DATAFILES keyword


SQL> drop pluggable database PDB1 including datafiles;

Pluggable database dropped.

SQL>


5. 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@rac2 ~]$ cd /u01/app/oracle/oradata/CDB1/PDB1
[oracle@rac2 PDB1]$ ls -ltr
total 0 <----
[oracle@rac2 PDB1]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.