Steps To Drop 19c Oracle Database Manually

In this document ,we will see how to drop an Oracle database.

Prerequisite Steps

Find the location of the data files ,control files and online redo logs of the database to be dropped. Later point , we will cross verify. 

SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/system01.dbf
/u01/app/oracle/oradata/STAGE/sysaux01.dbf
/u01/app/oracle/oradata/STAGE/undotbs01.dbf
/u01/app/oracle/oradata/STAGE/pdbseed/system01.dbf
/u01/app/oracle/oradata/STAGE/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/STAGE/users01.dbf
/u01/app/oracle/oradata/STAGE/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/system01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/STAGE/pdb1/users01.dbf

NAME
---------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_system_k9g4g13f_.dbf

/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_sysaux_k9g4g142_.dbf

/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_undotbs1_k9g4g143_.dbf

/u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_users_k9g4g145_.dbf

NAME
---------------------------------------------------------------------------------------------------------------


15 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/control01.ctl
/u01/app/oracle/fra/STAGE/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STAGE/redo03.log
/u01/app/oracle/oradata/STAGE/redo02.log
/u01/app/oracle/oradata/STAGE/redo01.log

SQL>

--> Stop Database and start in Exclusive mode 

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 1073738888 bytes

Fixed Size                  9143432 bytes

Variable Size             616562688 bytes

Database Buffers          440401920 bytes

Redo Buffers                7630848 bytes

Database mounted.

SQL>

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

STAGE     MOUNTED              PRIMARY

SQL>

--> Drop Database

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL>

--> Manually verify the file

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/system01.dbf

ls: cannot access /u01/app/oracle/oradata/STAGE/system01.dbf: No such file or directory

[oracle@srv1 ~]$

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_system_k9g4g13f_.dbf

ls: cannot access /u01/app/oracle/oradata/STAGE/pdb2/ORADB/E05E9736941377ACE0537C01A8C0C540/datafile/o1_mf_system_k9g4g13f_.dbf: No such file or directory

[oracle@srv1 ~]$

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/control01.ctl

ls: cannot access /u01/app/oracle/oradata/STAGE/control01.ctl: No such file or directory

[oracle@srv1 ~]$

[oracle@srv1 ~]$ ls -lrth /u01/app/oracle/oradata/STAGE/redo03.log

ls: cannot access /u01/app/oracle/oradata/STAGE/redo03.log: No such file or directory

[oracle@srv1 ~]$

Oracle : Drop Pluggable Database (PDB) 19c

 

Oracle : Drop Pluggable Database (PDB)

• Dropping pluggable database is similar to dropping any other regular database, you have two options while dropping pluggable database related to its datafiles
• Dropping PDB including datafiles
• Dropping PDB keeping datafiles

Before dropping pluggable database we can check PDB status by

a. SQL> show pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                   READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           READ WRITE NO

SQL>

b. select con_id, name,open_mode from v$containers;

SQL> set lin 1000

SQL> col name format a20

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

    CON_ID NAME                 OPEN_MODE

---------- -------------------- ----------

         1 CDB$ROOT        READ WRITE

         2 PDB$SEED         READ ONLY

         3 PDB1                 READ WRITE

         4 PDB2                 READ WRITE

         5 PDB3                 READ WRITE

--> We will drop PDB3 now. Before dropping we need to close database.

SQL> alter pluggable database PDB3 close immediate;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           MOUNTED

SQL>

if our database is multi node RAC then we can use instances=all option. 

SQL> alter pluggable database PDB3 close immediate instances=all;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           READ WRITE NO

SQL> alter pluggable database PDB3 close immediate instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           MOUNTED

SQL>

--> Dropping Database

SQL> drop pluggable database PDB3 including datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL>

SQL> drop pluggable database PDB3 keep datafiles; --> with keep datafiles option

Pluggable database dropped.