dg broker配置的問題及分析
今天在配置一個備庫的時候碰到了一些問題，話說配置dg broker真沒什麼特別需要注意的細節了，本身已經給DBA省了很大的事兒了。
但是有時候就是會出現一些稀奇古怪的小問題。這個環境又非常重要，備庫已經因為硬體故障報廢了，現在剛搭的備庫就想趕緊把它跑起來。
簡單新增配置之後，spfile,防火牆，埠，listener等等因素都滿足了。感覺就是一蹴而就的事情了。
但是show configuration的時候就是報錯。
DGMGRL> show configuration;
Configuration - test_dg
Protection Mode: MaxPerformance
Databases:
test - Primary database
Error: ORA-16778: redo transport error for one or more databases
stest1 - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
對於這個問題，常規思路如果想得到更多的明細資訊，直接使用verbose方式來檢視。
檢視主庫的verbose資訊
DGMGRL> show database verbose test;
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s): test
Error: ORA-16737: the redo transport service for standby database "stest1" has an error
Properties:
DGConnectIdentifier = 'test'
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 = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.127.65.111)(PORT=1535))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
ERROR
檢視備庫的verbose資訊
DGMGRL> show database verbose stest1;
Database - stest1
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s): test
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Properties:
DGConnectIdentifier = 'stest1'
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, /data/oracle/oradata/test, /U01/app/oracle/oradata/test, /other/app/oracle/oradata/test, /U01/app/oracle/oradata/test, +DATA, /U01/app/oracle/oradata/test, +ARCH, /U01/app/oracle/oradata/test'
LogFileNameConvert = '/U01/app/oracle/oradata/test, /U01/app/oracle/oradata/test, /data/oracle/oradata/test, /U01/app/oracle/oradata/test, /other/app/oracle/oradata/test, /U01/app/oracle/oradata/test, +DATA, /U01/app/oracle/oradata/test, +ARCH, /U01/app/oracle/oradata/test'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.14.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stest1_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
WARNING
我是橫豎看了很多遍，實在是沒找出哪裡的配置不一致了。
對於這類問題，一般都是推薦檢視主庫的歸檔路徑，是否出現了不一致，連線不通的問題，或者是db_unique_name的問題。
檢視v$archive_dest發現，歸檔路徑2確實顯示有問題。
SQL> select dest_id,error from v$archive_dest;
DEST_ID ERROR
---------- -----------------------------------------------------------------
1
2 ORA-16047: DGID mismatch between destination setting and target database
問題的原因說是DGID不匹配。那麼來看看歸檔路徑2，這個也是dg broker自動生成的，是在也沒發現那裡有問題。
log_archive_dest_2 string service="stest1", LGWR ASYNC NO AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_
unique_name="stest1" net_timeout=30, valid_for=(all_logfiles,primary_role)
檢視備庫dg broker的日誌，發現報出了這麼一段警告。但是原因未知。
11/18/2015 18:04:38
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
11/18/2015 18:05:14
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
11/18/2015 18:06:08
檢視備庫的alert日誌，提示接收gap的歸檔存在問題，我就開始慌了，很重要的一套庫，不能有任何閃失，要不又得重來一次了，真感覺實在是太酸爽了。
Error 12541 received logging on to the standby
Check whether the listener is up and running.
FAL[client, USER]: Error 12541 connecting to test for fetching gap sequence
Wed Nov 18 18:02:36 2015
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 460503-460515
DBID 1210367666 branch 622336050
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to retestve
archivelog gaps.
特別申明一下，這些操作都是在上午做的，如果沒有發現有什麼端倪，就可以繼續往下看。
這個時候嘗試重建dg broker檔案。發現朱備庫的dr的檔案大小相同，但是時間戳不同。
這個時候檢視備庫的時間
$ date
Wed Nov 18 18:30:49 CST 2015
發現時間壓根就不同步，要和主庫的保持一致，還是使用nftp來做。
# /usr/sbin/ntpdate 192.168.131.132
18 Nov 10:32:17 ntpdate[48502]: step time server 192.168.131.132 offset -28854.645360 sec
時間修正之後，再次檢視，就沒有任何問題了。
DGMGRL> show configuration;
Configuration - test_dg
Protection Mode: MaxPerformance
Databases:
test - Primary database
stest1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
這個問題也算是早上給自己的一個小警告，一個非常細小的問題就很可能造成很大的延誤。所以環境的檢查還是要細緻，不能輕視。
但是有時候就是會出現一些稀奇古怪的小問題。這個環境又非常重要，備庫已經因為硬體故障報廢了，現在剛搭的備庫就想趕緊把它跑起來。
簡單新增配置之後，spfile,防火牆，埠，listener等等因素都滿足了。感覺就是一蹴而就的事情了。
但是show configuration的時候就是報錯。
DGMGRL> show configuration;
Configuration - test_dg
Protection Mode: MaxPerformance
Databases:
test - Primary database
Error: ORA-16778: redo transport error for one or more databases
stest1 - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
對於這個問題，常規思路如果想得到更多的明細資訊，直接使用verbose方式來檢視。
檢視主庫的verbose資訊
DGMGRL> show database verbose test;
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s): test
Error: ORA-16737: the redo transport service for standby database "stest1" has an error
Properties:
DGConnectIdentifier = 'test'
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 = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.127.65.111)(PORT=1535))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
ERROR
檢視備庫的verbose資訊
DGMGRL> show database verbose stest1;
Database - stest1
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s): test
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Properties:
DGConnectIdentifier = 'stest1'
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, /data/oracle/oradata/test, /U01/app/oracle/oradata/test, /other/app/oracle/oradata/test, /U01/app/oracle/oradata/test, +DATA, /U01/app/oracle/oradata/test, +ARCH, /U01/app/oracle/oradata/test'
LogFileNameConvert = '/U01/app/oracle/oradata/test, /U01/app/oracle/oradata/test, /data/oracle/oradata/test, /U01/app/oracle/oradata/test, /other/app/oracle/oradata/test, /U01/app/oracle/oradata/test, +DATA, /U01/app/oracle/oradata/test, +ARCH, /U01/app/oracle/oradata/test'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.14.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stest1_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
WARNING
我是橫豎看了很多遍，實在是沒找出哪裡的配置不一致了。
對於這類問題，一般都是推薦檢視主庫的歸檔路徑，是否出現了不一致，連線不通的問題，或者是db_unique_name的問題。
檢視v$archive_dest發現，歸檔路徑2確實顯示有問題。
SQL> select dest_id,error from v$archive_dest;
DEST_ID ERROR
---------- -----------------------------------------------------------------
1
2 ORA-16047: DGID mismatch between destination setting and target database
問題的原因說是DGID不匹配。那麼來看看歸檔路徑2，這個也是dg broker自動生成的，是在也沒發現那裡有問題。
log_archive_dest_2 string service="stest1", LGWR ASYNC NO AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_
unique_name="stest1" net_timeout=30, valid_for=(all_logfiles,primary_role)
檢視備庫dg broker的日誌，發現報出了這麼一段警告。但是原因未知。
11/18/2015 18:04:38
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
11/18/2015 18:05:14
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
11/18/2015 18:06:08
檢視備庫的alert日誌，提示接收gap的歸檔存在問題，我就開始慌了，很重要的一套庫，不能有任何閃失，要不又得重來一次了，真感覺實在是太酸爽了。
Error 12541 received logging on to the standby
Check whether the listener is up and running.
FAL[client, USER]: Error 12541 connecting to test for fetching gap sequence
Wed Nov 18 18:02:36 2015
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 460503-460515
DBID 1210367666 branch 622336050
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to retestve
archivelog gaps.
特別申明一下，這些操作都是在上午做的，如果沒有發現有什麼端倪，就可以繼續往下看。
這個時候嘗試重建dg broker檔案。發現朱備庫的dr的檔案大小相同，但是時間戳不同。
這個時候檢視備庫的時間
$ date
Wed Nov 18 18:30:49 CST 2015
發現時間壓根就不同步，要和主庫的保持一致，還是使用nftp來做。
# /usr/sbin/ntpdate 192.168.131.132
18 Nov 10:32:17 ntpdate[48502]: step time server 192.168.131.132 offset -28854.645360 sec
時間修正之後，再次檢視，就沒有任何問題了。
DGMGRL> show configuration;
Configuration - test_dg
Protection Mode: MaxPerformance
Databases:
test - Primary database
stest1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
這個問題也算是早上給自己的一個小警告，一個非常細小的問題就很可能造成很大的延誤。所以環境的檢查還是要細緻，不能輕視。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1840854/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 配置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 11.2.0.4 DataGuard Broker配置過程中可能遇到的問題及解決方法Oracle
- 使用DG_broker工具管理DG之switchover
- Linux下建立Oracle 10g DG和Broker配置LinuxOracle 10g
- Linux 下建立Oracle 10g DG和Broker配置LinuxOracle 10g
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.1)--Data Guard Broker 的配置客戶端
- 使用Broker實現DG切換
- ORACLE資料庫Dataguard dg brokerOracle資料庫
- oracle_DG+broker+Keepalived的部署文件Oracle
- ?【Alibaba中介軟體技術系列】「RocketMQ技術專題」Broker配置介紹及傳送流程、異常(XX Busy)問題分析MQ
- ORACLE 11.2.0.4 DG(Broker) for linux 部署OracleLinux
- Oracle 11g dg broker自動failoverOracleAI
- oracle dataguard broker 配置Oracle
- 曲折的dump匯入及問題分析
- Harbor搭建及配置 問題解決
- 【DG】備庫斷檔問題
- AWD平臺搭建及遇到的問題分析
- React的零渲染問題及原始碼分析React原始碼
- LIS問題；及最少插入字元分析字元
- Oracle 12C TDE問題引發DG不同步案例分析Oracle
- Data Guard Broker系列之二：Data Guard Broker配置實戰
- Oracle 19c Broker配置Oracle
- 資料庫突然當機的問題及分析資料庫
- Zabbix中Orabbix監控失效的問題及分析
- 物化檢視重新整理的問題及分析
- 關於desc的一個奇怪問題及分析
- 關於oracle的索引重建問題及原因分析Oracle索引
- ArrayList 原始碼分析 — 擴容問題及序列化問題原始碼
- ArrayList 原始碼分析 -- 擴容問題及序列化問題原始碼
- CAS原理分析及ABA問題詳解
- Jedis介紹及常見問題分析
- 分詞問題；及最大乘積分析分詞
- 最少插入字元；及分詞問題分析字元分詞