为了更简单的管理data guard可以配置data guard broker来进行管理，配置broker过程如下：
[oracle@oracle11g ~]$ dgmgrl xxx/xxxxx@xxx DGMGRL for Linux: Version 10.2.0.5.0 - Production Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> help The following commands are available: add Add a standby database to the broker configuration connect Connect to an Oracle instance create Create a broker configuration disable Disable a configuration, a database, or Fast-Start Failover edit Edit a configuration, database, or instance enable Enable a configuration, a database, or Fast-Start Failover exit Exit the program failover Change a standby database to be the primary database help Display description and syntax for a command quit Exit the program reinstate Change a disabled database into a viable standby database rem Comment to be ignored by DGMGRL remove Remove a configuration, database, or instance show Display information about a configuration, database, or instance shutdown Shutdown a currently running Oracle instance start Start Fast-Start Failover observer startup Start an Oracle database instance stop Stop Fast-Start Failover observer switchover Switch roles between the primary database and a standby database Use "help" to see syntax for individual commands DGMGRL> show configuration Error: ORA-16532: Data Guard broker configuration does not exist Configuration details cannot be determined by DGMGRL DGMGRL> help create Create a broker configuration Syntax: CREATE CONFIGURATION AS PRIMARY DATABASE IS CONNECT IDENTIFIER IS ;
创建broker配置文件
DGMGRL> create configuration 'broker_dg' as primary database is test connect identifier is test;
Configuration "broker_dg" created with primary database "test"
DGMGRL> show configuration
Configuration
Name: broker_dg
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
test - Primary database
Current status for "broker_dg":
DISABLED
DGMGRL> help show configuration
Display information about a configuration, database, or instance
Syntax:
SHOW CONFIGURATION;
SHOW DATABASE [VERBOSE] [];
SHOW INSTANCE [VERBOSE] []
[ON DATABASE ];
DGMGRL> help add
Add a standby database to the broker configuration
Syntax:
ADD DATABASE AS
CONNECT IDENTIFIER IS
MAINTAINED AS {PHYSICAL|LOGICAL};
向配置文件添加备库(物理备库test_dg)
DGMGRL> add database test_dg as connect identifier is test_dg maintained as physical;
Database "test_dg" added
DGMGRL> show configuration
Configuration
Name: broker_dg
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
test - Primary database
test_dg - Physical standby database
Current status for "broker_dg":
DISABLED
启用broker配置
DGMGRL> enable configuration Enabled.
显示broker配置信息,显示如下错误信息:
DGMGRL> show configuration
Configuration
Name: broker_dg
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
test - Primary database
test_dg - Physical standby database
Current status for "broker_dg":
Warning: ORA-16607: one or more databases have failed
显示主库test的状态报告
DGMGRL> show database test statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
显示备库test_dg的状态报告，显示如下错误信息:
DGMGRL> show database test_dg statusreport Error: ORA-16664: unable to receive the result from a remote database
显示主库test的详细信息
DGMGRL> show database verbose test
Database
Name: test
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
test
Properties:
InitialConnectIdentifier = 'test'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test_ldg/, /u01/app/oracle/oradata/test/'
LogFileNameConvert = '/u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test_ldg/, /u01/app/oracle/oradata/test/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'xxxxxx'
SidName = 'test'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxx)(PORT=1521))'
StandbyArchiveLocation = '/u02/archive/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "test":
SUCCESS
显示备库test_dg的详细信息，显示如下错误:
DGMGRL> show database verbose test_dg
Database
Name: test_dg
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
test_dg
Properties:
InitialConnectIdentifier = 'test_dg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
LogFileNameConvert = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'jingyong1'
SidName = 'test_dg'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521))'
StandbyArchiveLocation = '/u03/app/oracle/archive/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "test_dg":
Error: ORA-16664: unable to receive the result from a remote database
显然是物理备库test_dg出了故障，检查备库的drctest_dg.log该日志文件在oracle10g中存储bdump文件中:
DG 2015-08-04-17:07:48 0 2 0 NSV0: Failed to connect to remote database test. Error is ORA-12514 DG 2015-08-04-17:07:48 0 2 0 NSV0: Failed to send message to site test. Error code is ORA-12514. DG 2015-08-04-17:07:48 0 2 0 DMON: Database test returned ORA-12514 DG 2015-08-04-17:07:48 0 2 0 for opcode = CTL_GET_STATUS, phase = BEGIN, req_id = 1.1.886847999 DG 2015-08-04-17:07:59 0 2 0 RSM 0 received GETPROP request: rid=0x02010000, pid=54 DG 2015-08-04-17:07:59 0 2 0 Database Resource: Get Property InconsistentProperties DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='2', SPFILE='', DATABASE='2' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 SPFILE is missing value for property 'LogArchiveTrace' with sid='test_dg' DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 SPFILE is missing value for property 'LogArchiveFormat' with sid='test_dg' DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 Database Resource GetProperty succeeded DG 2015-08-04-17:07:59 2010000 4 886848003 DMON: MON_PROPERTY operation completed DG 2015-08-04-17:07:59 0 2 0 NSV0: Failed to connect to remote database test. Error is ORA-12514 DG 2015-08-04-17:07:59 0 2 0 NSV0: Failed to send message to site test. Error code is ORA-12514. DG 2015-08-04-17:07:59 0 2 0 DMON: Database test returned ORA-12514 DG 2015-08-04-17:07:59 0 2 0 for opcode = MON_PROPERTY, phase = NULL, req_id = 1.1.886848003 DG 2015-08-04-17:08:03 0 2 0 DRCX: could not find task req_id=1.1.886847999 for PROBE.
从上面的信息中可以看到如下信息:
RSM Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='2', SPFILE='', DATABASE='2' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 SPFILE is missing value for property 'LogArchiveTrace' with sid='test_dg' DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting DG 2015-08-04-17:07:59 0 2 0 SPFILE is missing value for property 'LogArchiveFormat' with sid='test_dg' DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf' DG 2015-08-04-17:07:59 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
这里显示
‘ArchiveLagTarget’ has inconsistent values:METADATA=’0′, SPFILE=”, DATABASE=’0′
这说明archive_lag_target参数spfile文件的值与database,metadata的值不相同(它们都为0)。
‘LogArchiveMaxProcesses’ has inconsistent values:METADATA=’2′, SPFILE=”, DATABASE=’2′ 这说明log_archive_max_processes参数spfile文件的值与database,metadata的值不相同(它们都为2)。
‘LogArchiveMinSucceedDest’ has inconsistent values:METADATA=’1′, SPFILE=”, DATABASE=’1′ 这说明log_archive_min_succeed_dest参数spfile文件的值与database,metadata的值不相同(它们都为1)。
‘LogArchiveTrace’ has inconsistent values:METADATA=’0′, SPFILE='(missing)’, DATABASE=’0′ 这说明log_archive_trace参数spfile文件的值与database,metadata的值不相同（它们都为0)。
‘LogArchiveFormat’ with sid=’test_dg’
DG 2015-08-04-17:07:59 0 2 0 RSM Warning: Property ‘LogArchiveFormat’ has inconsistent values:METADATA=’%t_%s_%r.dbf’, SPFILE='(missing)’, DATABASE=’%t_%s_%r.dbf’ 这说明log_archive_format参数spfile文件的值与database,metadata的值不相同（它们都为’%t_%s_%r.dbf’)。
对以上不一致参数进行修改
SQL> alter system set log_archive_max_processes=2 scope=spfile; System altered. SQL> alter system set archive_lag_target=0 scope=spfile; System altered. SQL> alter system set log_archive_min_succeed_dest=1 scope=spfile; System altered. SQL> alter system set log_archive_trace=0 scope=spfile; System altered. SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile; System altered.
再次检查broker配置
DGMGRL> show database verbose test_dg
Database
Name: test_dg
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
test_dg
Properties:
InitialConnectIdentifier = 'test_dg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
LogFileNameConvert = '/u03/app/oracle/oradata/test_ldg/, /u03/app/oracle/oradata/test/, /u01/app/oracle/oradata/test/, /u03/app/oracle/oradata/test/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'jingyong1'
SidName = 'test_dg'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521))'
StandbyArchiveLocation = '/u03/app/oracle/archive/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "test_dg":
SUCCESS
DGMGRL> show configuration
Configuration
Name: broker_dg
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
test - Primary database
test_dg - Physical standby database
Current status for "broker_dg":
SUCCESS
现在已经能成功显示broker配置中的数据库信息。