Cannot delete Tablespace without datafile

Cannot delete Tablespace without datafile

Post by Sudarshan Sampat » Sun, 31 Dec 1899 09:00:00



Is there a way to drop a Tablespace that has no datafile?
I accidently deleted the datafile associated with the
tablespace and I have no back up for it.

I performed the following steps to fix the problem after shutting down
the
database and mounting it in exclusive mode:

Quote:> alter database create datafile 'raid/oracle/app/abcd.dbf'

Media recovery is needed after this step
Quote:> alter database recover datafile 'raid/oracle/app/abcd.dbf'

Now, it complains that with this error:

ORA-00279: Change generated at 5/31/00 12:50pm needed for thread 1
ORA-00289: Suggestion : '/raid/oracle/app/000014244.arc'
ORA-00280: Change 1234567865 for thread 1 is in sequence #86831

After this step, the database doesn't open at all. It complains bad
header
file.

Is there a simple way to delete the datafile or tablespace when database

is mounted only?

Please advise.

-Sudarshan

 
 
 

Cannot delete Tablespace without datafile

Post by Syltre » Sun, 31 Dec 1899 09:00:00


Have you tried RECOVER UNTIL CANCEL ?

I'm not sure but it may work

Syltrem


Quote:> Is there a way to drop a Tablespace that has no datafile?
> I accidently deleted the datafile associated with the
> tablespace and I have no back up for it.

> I performed the following steps to fix the problem after shutting down
> the
> database and mounting it in exclusive mode:
> > alter database create datafile 'raid/oracle/app/abcd.dbf'
> Media recovery is needed after this step
> > alter database recover datafile 'raid/oracle/app/abcd.dbf'
> Now, it complains that with this error:

> ORA-00279: Change generated at 5/31/00 12:50pm needed for thread 1
> ORA-00289: Suggestion : '/raid/oracle/app/000014244.arc'
> ORA-00280: Change 1234567865 for thread 1 is in sequence #86831

> After this step, the database doesn't open at all. It complains bad
> header
> file.

> Is there a simple way to delete the datafile or tablespace when database

> is mounted only?

> Please advise.

> -Sudarshan


 
 
 

Cannot delete Tablespace without datafile

Post by Sudarshan Sampat » Sun, 31 Dec 1899 09:00:00


Yes, I did. I tried that option out.

Is there a way fix the header information in a database file? This question
I ask as it I performed one operation as follows:

I tried to copy a datafile from another tablespace. But somehow, Oracle
knows that I copied the datafile # and wouldn't let me open the database.

The media recovery option did not work as I did not have the database
running in the ArchiveLog mode.

Getting pretty tough here. Thanks for your response though.

-Sudarshan


> Have you tried RECOVER UNTIL CANCEL ?

> I'm not sure but it may work

> Syltrem



> > Is there a way to drop a Tablespace that has no datafile?
> > I accidently deleted the datafile associated with the
> > tablespace and I have no back up for it.

> > I performed the following steps to fix the problem after shutting down
> > the
> > database and mounting it in exclusive mode:
> > > alter database create datafile 'raid/oracle/app/abcd.dbf'
> > Media recovery is needed after this step
> > > alter database recover datafile 'raid/oracle/app/abcd.dbf'
> > Now, it complains that with this error:

> > ORA-00279: Change generated at 5/31/00 12:50pm needed for thread 1
> > ORA-00289: Suggestion : '/raid/oracle/app/000014244.arc'
> > ORA-00280: Change 1234567865 for thread 1 is in sequence #86831

> > After this step, the database doesn't open at all. It complains bad
> > header
> > file.

> > Is there a simple way to delete the datafile or tablespace when database

> > is mounted only?

> > Please advise.

> > -Sudarshan

 
 
 

Cannot delete Tablespace without datafile

Post by Bob Fazi » Sun, 31 Dec 1899 09:00:00


I have had to do this before, and I can't remember all of the syntax, but I
believe this is what you will have to do.

First you need to drop the file from the database.

svrmgrl> alter database datafile '...' offline drop;
The tablespace is now offline (and you should be able to open the database);
svrmgrl> alter database open;
svrmgrl> drop tablespace ... including contents cascade constraints;

--
Robert Fazio, Oracle DBA

remove nospam from reply address
http://24.8.218.197/

Quote:> Is there a way to drop a Tablespace that has no datafile?
> I accidently deleted the datafile associated with the
> tablespace and I have no back up for it.

> I performed the following steps to fix the problem after shutting down
> the
> database and mounting it in exclusive mode:
> > alter database create datafile 'raid/oracle/app/abcd.dbf'
> Media recovery is needed after this step
> > alter database recover datafile 'raid/oracle/app/abcd.dbf'
> Now, it complains that with this error:

> ORA-00279: Change generated at 5/31/00 12:50pm needed for thread 1
> ORA-00289: Suggestion : '/raid/oracle/app/000014244.arc'
> ORA-00280: Change 1234567865 for thread 1 is in sequence #86831

> After this step, the database doesn't open at all. It complains bad
> header
> file.

> Is there a simple way to delete the datafile or tablespace when database

> is mounted only?

> Please advise.

> -Sudarshan

 
 
 

Cannot delete Tablespace without datafile

Post by Sudarshan Sampat » Sun, 31 Dec 1899 09:00:00


Thank you so much. This solution worked like a charm....and I was
finally able to open the database.

Thanks again for your great help.


> I have had to do this before, and I can't remember all of the syntax, but I
> believe this is what you will have to do.

> First you need to drop the file from the database.

> svrmgrl> alter database datafile '...' offline drop;
> The tablespace is now offline (and you should be able to open the database);
> svrmgrl> alter database open;
> svrmgrl> drop tablespace ... including contents cascade constraints;

> --
> Robert Fazio, Oracle DBA

> remove nospam from reply address
> http://24.8.218.197/