Monday, July 13, 2015

ORA-10458: standby database requires recovery

Cause: Standby database recovery not completed. In my case, standby database was stopped cause of power failure, for that archive log file was not transmit to standby server. I have found log gap in my primary database by using the following query :

SELECT INST_ID,
     SEQUENCE#,
     TO_CHAR (COMPLETION_TIME, 'DD-MM-YYYY HH24:MI:SS') "Time",
     NAME,
     STATUS,
     ARCHIVED,
     STANDBY_DEST,
     APPLIED
FROM GV$ARCHIVED_LOG
WHERE TO_CHAR (COMPLETION_TIME, 'DD-MM-YYYY') =TO_CHAR (SYSDATE, 'DD-MM-YYYY')
ORDER BY SEQUENCE#;

Solution: I have switch log file in my primary database, standby server receives archive log file and applies all of the archive logs. If the problem still exists Resolve GAP By Apply RMAN Incremental Backup.

The primary database is not able to shift redo data to standby database for that standby database are required to recover. You need to identify this reason. The following query helps you why primary DB is not shifting the redo.

You may check the following two post also.

http://rajiboracle.blogspot.com/2017/05/resolve-standby-gaps-by-applying.html

http://rajiboracle.blogspot.com/2015/09/resolve-archivelog-gaps-in-data-guard.html

col DESTINATION format a35
col ERROR format a65
set lines 130
set pages 100
SELECT INST_ID, DESTINATION, ERROR FROM GV$ARCHIVE_DEST

WHERE DESTINATION IS NOT NULL;



[oracle@drdb1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 13 13:59:50 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2228024 bytes
Variable Size             805306568 bytes
Database Buffers          469762048 bytes
Redo Buffers                8769536 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/sblcbsdr/system01.dbf'


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/sblcbsdr/system01.dbf'

In Primary
---------------------------------------
[oracle@dcdb01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 13 14:06:08 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

In Standby 
---------------------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1585093

14 comments:

  1. Thanks for this post. I knew I was missing something obvious.

    ReplyDelete
  2. It did not work for me.

    ReplyDelete
    Replies
    1. Dear,
      At first you have to resolve all redo gap, Check the alert log file find out the last archive log sequence which applied in your standby database from the alert log, If archive log in your primary database exists please follow this link (http://rajiboracle.blogspot.com/2015/09/resolve-archivelog-gaps-in-data-guard.html), If it is not exist please apply RMAN backup (using scn) from your primary to standby database and resolve the gap, open your standby database.


      Thanks you for your valuable comments.

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi, Thanks for this post. It worked for me.

    ReplyDelete
  5. your way of explanation is very unique thanks for sharing oracle fusion financials training

    ReplyDelete
  6. Hi Rajib Pradhan,

    I don't have archive log GAP but still unable to start standby database with read-only mode. error issue is same and requiring sysaux data file.

    ReplyDelete
    Replies
    1. You are going to create new standby db or your current production standby database ?

      Delete
  7. Saved me on Christmas break eve! I didn't want to have to spend it recreating this bugger! Thanks,

    ReplyDelete
  8. as Buyantugs Luu says I also have the same issue there is no gap, I created it through adcfgclone, perhaps this one is the problem, but I must need to clone the standby

    ReplyDelete
  9. Hello,
    I got my mistake, during cloning if you clone through perl adcfgclone.pl dbTier instead of perl adcfgclone.pl dbTechStack, you have to face
    ERROR at line 1:
    ORA-10458: standby database requires recovery
    ORA-01196: file 1 is inconsistent due to a failed media recovery session
    ORA-01110: data file 1: '/u01/app/oracle/oradata/sblcbsdr/system01.dbf'
    so use perl adcfgclone.pl dbTechStack to get rid

    ReplyDelete
  10. Thank you for the post. Resolved my issue.

    ReplyDelete