配置dg broker的問題分析及修復
最近從同事那兒接手了一套新環境，備庫因為伺服器問題已經下架，重新配了一臺伺服器，所以需要搭一套備庫，主庫已經配置好了，而且同事已經把在主庫把dg broker配好了。
使用dgmgrl來驗證，只有主庫孤零零的在那兒。
DGMGRL> show configuration;
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
stest - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
備庫搭建的過程也還算順利，中間也經歷了一些小插曲，不過問題最後都得到解決。duplicate同步之後，開始啟用dg broker，因為主庫的dg broker配置已經有了，所以直接新增備庫。
資料庫db_name為test,主庫的unique_name為stest,備庫為s2test
DGMGRL> add database s2test as
connect identifier is s2test
maintained as physical;
想必新增完之後就開始enable database就大功告成，沒想到還是有問題。
DGMGRL> show database verbose s2test
Database - s2test
Database Status:
。。。
DGM-17016: failed to retrieve status for database "s2tlest"
ORA-16664: unable to receive the result from a database
這個時候藉助oerr來看看錯誤資訊
$ oerr ora 16664
16664, 0000, "unable to receive the result from a database"
// *Cause: During execution of a command, a database in the Data Guard
// broker configuration failed to return a result.
// *Action: Check Data Guard broker logs for the details of the failure.
// Ensure network communication is working properly amongst the
// members of the configuration. Fix any possible network problems
// and reissue the command.
dg broker的錯誤解釋
$ oerr dgm 17016
17016, 00000, "failed to retrieve status for database \"%s\""
// *Cause: DGMGRL could not retrieve the StatusReport property from the given
// database and thus could not report the database status.
// *Action: See accompanying messages for details.
看來沒有得到更多的資訊，就從備庫的dg broker日誌中檢視，
######dataguard log
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
Failed to send message to site stest. Error code is ORA-16501.
10/10/2015 14:04:36
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='4', SPFILE='', DATABASE='4'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
SPFILE is missing value for property 'LogArchiveTrace' with sid='test'
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
SPFILE is missing value for property 'LogArchiveFormat' with sid='test'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
Failed to send message to site stest. Error code is ORA-16501.
從日誌來看備庫是連線主庫有問題，
$ oerr ora 16501
16501, 00000, "the Data Guard broker operation failed"
// *Cause: The Data Guard broker operation failed.
// *Action: See accompanying messages for details.
如此來看主庫連線備庫有問題，備庫說連線主庫有問題，檢視了好幾遍網路配置，都沒有發現任何問題。
在主庫中使用dgmgrl來檢視更多的細節資訊。
DGMGRL> show database verbose stest;
Database - stest
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Properties:
DGConnectIdentifier = 'test01'
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 = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/U01/app/oracle/oradata/test, /U01/app/oracle/oradata/test'
LogFileNameConvert = '/U01/app/oracle/oradata/test, /U01/app/oracle/oradata/test'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test01.cyou.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stest_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/U01/app/oracle/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
檢視備庫的
DGMGRL> show database verbose s2test
Database - s2test
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
test
Properties:
DGConnectIdentifier = 's2test'
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)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stest.cyou.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=s2test_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/U01/app/oracle/product/11.2.3/db_1/dbs/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
DGM-17016: failed to retrieve status for database "s2test"
ORA-16664: unable to receive the result from a database
最後檢視mos，發現一篇文章比較貼近，不過描述的問題原因還是有些差別。
Data Guard Standby Database Broker Configuration error DGM-17016: failed to retrieve status for database (文件 ID 1631552.1)
進一步排除，排除了防火牆的影響，甚至考慮重啟一下資料庫，結果重啟庫的時候還確實有問題
SQL> startup
ORACLE instance started.
Total System Global Area 1.5734E+10 bytes
Fixed Size 2243832 bytes
Variable Size 1375732488 bytes
Database Buffers 1.4328E+10 bytes
Redo Buffers 27852800 bytes
Database mounted.
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","ALTER
DATABASE OPEN","SQLA","tmp")
不過這個和dg broker的問題是沒有關係的，是sga配置的shared pool太小，process配置了3000，結果就導致啟動的時候shared pool過小起不來了。
備庫資料庫啟動了，但是dg broker的驗證還是照樣有問題，這個時候就仔細比對show database verbose中的資訊
發現主庫的資訊有一點特別。
DGMGRL> show database verbose stest;
Database - stest
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Properties:
DGConnectIdentifier = 'test01'
特別就特別在這個地方，這個地方最好應該是和db_unique_name一致，結果設定成了test01,備庫中網路服務中也沒有配置這個服務名
簡單修改一下，修改為db_unique_name
DGMGRL> edit database stest set property DGConnectIdentifier ='stest';
Property "dgconnectidentifier" updated
再次檢視就沒有問題了。
DGMGRL> show configuration;
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
stest - Primary database
s2test - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
可能之前的同事都是習慣使用db_name來作為主庫的dg配置，結果自己還糾結了半天，排除了不少的因素，最後發現竟然是這麼一個細小的地方，修改為db_unique_name就可以了，可見對於這些小的細節上最好還是能夠有一個統一的標準規範，這樣也好規範大家，避免這類問題帶來的困擾。
使用dgmgrl來驗證，只有主庫孤零零的在那兒。
DGMGRL> show configuration;
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
stest - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
備庫搭建的過程也還算順利，中間也經歷了一些小插曲，不過問題最後都得到解決。duplicate同步之後，開始啟用dg broker，因為主庫的dg broker配置已經有了，所以直接新增備庫。
資料庫db_name為test,主庫的unique_name為stest,備庫為s2test
DGMGRL> add database s2test as
connect identifier is s2test
maintained as physical;
想必新增完之後就開始enable database就大功告成，沒想到還是有問題。
DGMGRL> show database verbose s2test
Database - s2test
Database Status:
。。。
DGM-17016: failed to retrieve status for database "s2tlest"
ORA-16664: unable to receive the result from a database
這個時候藉助oerr來看看錯誤資訊
$ oerr ora 16664
16664, 0000, "unable to receive the result from a database"
// *Cause: During execution of a command, a database in the Data Guard
// broker configuration failed to return a result.
// *Action: Check Data Guard broker logs for the details of the failure.
// Ensure network communication is working properly amongst the
// members of the configuration. Fix any possible network problems
// and reissue the command.
dg broker的錯誤解釋
$ oerr dgm 17016
17016, 00000, "failed to retrieve status for database \"%s\""
// *Cause: DGMGRL could not retrieve the StatusReport property from the given
// database and thus could not report the database status.
// *Action: See accompanying messages for details.
看來沒有得到更多的資訊，就從備庫的dg broker日誌中檢視，
######dataguard log
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
Failed to send message to site stest. Error code is ORA-16501.
10/10/2015 14:04:36
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='4', SPFILE='', DATABASE='4'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
SPFILE is missing value for property 'LogArchiveTrace' with sid='test'
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
SPFILE is missing value for property 'LogArchiveFormat' with sid='test'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
Failed to send message to site stest. Error code is ORA-16501.
從日誌來看備庫是連線主庫有問題，
$ oerr ora 16501
16501, 00000, "the Data Guard broker operation failed"
// *Cause: The Data Guard broker operation failed.
// *Action: See accompanying messages for details.
如此來看主庫連線備庫有問題，備庫說連線主庫有問題，檢視了好幾遍網路配置，都沒有發現任何問題。
在主庫中使用dgmgrl來檢視更多的細節資訊。
DGMGRL> show database verbose stest;
Database - stest
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Properties:
DGConnectIdentifier = 'test01'
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 = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/U01/app/oracle/oradata/test, /U01/app/oracle/oradata/test'
LogFileNameConvert = '/U01/app/oracle/oradata/test, /U01/app/oracle/oradata/test'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test01.cyou.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stest_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/U01/app/oracle/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
檢視備庫的
DGMGRL> show database verbose s2test
Database - s2test
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
test
Properties:
DGConnectIdentifier = 's2test'
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)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stest.cyou.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=s2test_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/U01/app/oracle/product/11.2.3/db_1/dbs/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
DGM-17016: failed to retrieve status for database "s2test"
ORA-16664: unable to receive the result from a database
最後檢視mos，發現一篇文章比較貼近，不過描述的問題原因還是有些差別。
Data Guard Standby Database Broker Configuration error DGM-17016: failed to retrieve status for database (文件 ID 1631552.1)
進一步排除，排除了防火牆的影響，甚至考慮重啟一下資料庫，結果重啟庫的時候還確實有問題
SQL> startup
ORACLE instance started.
Total System Global Area 1.5734E+10 bytes
Fixed Size 2243832 bytes
Variable Size 1375732488 bytes
Database Buffers 1.4328E+10 bytes
Redo Buffers 27852800 bytes
Database mounted.
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","ALTER
DATABASE OPEN","SQLA","tmp")
不過這個和dg broker的問題是沒有關係的，是sga配置的shared pool太小，process配置了3000，結果就導致啟動的時候shared pool過小起不來了。
備庫資料庫啟動了，但是dg broker的驗證還是照樣有問題，這個時候就仔細比對show database verbose中的資訊
發現主庫的資訊有一點特別。
DGMGRL> show database verbose stest;
Database - stest
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Properties:
DGConnectIdentifier = 'test01'
特別就特別在這個地方，這個地方最好應該是和db_unique_name一致，結果設定成了test01,備庫中網路服務中也沒有配置這個服務名
簡單修改一下，修改為db_unique_name
DGMGRL> edit database stest set property DGConnectIdentifier ='stest';
Property "dgconnectidentifier" updated
再次檢視就沒有問題了。
DGMGRL> show configuration;
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
stest - Primary database
s2test - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
可能之前的同事都是習慣使用db_name來作為主庫的dg配置，結果自己還糾結了半天，排除了不少的因素，最後發現竟然是這麼一個細小的地方，修改為db_unique_name就可以了，可見對於這些小的細節上最好還是能夠有一個統一的標準規範，這樣也好規範大家，避免這類問題帶來的困擾。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1813727/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dg broker配置的問題及分析
- Oracle DG管理Broker配置Oracle
- Oracle DG Broker配置的管理週期Oracle
- Oracle DG 管理Broker配置成員的狀態Oracle
- oracle 11g dg broker開啟和配置Oracle
- Oracle物理DG自動切換——Dataguard Broker配置Oracle
- Oracle分割槽資料問題的分析和修復Oracle
- oracle 11.2.0.4 DataGuard Broker配置過程中可能遇到的問題及解決方法Oracle
- CocoaPods使用及安裝常見問題修復
- 使用DG_broker工具管理DG之switchover
- sqlServer修復有問題的表SQLServer
- 修復win10工作列及桌面假死問題的方法Win10
- Linux下建立Oracle 10g DG和Broker配置LinuxOracle 10g
- Linux 下建立Oracle 10g DG和Broker配置LinuxOracle 10g
- GRPC使用問題修復RPC
- 如何修復Vue中的 “this is undefined” 問題VueUndefined
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.1)--Data Guard Broker 的配置客戶端
- 使用Broker實現DG切換
- ORACLE資料庫Dataguard dg brokerOracle資料庫
- oracle_DG+broker+Keepalived的部署文件Oracle
- 修復 SSL Certificate Problem，如何定位及常見問題的處理策略
- ?【Alibaba中介軟體技術系列】「RocketMQ技術專題」Broker配置介紹及傳送流程、異常(XX Busy)問題分析MQ
- Oracle日常問題-壞塊修復Oracle
- 使用git修復線上指定版本的問題Git
- 【DG】利用閃回資料庫（flashback）修復Failover後的DG環境資料庫AI
- ORACLE 11.2.0.4 DG(Broker) for linux 部署OracleLinux
- DG修復：異常關庫導致的資料庫啟動失敗ORA-01110及GAP修復資料庫
- 物化檢視中的統計資訊導致的查詢問題分析和修復
- 二進位制修復中文亂碼的問題
- oracle ora-600[2662]問題分析及異常恢復Oracle
- oracle dataguard broker 配置Oracle
- Oracle 11g dg broker自動failoverOracleAI
- 修復PG.conf檔案出現的問題
- Windows啟動問題修復（重建活動分割槽）Windows
- MySQL修復表的簡單分析MySql
- 曲折的dump匯入及問題分析
- 某殼分析+修復（二）
- DG主備日誌缺失，資料無法同步修復