ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

Error occur while restoring the database from the backup of RMAN

RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/03/2017 16:05:14
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

To resolve this error fallow the below procedure – Hopefully you will succeed

If you’re primary database is flashback & it has the standby – Fallow below procedure

RMAN> run {
2> recover database;
3> }

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database

Disable the flashback

[oracle@afc backup]$ !sq
sqlplus / as sysdba

SQL> alter database flashback off;
Database altered.

Change the standby db setting

SQL> alter database set standby database to maximize performance;
Database altered.

After setting this – Give a try to open the database

[oracle@afc backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Sep 3 16:06:42 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PRIM (DBID=4187434325, not open)
RMAN> alter database open resetlogs;

No luck

using target database control file instead of recovery catalog

RMAN-00571:
RMAN-00569: ===== ERROR MESSAGE STACK FOLLOWS
RMAN-00571:
RMAN-03002: failure of alter db command at 09/03/2017 16:06:51
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 399862341 generated at 08/30/2017 00:30:10 needed for thread1
ORA-00289: suggestion : /oradata/archive_log/1_11051_905677721.dbf
ORA-00280: change 399862341 for thread 1 is in sequence #11051
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'
ORA-01112: media recovery not started

No luck

SQL> recover database using backup controlfile;
ORA-00279: change 399862341 generated at 08/30/2017 00:30:10 needed for thread1
ORA-00289: suggestion : /oradata/archive_log/1_11051_905677721.dbf
ORA-00280: change 399862341 for thread 1 is in sequence #11051
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

No luck

SQL>  alter database open noresetlogs;
 alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

Succeed

Finally it will be resolved if you set the below parameter in pfile or (if the database uses the spfile)

*._ALLOW_RESETLOGS_CORRUPTION = TRUE
*.UNDO_MANAGEMENT = MANUAL

SQL> shu immediate

ORA-01109: database not open
Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 1.5734E+10 bytes
Fixed Size                  2268712 bytes
Variable Size            2415919576 bytes
Database Buffers         1.3288E+10 bytes
Redo Buffers               27828224 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select status from V$instance;

STATUS
------------
OPEN

Hence Tested in my test env !

If there is any clarification, Please reach me 24/7