Removing a db 1

[oracleSQLdba]

How do I remove a 9i db from solaris with multiple databases with a single oracle_home?

 

[SantaMufasa]

Do you want to remove the Oracle software, or do you want to remove a single database of the multiple databases on that machine? (I suspect you want the latter, right?)

If you wish to simply remove a single database and leave the Oracle software intact, then you do the following:

1) Identify all data files involved in the obsolete database.

a) Confirm the location and name of your init<SID>.ora parameter file. (This file name is the only database-related file that you cannot query for from your database.)

Then, with your obsolete database still up and running, do the following:

b) Confirm the names of your database control files:

col name format a60
select * from v$controlfile;

c) Confirm the names of your on-line redo log files:

col member format a60
select * from v$logfile;

d) Confirm the names of database data files:

col name format a60
select name from v$datafile;

2) For the sake of documentation (and as a CYA), make a human-readable, text-based backup copy of your database's configuration:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This human-readable backup controlfile will reside in the directory that appears is your database instance's parameters as "USER_DUMP_DEST".

3) Shutdown your database for one last time.

SHUTDOWN ABORT

4) Using your operating system's "copy" command of choice, backup ALL of the files that you identified in Steps 1 & 2, above.

5) Using your operating system's "file-erase" command (e.g., Windows "erase" or "del", Unix "rm", et cetera), delete all of the files you identified in Steps 1 & 2.

6) Cleanup residual references that may exist of your database:

a) On Windows, remove the obsolete database's service. From a command prompt, issue this command:

oradim -delete -sid <SID name>

b) On Unix, "vi" your "oratab" file to remove references to the defunct Oracle instance. On solaris, the "oratab" file resides in the "/var/opt/oracle" path; on most other *nix operating systems, "oratab" resides in the "/etc" path.

c) Edit your tnsnames.ora connectivity file to remove any references to the defunct Oracle instance.

Let us know if this is a useful checklist.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oracleSQLdba]

Wow. Thanks. But you're gonna think I'm nuts but the USER_DUMP_DEST isn't in the initsid.ora file. Thoughts?

 

[SantaMufasa]

No, I don't think you are nuts...I just think that you are probably not aware that if USER_DUMP_DEST is not in your init<SID>.ora, then it defaults to a value that you can confirm by doing the following query:

col name format a30
col value format a50
select name , value
from v$parameter
where name = 'user_dump_dest';

...And if the result shows a "?", then that expands to "$ORACLE_HOME".

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oracleSQLdba]

Great info. Thanks.

 

[slaing]

Dave -

Any particular resaon why you're not advocating using DBCA to do most of the work in deleting an instance ??

I've not had any bad experiences with it ,although IIRC there might be a few minor files to delete manually afterwards.

I'd also suggest taking a full export of the DB as well as the cold backup - makes it easier to recover the data that someone told you they'd "never need again" !

Steve

 

[SantaMufasa]

Steve,

Excellent suggestion about "a full export of the DB as well as the cold backup" !

I'm sure that DBCA performs very nicely (even flawlessly). My typical approach, however, to Tek-Tips questions is to provide an answer that equips the requester with as much understanding as possible, versus a response of, "Just drop it using DBCA."

But for completeness, I certainly should have included the "Just drop it using DBCA" alternative. So, thank you for doing that.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]