Saturday, November 27, 2010

Dropping Database in oracle for Windows Server

Dropping a Database is a rare task assigned to DBA.The procedure I adopted for Dropping Database in windows server in order to release space is as described below:

Step 1 :- Checking/spooling the database files and non database files location.

This step is for manually deleting the files if the space is not released after the Database is dropped.
SQL>Spool DropDB.txt
For Datafiles location: dba_data_files,
For Controlfiles location:v$controlfile,
For logfiles location:v$logfile
SQL>spool off

step 2 :- set oracle_home=d:\oracle\product\ora10204
set oracle_sid=xyz

Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

step 3 :- Startup the database in mount stage and restricted exclusively.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size 2139224 bytes
Variable Size 647846824 bytes
Database Buffers 922746880 bytes
Redo Buffers 4325376 bytes
Database mounted.

step 4 :- Drop the database using bellow commond and exit for sql prompt.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> exit

step 5 :- check the database files are removed for the operating system and remove the directories and file which are
created manully.
Step 6 :- Drop the xyz related services (Using the ORADIM UTILITY please below for an example).

d:\oradim -DELETE -SID xyz
Instance deleted.

Step 7 :- Check the database files and non database files related to xyz and if any files exist please remove those files from the system.

Step 8 :- Verify the Listener if any xyz services are listening, if yes then unregister the dbcentre from the listener (default It automatically unregister).

Hope this helps.

Best regards,