Category: Oracle

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.

    Opatch or Opatchauto takes long time Due to High Number of Inactive Patches


    “opatch lsinventory -inactive” shows many inactive patches.
    Large number of inactive patches can slow down the opatch apply process.
    Issue still reproduce after executing “opatch util deleteinactivepatches” in this case opatch.properties:retain was set to retain=2.
    There is also known issue with certain oracle homes where there are more than 1 inactive PSU or RU, a user needs to run deleteinactivepatches multiple times.

    SOLUTION

    Execute deleteinactivepatches without setting opatch.properties to retain=2 (default is retain=1)

    $ cat $ORACLE_HOME/dbhome_1/OPatch/config/opatch.properties

OPATCH_HEAP_MEMORY=3072
PS_OBFUSCATION=true
RETAIN_INACTIVE_PATCHES=1
SKIP_FUSER_WARNINGS=true

$ opatch util deleteinactivepatches -silent

    Display inactive patches:

    $ opatch lsinventory -inactive

    Repeat previous steps until only 1 inactive patch (for each product) is left.

    “ORA-16642: DB_UNIQUE_NAME mismatch” while adding a Standby Database into DG Broker

    The following error occurs while trying to add standby database into data guard broker.

    DGMGRL>  ADD DATABASE TESTDB  AS CONNECT IDENTIFIER is TESTDB_DR MAINTAINED AS PHYSICAL;
    Error: ORA-16642: DB_UNIQUE_NAME mismatch

    REASON

    Here primary and standby database DB_UNIQUE_NAME is same as “TESTDB”. Make sure DB_UNIQUE_NAME for primary and all standby databases are different.

    How to Manually Apply OCW Release Update onto Database Home

    Found OCW patch was not applied onto database home after applied 19.24 RU, so manual applying is required.

    $ORACLE_HOME/OPatch/opatch lspatches
    ...
    ..
    .
    30159782;OCW Interim patch for 30159782

    For 19.24, the OCW patch id should be “36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)”. Let’s manually apply it.

    $ cd 36582629/36587798
    $ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch apply
    Oracle Interim Patch Installer version 12.2.0.1.44
    Copyright (c) 2024, Oracle Corporation.  All rights reserved.


    Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
    OPatch version    : 12.2.0.1.44
    OUI version       : 12.2.0.7.0
    Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-11-26_11-01-26AM_1.log

    Verifying environment and performing prerequisite checks...

    --------------------------------------------------------------------------------
    Start OOP by Prereq process.
    Launch OOP...

    Oracle Interim Patch Installer version 12.2.0.1.44
    Copyright (c) 2024, Oracle Corporation.  All rights reserved.


    Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
    OPatch version    : 12.2.0.1.44
    OUI version       : 12.2.0.7.0
    Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-11-26_11-01-58AM_1.log

    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   36587798

    Do you want to proceed? [y|n]
    y
    User Responded with: Y
    All checks passed.

    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files...
    Applying interim patch '36587798' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'
    ApplySession: Optional component(s) [ oracle.has.crs, 19.0.0.0.0 ] , [ oracle.rhp.crs, 19.0.0.0.0 ] , [ oracle.xag, 19.0.0.0.0 ] , [ oracle.has.cvu, 19.0.0.0.0 ] , [ oracle.has.crs.cvu, 19.0.0.0.0 ]  not present in the Oracle Home or a higher version is found.

    Patching component oracle.rdbms, 19.0.0.0.0...

    Patching component oracle.rhp.common, 19.0.0.0.0...

    Patching component oracle.has.common, 19.0.0.0.0...

    Patching component oracle.has.common.cvu, 19.0.0.0.0...

    Patching component oracle.rhp.db, 19.0.0.0.0...

    Patching component oracle.has.db, 19.0.0.0.0...

    Patching component oracle.has.db.cvu, 19.0.0.0.0...

    Patching component oracle.has.rsf, 19.0.0.0.0...
    Patch 36587798 successfully applied.
    Sub-set patch [30159782] has become inactive due to the application of a super-set patch [36587798].
    Please refer to Doc ID 2161861.1 for any possible further required actions.
    Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-11-26_11-01-58AM_1.log

    OPatch succeeded.
    $ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch lspatches

    ...
    ..
    .
    36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)