Delete database manually in Oracle

 Delete database manually in Oracle

 Here is how you can remove an Oracle database manually:

1. Log On as sysdba.

sqlplus / as sysdba

2. Check oracle related files. You can create a sh file that removes all files as i mentioned below. Please be careful when executing commands. Some of them may be directory instead of file. You can use rmdir command instead.


SQL> SET SERVEROUT ON
SQL>  DECLARE
                  TYPE string_arr IS TABLE OF VARCHAR2(1024);
                  file_list string_arr;
            BEGIN
                  SELECT t.file_path BULK COLLECT
                  INTO file_list
                  FROM (
                                SELECT NAME file_path
                                FROM V$DATAFILE
                          UNION
                                SELECT MEMBER file_path
                                FROM V$LOGFILE
                          UNION
                                SELECT NAME file_path
                                 FROM v$controlfile
                         UNION
                                 SELECT VALUE file_path
                                 FROM v$parameter
                                 WHERE NAME LIKE '%dest'
                         UNION
                                 SELECT VALUE file_path
                                 FROM v$parameter2
                                 WHERE NAME = 'utl_file_dir'
                         UNION
                                 SELECT '$ORACLE_BASE/admin/$ORACLE_SID' file_path
                                  FROM dual
                                ) t;

                 FOR i IN file_list.FIRST .. file_list.LAST LOOP
                            DBMS_OUTPUT.PUT_LINE('rm -f ' || file_list(i));
                 END LOOP;

         END;        /
 

rm -rf  $ORACLE_BASE/admin/bsnl

rm -rf $ORACLE_HOME/rdbms/audit

 $ find /orahome -name control*.ctl

find: cannot open /orahome/download

/orahome/oracle/oradata/bsnl/control01.ctl

/orahome/oracle/oradata/bsnl/control02.ctl

/orahome/oracle/oradata/bsnl/control03.ctl

 

rm -rf /orahome/oracle/oradata

rm /temp/temp01.dbf

rm /undo/undotbs01.dbf

5. Remove entry in tnsnames.ora

vi $ORACLE_HOME/network/admin/tnsnames.ora

6. Remove entry in listener.ora

vi $ORACLE_HOME/network/admin/listener.ora

7. Remove entry in oratab
Examples:
vi /var/opt/oracle/oratab      for solaris
vi /etc/oracle/oratab            for AIX

vi /etc/oratab

$ cat /etc/oratab

#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
 
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/orahome2/oracle/OraHome1:N
*:/orahome/oracle:N
#bsnl:/orahome/oracle:N
# *:/orahome/oracle/product/9.2.0:N
bsnl:/orahome/oracle/product/9.2.0:N
*:/orahome/oracle/product/9.2.0:N

$

Comment the bsnl:

 

$ cd $ORACLE_HOME/dbs

$ ls -lrt

total 104
-rwxrwxr-x   1 oracle     dba           8385 Mar  9  2002 init.ora
-rwxrwxr-x   1 oracle     dba          12920 Mar  9  2002 initdw.ora
-rwxrwxr-x   1 oracle     dba           5120 Jun 25 06:04 spfilebsnl.ora
-rwxrwxr-x   1 oracle     dba           3419 Jun 25 08:59 initBSNL_bak_250609.ora
-rwxrwxr-x   1 oracle     dba           3072 Jul 21 10:14 orapw
-rwxrwxr-x   1 oracle     dba           5120 Jul 25 15:36 spfileBSNL.ora
-rwxrwxr-x   1 oracle     dba           1356 Aug 12 15:40 initBSNL.ora
-rwxrwxr-x   1 oracle     dba             24 Aug 14 13:23 lkBSNL
-rwSr-----   1 oracle     dba           3072 Aug 14 13:26 orapwbsnl

$ mv initBSNL.ora initBSNL.ora.140809

$ mv spfileBSNL.ora spfileBSNL.ora.140809

$ mv orapwbsnl orapwbsnl.140809

$ mv spfilebsnl.ora spfilebsnl.ora.140809

This deletes the Database