Data Guard配置Broker解決ORA-16664、ORA-16792
給某一個客戶安裝ADG完畢,配置Broker方便日後的管理與切換,碰到些許問題,以作記錄
建立完configuration之後,enable的過程很慢,而且狀態出現error
主庫查詢:
備庫查詢:
檢視備資料庫的狀態
此時主備alert日誌均有報錯 Fatal NI connect error 12514, connecting to:
主庫:
備庫:
備庫狀態報告4個屬性值與資料庫設定不一致,重新設定
再次檢視broker狀態
備庫狀態
已經成功了。
接下來測試切換的時候又出現了問題
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的值
均去連線一個db_unique_name_DGMGRL.db_domain格式的服務,那麼需要在監聽裡靜態註冊一個db_unique_name_DGMGRL.db_domain的服務
主:
備:
再次測試,問題依舊,莫非和之前alert裡的 Fatal NI connect error 12514報錯有關?錯誤資訊裡的連結描述符均去請求一個db_unique_name_DGB.db_domain的服務,那麼再依樣新增到靜態註冊裡試試
成功了,那麼這個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
建立完configuration之後,enable的過程很慢,而且狀態出現error
主庫查詢:
- DGMGRL> SHOW CONFIGURATION VERBOSE;
- Configuration - anxinconf
- Protection Mode: MaxAvailability
- Databases:
- anxin - Primary database
- anxinstd - Physical standby database
- Error: ORA-16664: unable to receive the result from a database
- Properties:
- FastStartFailoverThreshold = '30'
- OperationTimeout = '30'
- FastStartFailoverLagLimit = '30'
- CommunicationTimeout = '180'
- ObserverReconnect = '0'
- FastStartFailoverAutoReinstate = 'TRUE'
- FastStartFailoverPmyShutdown = 'TRUE'
- BystandersFollowRoleChange = 'ALL'
- ObserverOverride = 'FALSE'
- ExternalDestination1 = ''
- ExternalDestination2 = ''
- PrimaryLostWriteAction = 'CONTINUE'
- Fast-Start Failover: DISABLED
- Configuration Status:
- ERROR
- DGMGRL> SHOW CONFIGURATION VERBOSE;
- Configuration - anxinconf
- Protection Mode: MaxAvailability
- Databases:
- anxin - Primary database
- anxinstd - Physical standby database
- Warning: ORA-16792: configurable property value is inconsistent with database setting
- Properties:
- FastStartFailoverThreshold = '30'
- OperationTimeout = '30'
- FastStartFailoverLagLimit = '30'
- CommunicationTimeout = '180'
- ObserverReconnect = '0'
- FastStartFailoverAutoReinstate = 'TRUE'
- FastStartFailoverPmyShutdown = 'TRUE'
- BystandersFollowRoleChange = 'ALL'
- ObserverOverride = 'FALSE'
- ExternalDestination1 = ''
- ExternalDestination2 = ''
- PrimaryLostWriteAction = 'CONTINUE'
- Fast-Start Failover: DISABLED
- Configuration Status:
- WARNING
- DGMGRL> show database verbose anxinstd;
- Database - anxinstd
- Role: PHYSICAL STANDBY
- Intended State: APPLY-ON
- Transport Lag: 0 seconds (computed 1 second ago)
- Apply Lag: 0 seconds (computed 1 second ago)
- Apply Rate: 54.00 KByte/s
- Real Time Query: ON
- Instance(s):
- anxinstd
- Warning: ORA-16714: the value of property ArchiveLagTarget 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
- Properties:
- DGConnectIdentifier = 'anxinstd'
- ObserverConnectIdentifier = ''
- LogXptMode = 'SYNC'
- 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 = '30'
- LogArchiveMinSucceedDest = '1'
- DbFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
- LogFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
- FastStartFailoverTarget = ''
- InconsistentProperties = '(monitor)'
- InconsistentLogXptProps = '(monitor)'
- SendQEntries = '(monitor)'
- LogXptStatus = '(monitor)'
- RecvQEntries = '(monitor)'
- ApplyLagThreshold = '0'
- TransportLagThreshold = '0'
- TransportDisconnectedThreshold = '30'
- SidName = 'anxinstd'
- StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'
- StandbyArchiveLocation = '/u02/archived_log/'
- AlternateLocation = ''
- LogArchiveTrace = '0'
- LogArchiveFormat = '%t_%s_%r.arc'
- TopWaitEvents = '(monitor)'
- Database Status:
- WARNING
主庫:
- (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))))
- (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個屬性值與資料庫設定不一致,重新設定
- DGMGRL> edit database anxinstd set property LogArchiveFormat='%t_%s_%r.arc';
- Property "logarchiveformat" updated
- DGMGRL> edit database anxinstd set property ArchiveLagTarget=0;
- Property "archivelagtarget" updated
- DGMGRL> edit database anxinstd set property LogArchiveTrace=0;
- Property "logarchivetrace" updated
- DGMGRL> edit database anxinstd set property LogArchiveMinSucceedDest=1;
- Property "logarchiveminsucceeddest" updated
- DGMGRL> show database verbose anxinstd;
再次檢視broker狀態
- DGMGRL> SHOW CONFIGURATION VERBOSE;
- Configuration - anxinconf
- Protection Mode: MaxAvailability
- Databases:
- anxin - Primary database
- anxinstd - Physical standby database
- Properties:
- FastStartFailoverThreshold = '30'
- OperationTimeout = '30'
- FastStartFailoverLagLimit = '30'
- CommunicationTimeout = '180'
- ObserverReconnect = '0'
- FastStartFailoverAutoReinstate = 'TRUE'
- FastStartFailoverPmyShutdown = 'TRUE'
- BystandersFollowRoleChange = 'ALL'
- ObserverOverride = 'FALSE'
- ExternalDestination1 = ''
- ExternalDestination2 = ''
- PrimaryLostWriteAction = 'CONTINUE'
- Fast-Start Failover: DISABLED
- Configuration Status:
- SUCCESS
- DGMGRL> show database verbose anxinstd;
- Database - anxinstd
- Role: PHYSICAL STANDBY
- Intended State: APPLY-ON
- Transport Lag: 0 seconds (computed 0 seconds ago)
- Apply Lag: 0 seconds (computed 0 seconds ago)
- Apply Rate: 54.00 KByte/s
- Real Time Query: ON
- Instance(s):
- anxinstd
- Properties:
- DGConnectIdentifier = 'anxinstd'
- ObserverConnectIdentifier = ''
- LogXptMode = 'SYNC'
- 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 = '30'
- LogArchiveMinSucceedDest = '1'
- DbFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
- LogFileNameConvert = '/u02/oradata/anxin, /u02/oradata/anxinstd'
- FastStartFailoverTarget = ''
- InconsistentProperties = '(monitor)'
- InconsistentLogXptProps = '(monitor)'
- SendQEntries = '(monitor)'
- LogXptStatus = '(monitor)'
- RecvQEntries = '(monitor)'
- ApplyLagThreshold = '0'
- TransportLagThreshold = '0'
- TransportDisconnectedThreshold = '30'
- SidName = 'anxinstd'
- StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'
- StandbyArchiveLocation = '/u02/archived_log/'
- AlternateLocation = ''
- LogArchiveTrace = '0'
- LogArchiveFormat = '%t_%s_%r.arc'
- TopWaitEvents = '(monitor)'
- Database Status:
- SUCCESS
接下來測試切換的時候又出現了問題
- DGMGRL> SWITCHOVER TO anxinstd;
- Performing switchover NOW, please wait...
- Operation requires a connection to instance "anxinstd" on database "anxinstd"
- Connecting to instance "anxinstd"...
- Unable to connect to database
- ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
- Failed.
- Warning: You are no longer connected to ORACLE.
- connect to instance "anxinstd" of database "anxinstd"
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的值
- DGMGRL> show database anxin StaticConnectIdentifier;
- StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxin_DGMGRL)(INSTANCE_NAME=anxin)(SERVER=DEDICATED)))'
- DGMGRL> show database anxinstd StaticConnectIdentifier;
- 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的服務
主:
- xin =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
- )
- )
- SID_LIST_xin=
- (SID_LIST=
- (SID_DESC=
- (GLOBAL_DBNAME=anxin)
- (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
- (SID_NAME=anxin)
- )
- (SID_DESC=
- (GLOBAL_DBNAME=anxin_DGMGRL)
- (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
- (SID_NAME=anxin)
- )
- )
- )
- ADR_BASE_LISTENER = /u01/app/oracle
- xin =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
- )
- )
- SID_LIST_xin=
- (SID_LIST=
- (SID_DESC=
- (GLOBAL_DBNAME=anxinstd)
- (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
- (SID_NAME=anxinstd)
- )
- (SID_DESC=
- (GLOBAL_DBNAME=anxinstd_DGMGRL)
- (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
- (SID_NAME=anxinstd)
- )
- )
- )
- ADR_BASE_LISTENER = /u01/app/oracle
- DGMGRL> switchover to anxinstd;
- Performing switchover NOW, please wait...
- Operation requires a connection to instance "anxinstd" on database "anxinstd"
- Connecting to instance "anxinstd"...
- Connected.
- New primary database "anxinstd" is opening...
- Operation requires startup of instance "anxin" on database "anxin"
- Starting instance "anxin"...
- ORACLE instance started.
- Database mounted.
- Database opened.
- Switchover succeeded, new primary is "anxinstd"
{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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Data Guard Broker元件Oracle元件
- 使用Data Guard Broker進行Data Guard物理備用庫配置(Oracle 19c)Oracle
- 1 Oracle Data Guard Broker 概念Oracle
- Oracle Data Guard和Broker概述Oracle
- [20221111]19c配置Data Guard Broker問題.txt
- 8 Oracle Data Guard Broker 屬性Oracle
- 【DATAGUARD】Oracle19c Data Guard BrokerOracle
- [20221111]19c配置Data Guard Broker問題2.txt
- A Oracle Data Guard Broker 升級和降級Oracle
- 使用Broker管理Data Guard——停用、改保護模式等模式
- [20201110]How to get the Data Guard broker configuration from a SQL query.txtSQL
- Oracle Data Guard簡介Oracle
- 單機搭建Data Guard
- 【DG】Data Guard搭建(physical standby)
- 1 關於 Oracle Data GuardOracle
- 2 Oracle Data Guard 安裝Oracle
- bd_ticket_guard_client_dataclient
- 9 Oracle Data Guard 故障診斷Oracle
- Bd-Ticket-Guard-Client-Data逆向client
- 3 管理 Broker 配置
- oracle dataguard broker 配置Oracle
- oracle 11g data guard維護Oracle
- 4.1.6 Oracle Restart 與 Oracle Data Guard 整合OracleREST
- 2 開始實用 Oracle Data GuardOracle
- 19 Oracle Data Guard 相關檢視Oracle
- oracle 11.2.0.4 DataGuard Broker配置過程中可能遇到的問題及解決方法Oracle
- Oracle DG管理Broker配置Oracle
- 6 Oracle Data Guard Protection Modes 保護模式Oracle模式
- 【DG】Data Guard主備庫Failove切換AI
- 【DG】Data Guard主備庫Switchover切換
- 15 Oracle Data Guard Scenarios 保護場景OracleiOS
- Oracle 19c Broker配置Oracle
- Oracle 12.2 How to Generate AWRs in Active Data Guard Standby DatabasesOracleDatabase
- Oracle Data Guard Feature 12cR2系列(二)Oracle
- Oracle Data Guard Feature 12cR2系列(一)Oracle
- 【ASK_ORACLE】Oracle Data Guard(一)DG架構Oracle架構
- 4 管理Broker配置的成員
- 18 與Oracle Data Guard 相關的SQL語句OracleSQL
- 需要了解的Data Guard理論知識(一)