Datafile recover or removal

From: <1dhowell_at_my-deja.com>
Date: Wed, 04 Oct 2000 01:24:50 GMT
Message-ID: <8re0ss$rld$1@nnrp1.deja.com>

I am currently experiencing a serious problem that I cannot solve, so hopefully someone out there can help me.

The problem: I was attempting to remove datafiles of a tablespace that is no longer being used. After removing the files and running a sql statement against another tablespace I recieved the following message

	ORA-00376: file 19 cannot be read at this time
	ORA-01110: data file 19: 'G:\ROLLBACKS\BARSROLLBACK.ORA'

When I looked at the status of the datafile it stated "RECOVER".

At that point I attempted to recover the file using the following command

        RECOVER DATAFILE 'G:\ROLLBACKS\BARSROLLBACK.ORA' When I execute this statement I get the following recovery dialog message

	ORA-00279: Change 25625763 generated at 10/03/00 10:57:28 needed
         for thread 1
	ORA-00289: Suggestion : E:\TEMP\'ARC15789.001'
	ORA-00280: Change 25625763 for thread 1 is in sequence #157892

I first used the suggested default with no success and then several of the other archive files.

After unsuccessfully recovering the file, I attempted to remove the file using the following command

	ALTER DATABASE DATAFILE  'G:\ROLLBACKS\BARSROLLBACK.ORA' OFFLINE
          DROP;

The systems returns a message telling me that the statement has been processed. But, when I check the status of the datafile it is still in the system with a status of "RECOVER".

I have also tried to create a new file using the same name and get the following error message

	ORA-01537: cannot add data file 'G:\ROLLBACKS\BARSROLLBACK.ORA'-
          file already part of database.

I also attempted to remove the tablespace and all it's components and get the following message

 	ORA-01548: active rollback segment 'BARSROLLBACK' found,
          terminate dropping tablespace

After all of this I have the following questions that I hope someone has the answers.
1) WHICH ARCHIVE FILE SHOULD BE USED FOR A SUCESSFUL DATAFILE RECOVER? 2) HOW CAN I GET THE SYSTEM TO EITHER RECOGNIZE THE FILES OR REMOVE THEM FROM THE DATABASE? Thaks in advance for you help in this situation. DMH Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 03 2000 - 20:24:50 CDT