When Opening Database - 'ORA-01113: file 1 needs media recovery '

Hello All,

After restore and recovery is complete ,trying to open database with resetlogs option showed the following error message

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oracle/UPG/apps/apps_st/db/system01.dbf' was restored from an oldbackup

I have verified the time and the scn number until which I ran to restore, everything was correct but still unable to open the database.I tried applying archive logs and then to open failed again.

Research

I have issued the following command to recover 'recover database using backup controlfile' .

After some research and googling I issued the following command to open database

sqlplus > recover database using backup controlfile until cancel;

When prompted issued CANCEL.Now tried to open the database with resetlogs and database opened without any issues.

Session Details

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 370577110 generated at 06/12/2010 16:19:22 needed for thread
1
ORA-00289: suggestion :
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2671_699752349.arc
ORA-00280: change 370577110 for thread 1 is in sequence #2671


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2671_699752349.arc
ORA-00279: change 370578253 generated at 06/12/2010 16:27:55 needed for thread
1
ORA-00289: suggestion :
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2672_699752349.arc
ORA-00280: change 370578253 for thread 1 is in sequence #2672
ORA-00278: log file
'/u01/oracle/UPG/db/apps_st/arch/UPG_1_2671_699752349.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2672_699752349.arc
ORA-00279: change 370610644 generated at 06/12/2010 18:27:47 needed for thread
1
ORA-00289: suggestion :
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2673_699752349.arc
ORA-00280: change 370610644 for thread 1 is in sequence #2673
ORA-00278: log file
'/u01/oracle/UPG/db/apps_st/arch/UPG_1_2672_699752349.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE
SYSTEM


- Hope this helps when performing simple recovery to a different server.