Oracle 11g Recovery – Resolving ORA-01190 Control File Error

oracleoracle-11g-r2recoveryrestorerman

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!

Best Answer

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.

.