June 2021

I got the below error in one of my standby environment. Error – ORA-16853: apply lag has exceeded specified threshold

DGMGRL> show database dev12cdr

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      4 minutes 37 seconds (computed 32 seconds ago)
  Apply Lag:          4 minutes 37 seconds (computed 32 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    dev12c

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
WARNING

DGMGRL>

Cause: This is due the Apply Lag Threshold. Check and modify the dataguard configuration parameter ApplyLagThreshold.

Solution: Modify the ApplyLagThreshold parameter in database configuration

show database verbose dev12cdr;
edit database dev12cdr set property ApplyLagThreshold=3600;

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>

On standby side, you may encountered the Error “ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed” while restoring a standby database from using duplicate command

Finished recover at 17-JUL-21
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/oradata/dev12c/redo01.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/oradata/dev12c/redo02.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/u01/oradata/dev12c/redo03.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 8 thread 1: '/u01/oradata/dev12c/std_redo01.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 9 thread 1: '/u01/oradata/dev12c/std_redo02.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 10 thread 1: '/u01/oradata/dev12c/std_redo03.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 11 thread 1: '/u01/oradata/dev12c/std_redo04.log'

RMAN-05535: warning: All redo log files were not defined properly.
Finished Duplicate Db at 17-JUL-21

Solution:

Add the below parameter in the pfile file and then re-run the duplicate database command.

log_file_name_convert='dummy','dummy'

Re-run the Duplciate database command

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Output:

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 17-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived
.
.
<output truncated>
.
.
archived log file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_17_1066823600.dbf thread=1 sequence=17
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-JUL-21
Finished Duplicate Db at 17-JUL-21

RMAN>

Check the redo logs are created:

[oracle@ol7-db-dr dev12c]$ pwd
/u01/oradata/dev12c
[oracle@ol7-db-dr dev12c]$ ls -ltrh *.log
-rw-r-----. 1 oracle oinstall 201M Jul 17 12:09 redo01.log
-rw-r-----. 1 oracle oinstall 201M Jul 17 12:09 redo02.log
-rw-r-----. 1 oracle oinstall 201M Jul 17 12:09 redo03.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo01.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo02.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo03.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo04.log
[oracle@ol7-db-dr dev12c]$

Hope this helps…