dataguard error

[Former Member]

Hello ,

We have dataguard error on a system.

-----------------

DGMGRL> show configuration

Configuration - sid

  Protection Mode: MaxAvailability

  Databases:

    sid_primary - Primary database

      Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

    sid_standby - (*) Physical standby database

      Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

Fast-Start Failover: ENABLED

Configuration Status:

ERROR

-------------

DGMGRL> show database sid_host

Database -

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   8 days 8 hours 44 minutes 12 seconds (computed 0 seconds ago)

  Apply Lag:       (unknown)

  Apply Rate:      (unknown)

  Real Time Query: OFF

  Instance(s):

    SID

  Database Error(s):

    ORA-16766: Redo Apply is stopped

  Database Warning(s):

    ORA-16817: unsynchronized fast-start failover configuration

Database Status:

ERROR

--------------------------

2.)  On standby :

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

   THREAD# LAST_APPLIED_LOG

---------- ----------------

         1            48210

Primary :

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

   THREAD# LAST_APPLIED_LOG

---------- ----------------

         1            49043

-----------------------------------------------------------------------

3.) on alertlog _ standby :

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Errors in file /oracle/SID/saptrace/diag/rdbms/sid_standby/SID/trace/SID_pr09_846.trc:

ORA-00600: internal error code, arguments: [3020], [31], [234430], [130257854], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 31, block# 234430, file offset is 1920450560 bytes)

ORA-10564: tablespace PSAPUNDO

ORA-01110: data file 31: '/oracle/SID/sapdata1/undo_4/undo.data4'

ORA-10560: block type 'KTU UNDO BLOCK'

Errors in file /oracle/SID/saptrace/diag/rdbms/sid_nstandby/SID/trace/SID_pr03_834.trc  (incident=16331):

ORA-00600: internal error code, arguments: [3020], [60], [686250], [252344490], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 60, block# 686250, file offset is 1326792704 bytes)

ORA-10564: tablespace PSAPUNDO

ORA-01110: data file 60: '/oracle/SID/sapdata3/undo_7/undo.data7'

ORA-10560: block type 'KTU UNDO BLOCK'

-------------

There are many such files not applied on primary :

SEQUENCE# APPLIED

---------- ---------

     47924 YES

     47925 NO

     47925 YES

     47926 NO

     47926 YES

     47927 NO

     47927 YES

     47928 NO

     47928 YES

     47929 NO

     47929 YES

SEQUENCE# APPLIED

---------- ---------

     47930 NO

     47930 YES

     47931 NO

     47931 YES

     47932 NO

     47932 YES

     47933 NO

     47933 YES

     47934 NO

     47934 YES

     47935 NO

--------------------

how to proceed in making standby consistent?

Thanks,

Uday

[stefan_koehler]

Hi Uday,

the root cause of your issue is absolutely obvious.

ORA-00600: internal error code, arguments: [3020], [31], [234430], [130257854], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 31, block# 234430, file offset is 1920450560 bytes)

MOS ID #1265884.1 describes the reasons for this issue (and how to fix it). However it is important to understand that "in the majority of cases, standby stuck recovery errors indicate a corruption of the primary database. No errors may have been reported on the primary. The ORA-600 [3020] stuck recovery error could occur on the Standby database for several reasons including: a lost write on the Primary, a lost write on the Standby, missing redo, or logical corruption on the primary resulting in an incomplete redo chain."

Regards

Stefan

[former_member182657]

Hi Uday,

Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

Could you share result for query

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024/1024 ,status from v$standby_log;

Regards,

[Former Member]

Hi Gaurav,

Thanks for the reply.

Please find the output.

----------------

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024/1024 ,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# BYTES/1024/1024/1024 STATUS

---------- ---------- ---------- -------------------- ----------

         1          1          0           .146484375 UNASSIGNED

         2          1      49047           .146484375 ACTIVE

         3          1          0           .146484375 UNASSIGNED

         4          1          0           .146484375 UNASSIGNED

        11          1          0           .146484375 UNASSIGNED

        12          1          0           .146484375 UNASSIGNED

        13          1          0           .146484375 UNASSIGNED

7 rows selected.

----------------------

Thanks,

Uday

[former_member182657]

Hi Uday,

Could you try to stop the MRP in standby system with query

alter database recover managed standby database cancel ;

& can proceed to drop the standby redo logs with help of query

e.g  ALTER DATABASE DROP STANDBY LOGFILE GROUP 1;

      ALTER DATABASE DROP STANDBY LOGFILE GROUP 3;

      ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

      ....... up to 13th.

After that recreate all with reuse option,e.g

alter database add standby logfile group 1 '/?.log'  size ? reuse;

Here you need to opt the location & size to create the the above said standby log files. After the procedure share the result for dgmgrl status & show configuration verbose.

Regards,