Oracle DG 之--DG Broker 配置
系统环境：
操作系统： RedHat EL55_64
Oracle: Oracle 11.2.0.3.0
Data Guard 配置：
主库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 Name | TestDB12 | TestDB12 |
| Database Unqie Name | bjdb | shdb |
| Net Service Name | bjdb | shdb |
| Version | 11.2.0.3 for x86_64 | 11.2.0.3 for x86_64 |
1.主库设置
DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径,DG_BROKER_START参数设置实例启动的时候是否自动启动Broken.
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1bjdb.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/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/product/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/product/11.2.0/dbhome_1/dbs/dr1shdb.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/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/product/11.2.0/dbhome_1)))
3.创建DataGuard Broker配置
在主库上使用dgmgrl连接到数据库.创建配置.
[oracle@dg1 admin]$ dgmgrlDGMGRL for Linux: Version 11.2.0.1.0 - 64bit ProductionCopyright (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 - 64bit Production
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'
LogFileNameConvert = '/dsk1/oradata/sh, /dsk1/oradata/bj, /dsk2/oradata/sh, /dsk2/oradata/bj'
FastStartFailoverTarget = 'shdb'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'TestDB12'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bjsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=bjdb_DGMGRL)(INSTANCE_NAME=TestDB12)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/dsk4/arch_bj'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_%t_%s_%r.log'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database verbose shdb;
Database - shdb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
shdb
Properties:
DGConnectIdentifier = 'shdb'
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/TestDB12, /u01/app/oracle/oradata/sh'
LogFileNameConvert = '/dsk1/oradata/bj, /dsk1/oradata/sh, /dsk2/oradata/bj, /dsk2/oradata/sh'
FastStartFailoverTarget = 'bjdb'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'shdb'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=shdb_DGMGRL)(INSTANCE_NAME=shdb)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/dsk4/arch_sh'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_%t_%s_%r.log'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
6.验证swictover
DGMGRL> switchover to 'shdb';
Performing switchover NOW, please wait...
New primary database "shdb" is opening...
Operation requires shutdown of instance "bjdb" on database
"bjdb"Shutting down instance "bjdb"..
.ORACLE instance shut down.Operation requires startup of instance "bjdb" on database
"bjdb"Starting instance "bjdb"...
ORACLE instance started.
Database mounted.Switchover succeeded,
new primary is "shdb"
bjdb：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
shdb：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
7、failover 验证：
在主库上做shutdown abort的操作，备库会主动的切换为主库！
中止主数据库。
shutdown abort观察器日志：
Initiating Fast-Start Failover to database
"bjdb"...Performing failover NOW, please wait...
Failover succeeded, new primary is "shdb"
通过登录到新主数据库上的 dgmgrl 查看 Broker 配置。 您会看到之前的主数据库现在已禁用。
dgmgrl sys/oracle@shdb configuration
Configuration Name:
FSF Enabled: YES
Protection Mode: MaxAvailability
Databases:
shdb - Primary database
bjdb - Physical standby database (disabled) -
Fast-Start Failover target
Fast-Start Failover: ENABLED
Current status for "FSF":
Warning: ORA-16608: one or more databases have warnings
查看测试数据
登录到新的主数据库并验证更改与之前主数据库一致。
select count(*) from x;
COUNT(*)
----------
68855
将之前中止的主数据库恢复为备用数据库
通过安装数据库启动恢复。注意，数据库此时不会打开。仅当观察器验证主数据库仍为主数据库后，主数据库才会打开。如果观察器发现该数据库不再是主数据库，会尝试将其恢复为故障切换的目标备用数据库。
恢复的第一步是将数据库闪回到备用数据库变为主数据库的 SCN 处（新主数据库上的v$database.standby_became_primary_scn）。如闪回数据库部分中所述，闪回数据库将分成两个阶段进行：恢复阶段和介质恢复阶段。在恢复阶段，闪回数据库使用闪回数据库日志中的前映像块将数据库恢复到standby_became_primary_scn 之前的一点。在介质恢复阶段中，闪回数据库应用重做以将数据库带到standby_became_primary_scn。为使闪回数据库成功，闪回数据库日志中必须包括足够的可用历史记录，并且恢复点和 standby_became_primary_scn 之间生成的所有重做必须可用。如果闪回数据库失败，自动恢复将停止，您将需要手动执行基于 SCN 的恢复以恢复到standby_became_primary_scn，直到完成该恢复。
一旦闪回数据库成功，观察器会将该数据库转换为备用数据库，执行回弹并开始应用服务。
startup mount观察器日志：
Initiating reinstatement for database "bjdb"...Reinstating database "db1_a", please wait...
Operation requires shutdown of instance "TestDB12" on database
"bjdb" Shutting down instance "TestDB12"...
ORA-01109: database not openDatabase dismounted.
ORACLE instance shut down.
Operation requires startup of instance "TestDB12" on database
"bjdb" Starting instance "TestDB12"...
ORACLE instance started.Database mounted.
Continuing to reinstate database "bjdb" ...Reinstatement of database "bjdb" succeeded
dgmgrl 状态：
Configuration Name:
FSF Enabled: YES
Protection Mode: MaxAvailability
Databases:
shdb - Primary database
bjdb - Physical standby database -
Fast-Start Failover target
Fast-Start Failover: ENABLED
Current status for "FSF":SUCCESS
@至此,整个配置过程结束！
