Oracle dataguard errors : ORA-16853 / ORA-16853:

 

I was getting below error when I ran the 'show configuration' command from the Datagaurd broker.

DGMGRL>  show configuration;

Configuration - renodbdg
  Protection Mode: MaxPerformance
  Members:
  renodbpr - Primary database
    renodbdr - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member
Fast-Start Failover:  Disabled
Configuration Status:
WARNING   
DGMGRL> 

I ran the 'show database ' command for the standby database and found below errors. 

DGMGRL>  show database renodbdr
Database - renodbdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      1 hour(s) 14 minutes 35 seconds (computed 0 seconds ago)
  Apply Lag:          1 hour(s) 15 minutes (computed 0 seconds ago)
  Average Apply Rate: 2.83 MByte/s
  Real Time Query:    ON
  Instance(s):
    renodbdr1 (apply instance)
    renodbdr2
  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold
    ORA-16855: transport lag has exceeded specified threshold

Database Status:
WARNING
DGMGRL> 

Cause : The standby database is RAC database and I see that the SRL logs were only created for one instance.

As we can see from the above, all the Standby redo logs were created for the first instance. 

Our Prim,ary database has 2 redo logs for each node. I will drop the last three standby redo logs from the standby database and will re-create them for the thread 2.

1. Stop the MRP process

SQL> ALTER DATABASE RECOVER  managed standby database cancel;
Database altered.

2. Drop the unwanted standby redo logs.

SQL> alter database drop standby logfile group 8;
Database altered.
SQL> alter database drop standby logfile group 9;
Database altered.
SQL> alter database drop standby logfile group 10;
Database altered.

3) Add the SRLs for the thread 2.

SQL> alter database add standby logfile thread 2 group 8 size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 9  size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 10  size 200M;
Database altered.

Check the database to see if the SRLs shows correct

4. Start the MRP process

SQL> ALTER DATABASE RECOVER  managed standby database using current logfile disconnect;
Database altered.

5. Check the configuration in the dataguard broker.

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 28 10:51:52 2023Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "renodbpr"
Connected as SYSDG.
DGMGRL> connect sys/welcome;
Connected to "renodbpr"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - renodbdg
  Protection Mode: MaxPerformance
  Members:
  renodbpr - Primary database
    renodbdr - Physical standby database
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 26 seconds ago)

DGMGRL> 

that fixed the issue and the configuration looks good in the Dataguard broker.

hope this helps you if you are seeing the same issue :)

Thanks
Sambaiah Sammeta