Oracle11g DataGuard Broker配置

 --本次环境为Oracle11.2.0.3 + Readhat 6.5 单实例环境，一个主库，一个物理备库
Data Guard Broker基于分布式的管理框架，可以用来集中创建,管理,配置和监控data guard。

客户端：Oracle grid control和命令行工具DGMGRL。

服务端：DMON进程和配置文件。DMON进程的作用: 响应用户的请求，更新broker的配置文件，和data guard配置中的其他服务器通信

点击(此处)折叠或打开

1. 配置要求：
   
2. 在主库和备库上的COMPATIBLE参数必须设定为9.2.0或更高（11g2.0.3版本的值为11.2.0.0.0完全满足）。
   
3. 必须有oracle网络支持，必须配置LOCAL_LISTENER静态监听注册。
   
4. GLOBAL_DBNAME属性必须设定为db_uniquename_DGMGRL。
   
5. DG_BROKER_START参数要设置为TRUE。

切换前DG示意图

配置详细步骤
1.主备库修改 dg_broker_config_file 参数，并启用broker

点击(此处)折叠或打开

1. 主库
   
2. alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/db_1/dbs/dr1orcl.dat';
   
3. alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/db_1/dbs/dr2orcl.dat';
   
4. 备库
   
5. alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/db_1/dbs/dr1orcl.dat';
   
6. alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/db_1/dbs/dr2orcl.dat';
   
7. 启用BROKER：主备库均执行
   
8. alter system set dg_broker_start=true scope=both;
9. --备注：RAC环境，建议dat文件放在asm共享磁盘上

2.创建并启用配置文件

点击(此处)折叠或打开

1. 主库上执行即可
   
2. [oracle@dg admin]$ dgmgrl connect sys/oracle@orcl;
   
3. 添加主库
   
4. DGMGRL> create configuration orcltest as primary database is orcl connect identifier is orcl;
   
5. Configuration "orcltest" created with primary database "orcl"
   
6. #启用配置文件
   
7. DGMGRL> enable configuration;
   
8. #添加备库
   
9. DGMGRL> add database standby as connect identifier is standby;
   
10. DGMGRL> enable database standby;
    
11. #查看配置信息
    
12. DGMGRL> show configuration
    
13. Configuration - orcltest
    
14.   Protection Mode: MaxPerformance
    
15.   Databases:
    
16.     orcl - Primary database
    
17.     standby - Physical standby database
    
18. Fast-Start Failover: DISABLED
    
19. Configuration Status:
    
20. SUCCESS

3.查看主备库配置信息

点击(此处)折叠或打开

1. 主库
   
2. DGMGRL> show database verbose orcl;
   
3. 
   
4. Database - orcl
   
5. 
   
6.   Role: PRIMARY
   
7.   Intended State: TRANSPORT-ON
   
8.   Instance(s):
   
9.     orcl
   
10. 
    
11.   Properties:
    
12.     DGConnectIdentifier = 'orcl'
    
13.     ObserverConnectIdentifier = ''
    
14.     LogXptMode = 'SYNC'
    
15.     DelayMins = '0'
    
16.     Binding = 'optional'
    
17.     MaxFailure = '0'
    
18.     MaxConnections = '1'
    
19.     ReopenSecs = '300'
    
20.     NetTimeout = '30'
    
21.     RedoCompression = 'DISABLE'
    
22.     LogShipping = 'ON'
    
23.     PreferredApplyInstance = ''
    
24.     ApplyInstanceTimeout = '0'
    
25.     ApplyParallel = 'AUTO'
    
26.     StandbyFileManagement = 'AUTO'
    
27.     ArchiveLagTarget = '0'
    
28.     LogArchiveMaxProcesses = '4'
    
29.     LogArchiveMinSucceedDest = '1'
    
30.     DbFileNameConvert = ''
    
31.     LogFileNameConvert = ''
    
32.     FastStartFailoverTarget = 'standby'
    
33.     InconsistentProperties = '(monitor)'
    
34.     InconsistentLogXptProps = '(monitor)'
    
35.     SendQEntries = '(monitor)'
    
36.     LogXptStatus = '(monitor)'
    
37.     RecvQEntries = '(monitor)'
    
38.     SidName = 'orcl'
    
39.     StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.110)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    
40.     StandbyArchiveLocation = '/u01/archivelog/'
    
41.     AlternateLocation = ''
    
42.     LogArchiveTrace = '0'
    
43.     LogArchiveFormat = 'arch_%r_%t_%s.arc'
    
44.     TopWaitEvents = '(monitor)'
    
45. 
    
46. Database Status:
    
47. SUCCESS

点击(此处)折叠或打开

1. 备库
   
2. DGMGRL> show database verbose standby
   
3. 
   
4. Database - standby
   
5. 
   
6.   Role: PHYSICAL STANDBY
   
7.   Intended State: APPLY-ON
   
8.   Transport Lag: 0 seconds
   
9.   Apply Lag: 0 seconds
   
10.   Real Time Query: ON
    
11.   Instance(s):
    
12.     standby
    
13. 
    
14.   Properties:
    
15.     DGConnectIdentifier = 'standby'
    
16.     ObserverConnectIdentifier = ''
    
17.     LogXptMode = 'SYNC'
    
18.     DelayMins = '0'
    
19.     Binding = 'OPTIONAL'
    
20.     MaxFailure = '0'
    
