Drop Database Manually Oracle

————- Drop Database Manually Oracle ———————

1. First get the datafiles, control files and redo log files location

Login to sqlplus as sysdba and execute below script :

[sourcecode language="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;
/
[/sourcecode]

It will generate output like

[sourcecode language="sql"]
rm -f $ORACLE_BASE/admin/$ORACLE_SID
rm -f +DATA/orcl/control01.ctl
rm -f +DATA/orcl/control02.ctl
rm -f +DATA/orclstandby/datafile/mgmt_ad4j_ts.270.805385125
rm -f +DATA/orclstandby/datafile/mgmt_ecm_depot_ts.268.805385111
rm -f +DATA/orclstandby/datafile/mgmt_tablespace.269.805385119
rm -f +DATA/orclstandby/redo01.log
rm -f +DATA/orclstandby/redo02.log
rm -f +DATA/orclstandby/redo03.log
rm -f +DATA/orclstandby/sysaux01.dbf
rm -f +DATA/orclstandby/system01.dbf
rm -f +DATA/orclstandby/undotbs01.dbf
rm -f +DATA/orclstandby/users01.dbf
rm -f +FRA
rm -f +FRA/orclstandby/onlinelog/group_4.368.801307819
rm -f +FRA/orclstandby/onlinelog/group_5.369.801307819
rm -f +FRA/orclstandby/onlinelog/group_6.370.801307821
rm -f +FRA/orclstandby/onlinelog/group_7.371.801307821
rm -f /oracle/app/oracle
rm -f /oracle/app/oracle/admin/orcl/adump
rm -f /oracle/app/oracle/diag/rdbms/orclstandby/orcl1/cdump
rm -f /oracle/app/oracle/diag/rdbms/orclstandby/orcl1/trace
rm -f 1
rm -f ?/dbs/arch
rm -f
[/sourcecode]

2. Drop the database using command

[sourcecode language="sql"]
SQL > drop database;
[/sourcecode]

ORA-02231: missing or invalid option to ALTER DATABASE If you get like above error then restart database and execute command below ………………….

[sourcecode language="sql"]
SQL> alter system enable restricted session;
System altered.
SQL> drop database;
[/sourcecode]

3. Delete all the OS files using the commands generated in output before

Category: DatabaseUncategorized

Tags:

Leave a Reply

Article by: Shadab Mohammad