19 December 2006

How To Remove an Oracle Database

You can remove an oracle database via Graphical User Interfaced programs. I can suggest an alternative method to remove an Oracle database manually. I define the steps below:
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.

  2    TYPE string_arr IS TABLE OF VARCHAR2(1024);
  3    file_list string_arr;
  4  BEGIN
  5    SELECT t.file_path BULK COLLECT
  6      INTO file_list
  7      FROM (SELECT NAME file_path
  8               FROM V$DATAFILE
  9             UNION
 10             SELECT MEMBER file_path
 11               FROM V$LOGFILE
 12             UNION
 13             SELECT NAME file_path
 14               FROM v$controlfile
 15             UNION
 16             SELECT VALUE file_path
 17               FROM v$parameter
 18              WHERE NAME LIKE '%dest'
 19             UNION
 20             SELECT VALUE file_path
 21               FROM v$parameter2
 22              WHERE NAME = 'utl_file_dir'
 23             UNION
 24             SELECT '$ORACLE_BASE/admin/$ORACLE_SID' file_path FROM dual) t;
 26    FOR i IN file_list.FIRST .. file_list.LAST LOOP
 27      DBMS_OUTPUT.PUT_LINE('rm -f ' || file_list(i));
 28    END LOOP;
 29  END;
 30  /

rm -f /data06/app/oracle/admin/cc/bdump
rm -f /data06/app/oracle/admin/cc/cdump
rm -f /data06/app/oracle/admin/cc/udump
rm -f /data06/app/oracle/product/10.1/rdbms/audit
rm -f /data09/oradata/cc/control01.ctl
rm -f /data09/oradata/cc/control02.ctl
rm -f /data09/oradata/cc/control03.ctl
rm -f /data09/oradata/cc/data01.dbf
rm -f /data09/oradata/cc/data02.dbf
rm -f /data09/oradata/cc/redo01.log
rm -f /data09/oradata/cc/redo02.log
rm -f /data09/oradata/cc/redo03.log
rm -f /data09/oradata/cc/sysaux01.dbf
rm -f /data09/oradata/cc/system01.dbf
rm -f /data09/oradata/cc/undotbs01.dbf
rm -f /data09/oradata/cc/users01.dbf
rm -f ?/dbs/arch

PL/SQL procedure successfully completed
3. Shutdown database
shutdown immediate
4. Remove all data files as outputted above.
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
vi $ORACLE_HOME/network/admin/listener.ora

No comments: