Re: Tablespace problems - db can't shutdown

In article <3638C0EC.165C6D7E_at_aldiscon.ie>, dylanf_at_aldiscon.ie says...

>Any suggestions would be welcomed as this is causing extreme hedaches.
>Im new to the DB world so feel free to assume ignorance.
{snip}
>unfortunately the datafile was deleted from the drive without being
>removed from
>the database so now the db gives the following error when a shutdown is
>requested:

>SVRMGR> shutdown immediate
>ORA-01116: error in opening database file 6
>ORA-01110: data file 6: '/usr1/rob072.ts2'
>ORA-07368: sfofi: open error, unable to open database file.
>HP-UX Error: 2: No such file or directory

Do a 'shutdown abort' to kill the instance.

>What I need to know is how to get rid of all references to
>'/usr1/rob072.ts2'

In svrmgrl or whatever...

startup mount (this gets the instance up but doesn't open the datafiles). alter database 'filename' offline drop; (where 'filename' is your file) alter database open;

That should do the trick. Last time I tried this however, Oracle seemed to check that the file I was dropping existed, so you may need to create an empty file of the correct name for it to work.

Cheers
Martin

--
Martin Rapier, Database Administrator
Corporate Information & Computing Services. University of Sheffield Tel 0114 222 1137 The opinions expressed here may be those of my employer, or they may not. http://rhino.shef.ac.uk:3001/mr-home/ Received on Fri Oct 30 1998 - 08:54:17 CST