Re: Tablespace problems - db can't shutdown

> Im new to the DB world so feel free to assume ignorance.

That is the funniest statement that I have ever seen. Well Anyway, to solve your problem, I am asuming the worst case scenario:

You do not have a backup of the datafile (or database) and you are running in NOARCHIEVELOG mode. The following steps will remove the datafile and the Tablespace that the datafile belongs too. All the data belonging to the Tablespace will need to be recreated and re-entered.

1. 'shutdown abort' the database
2. 'Mount' the database.
3. Take the datafile offline ALTER DATABASE DATAFILE '/usr1/rob072.ts2' OFFLINE DROP;
4. Open the database.
5. Drop the TS3 tablespace DROP TABLESPACE TS3 INCLUDING CONTENTS;
6. Re-create the tablespace
7. Re-create the previously existing objects in the tablespace.

As you probally figuired out, you will lose all objects in this tablespace, including anything that resides on the /usr1/rob072.ts3 datafile.

Good Luck,
Satar

In article <3638C0EC.165C6D7E_at_aldiscon.ie>,   Dylan Fairbairn <dylanf_at_aldiscon.ie> wrote:
> Hi all,
>
> Any suggestions would be welcomed as this is causing extreme hedaches.
> Im new to the DB world so feel free to assume ignorance.
>
> prob:- a new tablespace and datafile was created and a new table created
> in this space.
> the table was subsequently dropped
> 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
>
> What I need to know is how to get rid of all references to
> '/usr1/rob072.ts2'
>
> also note:
> this may be complicated by the fact that in the view dba_data_files we
> have
> /usr1/rob072.ts2
> 6 TS3 10485760 2560 AVAILABLE
> /usr1/rob072.ts3
> 7 TS3 10485760 2560 AVAILABLE
> would this cause an additional conflict?
>
> thanks in advance
>
> Dylan
>
>

--
Oracle DBA/UNIX System Admin
Advanced Enterprise Solutions
(949) 756-0588
Oracle Re-Seller

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 30 1998 - 17:52:12 CST