Skip to content

How to Resolve ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

  • by

ORA-01610

Tried to perform RECOVER DATABASE on a mounted database by SQL statement, but it failed with ORA-01610.

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

ORA-01610 means that you should use USING BACKUP CONTROLFILE in your statement to explicitly declare the controlfile is a backup one.

Further reading: How to Backup Controlfile?

Theoretically, doing RECOVER DATABASE in SQL, you should use the CURRENT, the original controlfile, if you're using a BACKUP or STANDBY one, you should explicitly say it. We have introduced several controlfile types in another post, you may take a look.

In this case, the standby controlfile is some kind of backup one.

Solutions

There're several ways to solve ORA-01610.

1. Using RMAN

On the other side, RMAN does not care it's a backup one or not, so we can do it in RMAN.

RMAN> recover database;

Starting recover at 2021-10-28 22:44:24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=758 instance=ORCLSTB1 device type=DISK

starting media recovery

archived log for thread 1 with sequence 45544 is already on disk as file +DATA/ORCLSTB/ARCHIVELOG/2021_10_28/thread_1_seq_45544.1883.1089107083
archived log for thread 2 with sequence 5713 is already on disk as file +DATA/ORCLSTB/ARCHIVELOG/2021_10_28/thread_2_seq_5713.1882.1089107083
archived log file name=+DATA/ORCLSTB/ARCHIVELOG/2021_10_28/thread_1_seq_45544.1883.1089107083 thread=1 sequence=45544
archived log file name=+DATA/ORCLSTB/ARCHIVELOG/2021_10_28/thread_2_seq_5713.1882.1089107083 thread=2 sequence=5713
media recovery complete, elapsed time: 00:00:02
Finished recover at 2021-10-28 22:45:53

By the way, there's no RECOVER DATABASE UNTIL CANCEL command in RMAN.

2. Specify STANDBY

For a standby database, we can explicitly use STANDBY modifier to pass the error.

SQL> recover standby database until cancel;
ORA-00279: change 285531163 generated at 10/28/2021 10:10:39 needed for thread 1
ORA-00289: suggestion :
+DATA/ORCLSTB/ARCHIVELOG/2021_10_28/thread_1_seq_45544.1883.1089107083
ORA-00280: change 215423759142 for thread 1 is in sequence #45544


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

As we can see, Oracle suggests the first archived log for us.

3. USING BACKUP CONTROLFILE

If this is not a standby database, you can explicitly USING BACKUP CONTROLFILE.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 292297124 generated at 10/28/2021 01:42:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_10_28/o1_mf_1_5981_jtg1tpg9_.arc
ORA-00280: change 292297124 for thread 1 is in sequence #5981


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Some other error might occur later, but we have some progress here.

A question you may ask, how do I know the currently used controlfile is a backup one or not? You may take a look.

Leave a Reply

Your email address will not be published.