How to recover a datafile in recovery mode

Published by Jacob Martin on March 1, 2014

Oracle is one of the widely used databases among the database enterprises because it is one of flexible, cost effective and provides accurate work and performance.  It is relational database management system which is made for database industries and enterprises for computing gridding and managing information, data and application. Though oracle database is popular and used by many big industries and enterprises, but sometimes administrator or users gets error when they try to put datafile online when it is in recovery mode. For example – I Have a datafile that shows file size as zero byte. I see that datafile is displaying as it is present in dba_data_files but in v$datafile, it is display the status is in recovery mode. This is in production and tablespace has been in this state and I am not able to put the datafile online as it is recovery mode and whe I try to put it online it is showing following errors:

Error at line 1:

ORA-01113: file 598 needs media recovery

ORA-01110: dat in file 598: ‘/home/oradaata/tbs01.dbf’

I need any solution how to solve this issue without bringing the instance down. Any help will be most welcomed.

When any users or administrator gets such type of error it takes months to solve the issue and they are not able to access the data because it is corrupted or damaged and even they can put it online as well. In this case it is recommended that one must take help of any professional and third party tool to fix the issue and recover your oracle datafiles. But do you know why you get such errors or how Oracle datafile gets corrupted. Let us know some of the common reasons.

Causes that corrupts Oracle database file

• Improper or sudden shutdown of datafile when it is opened
• Malfunction of operating system
• Due to virus or malware attacks may lead to corrupt or damage datafile
• Hardware crash or software problem may lead to corruption to datafile
• Any interruption or disconnection in network while databse is open

Here are some manual methods that may resolve your problem and may take some production downtime to complete:

1. Transfer everything that is stored in the tablespace that contains the datafile. Drop the tablespace with contents, generate the tablespace and then import everything back in.
2. Users can try another possible method is that move tables within the tablespace to a new tablespace and then remove the old tablespace.
3. Generate logical standby with correct structure and connect to switch to logical standby for production when it complete. It may take a lot of time and work.

Once you get everything OK and then clear out of the old tablespace including data stored in it.

But even if your problem is not solved and it persist again and again then you have left with no other option rather than taking help of third party tool. Oracle file repair tool one of the professional and worldwide used third party tools which is designed to fix any errors and corruption of Oracle file and restore them with a new healthy Oracle database file.