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 doneORA-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:53By 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.