Oracle 12c

ORA-16857: member disconnected from redo source for longer than specified threshold

In one of my dataguard environment I got the Error: ORA-16857: member disconnected from redo source for longer than specified threshold

DGMGRL> show database dev12cdr;

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      1 minute 39 seconds (computed 1 second ago)
  Apply Lag:          3 minutes 51 seconds (computed 1 second ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dev12c

  Database Warning(s):
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

DGMGRL>

Solution 1:

The size of Online Redo logs (ORLs) and Standby Redo Logs(SRLs) are different on both primary and standby databases

On Standy:

SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         2          1             200
         3          1             200

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

    GROUP#    THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
         8          1              50 UNASSIGNED
         9          1              50 UNASSIGNED
        10          1              50 UNASSIGNED
        11          1              50 UNASSIGNED

On Primary:

SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         3          1             200
         2          1             200

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

    GROUP#    THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
         8          1              50 UNASSIGNED
         9          1              50 UNASSIGNED
        10          1              50 UNASSIGNED
        11          1              50 UNASSIGNED

Drop all standby redologs from both primary and standby and recreate with the same size.

On Primary:

--add the SRLs with the same size as ORLs
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;

--drop the old SRLs
SQL> alter database drop standby logfile group 8;
SQL> alter database drop standby logfile group 9;
SQL> alter database drop standby logfile group 10;
alter database drop standby logfile group 11;

On Standby:

--stop the managed recovery
alter database recover managed standby database cancel;

--add the SRLs with the same size as ORLs
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;

--drop the old SRLs
SQL> alter database drop standby logfile group 8;
SQL> alter database drop standby logfile group 9;
SQL> alter database drop standby logfile group 10;
alter database drop standby logfile group 11;

--Start the managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Solution 2:

Modify the value of TransportDisconnectedThreshold, if  a  delay in the redo transport is expected.

Default value is 30 Seconds

EDIT DATABASE dev12c SET PROPERTY TransportDisconnectedThreshold='150';

After making the changes check the dataguard status

DGMGRL> show database dev12cdr;

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      2 minutes 39 seconds (computed 16 seconds ago)
  Apply Lag:          4 minutes 51 seconds (computed 16 seconds ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dev12c

Database Status:
SUCCESS

DGMGRL>