Thursday, April 4, 2013

DataGuard - Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting

Problem
Warning: ORA-16714 shown in "Show database tprisby" dgmgrl command

Action
Find the reason for warnings

Step 1

DGMGRL> show database verbose tprisby

Database - tprisby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    tprisby1 (apply instance)
    tprisby2
      Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting


  Properties:
    DGConnectIdentifier             = 'tprisby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+PSSDEV, +CSPDEVD01, +PSSFLASH, +CSPDEVF1'
    LogFileNameConvert              = '+PSSDEV, +CSPDEVD01, +PSSFLASH, +CSPDEVF1'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
WARNING


Step 2 : Find which properties are Inconsistent

DGMGRL> show database tpri InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE 

--> No Inconsistent properties found 

DGMGRL> show database tprisby InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
        tprisby2     ArchiveLagTarget                    0                                         0
        tprisby2 LogArchiveMaxProcesses                    4                                         4
        tprisby2 LogArchiveMinSucceedDest                    1                                         1 

--> Here you go. 3 Inconsistent properties found.

Solution :
SQL(tprisby2)>alter system set log_archive_max_processes=4 scope=both sid='*';

System altered.

SQL(tprisby2)>alter system set archive_lag_target=0 scope=both sid='*';

System altered.

SQL(tprisby2)>alter system set log_archive_min_succeed_dest=1 scope=both sid='*';

System altered.





Verify 

DGMGRL> show database tprisby
Database - tprisby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       6 minutes 39 seconds
  Real Time Query: OFF
  Instance(s):
    tprisby1 (apply instance)
    tprisby2

Database Status:
SUCCESS




1 comment:

  1. Hello Ajay,
    I had a similar issue today, and with the help of your very useful post was able to solve the problem.
    Khalid

    ReplyDelete