ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle11g DataGuard Broker配置

Oracle11g DataGuard Broker配置

原创 Oracle 作者：shiable 时间：2016-11-02 16:18:28 1 删除 编辑

 --本次环境为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;

  4. Database - orcl

  6.   Role: PRIMARY
  7.   Intended State: TRANSPORT-ON
  8.   Instance(s):
  9.     orcl

  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)'

  46. Database Status:
  47. SUCCESS

点击(此处)折叠或打开

  1. 备库
  2. DGMGRL> show database verbose standby

  4. Database - standby

  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

  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)'

  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;

  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博客 ” ，链接：https://blog.itpub.net/28551528/viewspace-2127523/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Real-Time apply异常，求解
下一篇： 不可见索引 invisible
请登录后发表评论 登录
全部评论
shiable

注册时间：2014-04-16

  • 博文量
    15
  • 访问量
    19035

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593