Mir Sayeed Hassan – Oracle Blog

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 {
recover database;
}
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: {=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: {=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 & verified in our test env====