配置dataguard broker

配置dataguard broker

     配置Dataguard的前提是你已经将Dataguard搭建完成了。DG_broker的作用在于，它不但自动化了 DataGuard 配置的创建、维护和监视，并对这些操作进行统一管理。还可以通过 Broker 的专用命令行界面 (DGMGRL) 执行所有管理操作。Data Guard Broker 11g 还可以使用最大可用性或最大性能模式将 Data Guard 配置为在数据库出现故障时自动切换。

DG_broker其实就是dgmgrl工具，安装数据库软件或数据库管理客户端都可以获取到这个工具。

Data Guard Broker

• 使用最大可用性或最佳性能模式针对配置启用自动数据库故障切换。
• 启用可配置事件来触发对目标备用数据库的即时自动切换。
• 改善了对重做传输选项的支持，使管理员可以为重做传输服务指定连接描述。
• 消除在最大可用性和最佳性能保护模式间更换的数据库停机时间。
• 支持使用 Oracle 集群件和冷故障切换集群针对高可用性配置单一实例数据库。

大体分为五部
（1）Tnsname检查
（2）添加静态监听
（3）修改参数
（4）创建broker configuration
（5）添加备库到broker configuration

primary_tns 主库的tns连接名
standby_tns 备库的tns连接名
<Primary db_unique_name> 主库的唯一名
<Standby db_unique_name> 备库的唯一名
<ORACLE_SID> 主备库的SID


（1）Tnsname检查
[oracle@host1 admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
primary_tns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host1.us.oracle.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <Primary db_unique_name>.us.oracle.com)
    )
  )


standby_tns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2.us.oracle.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <Standby db_unique_name>.us.oracle.com)
    )
  )
[oracle@host2 admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
primary_tns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host1.us.oracle.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <Primary db_unique_name>.us.oracle.com)
    )
  )


standby_tns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2.us.oracle.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <Standby db_unique_name>.us.oracle.com)
    )
  )

（2）添加静态监听
[oracle@host1 admin]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = <Primary db_unique_name>.us.oracle.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = <ORACLE_SID>)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = <Primary db_unique_name> _DGMGRL.us.oracle.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = <ORACLE_SID>)
    )
  )
[oracle@host2 admin]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host2.us.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = <Standby db_unique_name>.us.oracle.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = <ORACLE_SID>)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = <Standby db_unique_name> _DGMGRL.us.oracle.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = <ORACLE_SID>)
    )
  )
  
如果监听中为出现新的静态监听，可reload一下监听
[oracle@host1 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-JUN-2017 13:21:33
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521)))
The command completed successfully  
（3）修改参数
SYS@<ORACLE_SID> > show parameter broker
NAME                                     TYPE         VALUE
------------------------------------          -----------     ------------------------------
dg_broker_config_file1               string        /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1<Primary db_unique_name>.dat
dg_broker_config_file2               string        /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2<Primary db_unique_name>.dat
dg_broker_start                        boolean     FALSE

SYS@<ORACLE_SID> > alter system set dg_broker_start=true;
System altered.

日志内容
Thu Jun 22 11:26:09 2017
ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;
Thu Jun 22 11:26:09 2017
DMON started with pid=23, OS id=3383 
Starting Data Guard Broker (DMON)
Thu Jun 22 11:26:12 2017
INSV started with pid=24, OS id=3385 
Thu Jun 22 11:26:16 2017
NSV0 started with pid=31, OS id=3387 
Thu Jun 22 11:26:19 2017
RSM0 started with pid=35, OS id=3390 
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='<ORACLE_SID>';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='<ORACLE_SID>';


（4）创建broker configuration
[oracle@host1 ~]$ dgmgrl sys/oracle
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.
Connected.
--通过help命令可以查看都可以进行哪些操作(此不可不做)
DGMGRL> help
The following commands are available:

add            Adds a standby database to the broker configuration
connect        Connects to an Oracle database instance
convert        Converts a database from one type to another
create         Creates a broker configuration
disable        Disables a configuration, a database, or fast-start failover
edit           Edits a configuration, database, or instance
enable         Enables a configuration, a database, or fast-start failover
exit           Exits the program
failover       Changes a standby database to be the primary database
help           Displays description and syntax for a command
quit           Exits the program
reinstate      Changes a database marked for reinstatement into a viable standby
rem            Comment to be ignored by DGMGRL
remove         Removes a configuration, database, or instance
show           Displays information about a configuration, database, or instance
shutdown       Shuts down a currently running Oracle database instance
sql            Executes a SQL statement
start          Starts the fast-start failover observer
startup        Starts an Oracle database instance
stop           Stops the fast-start failover observer
switchover     Switches roles between a primary and standby database

Use "help <command>" to see syntax for individual commands
--用帮助查如何去创建一个configuration(此不可不做)
DGMGRL> help create
Creates a broker configuration
Syntax:
  CREATE CONFIGURATION <configuration name> AS
    PRIMARY DATABASE IS <database name>
    CONNECT IDENTIFIER IS <connect identifier>;
--创建一个configuration并添加主库到broker，此步只能在主库的机器上操作。如用其他机器，需要@primary_tns
--其中 <Primary db_unique_name> 为主库的唯一名db_unique_name，primary_tns为连接到<Primary db_unique_name>的Tnsname
DGMGRL> CREATE CONFIGURATION <configuration_name> AS PRIMARY DATABASE IS <Primary db_unique_name> CONNECT IDENTIFIER IS primary_tns;
Configuration "<configuration_name>" created with primary database "<Primary db_unique_name>"
--启用配置
DGMGRL> enable configuration
Enabled.
--显示配置
--可以看到主库已经添加进去并SUCCESS(此不可不做)
DGMGRL> show configuration
Configuration - <configuration_name>
  Protection Mode: MaxPerformance
  Databases:
    <Primary db_unique_name> - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

