ITPub博客

首页 > 数据库 > Oracle > Data Guard配置Broker解决ORA-16664、ORA-16792

Data Guard配置Broker解决ORA-16664、ORA-16792

原创 Oracle 作者:liupzmin 时间:2016-03-22 17:55:32 0 删除 编辑
给某一个客户安装ADG完毕,配置Broker方便日后的管理与切换,碰到些许问题,以作记录

创建完configuration之后,enable的过程很慢,而且状态出现error

主库查询:

  1. DGMGRL> SHOW CONFIGURATION VERBOSE;
  2. Configuration - anxinconf
  3. Protection Mode: MaxAvailability
  4. Databases:
  5. anxin - Primary database
  6. anxinstd - Physical standby database
  7. Error: ORA-16664: unable to receive the result from a database
  8. Properties:
  9. FastStartFailoverThreshold = '30'
  10. OperationTimeout = '30'
  11. FastStartFailoverLagLimit = '30'
  12. CommunicationTimeout = '180'
  13. ObserverReconnect = '0'
  14. FastStartFailoverAutoReinstate = 'TRUE'
  15. FastStartFailoverPmyShutdown = 'TRUE'
  16. BystandersFollowRoleChange = 'ALL'
  17. ObserverOverride = 'FALSE'
  18. ExternalDestination1 = ''
  19. ExternalDestination2 = ''
  20. PrimaryLostWriteAction = 'CONTINUE'
  21. Fast-Start Failover: DISABLED
  22. Configuration Status:
  23. ERROR
备库查询:

  1. DGMGRL> SHOW CONFIGURATION VERBOSE;
  2. Configuration - anxinconf
  3. Protection Mode: MaxAvailability
  4. Databases:
  5. anxin - Primary database
  6. anxinstd - Physical standby database
  7. Warning: ORA-16792: configurable property value is inconsistent with database setting
  8. Properties:
  9. FastStartFailoverThreshold = '30'
  10. OperationTimeout = '30'
  11. FastStartFailoverLagLimit = '30'
  12. CommunicationTimeout = '180'
  13. ObserverReconnect = '0'
  14. FastStartFailoverAutoReinstate = 'TRUE'
  15. FastStartFailoverPmyShutdown = 'TRUE'
  16. BystandersFollowRoleChange = 'ALL'
  17. ObserverOverride = 'FALSE'
  18. ExternalDestination1 = ''
  19. ExternalDestination2 = ''
  20. PrimaryLostWriteAction = 'CONTINUE'
  21. Fast-Start Failover: DISABLED
  22. Configuration Status:
  23. WARNING
查看备数据库的状态

  1. DGMGRL> show database verbose anxinstd;
  2. Database - anxinstd
  3. Role: PHYSICAL STANDBY
  4. Intended State: APPLY-ON
  5. Transport Lag: 0 seconds (computed 1 second ago)
  6. Apply Lag: 0 seconds (computed 1 second ago)
  7. Apply Rate: 54.00 KByte/s
  8. Real Time Query: ON
  9. Instance(s):
  10. anxinstd
  11. Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
  12. Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
  13. Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
  14. Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
  15. Properties:
  16. DGConnectIdentifier = 'anxinstd'
  17. ObserverConnectIdentifier = ''
  18. LogXptMode = 'SYNC'
  19. DelayMins = '0'
  20. Binding = 'OPTIONAL'
  21. MaxFailure = '0'
  22. MaxConnections = '1'
  23. ReopenSecs = '300'
  24. NetTimeout = '30'
  25. RedoCompression = 'DISABLE'
  26. LogShipping = 'ON'
  27. PreferredApplyInstance = ''
  28. ApplyInstanceTimeout = '0'
  29. ApplyParallel = 'AUTO'
  30. StandbyFileManagement = 'AUTO'
  31. ArchiveLagTarget = '0'
  32. LogArchiveMaxProcesses = '30'
  33. LogArchiveMinSucceedDest = '1'
  34. DbFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  35. LogFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  36. FastStartFailoverTarget = ''
  37. InconsistentProperties = '(monitor)'
  38. InconsistentLogXptProps = '(monitor)'
  39. SendQEntries = '(monitor)'
  40. LogXptStatus = '(monitor)'
  41. RecvQEntries = '(monitor)'
  42. ApplyLagThreshold = '0'
  43. TransportLagThreshold = '0'
  44. TransportDisconnectedThreshold = '30'
  45. SidName = 'anxinstd'
  46. StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'
  47. StandbyArchiveLocation = '/u02/archived_log/'
  48. AlternateLocation = ''
  49. LogArchiveTrace = '0'
  50. LogArchiveFormat = '%t_%s_%r.arc'
  51. TopWaitEvents = '(monitor)'
  52. Database Status:
  53. WARNING
