ORA-01207 File is More Recent Than Controlfile - Old Controlfile tips Oracle Database Tips by Donald Burleson

Question:  I have of test a server which recreates my database exactly equal PRD in another server, taking a Full Backup Offline of the original system. I successfully obtained an exact copy of my system PRD with a backup offline, and I included control files, data files, redo logs, etc.  When I try to start the database recovery, I get this error:

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'u01/oracle/app/data/xxx'
ORA-01207: file is more recent than controlfile - old controlfile

Answer:  Let's use the oerr utility to look at the ORA-01207 error:

ORA-01207:  ORA-01207 file is more recent than controlfile - old controlfile

Cause: The control file change sequence number in the datafile is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough.

Action: Use the current control file or do BACKUP CONTROLFILE RECOVERY to make the control file current. Be sure to follow all restrictions on doing a BACKUP CONTROLFILE RECOVERY.

The Oracle documentation notes these restrictions on doing a BACKUP CONTROLFILE RECOVERY:  

The following notes and restrictions apply regardless of whether you use a recovery catalog:

• You must run the RECOVER command after restoring a backup control file, even if no datafiles have been restored.

• You must open the database with the RESETLOGS option after performing either complete or point-in-time recovery with a backup control file.

• If the online redo logs are inaccessible, then you must perform incomplete recovery to an SCN before the earliest SCN in the online redo logs. This limitation is necessary because RMAN does not back up online logs.

• During recovery, RMAN automatically searches for online and archived redo logs that are not recorded in the RMAN repository, and catalogs any that it finds so that it can use them in recovery.  
  
  RMAN attempts to find a valid archived log in any of the current archiving destinations with the current log format. The current format is specified in the initialization parameter file used to start the instance (or all instances in a Real Application Clusters installation). Similarly, RMAN attempts to find the online redo logs by using the filenames as specified in the control file.  
  
  If you changed the archiving destination or format during recovery, or if you added new online log members after the backup of the control file, then RMAN may not be able to automatically catalog a needed online or archived log. In this situation, RMAN reports errors similar to the following:
  
  RMAN-00571:==========================================================
  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============
  RMAN-00571: =========================================================
  RMAN-03002: failure of recover command at 08/29/2001 14:23:09
  RMAN-06054: media recovery requesting unknown log: thread 1 scn 86945

*******************************************************

If this is a production recovery, first open an emergency (Sev 1) Oracle SR (Service Request) on MOSC.

First, how come the controlfile is too old?  That's the root cause, the error is just the symptom.  The controlfile MUST be more recent than the datafiles.  Note:  Check your alert logs to GUARANTEE that no data files were added since the data of the "old" controlfile.

You have several recovery options:

1 - Get the "original" controlfile at the time of the crash and replace it in all replicated places noted in your control_files parameter. Recover database.

2 - If you are POSITIVE that it's right for you, do an "Alter database backup controlfile to trace;" and use the current "old" controlfile to re-create the controlfile. Re-create the database with this controlfile.  Recover database and loose data.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.