Data Guard Broker配置

          Oracle Data Guard Broker 是一个分布式管理框架，它不但自动化了 Data Guard 配置的创建、维护和监视，并对这些操作进行统一管理。可以通过 Oracle 企业管理器（它使用 Broker）或 Broker 的专用命令行界面 (DGMGRL) 执行所有管理操作。Data Guard Broker 11 g 还可以使用最大可用性或最佳性能模式将 Data Guard 配置为在数据库出现故障时自动切换。

Data Guard Broker

• 使用最大可用性或最佳性能模式针对配置启用自动数据库故障切换

• 启用可配置事件来触发对目标备用数据库的即时自动切换

• 改善了对重做传输选项的支持，使管理员可以为重做传输服务指定连接描述

• 消除在最大可用性和最佳性能保护模式间更换的数据库停机时间

• 支持使用 Oracle 集群件和冷故障切换集群针对高可用性配置单一实例数据库

1.配置Broker为自启动

在主备库查看dg_broker_start 参数：

该参数默认会被设为FALSE，设为TRUE会随数据库实例而自动启动

该参数只能在Broker未处于运行状态时进行修改

DMON进程是被Broker管理的Oracle后台进程，Broker启动后，DMON进程也就被随之创建

SQL>SHOW PARAMETER DG

 

NAME                              TYPE    VALUE

----------------------------------------------- ------------------------------

dg_broker_config_file1             string    /u01/app/oracle/product/11.2.0/db_1/dbs/dr1standby.dat

dg_broker_config_file2             string    /u01/app/oracle/product/11.2.0/db_1/dbs/dr2standby.dat

dg_broker_start                boolean TRUE

 

SQL>alter system set dg_broker_start = true;

Systemaltered.

SQL>SHOW PARAMETER DG_BROKER_START

 

NAME                              TYPE    VALUE

----------------------------------------------- ------------------------------

dg_broker_start                boolean TRUE

 

参数DG_BROKER_CONFIG_FILE1，DG_BROKER_CONFIG_FILE2，对于RAC，需要改到共享存储上；

只能在Broker处于未运行状态（DG_BROKER_START=FALSE）该参数才能被修改

这一步如果不配置，使用Broker的时候会提示不可用：

[oracle@node3admin]$ dgmgrl sys/oracle@std

DGMGRLfor Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright(c) 2000, 2009, Oracle. All rights reserved.

 

Welcometo DGMGRL, type "help" for information.

Connected.

DGMGRL>show configuration

Error:

ORA-16525: the Data Guard broker is not yet available

 

Configuration details cannot be determined by DGMGRL

=================================================

// *Cause:  The Data Guard broker process was either notyet started, was

//          initializing, or failed to start.

// *Action: If the broker has notbeen started, set the DG_BROKER_START

//          initialization parameter to true andallow the broker to finish

//          initializing before making therequest. If the broker failed to

//          start, check the Data Guard log forpossible errors. Otherwise,

//          retry the operation.

2.启用 Flashback

可以通过如下SQL 查看是否启用了Flashback：

SQL>select flashback_on from v$database;

FLASHBACK_ON

------------------

NO

 

在主备库查看：

SQL>show parameter db_recovery_file_dest

 

NAME                              TYPE    VALUE

----------------------------------------------- ------------------------------

db_recovery_file_dest               string    /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size        big integer 1G

在启用Flashback database 之前，需要先设置db_recovery_file_dest_size参数，而后才可以设置db_recovery_file_dest

如果在备库开启闪回需要先取消recover 进程，不然会报错。

SQL>alter database flashback on;

alterdatabase flashback on

*

ERRORat line 1:

ORA-01153:an incompatible media recovery is active

SQL>alter database recover managed standby database cancel;

Databasealtered.

SQL>alter database flashback on;

Database altered.

SQL>select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

SQL>alter database recover managed standby database disconnect from session usingcurrent logfile;

Database altered.

3.创建配置及添加备库

[oracle@node2/]$ dgmgrl

