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

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, issuing RECOVER DATABASE in SQL*Plus, you should use the CURRENT, original controlfile. If you're using a BACKUP or STANDBY one, you should explicitly say it.

Although they look like the same, controlfiles do have different types to fit different scenarios. In this case, the standby controlfile is some kind of backup one.

Solutions

There're several ways to solve ORA-01610.

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.

Specify STANDBY

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

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. This is expected behavior.

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.