how to put datafile online


I try to put datafile online after the operations :
 alter database datafile '/srv/test.dbf' offline drop;

ERROR at line 1:
ORA-01113: file 394 needs media recovery
ORA-01110: data file 394:

 SQL> recover datafile 394;
ORA-00275: media recovery has already been started

I can't restart the db.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Once you do an offline drop of a file, you cannot recover it.  Unless there is something new that I don't know about.  You should never really do an offline drop unless directed to by support or you really know the consequences.

Offline drop removes the file from the database.  Kind of the big hammer approach.  It could certainly leave pointers in the data dictionary to the file and those have to be cleaned up (the only way I know is through Oracle Support).

Under the heading, it's too late now, what were you trying to accomplish?  My guess is you were trying to remove a tablespace and the associated files, which would be a DROP TABLESPACE command, not an ALTER DATABASE command.
slightwv (䄆 Netminder) Commented:
FIRST thing to do is BACKUP EVERYTHING.  Moving forward might make things worse.  Make sure you can get back to the point you are now.

What were you attempting to do before the drop?

To add to the comment above:
You dropped it.  What makes you think you can recover it?

You might be able to do a point in time recovery to a point before the drop but that can cause data loss.

As a LAST RESORT you might be able to recreate the control files and just leave out that one data file.  Again, could easily lose data and might leave other things unusable if there was data in the now deleted data file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Not sure that recreating the control file would actually get rid of it.  There seems to still be records in the dictionary that relate to the file, which would be where the needs recovery message is coming from.  Once you open after recreating the control file, it would likely have been recreated as a MISSING file.

The offline drop should have removed it from the control file anyway.  You should be able to do a ALTER DATABASE BACKUP CONTROLFILE TO TRACE to see if the file is still there.  I would doubt it, but it is possible.  If it is still there, I would try to offline drop it again, could just be something didn't complete there.  That of course assumes that you really wanted to drop it in the first place.

You can totally make it worse, and asking questions about the situation may not be the right way to get it solved.  Knowing exactly what has been done and what you are trying to accomplish and then calling support to have them walk you through the steps is going to be the way to go.  There are too many variables in a situation like this for us to be guessing.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
I agree about contacting Support at this stage!!!

>>Not sure that recreating the control file would actually get rid of it.

I agree the drop should have removed the reference.  I'm confused by the database still wanting to recover something that should be gone.  The thought was it might still be in the control file which is why it is asking about it.

I also agree that without knowing what got you to where you attempted the drop, anything we post here can actually make matters worse!!!
johnsoneSenior Oracle DBACommented:
I saw a similar situation a long time ago and I don't recall all the circumstances.  It had to do with pieces left in the dictionary.  The controlfile was OK, but there were records in one (or more) of the dictionary tables that still held references to the file that was dropped.  The way out was to do catalog "surgery" and delete the records.  May not be what is  happening here, but the only people that can fix that is Support.  Part of the reason that OFFLINE DROP should only be used in very few situations.
bibi92Author Commented:
I found a solution.
The datafile contains no object :

channel ORA_DISK_1: restore complete
Finished recover at 26-AUG-15

RMAN> sql 'alter database datafile 394 online';

sql statement: alter database datafile 394 online

RMAN> exit

bibi92Author Commented:
This help me to find solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.