Day: November 30, 2024

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.

RMAN-06023 Although Backups Are Available 

‘RMAN  RESTORE DATABASE’ command fails with the following errors by using a backup control file:

RMAN-00571: ====================================================       
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ========       
RMAN-00571: ====================================================       
RMAN-03002: failure of restore command at 21/11/2024 11:09:9           
RMAN-06026: some targets not found - aborting restore                  
....
RMAN-06023: no backup or copy of datafile 3 found to restore           
RMAN-06023: no backup or copy of datafile 2 found to restore           
RMAN-06023: no backup or copy of datafile 1 found to restore

If you run ‘list backup of database’, we can see the database backups available.

REASON

The problem here is that there are some files in the Flash Recovery Area that belong to different incarnation than the available backups CURRENT incarnation.

If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.

SOLOTION

If the offending cataloged files are small, we can move those files to a directory outside the Flash Recovery Area. Then we need to restart the whole restore procedure. It’s necessary to restore controlfile again

OR

Another solution is to temporarily disable use of the flash recovery area for the duration of the restore and recovery commands.

    To disable Flash Recovery Area you need to undefine db_recovery_file_dest:

    #*.db_recovery_file_dest='<directory>'
    #*.db_recovery_file_dest_size=<size>

    Bounce database

    Restore controlfile and then restore/recover database commands.

    If there are some backuppieces or archivelogs in the Flash Recovery Area that need to be cataloged, then it will be necessary to catalog them manually with:  CATALOG BACKUPPIECE  or CATALOG ARCHIVELOG commands.