ORA-01610
recovery using the BACKUP CONTROLFILE option must be done

Version

11.2

Cause

Either an earlier database recovery session specified BACKUP CONTROLFILE, or the control file was recreated with the RESETLOGS option, or the control file being used is a backup control file. After that only BACKUP CONTROLFILE recovery is allowed and it must be followed by a log reset at the next database open.

Action

Perform recovery using the BACKUP CONTROFILE option.

Aman
2007-02-23 19:43:33
if your database is in noarchivelog...or you have also lost your archive file..then this is solution..
1.alter database backup controlfile to trace noresetlogs;

2.shut immediate;

3.startup nomount;

4.create controlfile from trace file...just copy paste (which is automatically comes into udump)

5.recover database;

6.alter database open

try this n leave comment ...is it working
Carol
2007-03-07 04:58:48
This solution was perfect. Thank You.. database up and running.
Georg
2007-10-05 06:10:48
Perfect solution from AMAN. We had an unusable CURRENT Redo-Log-File. This Redo-Log-File even blocked the system during automatic crash recovery.
sam
2007-10-17 12:52:06
Excelent man!! Thank you
VERONICA
2008-04-19 20:40:03
AMAN FELICITACIONES, GRACIAS A TU AYUDA PUDE RECUPERAR LA BASE DE DATOS.
bknox
2008-04-28 13:13:14
It worked. Thank you very much Aman!
dschulenberg
2008-07-24 20:06:56
This procedure just saved our test instance :) Thanks
leo
2008-08-28 06:54:51
great steps....

been looking for this for ages, finally did it within 5 minutes...

had a problem understanding step 4. Actually it's just copy and paste what's inside the latest .trc file inside udump directory starting from CREATE CONTROLFILE ......

thanks so much for the guide, really helpful...
Martijn
2009-07-15 10:14:26
Good stuff. I ran into the ORA-01113, ORA-01610 and the likes, but managed to save my database. You saved my day. Thanx.
Tony
2009-08-11 21:44:21
I lost half a day's work but thanks to this I don't have to lose a whole day's worth of data! This solution rocks!
Fake
2009-09-17 18:15:39
What you meant with the step 4?
Christine
2009-10-06 14:35:11
So simple and yet so perfect. THANK YOU SO MUCH!
dheeraj
2009-12-22 15:09:21
Hi can you please let me know the syntax for the 4th command "create controlfile from trace file". Thanks in advance.
Ben
2010-02-06 01:50:26
I need help I'm a new DBA can anybody tell me how to create a controlfile from a trace file
ovey
2010-02-06 02:01:10
Nice post... You are a DBA master... :)
Ion
2010-02-28 17:01:21
Simply awesome , after googling for more than 2 hours this solution fixed my issue ....
THANKS A ZILLION !!!!!!!
Ranjay
2010-03-26 15:58:36
Thanks alot.

This is the best solution you have given.

You are great.

Thanks
vivek
2010-09-02 18:47:42
hi i am also following d same steps but this error occur
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

pls help me
prakash
2010-09-19 10:49:12
ur genious man....
Thaks a lot... !
muthu
2010-10-19 11:52:14
it is working. thank you.
syed umer
2010-11-03 09:39:09
asalam walaikum
JAHZAK ALLAH(THANKS ALOT)
Jamil Ahsan
2010-11-23 21:37:05
A great post. here is some more detail:

1. alter database backup controlfile to trace noresetlogs;
2. shutdowm immediate
3. startup nomount;
4. create control file from trace file which we created in step one. To do so go to /udump directory and using vi editor open most latest trace log...Copy control file info from Startup mount till what oracle locale is used. then create an empty file using touch controlfile.sql file and paste copied data from trace file. Now we have a controlfile in text format created from trace file
5. rename all original control files to something.
6. startup mount
7. @path to controlfile.sql (ignore if says ORA-01081: cannot start already-running ORACLE - shut it down first) at the end it should say file created successfully.
8. recover database
9. alter database open (Thats it...it should open successfully)
10. To make sure all is good....shutdown immediate
11. startup
Ella
2011-08-17 13:39:18
It works great. Thanks
Frank
2011-11-10 17:51:11
hello,
thank you for the steps but i get stuck on the recovery opening bit i get this error below
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\SYSTEM01.DBF'

i have tried to recover the file from old backups but it still gives me that error

please help, will be greatfull
RK
2011-12-16 12:11:35
I m wondering it is working very good for me too.Very simple steps to follow and before this i tried more but no luck.Not i m good and my DB opened.But i cannt able to see my tables which was in dropped TS.

SQL> select count(*) from test_new;
select count(*) from test_new
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4:
'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\MISSING00004'

looking Help here.

Thanks
RK

Sakthi
2013-02-27 06:35:47
What is the correct syntax?
Sakthi
2013-02-27 06:36:54
What is the correct syntax for ORA-02237?
Ajay
2013-03-22 05:14:16
Excellent post..!! Very Helpful.. Thank you so much..

More Information

The Google Search results shown above are obtained by searching for this error using a custom google search engine which is restricted to a specific list of oracle reference sites. If you know a good oracle reference/help site and think that site should be included in this search, you can contribute to this search engine here.