21.     MaxConnections = '1'
    
22.     ReopenSecs = '300'
    
23.     NetTimeout = '30'
    
24.     RedoCompression = 'DISABLE'
    
25.     LogShipping = 'ON'
    
26.     PreferredApplyInstance = ''
    
27.     ApplyInstanceTimeout = '0'
    
28.     ApplyParallel = 'AUTO'
    
29.     StandbyFileManagement = 'AUTO'
    
30.     ArchiveLagTarget = '0'
    
31.     LogArchiveMaxProcesses = '5'
    
32.     LogArchiveMinSucceedDest = '1'
    
33.     DbFileNameConvert = '/u01/app/oracle/oradata/orcl/, /u01/app/oracle/oradata/standby/'
    
34.     LogFileNameConvert = '/u01/app/oracle/oradata/orcl/, /u01/app/oracle/oradata/standby/'
    
35.     FastStartFailoverTarget = 'orcl'
    
36.     InconsistentProperties = '(monitor)'
    
37.     InconsistentLogXptProps = '(monitor)'
    
38.     SendQEntries = '(monitor)'
    
39.     LogXptStatus = '(monitor)'
    
40.     RecvQEntries = '(monitor)'
    
41.     SidName = 'standby'
    
42.     StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL)(INSTANCE_NAME=standby)(SERVER=DEDICATED)))'
    
43.     StandbyArchiveLocation = '/u01/archivelog/'
    
44.     AlternateLocation = ''
    
45.     LogArchiveTrace = '0'
    
46.     LogArchiveFormat = 'arch_%r_%t_%s.arc'
    
47.     TopWaitEvents = '(monitor)'
    
48. 
    
49. Database Status:
    
50. SUCCESS
51. --这里需要注意的一点是：
52. SERVICE_NAME=standby_DGMGRL 这个service_name参数，默认格式为db_unique_name_DGMGRL,如果这里采用默认格式，那么主备库的监听，就要配置静态监听。
53. 静态监听中的GLOBAL_DBNAME 参数就要配置 db_unique_name_DGMGRL，监听配置及状态如下：
    
54. ###备库监听配置信息（主库类似配置）
55. LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.112)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = standby)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
          (SID_NAME = standby)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = standby_DGMGRL)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
          (SID_NAME = standby)
        )
      )
    ADR_BASE_LISTENER = /u01/app/oracle
    
    
56. ###备库监听状态如下（主库类似）
    
57. LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-NOV-2016 16:12:34
    
    
    Copyright (c) 1991, 2011, Oracle.  All rights reserved.
    
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.112)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    Start Date                02-NOV-2016 08:56:26
    Uptime                    0 days 7 hr. 16 min. 8 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/dg/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.112)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary...
    Service "standby" has 2 instance(s).
      Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
      Instance "standby", status READY, has 1 handler(s) for this service...
    Service "standbyXDB" has 1 instance(s).
      Instance "standby", status READY, has 1 handler(s) for this service...
    Service "standby_DGB" has 1 instance(s).
      Instance "standby", status READY, has 1 handler(s) for this service...
    Service "standby_DGMGRL" has 1 instance(s).
      Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    
    

手工更改service_name参数值
--如果不采用静态监听的方式，那么就要用手工更改该参数的值，命令格式，可以用help帮助提示，help edit 

点击(此处)折叠或打开

1. DGMGRL> edit database htz set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.110)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(INSTANCE_NAME=htz)(SERVER=DEDICATED)))';
   
2. Property "staticconnectidentifier" updated
   
3. DGMGRL> edit database htzb set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby)(INSTANCE_NAME=standby)(SERVER=DEDICATED)))';
   
4. Property "staticconnectidentifier" updated

4.switchover切换测试

点击(此处)折叠或打开

1. 开始切换，原备库切换为主库
   
2. DGMGRL> switchover to standby
   
3. Performing switchover NOW, please wait...
   
4. New primary database "standby" is opening...
   
5. Operation requires shutdown of instance "orcl" on database "orcl"
   
6. Shutting down instance "orcl"...
   
7. ORACLE instance shut down.
   
8. Operation requires startup of instance "orcl" on database "orcl"
   
9. Starting instance "orcl"...
   
10. ORACLE instance started.
    
11. Database mounted.
    
12. Database opened.
    
13. Switchover succeeded, new primary is "standby"

手工查看原主备库成功切换，查看归档日志应用情况正常

点击(此处)折叠或打开

1. #查看主备库状态
2. set lines 1000 pages 1000
   
3. select db_unique_name,open_mode,database_role,protection_mode,switchover_status from v$database;
4. 
   
5. #备库上查看归档日志应用情况

1. set lines 1000 pages 1000
   
2. col NAME for a50
   
3. select NAME,THREAD#,SEQUENCE#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') FIRST_TIME,APPLIED,
   
4. to_char(NEXT_TIME,'yyyy-mm-dd hh24:mi:ss') NEXT_TIME from v$archived_log

切换后DG示意图

---备注：经过实际测试，broker方式来切换主备库，确实教手工敲命令行方式方便一些，在进行主库升级打补丁时，能简化操作，也就是能减少变更的时间，这为大半夜做变更的DBA们来说是一件很欣慰的事情。但是，观察我们网管中心这么多ADG环境，貌似没有该配置，不知为何？是怕丢失数据？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28551528/viewspace-2127523/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28551528/viewspace-2127523/