Oracle 11g ORA-01190: control file or data file 1 is from before the last RESETLOGS

Question

I am running Oracle 11GR2, Enterprise Edition Release 11.2.0.3.0. I got one of my Test DBs corrupted today. Luckily, I have 2 weeks of backups in the Net Backup server, so I thought, "This is easy, I just restore from the latest backup and I should be good in about 2 hours!"

But I kept getting this Oracle ORA-01190 error. So I changed my restore time about 30 minutes earlier, than I got the same error.

  SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+DATA/t1/datafile/system_01.dbf'

After I made the restore time changes, I got this error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/t1/datafile/system_01.dbf'

This is my rman restore scrpt

run
 {
 allocate channel D1 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D2 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D3 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D4 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 set until time "to_date('16-MAR-2016 15:20:00','DD-MON-YYYY HH24:MI:SS')";
 restore controlfile;
 restore database;
 alter database mount;
 recover database;
 alter database open resetlogs;
 }

Shall I start to use SCN method to restore my database? Is there a clear indicator for me to see the time I can surely restore my database from its backup files?

Thank you so much!

Answer 1

Since you've done an open resetlogs, you've changed the incarnation and are now trying to restore to a previous incarnation. From the Backup and Recovery User's Guide:

The procedure for DBPITR within the current incarnation is different from DBPITR to an SCN in a noncurrent incarnation. In the latter case, you must explicitly execute the RESET DATABASE to reset the database to the incarnation that was current at the target SCN. Also, you must restore a control file from the database incarnation containing the target SCN

After the above paragraph the manual goes on to detail the procedure, step by step.

It doesn't matter if you specify 'to time' or 'to scn'. If you specify 'to time', rman will internally convert that to an SCN.

.

Answer 2

I always believe that the output given by oerr utility helps alot to resolve the problem.

Oerr: Oerr is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files.

ORA-01190: control file or data file string is from before the last RESETLOGS

Cause: Attempting to use a data file when the log reset information in the file does not match the control file. Either the data file or the control file is a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS.
Action: Restore file from a more recent backup.

This is the important reason for why we should take backup immediately after opening database with resetlog.

ORA-01152: file string was not restored from a sufficiently old backup

Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.

Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery.

Here is link to Performing Flashback and Database Point-in-Time Recovery

Answer 3

I got one database restored from NetBackup and it is working great. I missed post an important info was that the 2nd Test DB shared a same server. I got an error when trying to open the 2nd DB, the error was something like

Your database name is T1, but the control file db name is T2.

Based on the above info, I concluded that I need to set the restore time the date before a data corruption happened between these two databases. I did not know exactly how one DB got corrupted by the 2nd DB sharing one server.

1. set the restore time before T1 db data corruption.
2. Wipe out all ASM data is a nice way to say: Let’s restart it all over again.
3. Reset incarnation resolved one confusing message about cannot find control file when started clean after ASM data was all gone.