Skip to content

Posts from the ‘Data Gaurd’ Category

Jun 30

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

Problem:

Data Guard configuration shows error at standby site

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

Solution:

Disable standby database in dgmgrl and Enable again. it solves problem after few minutes when log archive gap filled.

Demo:

-bash-4.2$ dgmgrl
DGMGRL> connect sys/a@db1
Connected to "db1"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - dg_db1_config

  Protection Mode: MaxPerformance
  Members:
  db1    - Primary database
    Error: ORA-16778: redo transport error for one or more members

    stadby_db - Physical standby database
      Warning: ORA-16857: member disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 2 seconds ago)

DGMGRL> show database db1

Database - db1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    db1_1
    db1_2
      Error: ORA-16737: the redo transport service for member "stadby_db" has an error

Database Status:
ERROR

DGMGRL> show database stadby_db

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 175714 seconds ago)
  Apply Lag:          0 seconds (computed 175714 seconds ago)
  Average Apply Rate: 188.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

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

Database Status:
WARNING

DGMGRL> disable database stadby_db;
Disabled.
DGMGRL> enable database stadby_db;
Enabled.
DGMGRL> show database stadby_db;

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      1 day(s) 14 hours 47 minutes 10 seconds (computed 0 seconds ago)
  Apply Lag:          1 day(s) 18 hours 16 minutes 17 seconds (computed 0 seconds ago)
  Average Apply Rate: 189.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

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

Database Status:
WARNING


DGMGRL> show database stadby_db;

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 254.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - dg_db1_config

  Protection Mode: MaxPerformance
  Members:
  db1    - Primary database
    stadby_db - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 52 seconds ago)

DGMGRL>

May 13

ORA-16810: multiple errors or warnings detected for the member / ORA-16766: Redo Apply is stopped / ORA-16853: apply lag has exceeded specified threshold

Error:

ORA-16810: multiple errors or warnings detected for the member  / ORA-16766: Redo Apply is stopped / ORA-16853: apply lag has exceeded specified threshold

Cause: Redo apply stopped on standby

Solution: Disable and Enable standby in dgmgrl

Example:

DGMGRL> show configuration

Configuration – dg_prmy_config

Protection Mode: MaxPerformance
Members:
prmy – Primary database
stby – Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 51 seconds ago)

DGMGRL>
DGMGRL> show database stby

Database – stby

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 13 hours 58 minutes 18 seconds (computed 1 second ago)
Average Apply Rate: 1.06 MByte/s
Real Time Query: OFF
Instance(s):
stby_1 (apply instance)
stby_2

Database Error(s):
ORA-16766: Redo Apply is stopped

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

Database Status:
ERROR

DGMGRL> disable database stby
Disabled.
DGMGRL>
DGMGRL> enable database stby
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration – dg_prmy_config

Protection Mode: MaxPerformance
Members:
prmy – Primary database
stby – Physical standby database
Warning: ORA-16853: apply lag has exceeded specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
WARNING (status updated 13 seconds ago)

DGMGRL>

DGMGRL>
DGMGRL> show database stby

Database – stby

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 second (computed 0 seconds ago)
Average Apply Rate: 24.06 MByte/s
Real Time Query: OFF
Instance(s):
stby_1 (apply instance)
stby_2

Database Status:
SUCCESS

DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration – dg_prmy_config

Protection Mode: MaxPerformance
Members:
prmy – Primary database
stby – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 30 seconds ago)

DGMGRL>

 

Aug 7

ORA-16792: configurable property value is inconsistent with member setting

Problem:

ORA-16792: configurable property value is inconsistent with member setting

DGMGRL> show database stby

Database – stby

Role: PHYSICAL STANDBY
Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 2.00 KByte/s
Real Time Query: OFF
Instance(s):
rmnpcmdn_1
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the member setting
Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the member setting
Warning: ORA-16675: database instance restart required for property value modification to take effect
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the member setting

Database Status:
WARNING

Solution:

modify property using sqlplus or dgmgrl

DGMGRL> show database stby ‘InconsistentProperties’;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
rmnpcmdn_1 ArchiveLagTarget 0 0
rmnpcmdn_1 LogArchiveMaxProcesses 4 4
rmnpcmdn_1 LogArchiveMinSucceedDest 1 1
rmnpcmdn_1 DataGuardSyncLatency 0 0
rmnpcmdn_1 LogArchiveTrace 0 (missing) 0
rmnpcmdn_1 LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf

DGMGRL> edit database stby set property ‘LogArchiveTrace’=0;

Property “LogArchiveTrace” updated

DGMGRL> edit database stby set property ‘ArchiveLagTarget’=0;
Property “ArchiveLagTarget” updated
DGMGRL> edit database stby set property ‘LogArchiveMaxProcesses’=4;
Property “LogArchiveMaxProcesses” updated
DGMGRL> edit database stby set property ‘LogArchiveMinSucceedDest’=1;
Property “LogArchiveMinSucceedDest” updated
DGMGRL> edit database stby set property ‘DataGuardSyncLatency’=0;
Property “DataGuardSyncLatency” updated

DGMGRL> edit database stby set property ‘LogArchiveFormat’=’%t_%s_%r.dbf’;
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property “LogArchiveFormat” updated
DGMGRL> show database stby ‘InconsistentProperties’;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

DGMGRL> show configuration;

Configuration – dg_prmy_config

Protection Mode: MaxPerformance
Members:
prmy – Primary database
stby – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 14 seconds ago)

Aug 7

ORA-16664: unable to receive the result from a member DGM-17016: failed to retrieve status for database “stby”

Problem:

DGMGRL> SHOW DATABASE stby;

Database Status:
DGM-17016: failed to retrieve status for database “stby”
ORA-16664: unable to receive the result from a member

DGMGRL> show configuration;

prmy – Primary database
Error: ORA-1017: invalid username/password; logon denied

Solution:

make password file same on both primary and standby

orapwd file=’+oradata1′ dbuniquename=prmy password=abc ignorecase=Y format=12 force=yes