Oracle 21c Drop Pluggable Database

Description:-

In this article we are going to see how to drop pluggable database.

Use the DROP PLUGGABLE DATABASE statement to drop a pluggable database (PDB). The PDB can be a traditional PDB, an application container, an application seed, or an application PDB.

When you drop a PDB, the control file of the multitenant container database (CDB) is modified to remove all references to the dropped PDB and its data files. Archived logs and backups associated with the dropped PDB are not deleted. You can delete them using Oracle Recovery Manager (RMAN), or you can retain them in case you subsequently want to perform point-in-time recovery of the PDB.

We have two options while dropping pluggable database related to its datafiles.

Dropping PDB including datafiles
Dropping PDB keeping datafiles

Dropping PDB keeping datafiles

Specify KEEP DATAFILES to retain the data files associated with the PDB after the PDB is dropped. The temp file for the PDB is deleted because it is no longer needed. This is the default.

Keeping data files may be useful in scenarios where a PDB that is unplugged from one CDB is plugged into another CDB, with both CDBs sharing storage devices.

Dropping PDB including datafiles

Specify INCLUDING DATAFILES to delete the data files associated with the PDB being dropped. The temp file for the PDB is also deleted.

Restriction on Dropping SNAPSHOT COPY PDBs

If a PDB was created with the SNAPSHOT COPY clause, then you must specify INCLUDING DATAFILES when you drop the PDB.

Demo:- (We are going to drop two pdb databases oradbwrpdb2 and oradbwrpdb3)

Let’s check current statues of PDBs.
[oracle@oracle21c ~]$ sql

SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 17:32:03 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
4 ORADBWRPDB2 READ WRITE NO
5 ORADBWRPDB3 READ WRITE NO

Step:-1 Check the PDB Status.

SQL> COLUMN PDB_NAME FORMAT A15
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;SQL>

PDB_ID PDB_NAME STATUS
———- ————— ———-
2 PDB$SEED NORMAL
3 ORADBWRPDB1 NORMAL
4 ORADBWRPDB2 NORMAL
5 ORADBWRPDB3 NORMAL

Step:-2 Close or shutting down all PDBs that needs to be dropped

SQL> alter pluggable database ORADBWRPDB2 close;
Pluggable database altered.

SQL> alter pluggable database ORADBWRPDB3 close;
Pluggable database altered.

If RAC PDB Database means below command needs execute

For a RAC database, you have to close the PDB on all instances. Which means, you should additionally specify INSTANCES=ALL clause.

alter pluggable database oradbwrpdb2 close immediate instances=all;

Validate the pdbs are in MOUNTED state now before drop.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
4 ORADBWRPDB2 MOUNTED
5 ORADBWRPDB3 MOUNTED

Step:-3 KEEP DATAFILES Method – ORADBWRPDB2

Before Dropping ORADBWRPDB2 PDB
[oracle@oracle21c oradata]$ df -h ORADBWR
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 82G 23G 60G 28% /

SQL> alter pluggable database oradbwrpdb2 unplug into ‘/u01/app/oracle/oradata/oradbwrpdb2.pdb’;
Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
4 ORADBWRPDB2 MOUNTED
5 ORADBWRPDB3 MOUNTED

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

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
5 ORADBWRPDB3 MOUNTED

After Dropping ORADBWRPDB2 PDB
[oracle@oracle21c oradata]$ df -h ORADBWR
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 82G 23G 60G 28% /

Note:- Datafile still present in the mount point. Later if we need same pdb database means using below command we can recreate new pdb database

Step:-4 Create pluggable database using xml file

[oracle@oracle21c oradbwrpdb2]$ mkdir -p /u01/app/oracle/oradata/ORADBWR/oradbwrpdb4
[oracle@oracle21c oradbwrpdb2]$ sql
SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 18:09:59 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
5 ORADBWRPDB3 MOUNTED

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/ORADBWR/system01.dbf
/u01/app/oracle/oradata/ORADBWR/sysaux01.dbf
/u01/app/oracle/oradata/ORADBWR/undotbs01.dbf
/u01/app/oracle/oradata/ORADBWR/pdbseed/system01.dbf
/u01/app/oracle/oradata/ORADBWR/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/ORADBWR/users01.dbf
/u01/app/oracle/oradata/ORADBWR/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/system01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/sysaux01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/undotbs01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/users01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb3/system01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb3/sysaux01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb3/undotbs01.dbf
/u01/app/oracle/oradata/ORADBWR/oradbwrpdb3/users01.dbf

15 rows selected.

SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 18:12:59 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0

SQL> create pluggable database oradbwrpdb4 using ‘/u01/app/oracle/oradata/oradbwrpdb2.pdb’
move file_name_convert=(‘/u01/app/oracle/oradata/’,’/u01/app/oracle/oradata/ORADBWR/oradbwrpdb4/’); 2

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
5 ORADBWRPDB3 MOUNTED
7 ORADBWRPDB4 MOUNTED
SQL> alter pluggable database ORADBWRPDB4 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
5 ORADBWRPDB3 MOUNTED
7 ORADBWRPDB4 READ WRITE NO

SQL> select name from v$datafile;

19 rows selected.

Step:-5 Dropping PDB including datafiles – ORADBWRPDB3

Before Dropping ORADBWRPDB3 PDB
[oracle@oracle21c ~]$ df -h ORADBWR
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 82G 24G 59G 29% /

[oracle@oracle21c ~]$ sql

SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 18:21:01 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0

SQL> show pdbs

SQL> drop pluggable database ORADBWRPDB3 including datafiles;
Pluggable database dropped.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
7 ORADBWRPDB4 READ WRITE NO
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0

After Dropping ORADBWRPDB3 PDB
[oracle@oracle21c ~]$ df -h ORADBWR
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 82G 23G 60G 28% /

Space will be released in the mount point. No more datafiles present in the server

Connect with me on:-

Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba

Recent Posts

Get updated

Oracle 21c Drop Pluggable Database

You may also like

Previous Post

Oracle 21c Cloning a PDB Database Using Sqldeveloper Tool

View Post

Next Post

Oracle 21c Point In Time Recovery of Pdb Database

View Post

Recent Posts

Get updated