【oracle】11g dg搭建报错：ORA-16664、ORA-16714、ORA-16810解决|IT运维中文博客

【oracle】11g dg搭建报错：ORA-16664、ORA-16714、ORA-16810解决

时间:2015-03-02 07:03:22 作者:beebol 标签: dg dgmgrl oracle standby 分类: Oracle

最近在弄11g的dg时，遇到如下问题，记录下。首先在主上查看报如下错误：

DGMGRL> show configuration;

Configuration - queue_zs_dg

  Protection Mode: MaxPerformance
  Databases:
    queuedb   - Primary database
    queuedb02 - Physical standby database
      Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> show database verbose queuedb02; 

Database - queuedb02

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    Queuedb

  Properties:
    DGConnectIdentifier             = 'queuedb_zs02'
    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               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'Queuedb'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Queuedb_ZS02.cyou.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=QUEUEDB02_DGMGRL)(INSTANCE_NAME=Queuedb)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DGM-17016: failed to retrieve status for database "queuedb02"ORA-16664: unable to receive the result from a database
ORA-16664: unable to receive the result from a database

alert日志狂刷：

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Queuedb_ZS.cyou.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=queuedb_DGB)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=Queuedb_ZS02.cyou.com)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 02-MAR-2015 18:27:06
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564

TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0nt OS err code: 0
    nt OS err code: 0

tnsping 都是通的,为什么会报这个错呢？到standby中查看database信息后，报如下错，primary和standby的报错信息是不一样的。

DGMGRL> show configuration;

Configuration - queue_zs_dg

  Protection Mode: MaxPerformance
  Databases:
    queuedb   - Primary database
    queuedb02 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> show database verbose queuedb02;   

Database - queuedb02

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    Queuedb
      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
      Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

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

  Properties:
    DGConnectIdentifier             = 'queuedb_zs02'
    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               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'Queuedb'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Queuedb_ZS02.cyou.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=QUEUEDB02_DGMGRL)(INSTANCE_NAME=Queuedb)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:ERROR
ERROR

看到报这个错：Error: ORA-16810: multiple errors or warnings detected for the database show database verbose里有5个warnning：

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
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database settingWarning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

需要重新设置下这些属性值：

edit database queuedb02 set property ArchiveLagTarget='0';
edit database queuedb02 set property LogArchiveMaxProcesses='4';
edit database queuedb02 set property LogArchiveMinSucceedDest='1';
edit database queuedb02 set property LogArchiveTrace='0';edit database queuedb02 set property LogArchiveFormat='%t_%s_%r.dbf';
edit database queuedb02 set property LogArchiveFormat='%t_%s_%r.dbf';

原因是由于：

ORA-16714:	the value of property string is inconsistent with the database setting
Cause:	The value of the specified configuration property is inconsistent with database in-memory settings or server parameter file settings. This may be caused by changing an initialization parameter that corresponds to a configuration property.
Action:	Query property InconsistentProperties on the database to determine the inconsistent values. Reset the property to make it consistent with the database setting.

然后再enable database 就可以了。之前报的Error: ORA-16664: unable to receive the result from a database这个问题也是这个属性的问题导致。

DGMGRL> show configuration;

Configuration - queue_zs_dg

  Protection Mode: MaxPerformance
  Databases:
    queuedb   - Primary database
    queuedb02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:SUCCESS
SUCCESS

时间:2015-03-02 07:03:22 作者:beebol 标签: dg dgmgrl oracle standby 分类: Oracle