（5）添加备库到broker configuration
--用帮助查看如何添加一个备库(此不可不做)
DGMGRL> help add
Adds a standby database to the broker configuration
Syntax:
  ADD DATABASE <database name>
    [AS CONNECT IDENTIFIER IS <connect identifier>]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];
--添加备库到配置
DGMGRL> ADD DATABASE <Standby db_unique_name> AS CONNECT IDENTIFIER IS standby_tns;
Database "<Standby db_unique_name>" added
--使备库可用
DGMGRL> enable database <Standby db_unique_name>
Enabled.
--此时可以看到备库已经添加到配置文件
DGMGRL> show configuration
Configuration - <configuration_name>
  Protection Mode: MaxPerformance
  Databases:
    <Primary db_unique_name> - Primary database
    <Standby db_unique_name> - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> 

主日志如下
Thu Jun 22 11:27:40 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Thu Jun 22 11:31:05 2017
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
ALTER SYSTEM SWITCH ALL LOGFILE start (<ORACLE_SID>)
Thu Jun 22 11:31:06 2017
Thread 1 advanced to log sequence 11 (LGWR switch)
  Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/<ORACLE_SID>/redo02.log
ALTER SYSTEM SWITCH ALL LOGFILE complete (<ORACLE_SID>)
Thu Jun 22 11:31:07 2017
Archived Log entry 495 added for thread 1 sequence 10 ID 0x2a1b1542 dest 1:
Thu Jun 22 11:31:18 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='<ORACLE_SID>';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='<ORACLE_SID>';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Thu Jun 22 11:31:47 2017
NSV1 started with pid=29, OS id=3417 
Thu Jun 22 11:32:01 2017
ALTER SYSTEM SET log_archive_config='dg_config=(<Primary db_unique_name>,<Standby db_unique_name>)' SCOPE=BOTH;
Thu Jun 22 11:32:05 2017
ALTER SYSTEM SET log_archive_dest_2='service="standby_tns"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="<Standby db_unique_name>" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Thu Jun 22 11:32:05 2017
NSA2 started with pid=36, OS id=3422 
Thu Jun 22 11:32:08 2017
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: /u01/app/oracle/oradata/<ORACLE_SID>/redo03.log
Archived Log entry 496 added for thread 1 sequence 11 ID 0x2a1b1542 dest 1:
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 12 for destination LOG_ARCHIVE_DEST_2
Thu Jun 22 11:32:49 2017
ARC3: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
备库日志如下
Thu Jun 22 11:31:06 2017
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
Thu Jun 22 11:31:07 2017
Media Recovery Waiting for thread 1 sequence 11
Thu Jun 22 11:31:07 2017
Archived Log entry 434 added for thread 1 sequence 10 ID 0x2a1b1542 dest 1:
Thu Jun 22 11:32:01 2017
ALTER SYSTEM SET log_archive_config='dg_config=(<Standby db_unique_name>,<Primary db_unique_name>)' SCOPE=BOTH;
Thu Jun 22 11:32:01 2017
NSV0 started with pid=31, OS id=3502 
Thu Jun 22 11:32:04 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='<ORACLE_SID>';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='<ORACLE_SID>';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_file_name_convert='<ORACLE_SID>','<ORACLE_SID>' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='primary_tns' SCOPE=BOTH;
Thu Jun 22 11:32:08 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process 3506
RFS[5]: Selected log 4 for thread 1 sequence 12 dbid 700188039 branch 947267305
Thu Jun 22 11:32:49 2017
RFS[6]: Assigned to RFS process 3511
RFS[6]: Selected log 5 for thread 1 sequence 11 dbid 700188039 branch 947267305
Thu Jun 22 11:32:49 2017
Archived Log entry 435 added for thread 1 sequence 11 ID 0x2a1b1542 dest 1:
Thu Jun 22 11:32:50 2017
Media Recovery Log /u01/app/oracle/fast_recovery_area/<Standby db_unique_name>/archivelog/2017_06_22/o1_mf_1_11_dnpgm1hp_.arc
Media Recovery Waiting for thread 1 sequence 12 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/<ORACLE_SID>/standby04.log


查看某个数据库的配置消息 
DGMGRL> show database verbose <Primary db_unique_name>

Database - <Primary db_unique_name>

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    <ORACLE_SID>

  Properties:
    DGConnectIdentifier             = 'primary_tns'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = '<ORACLE_SID>'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<Primary db_unique_name>_DGMGRL.us.oracle.com)(INSTANCE_NAME=<ORACLE_SID>)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database verbose <Standby db_unique_name>

Database - <Standby db_unique_name>
--Role：当前数据库的角色
--Intended State：APPLY-ON 在实时应用日志
--Transport Lag:   传输延迟的时间
--Apply Lag:       应用延迟的时间
--Real Time Query：实时查询
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    <ORACLE_SID>

  Properties:
    DGConnectIdentifier             = 'standby_tns'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = '<ORACLE_SID>, <ORACLE_SID>'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = '<ORACLE_SID>'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<Standby db_unique_name>_DGMGRL.us.oracle.com)(INSTANCE_NAME=<ORACLE_SID>)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> 
  
broker的功能还是很强大的，如果不会使用可以敲入help查看。也可查看单独命令的帮助，例如：help create


--end--

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

转载于:http://blog.itpub.net/30820196/viewspace-2141111/