Oracle 12c – How to Recreate the Control File

oracle-12c

I have experienced a problem with flashback and the solution is to recreate the control file (the error was ORA-01207).

So to do this I tried

 alter database backup controlfile to trace as '/tmp/trace.sql';

This creates a file that instructs one to either use set one or set two. i.e. with or without resetlogs.

How does one determine what set is the most applicable? Is there any tests that one needs to perform to find out?

Best Answer

From /tmp/trace.sql:

The first set opens the database with the NORESETLOGS option and should be used only if the current versions of all online logs are available. The second set opens the database with the RESETLOGS option and should be used if online logs are unavailable.

If you're only recreating the controlfile you should be able to perform a complete recovery. i.e. without resetlogs. If you are missing some online redo logs or if they are not complete, then it will be a partial recovery, and RESETLOGS will be required.