Tag: ORA-01110

ORA-01157 When Standby Database Open Read Only

The following errors occur when trying to open a standby database read only:

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA2/TESTDB/DATAFILE/system.3203.1186181325'

When checking ASM file for SYSTEM tablespace, the file exists but with different name:

ASMCMD> ls -lt +DATA2/TESTDB/DATAFILE/system*
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   NOV 30 10:00:00  Y    SYSTEM.577.1186394617

It seems incantation issue from database control file.

SOLUTION

List incarnation:

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TESTDB   2969509608       PARENT  1          17-APR-19
2       2       TESTDB   2969509608       PARENT  1920977    27-NOV-24
3       3       TESTDB   2969509608       CURRENT 2848922    29-NOV-24

Rest incarnation:

RMAN> RESET DATABASE TO INCARNATION 2;

database reset to incarnation 2

RMAN>  list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TESTDB   2969509608       PARENT  1          17-APR-19
2       2       TESTDB   2969509608       CURRENT 1920977    27-NOV-24
3       3       TESTDB   2969509608       ORPHAN  2848922    29-NOV-24

Then standby database open read only successfully.

SQL>  alter database open read only;

Database altered.

ORA-19909: datafile 1 belongs to an orphan incarnation

One database might have multiple incarnations.

The following errors occurred in a standby database alert log :

MRP0: Detected orphaned datafiles!
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA/PROD/datafile/system.238.875096219

Check standby database incarnation :

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID    STATUS Reset SCN    Reset Time
------ ------- ------ ---------- ----- ------------ ----------------
1      1       PROD   3779924890 PARENT 1           08-01-2015 10:00
2      2       PROD   3779924890 CURRENT 2620491565 01-02-2016 12:01

Reset database to old incarnation :

RMAN> reset database to incarnation 1; 

database reset to incarnation 1 


RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name DB ID      STATUS  Reset      SCN Reset Time
------- ------- ------- ---------- ------- ---------- ---------------
1       1       PROD    3779924890 CURRENT 1          08-01-2015 10:00
2       2       PROD    3779924890 ORPHAN  2620491565 01-02-2016 12:01

Then everything is working fine.