How to purge databases
There can be
many databases that are hosted on an Oracle server, and requirement is to
delete one DB without impacting other databases. This can be done by two ways:
1. Manual method
2. Using DBCA
Experienced
DBAs can opt for any path but naive DBAs should choose Manual method after lots
of analysis. In any way, backup of all
the DB files should be taken.
Follow below
steps to remove a Database:
1. Stop all Backups (RMAN, Export).
2. Get a list of physical files that are to be removed.
a. Get a list of all the data files using query: select * from
dba_data_files;
b. List of log files using query: select * from v$logfile;
c. List of control files using query: select * from v$controlfile;
d. Details of DB links using query: select * from dba_db_links;
3. Drop each DB link from step 1d using query: DROP
<<PUBLIC>> DATABASE LINK <<link_name>>;
4. Prior to Oracle 9i there was no command to drop all the physical files
in one go; so manually delete the files logged from step 1a to 1c. (Take a
backup of the DB files before dropping)
5. If on oracle 9i onwards, follow below steps as SYSTEM to drop
database:
a. shutdown abort;
b. startup mount exclusive restrict;
c. drop database;
6. Drop database will close your connection, validate in the alert log
the list of files deleted.
7. Comment out the entry in oratab file for the database.
8. Comment out other environment settings for the DB.
9. Remove initSID.ora file
10. In addition you can clean the UDUMP, BDUMP, scripts etc
11. Update the password file if used by your database.
Using DBCA
Oracle has provided DBCA (Database
Configuration Assistant) to perform database administrative tasks. We can even
drop a database following below steps. When Oracle DBA selects the
option to delete database, DBCA deletes all the files associated with this
database. On Windows, any associated services are also deleted.
1. The database must
be mounted as RESTRICTED.
2. Run DBCA, (if on
Unix server then use some xserver tools like Attachmate reflection or CYGWIN software
has to be installed).
3. On the Operations
page, select Delete a Database, and click Next.
4. Select the database
that you wanted to delete and click Finish.
Database is on Oracle failsafe
If the database is on failsafe
(windows cluster high availability environment), then below steps have to be
followed first to remove the database from the failsafe, thereafter any of the
above manual or DBCA method is chosen to remove the database.
1.
Login
to the physical node where failsafe is installed.
2.
In
OFS you will find groups in a tree structure where all the databases are added.
3.
Remove
the database from the group.
4.
NOTE:
ALL THE DATABASES IN THE GROUP WILL BE RESTARTED, SO MAKE SURE THAT ALL THE
OTER DATABASES IN THE GROUP CAN BE RESTARTED.
5.
Follow
any of the above two methods (manual or DBCA) for the actual oracle database
deletion.
Database is on RAC
If the database is on RAC
(real application cluster), then below steps have to be followed first:
1.
Except
primary node (node 1) shutdown all other nodes (database and ASM instance).
2.
Login
to node 1.
3.
Follow
any of the above two methods (manual or DBCA) for the actual oracle database
deletion.
4.
To
remove the database from CRS repository run below command:
srvclt remove database -d <db_name> :
5.
Start
all the nodes.
Word of advice
Both the
methods can be followed; if on Unix then manual method is preferred; we have
covered around 90% of steps, there can be other changes also that are not
covered in the doc based on your application associated with the database; so
analyze your environment properly.