此时主备alert日志均有报错 Fatal NI connect error 12514, connecting to:

主库:

  1. (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGB)(CID=(PROGRAM=oracle)(HOST=db01)(USER=oracle))))
备库:

  1. (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=anxin_DGB)(CID=(PROGRAM=oracle)(HOST=db02)(USER=oracle))))

备库状态报告4个属性值与数据库设置不一致,重新设置


  1. DGMGRL> edit database anxinstd set property LogArchiveFormat='%t_%s_%r.arc';
  2. Property "logarchiveformat" updated
  3. DGMGRL> edit database anxinstd set property ArchiveLagTarget=0;
  4. Property "archivelagtarget" updated
  5. DGMGRL> edit database anxinstd set property LogArchiveTrace=0;
  6. Property "logarchivetrace" updated
  7. DGMGRL> edit database anxinstd set property LogArchiveMinSucceedDest=1;
  8. Property "logarchiveminsucceeddest" updated
  9. DGMGRL> show database verbose anxinstd;

再次查看broker状态

  1. DGMGRL> SHOW CONFIGURATION VERBOSE;
  2. Configuration - anxinconf
  3. Protection Mode: MaxAvailability
  4. Databases:
  5. anxin - Primary database
  6. anxinstd - Physical standby database
  7. Properties:
  8. FastStartFailoverThreshold = '30'
  9. OperationTimeout = '30'
  10. FastStartFailoverLagLimit = '30'
  11. CommunicationTimeout = '180'
  12. ObserverReconnect = '0'
  13. FastStartFailoverAutoReinstate = 'TRUE'
  14. FastStartFailoverPmyShutdown = 'TRUE'
  15. BystandersFollowRoleChange = 'ALL'
  16. ObserverOverride = 'FALSE'
  17. ExternalDestination1 = ''
  18. ExternalDestination2 = ''
  19. PrimaryLostWriteAction = 'CONTINUE'
  20. Fast-Start Failover: DISABLED
  21. Configuration Status:
  22. SUCCESS
备库状态

  1. DGMGRL> show database verbose anxinstd;
  2. Database - anxinstd
  3. Role: PHYSICAL STANDBY
  4. Intended State: APPLY-ON
  5. Transport Lag: 0 seconds (computed 0 seconds ago)
  6. Apply Lag: 0 seconds (computed 0 seconds ago)
  7. Apply Rate: 54.00 KByte/s
  8. Real Time Query: ON
  9. Instance(s):
  10. anxinstd
  11. Properties:
  12. DGConnectIdentifier = 'anxinstd'
  13. ObserverConnectIdentifier = ''
  14. LogXptMode = 'SYNC'
  15. DelayMins = '0'
  16. Binding = 'OPTIONAL'
  17. MaxFailure = '0'
  18. MaxConnections = '1'
  19. ReopenSecs = '300'
  20. NetTimeout = '30'
  21. RedoCompression = 'DISABLE'
  22. LogShipping = 'ON'
  23. PreferredApplyInstance = ''
  24. ApplyInstanceTimeout = '0'
  25. ApplyParallel = 'AUTO'
  26. StandbyFileManagement = 'AUTO'
  27. ArchiveLagTarget = '0'
  28. LogArchiveMaxProcesses = '30'
  29. LogArchiveMinSucceedDest = '1'
  30. DbFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  31. LogFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  32. FastStartFailoverTarget = ''
  33. InconsistentProperties = '(monitor)'
  34. InconsistentLogXptProps = '(monitor)'
  35. SendQEntries = '(monitor)'
  36. LogXptStatus = '(monitor)'
  37. RecvQEntries = '(monitor)'
  38. ApplyLagThreshold = '0'
  39. TransportLagThreshold = '0'
  40. TransportDisconnectedThreshold = '30'
  41. SidName = 'anxinstd'
  42. StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'
  43. StandbyArchiveLocation = '/u02/archived_log/'
  44. AlternateLocation = ''
  45. LogArchiveTrace = '0'
  46. LogArchiveFormat = '%t_%s_%r.arc'
  47. TopWaitEvents = '(monitor)'
  48. Database Status:
  49. SUCCESS
已经成功了。

