When to use “using backup controlfile” in Oracle

Question

I know this is something not new to ask, however, I did not get the satisfactory answer where and where not to use "RECOVER DATABASE USING BACKUP CONTROLFILE" or "RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE".

It will really help if someone can explain with proper examples and scenarios.

Thanks a bunch.

Answer 1

The syntax is quite verbose: If the original/current controlfiles are not available you can resort to using a backup controlfile.

The controlfile contains the layout und state of the database:

• files comprising the database.
• current SCN, the system change number, incremented with each transaction.

The datafiles also store the respective SCN. If the SCN in the controlfile is lower than the SCN in the datafile Oracle knows it is dealing with a backup controlfile.

One scenario where this is used is creating a copy of a productive database for testing. To this purpose you can do the following:

• Restore the controlfile from the backup on the test server. Now the file layout of the database is known (and can be adapted if necessary).
• Restore the data files.
• Start recovering the database (USING BACKUP CONTROLFILE).
• Recovery will consume archived redo logs you will have to provide. Stop by canceling the recovery after a redo log (e.g. the last available one) or by specifying a point in time (PIT recovery).
• Open the database with RESETLOGS option.

Usually the the database is also renamed to avoid confusion.

Another scenario is a recovery due to a datafile restored from a backup. In that case the current controlfile is available and USING BACKUP CONTROLFILE is not needed.