DGMGRLfor Linux: Version 11.2.0.1.0 - Production Copyright (c) 2000, 2005, Oracle.All rights reserved. Welcome to DGMGRL, type "help" for information.

DGMGRL>connect sys/oracle@primary

Connected.

 

DGMGRL>createconfiguration 'Broker' as primary database is 'primary' connect identifier is primary;

Configuration"Broker" created with primary database "primary"

 

DGMGRL>add database'standby' as connect identifier is standby maintained as physical;

Database"standby" added

DGMGRL> show configuration

 

Configuration

  Name:                Broker

  Enabled:             NO

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    primary - Primary database

    standby - Physical standby database

 

Currentstatus for "Broker":

DISABLED

 

启用 configuration：

DGMGRL> enable configuration

Enabled.

 

DGMGRL>show configuration

 

Configuration

  Name:                Broker

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    primary - Primary database

    standby - Physical standby database

 

Currentstatus for "Broker":

SUCCESS

 

DGMGRL>show database verbose 'primary';

 

Database

  Name:            primary

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    king

 

  Properties:

    InitialConnectIdentifier        = 'primary'

    LogXptMode                      = 'ASYNC'

    Dependency                      = ''

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '180'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '2'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'node1'

    SidName                         = 'king'

    LocalListenerAddress   ='(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))'

    StandbyArchiveLocation          = '/u01/arch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

   LatestLog                       ='(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Currentstatus for "primary":

SUCCESS

DGMGRL>show database verbose 'standby'

 