接下来测试切换的时候又出现了问题


  1. DGMGRL> SWITCHOVER TO anxinstd;
  2. Performing switchover NOW, please wait...
  3. Operation requires a connection to instance "anxinstd" on database "anxinstd"
  4. Connecting to instance "anxinstd"...
  5. Unable to connect to database
  6. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  7. Failed.
  8. Warning: You are no longer connected to ORACLE.
  9. connect to instance "anxinstd" of database "anxinstd"
After performing a switchover using DGMGRL, Data Guard requires a shutdown and startup of both the primary and standby databases. This issue can occur if any necessary entry is missing in the listener.ora file.
DGMGRL is unable to connect to the database after it has been stopped while performing the switchover

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the listener of each instance.
The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain in the LISTENER.ORA file.

看一下StaticConnectIdentifier的值


  1. DGMGRL> show database anxin StaticConnectIdentifier;
  2. StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxin_DGMGRL)(INSTANCE_NAME=anxin)(SERVER=DEDICATED)))'
  3. DGMGRL> show database anxinstd StaticConnectIdentifier;
  4. StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'

均去连接一个db_unique_name_DGMGRL.db_domain格式的服务,那么需要在监听里静态注册一个db_unique_name_DGMGRL.db_domain的服务

主:

  1. xin =
  2. (DESCRIPTION_LIST =
  3. (DESCRIPTION =
  4. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
  5. )
  6. )
  7. SID_LIST_xin=
  8. (SID_LIST=
  9. (SID_DESC=
  10. (GLOBAL_DBNAME=anxin)
  11. (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
  12. (SID_NAME=anxin)
  13. )
  14. (SID_DESC=
  15. (GLOBAL_DBNAME=anxin_DGMGRL)
  16. (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
  17. (SID_NAME=anxin)
  18. )
  19. )
  20. )
  21. ADR_BASE_LISTENER = /u01/app/oracle
备:

  1. xin =
  2. (DESCRIPTION_LIST =
  3. (DESCRIPTION =
  4. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
  5. )
  6. )
  7. SID_LIST_xin=
  8. (SID_LIST=
  9. (SID_DESC=
  10. (GLOBAL_DBNAME=anxinstd)
  11. (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
  12. (SID_NAME=anxinstd)
  13. )
  14. (SID_DESC=
  15. (GLOBAL_DBNAME=anxinstd_DGMGRL)
  16. (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
  17. (SID_NAME=anxinstd)
  18. )
  19. )
  20. )
  21. ADR_BASE_LISTENER = /u01/app/oracle
再次测试,问题依旧,莫非和之前alert里的 Fatal NI connect error 12514报错有关?错误信息里的链接描述符均去请求一个db_unique_name_DGB.db_domain的服务,那么再依样添加到静态注册里试试


  1. DGMGRL> switchover to anxinstd;
  2. Performing switchover NOW, please wait...
  3. Operation requires a connection to instance "anxinstd" on database "anxinstd"
  4. Connecting to instance "anxinstd"...
  5. Connected.
  6. New primary database "anxinstd" is opening...
  7. Operation requires startup of instance "anxin" on database "anxin"
  8. Starting instance "anxin"...
  9. ORACLE instance started.
  10. Database mounted.
  11. Database opened.
  12. Switchover succeeded, new primary is "anxinstd"
成功了,那么这个db_unique_name_DGB.db_domain的服务究竟是做什么用的呢?

{db_unique_name}_DGB.{db_domain}: This Service is used by the DMON-Processes to communicate between each other
DMON是一个用来管理broker的后台进程,这个进程负责与本地数据库以及远程数据库的DMON进程进行通讯(与远端数据库的DMON进程进行通讯的时候使用的是一个 动态注册 的service name “db_unique_name_DGB.db_domain”)

既然是动态注册,那缘何注册失败呢?
文档 ID 365314.1给出了答案:Database Will Not Register With Listener configured on IP instead of Hostname

将主备的{db_unique_name}_DGB.{db_domain}静态entry删掉,host采用hostname,重启监听测试,switchover成功。

由此可见监听配置里还是采用hostname为好,通过本次事件也解惑了萦绕我心头很久的问题,很多时候建库完毕,使用工具创建动态注册的监听,监听状态里会有很多XDB之类的服务,而我改成静态监听之后(每次都用IP)却没有了之前的自动注册的服务,可见这就是根本原因:Database Will Not Register With Listener configured on IP instead of Hostname

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26838672/viewspace-2061959/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-05-17

  • 博文量
    29
  • 访问量
    106671