ora-01610: recovery using the BACKUP CONTROLFILE option must be done, but how does Oracle knows where does it see?

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

Am trying to recover a database after a controlfile lost and restored from the backup. But How does Oracle knows that we are using a backup control file where does it look after to get this information?

SQL> select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where name like 'users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';

SCN location        NAME                             CHECKPOINT_CHANGE#
------------------- ------------------------------   ------------------
controlfile         SYSTEM checkpoint                            333765
file header         /u02/oradata/OD2/users01.dbf                 355253
file in controlfile /u02/oradata/OD2/users01.dbf                 333765

If you see above

• checkpoint_change# from v$database indicated 333765 is last system checkpoint noted in control file
• checkpoint_change# from v$datafile indicates that 355353 is the last system checkpoint or last scn it has updated.
• checkpoint_change# from v$datafile_header is matched with control file.

Concept:-

Oracle maintains the last system check point number in control file and can be visible at

SQL> select checkpoint_change# from v$database;

You might be wondering why Oracle needs to maintain the last system checkpoint value in the control file as well as checkpoint SCNs in the control file for each datafile (as used in the previous example). There are two reasons for this.

The first is that you might have read-only tablespaces in your database or your datafiles experiencing failures. In this case, the database checkpoint SCN increases, and the checkpoint SCN for the datafiles in the read-only tablespace of offline tablespaces remains frozen in the control file.

The second reason for the maintenance of multiple checkpoint SCNs in the control file is that you might not have a current control file available at recovery time. In this case, you need to restore an earlier control file before you can perform a recovery. The system checkpoint in the control file may indicate an earlier change than the start SCN in the datafile headers.

For us its the second reason, Now if we want to recover this database using a old control file, but the as datafile changes is far ahead from the controlfile and datafile headers, we should use “using backup controlfile” option in order to get the controlfile and datafile header sync to datafile checkpoint number and get recovered.

-Thanks

Geek DBA