Re: DROP A DATABASE - urgent help please

Comments embedded.
Edwinah63 wrote:
> i need to drop a database asap as i cannot mount the oracle instance.
>
> my trouble started when i tried to rename the database using the
> DBNEWID utility.
>
> nid target=sys/pwd_at_mydb dbname=mydb2
>
> i also reset the dbid at the same time.
>
> because this is just a test db with no data, i didn't take a copy of
> the control files. altho' i was prompted that the change and
> validation were successful, it failed to startup.
>
> ORA-01103 database mydb2 in control file is not mydb
>

This indicates the init.ora file is inconisistent with the controlfiles, not the other way around. Did you copy your init.ora file to reflect the new db_name and also edit the db_name entry?

> not having the original control files BUT having made a copy of the
> control file to trace, i copied said trace file and edited it to
> recreate the control file. running:
>
> sql> @e:\mydb_ctl.sql;
>

This should not be necessary, as your controlfiles should be fine. Check your init.ora file.

> gets me SP2-0310 unable to open mydb_ctl.sql
>

This is because your mydb_ctl.sql isn't on E:\.

> my situation:
> * oracle 9.2 sp 5
> * win 2000k
> * can't mount or open mydb

Edit your current init.ora file named with the old db_name value to contain the NEW db_name value you just set, then copy that to a new init.ora file:

notepad initmydb.ora
...
db_name=mydb <---- change this line to be db_name=mydb2

Save the changes as initmydb2.ora. Then use initmydb2.ora as your pfile in the startup; you'll need to use oradim to alter the service by providing this new pfile name.

> * can't run my create control file script

It is not located where you believe it to be.

> * tried dropping thru GUI dbca but no go
> * want to DROP MYDB and start all over again - like i said this is just
> a test.

You cannot as the init.ora points to one name and the controlfiles list another.

> * alternatively why can't i run my create controlfile script??

You shouldn't need to do so, as the controlfiles, as I read the error, are fine. It's the init.ora you need to edit.

>
> any and all answers gratefully received
>

Following the instructions listed here would have been most helpful to you:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch14.htm#1656

Failing to backup your database prior to executing this utility was, in my opinion, a grave mistake. You have no ability to restore the original files prior to the use of DBNEWID as none exist. Recommendations such as this are not made lightly. Next time you attempt this possibly you'll remember this incident and properly prepare yourself beforehand.

Again, I believe the source of your error is in the current init.ora file. Edit that and rename it (as in the example provided) then start the instance using your new init.ora (this will require using oradim to reset the pfile parameter for the service). A startup mount, followed by an alter database open resetlogs, should get you up and running.

> EDWINAH63
David Fitzjarrell Received on Wed Aug 10 2005 - 07:41:06 CDT