Database

  Name:            standby

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    king

 

  Properties:

    InitialConnectIdentifier        = 'standby'

    LogXptMode                      = 'SYNC'

    Dependency                      = ''

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'auto'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '2'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'node2'

    SidName                         = 'king'

    LocalListenerAddress            ='(ADDRESS=(PROTOCOL=tcp)(HOST=node2)(PORT=1521))'

    StandbyArchiveLocation          = '/u01/arch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    LatestLog                       = '(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Currentstatus for "standby":

SUCCESS

 

参数属性可以用如下命令进行修改 

EDITDATABASE <database name> SET PROPERTY <property name> =<value>;

 

DGMGRL>edit database primary set property logxptmode=sync;

Property"logxptmode" updated

DGMGRL>show databaseverbose primary        

 

Database

  Name:            primary

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    king

 

  Properties:

    InitialConnectIdentifier        = 'primary'

    LogXptMode                      ='sync'

    Dependency                      = ''

    DelayMins                       = '0'

   Binding                         ='OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '180'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '2'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'node1'

    SidName                         = 'king'

    LocalListenerAddress            ='(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))'

    StandbyArchiveLocation          = '/u01/arch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    LatestLog                       = '(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Currentstatus for "primary":

 4.启用FAST_START_FAILOVER

如果要启用FaststartFailover，需要满足以下条件

   - The DataGuard configuration must be in either MaxAvailability or MaxPerformance protection mode.

   - The LogXptMode property for both the primary database and the fast-start failover target standby database must be set to SYNC if the configuration protection mode is set to MaxAvailability mode.

   - TheLogXptMode property for both the primary database and the fast-start failovertarget standby database must be set to ASYNC if the configuration protectionmode is set to MaxPerformance mode.

  - Theprimary database and the fast-start failover target standby database must both have flashback enabled.

   - No validtarget standby database was specified in the primary databaseFastStartFailoverTarget property prior to the attempt to enable fast-startfailover, and more than one standby database exists in the Data Guardconfiguration.

 

 

启用 FSFO：

DGMGRL> ENABLEFAST_START FAILOVER

Enabled.

 

DGMGRL> SHOW CONFIGURATION VERBOSE

 

Configuration

  Name:                Broker

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: ENABLED

  Databases:

    primary - Primary database

    standby - Physical standby database

            - Fast-Start Failover target

 

Fast-StartFailover

  Threshold: 30 seconds

  Observer: (none)

 

Currentstatus for "Broker":

Warning:ORA-16608: one or more databases have warnings

-- 发生警告是因为Observer没有启动，这个从DGMGRL日志中可以看到。

 

DG2015-07-28-16:23:05        0 2 0 RSM0:HEALTH CHECK WARNING:ORA-16819: Fast-Start Failover observer not started

 

DGMGRL>EDIT CONFIGURATION SET PROPERTY FASTSTARTFAILOVERTHRESHOLD=120;

Property"faststartfailoverthreshold" updated

 

FastStartFailoverThreshold：

Observer和Standby在该参数特性设定时间内均与Primary失去连接，fast-start failover就会发生；

将该参数设定为自己想要Observer和Standby等待的时间（当发现Primary已不可用时）在发起一个failover之前

DGMGRL>SHOW DATABASE VERBOSE PRIMARY

 

Database

  Name:            primary

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    king

 

  Properties:

    InitialConnectIdentifier        = 'primary'

    LogXptMode                      = 'sync'

    Dependency                      = ''

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '180'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '2'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = 'standby'

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

   HostName                        ='node1'

    SidName                         = 'king'

    LocalListenerAddress   ='(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))'

    StandbyArchiveLocation          = '/u01/arch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    LatestLog                       = '(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Currentstatus for "primary":

Warning: ORA-16819: Fast-Start Failoverobserver not started

-- 从这里也可以清楚地看出问题

启动Observer

DGMGRL>START OBSERVER

Observerstarted

注意，启动之后，该前台进程不会退出，会一直挂在这。 直到从其他窗口关闭。

默认情况下，observer会创建一个二进制的文件 fsfo.dat 来保存主库和备库的连接信息。 这个文件会在调用DGMGRL命令的当前窗口下生成。

5.检验查看配置信息

主库：

SQL>SELECTFS_FAILOVER_OBSERVER_PRESENT, FS_FAILOVER_OBSERVER_HOST, FS_FAILOVER_THRESHOLDFROM V$DATABASE;

 

FS_FAILFS_FAILOVE FS_FAILOVER_THRESHOLD

----------------- ---------------------

YESnode2                        120

备库：

SQL>SELECTFS_FAILOVER_OBSERVER_PRESENT, FS_FAILOVER_OBSERVER_HOST, FS_FAILOVER_THRESHOLDFROM V$DATABASE;

 

FS_FAILFS_FAILOVE FS_FAILOVER_THRESHOLD

----------------- ---------------------

YESnode2                        120

[oracle@node2~]$ dgmgrl

DGMGRLfor Linux: Version 11.2.0.1.0 - 64bit Production

 

Copyright(c) 2000, 2005, Oracle. All rights reserved.

 

Welcometo DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@primary

Connected.

DGMGRL>show configuration verbose

 

Configuration

  Name:                Broker

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: ENABLED

  Databases:

    primary - Primary database

   standby - Physical standby database

           - Fast-Start Failover target

 

Fast-StartFailover

  Threshold: 120 seconds

  Observer: node2

 

Currentstatus for "Broker":

SUCCESS

DGMGRL>show database verbose primary

 

Database

  Name:            primary

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    king

 

  Properties:

    InitialConnectIdentifier        = 'primary'

    LogXptMode                      = 'sync'

    Dependency                      = ''

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '180'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '2'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = 'standby'

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'node1'

    SidName                         = 'king'

    LocalListenerAddress            ='(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))'

    StandbyArchiveLocation          = '/u01/arch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    LatestLog                       = '(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Currentstatus for "primary":

SUCCESS

 

DGMGRL>show databaseverbose standby

 

Database

  Name:            standby

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    king

 

  Properties:

    InitialConnectIdentifier        = 'standby'

    LogXptMode                      = 'SYNC'

    Dependency                      = ''

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'auto'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '2'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = 'primary'

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'node2'

    SidName                         = 'king'

    LocalListenerAddress            ='(ADDRESS=(PROTOCOL=tcp)(HOST=node2)(PORT=1521))'

    StandbyArchiveLocation          = '/u01/arch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    LatestLog                       = '(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Currentstatus for "standby":

SUCCESS