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

liupzmin發表於2016-03-22
給某一個客戶安裝ADG完畢，配置Broker方便日後的管理與切換，碰到些許問題，以作記錄

建立完configuration之後，enable的過程很慢，而且狀態出現error

主庫查詢：

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

  1. DGMGRL> SHOW CONFIGURATION VERBOSE;
  3. Configuration - anxinconf
  5. Protection Mode: MaxAvailability
  6. Databases:
  7. anxin - Primary database
  8. anxinstd - Physical standby database
  9. Warning: ORA-16792: configurable property value is inconsistent with database setting
  11. Properties:
  12. FastStartFailoverThreshold = '30'
  13. OperationTimeout = '30'
  14. FastStartFailoverLagLimit = '30'
  15. CommunicationTimeout = '180'
  16. ObserverReconnect = '0'
  17. FastStartFailoverAutoReinstate = 'TRUE'
  18. FastStartFailoverPmyShutdown = 'TRUE'
  19. BystandersFollowRoleChange = 'ALL'
  20. ObserverOverride = 'FALSE'
  21. ExternalDestination1 = ''
  22. ExternalDestination2 = ''
  23. PrimaryLostWriteAction = 'CONTINUE'
  25. Fast-Start Failover: DISABLED
  27. Configuration Status:
  28. WARNING
檢視備資料庫的狀態

  1. DGMGRL> show database verbose anxinstd;
  3. Database - anxinstd
  5. Role: PHYSICAL STANDBY
  6. Intended State: APPLY-ON
  7. Transport Lag: 0 seconds (computed 1 second ago)
  8. Apply Lag: 0 seconds (computed 1 second ago)
  9. Apply Rate: 54.00 KByte/s
  10. Real Time Query: ON
  11. Instance(s):
  12. anxinstd
  13. Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
  14. Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
  15. Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
  16. Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
  18. Properties:
  19. DGConnectIdentifier = 'anxinstd'
  20. ObserverConnectIdentifier = ''
  21. LogXptMode = 'SYNC'
  22. DelayMins = '0'
  23. Binding = 'OPTIONAL'
  24. MaxFailure = '0'
  25. MaxConnections = '1'
  26. ReopenSecs = '300'
  27. NetTimeout = '30'
  28. RedoCompression = 'DISABLE'
  29. LogShipping = 'ON'
  30. PreferredApplyInstance = ''
  31. ApplyInstanceTimeout = '0'
  32. ApplyParallel = 'AUTO'
  33. StandbyFileManagement = 'AUTO'
  34. ArchiveLagTarget = '0'
  35. LogArchiveMaxProcesses = '30'
  36. LogArchiveMinSucceedDest = '1'
  37. DbFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  38. LogFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  39. FastStartFailoverTarget = ''
  40. InconsistentProperties = '(monitor)'
  41. InconsistentLogXptProps = '(monitor)'
  42. SendQEntries = '(monitor)'
  43. LogXptStatus = '(monitor)'
  44. RecvQEntries = '(monitor)'
  45. ApplyLagThreshold = '0'
  46. TransportLagThreshold = '0'
  47. TransportDisconnectedThreshold = '30'
  48. SidName = 'anxinstd'
  49. StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'
  50. StandbyArchiveLocation = '/u02/archived_log/'
  51. AlternateLocation = ''
  52. LogArchiveTrace = '0'
  53. LogArchiveFormat = '%t_%s_%r.arc'
  54. TopWaitEvents = '(monitor)'
  56. Database Status:
  57. 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;
  3. Configuration - anxinconf
  5. Protection Mode: MaxAvailability
  6. Databases:
  7. anxin - Primary database
  8. anxinstd - Physical standby database
  10. Properties:
  11. FastStartFailoverThreshold = '30'
  12. OperationTimeout = '30'
  13. FastStartFailoverLagLimit = '30'
  14. CommunicationTimeout = '180'
  15. ObserverReconnect = '0'
  16. FastStartFailoverAutoReinstate = 'TRUE'
  17. FastStartFailoverPmyShutdown = 'TRUE'
  18. BystandersFollowRoleChange = 'ALL'
  19. ObserverOverride = 'FALSE'
  20. ExternalDestination1 = ''
  21. ExternalDestination2 = ''
  22. PrimaryLostWriteAction = 'CONTINUE'
  24. Fast-Start Failover: DISABLED
  26. Configuration Status:
  27. SUCCESS
備庫狀態

  1. DGMGRL> show database verbose anxinstd;
  3. Database - anxinstd
  5. Role: PHYSICAL STANDBY
  6. Intended State: APPLY-ON
  7. Transport Lag: 0 seconds (computed 0 seconds ago)
  8. Apply Lag: 0 seconds (computed 0 seconds ago)
  9. Apply Rate: 54.00 KByte/s
  10. Real Time Query: ON
  11. Instance(s):
  12. anxinstd
  14. Properties:
  15. DGConnectIdentifier = 'anxinstd'
  16. ObserverConnectIdentifier = ''
  17. LogXptMode = 'SYNC'
  18. DelayMins = '0'
  19. Binding = 'OPTIONAL'
  20. MaxFailure = '0'
  21. MaxConnections = '1'
  22. ReopenSecs = '300'
  23. NetTimeout = '30'
  24. RedoCompression = 'DISABLE'
  25. LogShipping = 'ON'
  26. PreferredApplyInstance = ''
  27. ApplyInstanceTimeout = '0'
  28. ApplyParallel = 'AUTO'
  29. StandbyFileManagement = 'AUTO'
  30. ArchiveLagTarget = '0'
  31. LogArchiveMaxProcesses = '30'
  32. LogArchiveMinSucceedDest = '1'
  33. DbFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  34. LogFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
  35. FastStartFailoverTarget = ''
  36. InconsistentProperties = '(monitor)'
  37. InconsistentLogXptProps = '(monitor)'
  38. SendQEntries = '(monitor)'
  39. LogXptStatus = '(monitor)'
  40. RecvQEntries = '(monitor)'
  41. ApplyLagThreshold = '0'
  42. TransportLagThreshold = '0'
  43. TransportDisconnectedThreshold = '30'
  44. SidName = 'anxinstd'
  45. StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'
  46. StandbyArchiveLocation = '/u02/archived_log/'
  47. AlternateLocation = ''
  48. LogArchiveTrace = '0'
  49. LogArchiveFormat = '%t_%s_%r.arc'
  50. TopWaitEvents = '(monitor)'
  52. Database Status:
  53. 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
  8. Failed.
  9. Warning: You are no longer connected to ORACLE.
  11. 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章