怎么才能够修改配置oracle dg broker
发布网友
共1个回答
热心网友
系统环境：
操作系统： RedHat EL55_
Oracle: Oracle 11.2.0.3.0
wKioL1Ngr6CzJRlnAAHIh9Qh6K0020.jpg
Data Guard 配置：
wKioL1NguMjBENx9AAMDBx4eC-w905.jpg
主库bjdb：
02:21:10 SYS@ TestDB12>select name,dbid,database_role,protection_mode from v$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE
--------- ---------- ---------------- --------------------
TESTDB12 2811829300 PRIMARY MAXIMUM AVAILABILITY
Elapsed: 00:00:00.00
02:21:42 SYS@ TestDB12>
备库shdb：
02:21:18 SYS@ shdb>select name,dbid,database_role,protection_mode from v$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE
--------- ---------- ---------------- --------------------
TESTDB12 2811829300 PHYSICAL STANDBY MAXIMUM AVAILABILITY
Elapsed: 00:00:00.01
开启flashback database：
02:22:53 SYS@ TestDB12>select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
TESTDB12 NO
02:23:12 SYS@ TestDB12>show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4122M
recovery_parallelism integer 0
02:23:44 SYS@ TestDB12>alter database flashback on;
Database altered.
Elapsed: 00:00:01.60
02:24:03 SYS@ TestDB12>select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
TESTDB12 YES
Elapsed: 00:00:00.00
DG Broker 配置：
1.主库设置
2.备库设置
3.创建DataGuard Broker配置
4.添加standby database到配置
5.开启配置
6.验证配置和switch over
DG配置环境：
Database NameTestDB12TestDB12
Database Unqie Namebjdbshdb
Net Service Namebjdbshdb
Version11.2.0.3 for x86_11.2.0.3 for x86_
1.主库设置
DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径,DG_BROKER_START参数设置实例启动的时候是否自动启动Broken.
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/proct/11.2.0/dbhome_1/dbs/dr1bjdb.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/proct/11.2.0/dbhome_1/dbs/dr2bjdb.dat' scope=both sid='*';
System altered.
SQL> alter system set DG_BROKER_START=TRUE scope=both sid='*';
System altered.
设置完上面的参数后,我们还需要修改监听listener.ora文件.我们必须添加一个静态注册的service_name为db_unique_name_DGMGRL.db_domain,这个service_name会在DGMGRL重启数据库的时候用到.通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL在通过静态监听中的service_name连接到数据库,发送启动的命令.如果不这么做的话,在做switch over的时候我们容易遇到TNS-12514错误
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bjdb_DGMGRL)
(SERVICE_NAME = bjdb)
(SID_NAME = TestDB12)
(ORACLE_HOME = /u01/app/oracle/proct/11.2.0/dbhome_1)))
这里需要说明的是GLOBAL_DBNAME=<db_unique_name>_DGMGRL,<db_domain>.
SERVICE_NAME=<db_unique_name>,<db_domain>.
SID_NAME=echo $ORACLE_SID.
ORACLE_HOME=echo $ORACLE_HOME
主备库tnsnames 配置：
BJDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bjsrv)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bjdb)
)
SHDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = shsrv)(PORT = 1521))
)
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = shdb)
)
)
2.备库设置
和主库设置一样,同样需要设置DB_BROKER_CONFIG_FILEn参数和DG_BROKER_START参数.还有静态监听.
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/proct/11.2.0/dbhome_1/dbs/dr1shdb.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/proct/11.2.0/dbhome_1/dbs/dr2shdb.dat' scope=both sid='*';
System altered.
SQL> alter system set DG_BROKER_START=TRUE scope=both sid='*';
System altered.
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = shdb_DGMGRL)
(SERVICE_NAME = shdb)
(SID_NAME = shdb)
(ORACLE_HOME = /u01/app/oracle/proct/11.2.0/dbhome_1)))
3.创建DataGuard Broker配置
在主库上使用dgmgrl连接到数据库.创建配置.
[oracle@dg1 admin]$ dgmgrlDGMGRL for Linux: Version 11.2.0.1.0 - bit ProctionCopyright (c) 2000, 2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" for information.DGMGRL> connect sys/oracleConnected.
DGMGRL> create configuration 'bjdbcfg' as primary database is 'bjdb' connect identifier is 'bjdb';
Configuration "bjdbcfg" created with primary database "bjdb"
DGMGRL>
这里的参数要说明一下.bjdbcfg是配置的名称,这里可以随便填.PRIMARY DATABASE IS ‘bjdb′ ,这儿的bjdb是指database的db_unique_name,而connect identifier is ‘bjdb′这里的bjdb是指tnsname.ora连接到主库的net service name.
我们可以使用show confiruration查看配置信息.
DGMGRL> show configuration
Configuration - bjdbcfg
Protection Mode: MaxAvailability
Databases:
bjdb - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
4.添加standby database到配置
DGMGRL> add database 'shdb' as connect identifier is shdb maintained as physical;
Database "shdb" added
这里的参数要说明一下.add database ‘shdb′ ,这儿的shdb是指database的db_unique_name,而AS CONNECT IDENTIFIER IS shdb 这里的shdb是指tnsname.ora连接到standby database的net service name.
DGMGRL> show configuration
Configuration - bjdbcfg
Protection Mode: MaxAvailability
Databases:
bjdb - Primary database
shdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
5.开启配置
DGMGRL> enable Configuration;Enabled.DGMGRL> DGMGRL>DGMGRL>
DGMGRL> show configuration;
Configuration - bjdbcfg
Protection Mode: MaxAvailability
Databases:
bjdb - Primary database
shdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
修改DG broker 参数：
编辑数据库属性
LogXptMode
默认情况下，Broker 将主数据库设置为使用异步日志传输。针对最高可用性环境时，需要将此设置更改为同步。
NetTimeout
NetTimeout 属性指定在考虑连接丢失前 LGWR 将阻塞对同步模式中来自备用数据库的确认的等待秒数（对应于log_archive_dest_n 的 NET_TIMEOUT 选项）。默认值为 30 秒。使用最高可用性模式时，考虑降低该值以减少备用数据库不可用时的提交阻塞时间。选择一个足够高的值，避免由间歇性网络问题引起的假性断开。本示例使用 10 秒钟。
ObserverConnectIdentifier（11g 及更高版本）
Oracle 数据库 11g 将 ObserverConnectIdentifier 数据库属性添加到 Broker 配置，使您可以为观察器指定一个连接标识符，用于监视主数据库和故障切换目标。默认情况下，观察器和 Data Guard 使用相同的连接标识符在主数据库和备用数据库间进行重做传输和信息交换（Oracle 数据库 11g 中为DGConnectIdentifier，Oracle 数据库 10g 中为InitialConnectIdentifier）。ObserverConnectIdentifier 使您可以指定观察器使用不同的连接标识符。例如，您可以用此参数使观察器使用与客户端应用程序相同的连接标识符监视数据库。
在本指南中，我们将在保留其他属性的默认值，但您应熟悉所有 Broker 配置和数据库属性。Data Guard Broker 文档（10g 和 11g）第 9 章中包含了每个属性的描述。其中一些属性已经在这两个版本中有所改动。
注：Broker 的许多数据库属性与数据库 spfile 参数相对应。Broker 在角色转换、数据库启动/关闭以及其他事件期间，通过执行相应的 ALTER SYSTEM 命令来维护这些参数。如果这些参数在 Broker 外部进行了修改，将出现警告。要查看特定参数，使用“show database ... StatusReport”命令。
edit database db1_a set property LogXptMode='SYNC';edit database db1_a set property NetTimeout=10;edit database db1_b set property NetTimeout=10;
DGMGRL> edit database 'bjdb' set property 'logxptmode'='sync';
Property "logxptmode" updated
DGMGRL> edit database 'shdb' set property 'logxptmode'='sync';
Property "logxptmode" updated
DGMGRL>
DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> show configuration;
Configuration - bjdbcfg
Protection Mode: MaxAvailability
Databases:
bjdb - Primary database
Warning: ORA-16819: fast-start failover observer not started
shdb - (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
DGMGRL>
DGMGRL> start observer;
Observer started
打开新的窗口：
[oracle@shsrv ~]$dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - bit Proction
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> show configuration;
not logged on
DGMGRL> connect sys/oracle@bjdb
Connected.
DGMGRL> show configuration;
Configuration - bjdbcfg
Protection Mode: MaxAvailability
Databases:
bjdb - Primary database
shdb - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
验证FFS:
主库：
02:58:23 SYS@ TestDB12>col FS_FAILOVER_OBSERVER_HOST for a30
02:58:28 SYS@ TestDB12>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold
02:58:39 2 from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES shsrv 30
Elapsed: 00:00:00.01
02:58:46 SYS@ TestDB12>
备库：
02:59:14 SYS@ shdb>col FS_FAILOVER_OBSERVER_HOST for a30
02:59:16 SYS@ shdb>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES shsrv 30
Elapsed: 00:00:00.02
02:59:41 SYS@ shdb>
DGMGRL> show database verbose bjdb;
Database - bjdb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
TestDB12
Properties:
DGConnectIdentifier = 'bjdb'
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 = '3'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/app/oracle/oradata/sh, /u01/app/oracle/oradata/TestDB12'
