How can I manually delete an Oracle9i database

Solution

On all platforms, you are meant to use the Oracle Database Creation Assistant GUI (DBCA).
This not only deletes the database files, but cleans up the ORATAB (UNIX) or Oracle Registry
entries (MS-Windows) as well.

In cases where the DBCA is unavailable/unusable on the MS-Windows platform, to manually delete an Oracle9i database, you can do the following steps;

1.       Use the Oracle9i OUI to select the 9i Oracle Home. Record/remember the "Home Path".

2.       Open a DOS command prompt.

3.       Check the current setting of the Environment Variable ORACLE_SID:
DOS> SET ORACLE_SID

4.       If this has not been SET, do so now...
DOS> SET ORACLE_SID=

5.       Startup SQL-Plus and connect as SYSDBA:
DOS> sqlplus /nolog
SQL> connect / as sysdba;

6.       Capture the database filenames & directories, then exit.
spool
SELECT NAME FROM V$CONTROLFILE;
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT NAME FROM V$ARCHIVED_LOG;
SELECT VALUE FROM V$PARAMETER WHERE NAME LIKE '%dump_dest';
SELECT VALUE FROM V$PARAMETER WHERE NAME LIKE '%pfile';
exit;

7.       Using the MicroSoft SERVICES applet, shut down the OracleService service.

8.       Delete the OracleService service, from the DOS command prompt.
DOS> oradim -delete -sid

9.       To confirm that the service was actually deleted, check file \database\ORADIM.LOG. There should be an entry like:

Fri Feb 15 09:16:34 2008
Instance deleted.

10.   Re-start the MicroSoft SERVICES applet, and confirm if the services have been deleted.  If not, use:
DOS> oradim -delete -srvc ,

11.   Delete all the database files found in Step 6.
Delete all the ControlFiles, LogFiles, DataFiles, Archive logs and ParameterFiles.
The Paramater files are PWD.ORA, SPFILE.ORA, INIT.ORA in the \database directory.

12.   Delete all the based directories, derived from the directory names in the list of database files found in Step 6.  Typically, you will need to delete;
\admin\ (NOTE: This directory holds bdump, cdump, et cetera...)
\oradata\ (NOTE: By default, this directory should have contained the ControlFiles, LogFiles and DataFiles).

13.   Check the environment variables to make sure nothing refers to ;
Start\Settings\Control Panel\System
Choose the Tab Environment
Check the system variables and the user variables.
If there are references to the , change it to an existing Oracle9i database SID.

14.   Edit the LISTENER.ORA file and removed the entry for , and restart the Listener.

15.   Edit the TNSNAMES.ORA file and removed the entry for .

16.   Change the ORACLE_SID key in the Oracle Key Directory, in the MicroSoft Registry
DOS> regedit
The RegEdit GUI will start...


GUI> Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Oracle key directory, and click on the "+", to expand all the sub-keys.
GUI> Click on the HOMEx entries, until you find one where the ORACLE_HOME key has the same value as the "Home Path" from Step 1.
GUI> For that HOMEx, double-click on the ORACLE_SID key, and enter in a value for an existing Oracle9i database SID, then click OK.
GUI> Close the RegEdit application.

DOS> exit

17.   You have now successfully removed an Oracle9i database from MS-Windows using the manual method.