Data Guard Broker配置与主备库切换指南

Data Guard Broker基于分布式的管理框架，可以用来集中创建,管理,配置和监控oracle data guard; Data Guard Broker的组件：
客户端：Oracle grid control和命令行工具DGMGRL
服务端：DMON进程和配置文件
DMON进程的作用: 响应用户的请求，更新broker的配置文件，和data guard配置中的其他服务器通信

Data Guard Broker配置要求：
数据库版本为：企业版10G R1以上,可以是单实例或者rac环境；
在主库和备库上的COMPATIBLE参数必须设定为9.2.0或更高；
必须有oracle网络支持，必须配置LOCAL_LISTENER静态监听器注册(非1521端口必须)；
GLOBAL_DBNAME属性必须设定为db_uniquename_DGMGRL.db_domain；
DG_BROKER_START参数要设置为TRUE；
主库必须运行在归档模式；
使用spfile来保证broker的配置文件和服务器初始化参数文件的一致；
所有的数据库必须在mount(物理备库)或者open(主库和逻辑备库)状态；
在rac环境下还需要配置DB_BROKER_CONFIG_FILEn参数，将该参数指定共享存储上；
rac环境下，需要在OCR中要设定start_options参数为mount；

srvctl add database -d  -o  -s mount
或者
srvctl modify database -d  -o  -s mount

data guard broker体系结构示意图:

配置Data Guard Broker，开始之前应该配置好data guard，并保证日志同步和应用正常

1：配置listener.ora文件和tnsnames.ora文件，添加GLOBAL_DBNAME参数，重启监听器，所有的节点都需要配置

     
     

      
      
[oracle@orcl ~]$ cat $ORACLE_HOME/network/admin/listener.ora  
      
      
SID_LIST_LISTENER =  
      
      
  (SID_LIST =  
      
      
    (SID_DESC =  
      
      
      (SID_NAME = PLSExtProc)  
      
      
      (GLOBAL_DBNAME = physical_DGMGRL.herostart.com)  
      
      
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)  
      
      
      (PROGRAM = extproc)  
      
      
    )  
      
      
  )  
      
      
 
      
      
LISTENER =  
      
      
  (DESCRIPTION_LIST =  
      
      
    (DESCRIPTION =  
      
      
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.30)(PORT = 1521))  
      
      
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))  
      
      
    )  
      
      
  )  
      
      
 
      
      
[oracle@orcl ~]$ lsnrctl services |grep DGMGRL  
      
      
Service "physical_DGMGRL.herostart.com" has 1 instance(s).  
      
      
 
      
      
[oracle@physical ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora  
      
      
PRIMARY =  
      
      
  (DESCRIPTION =  
      
      
    (ADDRESS_LIST =  
      
      
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.20)(PORT = 1521))  
      
      
    )  
      
      
    (CONNECT_DATA =  
      
      
      (SERVICE_NAME = primary.herostart.com)  
      
      
    )  
      
      
  )  
      
      
 
      
      
PHYSICAL =  
      
      
  (DESCRIPTION =  
      
      
    (ADDRESS_LIST =  
      
      
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.30)(PORT = 1521))  
      
      
    )  
      
      
    (CONNECT_DATA =  
      
      
      (SERVICE_NAME = physical.herostart.com)  
      
      
    )  
      
      
  ) 
     
     

2：设置DG_BROKER_START参数，所有的节点都需要配置

     
     

      
      
SQL> show parameter dg_broker_start;  
      
      
 
      
      
NAME                                 TYPE        VALUE  
      
      
------------------------------------ ----------- ------------------------------  
      
      
dg_broker_start                      boolean     FALSE  
      
      
SQL> !ps -ef |grep dmon  
      
      
oracle   17111 17094  0 15:59 pts/2    00:00:00 /bin/bash -c ps -ef |grep dmon  
      
      
oracle   17113 17111  0 15:59 pts/2    00:00:00 grep dmon  
      
      
 
      
      
SQL> alter system set dg_broker_start=TRUE;  
      
      
System altered.  
      
      
 
      
      
SQL> !ps -ef |grep dmon  
      
      
oracle   17128     1  0 15:59 ?        00:00:00 ora_dmon_orcl  
      
      
oracle   17129 17094  0 15:59 pts/2    00:00:00 /bin/bash -c ps -ef |grep dmon 
     
     

3:创建并启用配置文件

     
     

      
      
[oracle@primary dbs]$ dgmgrl sys/123456@primary  
      
      
DGMGRL for Linux: Version 10.2.0.1.0 - Production  
      
      
Copyright (c) 2000, 2005, Oracle. All rights reserved.  
      
      
Welcome to DGMGRL, type "help" for information.  
      
      
Connected.  
      
      
DGMGRL> show configuration;  
      
      
Error: ORA-16532: Data Guard broker configuration does not exist  
      
      
 
      
      
Configuration details cannot be determined by DGMGRL  
      
      
DGMGRL> create configuration 'DG_YANG' as primary database   is 'primary' connect identifier is primary;  
      
      
Configuration "DG_YANG" created with primary database "primary"  
      
      
DGMGRL> show database verbose primary;  
      
      
 
      
      
Database  
      
      
  Name:            primary  
      
      
  Role:            PRIMARY  
      
      
  Enabled:         NO  
      
      
  Intended State:  OFFLINE  
      
      
  Instance(s):  
      
      
    orcl  
      
      
 
      
      
  Properties:  
      
      
    InitialConnectIdentifier        = 'primary' 
      
      
    LogXptMode                      = 'ASYNC' 
      
      
    Dependency                      = '' 
      
      
    DelayMins                       = '0' 
      
      
    Binding                         = 'OPTIONAL' 
      
      
    MaxFailure                      = '0' 
      
      
    MaxConnections                  = '1' 
      
      
    ReopenSecs                      = '300' 
      
      
    NetTimeout                      = '180' 
      
      
    LogShipping                     = 'ON' 
      
      
    PreferredApplyInstance          = '' 
      
      
    ApplyInstanceTimeout            = '0' 
      
      
    ArchiveLagTarget                = '0' 
      
      
    LogArchiveMaxProcesses          = '2' 
      
      
    LogArchiveMinSucceedDest        = '1' 
      
      
    FastStartFailoverTarget         = '' 
      
      
    StatusReport                    = '(monitor)' 
      
      
    InconsistentProperties          = '(monitor)' 
      
      
    InconsistentLogXptProps         = '(monitor)' 
      
      
    SendQEntries                    = '(monitor)' 
      
      
    LogXptStatus                    = '(monitor)' 
      
      
    RecvQEntries                    = '(monitor)' 
      
      
    HostName                        = 'primary.herostart.com' 
      
      
    SidName                         = 'orcl' 
      
      
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=primary.herostart.com)(PORT=1521))' 
      
      
    StandbyArchiveLocation          = 'dgsby_primary' 
      
      
    AlternateLocation               = '' 
      
      
    LogArchiveTrace                 = '0' 
      
      
    LogArchiveFormat                = '%t_%s_%r.dbf' 
      
      
    LatestLog                       = '(monitor)' 
      
      
    TopWaitEvents                   = '(monitor)' 
      
      
 
      
      
Current status for "primary":  
      
      
DISABLED  
      
      
 
      
      
DGMGRL>  add database 'physical' as connect identifier is physical  maintained as physical;  
      
      
Database "physical" added  
      
      
DGMGRL> show database verbose physical;  
      
      
 
      
      
Database  
      
      
  Name:            physical  
      
      
  Role:            PHYSICAL STANDBY  
      
      
  Enabled:         NO  
      
      
  Intended State:  OFFLINE  
      
      
  Instance(s):  
      
      
    orcl  
      
      
 
      
      
  Properties:  
      
      
    InitialConnectIdentifier        = 'physical' 
      
      
    LogXptMode                      = 'ARCH' 
      
      
    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                        = 'physical.herostart.com' 
      
      
    SidName                         = 'orcl' 
      
      
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=physical.herostart.com)(PORT=1521))' 
      
      
    StandbyArchiveLocation          = '/u01/arch/physical_logs/' 
      
      
    AlternateLocation               = '' 
      
      
    LogArchiveTrace                 = '0' 
      
      
    LogArchiveFormat                = '%t_%s_%r.dbf' 
      
      
    LatestLog                       = '(monitor)' 
      
      
    TopWaitEvents                   = '(monitor)' 
      
      
 
      
      
Current status for "physical":  
      
      
DISABLED  
      
      
DGMGRL> enable configuration;  
      
      
Enabled. 
     
     

备注：在enbale configration这步有个小插曲，出现了“Failed to connect to remote database primary. Error is ORA-12514”错误！
详细内容请参考：http://www.itpub.net/thread-1500089-1-1.html

     
     

      
      
 
      
      
enable过程需要点时间，可以在备库上查看日志：  
      
      
[oracle@physical ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/drcorcl.log   
      
      
 
      
      
DGMGRL> show configuration;  
      
      
 
      
      
Configuration  
      
      
  Name:                DG_YANG  
      
      
  Enabled:             YES  
      
      
  Protection Mode:     MaxPerformance  
      
      
  Fast-Start Failover: DISABLED  
      
      
  Databases:  
      
      
    primary  - Primary database  
      
      
    physical - Physical standby database  
      
      
 
      
      
Current status for "DG_YANG":  
      
      
SUCCESS 
     
     

更详细的资料请参考：http://download.oracle.com/docs/cd/B12037_01/server.101/b10822/cli.htm

4：主备库切换

     
     

      
      
SQL> select name,open_mode,database_role,switchover_status from v$database;  
      
      
 
      
      
NAME      OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS  
      
      
--------- ---------- ---------------- --------------------  
      
      
ORCL      READ WRITE PRIMARY          SESSIONS ACTIVE  
      
      
 
      
      
 
      
      
SQL> select name,open_mode,database_role,switchover_status from v$database;  
      
      
 
      
      
NAME      OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS  
      
      
--------- ---------- ---------------- --------------------  
      
      
ORCL      MOUNTED    PHYSICAL STANDBY NOT ALLOWED  
      
      
 
      
      
开始切换，中间会提示连接不到数据库，不知道11g版本会不会这样  
      
      
DGMGRL> switchover to physical;  
      
      
Performing switchover NOW, please wait...  
      
      
Operation requires shutdown of instance "orcl" on database "primary"  
      
      
Shutting down instance "orcl"...  
      
      
ORA-01109: database not open  
      
      
 
      
      
Database dismounted.  
      
      
ORACLE instance shut down.  
      
      
Operation requires shutdown of instance "orcl" on database "physical"  
      
      
Shutting down instance "orcl"...  
      
      
ORA-01109: database not open  
      
      
 
      
      
Database dismounted.  
      
      
ORACLE instance shut down.  
      
      
Operation requires startup of instance "orcl" on database "primary"  
      
      
Starting instance "orcl"...  
      
      
Unable to connect to database  
      
      
ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor Failed.  
      
      
You are no longer connected to ORACLE  
      
      
Please connect again.  
      
      
Unable to start instance "orcl"  
      
      
You must start instance "orcl" manually  
      
      
Operation requires startup of instance "orcl" on database "physical"  
      
      
You must start instance "orcl" manually  
      
      
Switchover succeeded, new primary is "physical"  
      
      
 
      
      
[oracle@primary dbs]$ sqlplus /nolog  
      
      
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 13 14:36:08 2011  
      
      
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
      
      
SQL> conn /as sysdba  
      
      
Connected to an idle instance.  
      
      
SQL> startup mount  
      
      
ORACLE instance started.  
      
      
 
      
      
Total System Global Area  167772160 bytes  
      
      
Fixed Size                  1218316 bytes  
      
      
Variable Size              71305460 bytes  
      
      
Database Buffers           92274688 bytes  
      
      
Redo Buffers                2973696 bytes  
      
      
Database mounted.  
      
      
SQL> alter database recover managed standby database disconnect from session;  
      
      
Database altered.  
      
      
 
      
      
SQL> select open_mode,name,database_role,switchover_status from v$database;  
      
      
 
      
      
OPEN_MODE  NAME      DATABASE_ROLE    SWITCHOVER_STATUS  
      
      
---------- --------- ---------------- --------------------  
      
      
MOUNTED    ORCL      PHYSICAL STANDBY NOT ALLOWED  
      
      
 
      
      
 
      
      
 
      
      
[oracle@physical ~]$ sqlplus /nolog  
      
      
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 13 14:37:06 2011  
      
      
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
      
      
SQL> conn /as sysdba  
      
      
Connected to an idle instance.  
      
      
SQL> startup  
      
      
ORACLE instance started.  
      
      
 
      
      
Total System Global Area  167772160 bytes  
      
      
Fixed Size                  1218316 bytes  
      
      
Variable Size              62916852 bytes  
      
      
Database Buffers          100663296 bytes  
      
      
Redo Buffers                2973696 bytes  
      
      
Database mounted.  
      
      
Database opened.  
      
      
SQL> select open_mode,name,database_role,switchover_status from v$database;  
      
      
 
      
      
OPEN_MODE  NAME      DATABASE_ROLE    SWITCHOVER_STATUS  
      
      
---------- --------- ---------------- --------------------  
      
      
READ WRITE ORCL      PRIMARY          SESSIONS ACTIVE  
      
      
 
      
      
 
      
      
SQL> archive log list;  
      
      
Database log mode              Archive Mode  
      
      
Automatic archival             Enabled  
      
      
Archive destination            /u01/arch/physical_logs/  
      
      
Oldest online log sequence     15  
      
      
Next log sequence to archive   16  
      
      
Current log sequence           16  
      
      
SQL> alter system switch logfile;  
      
      
System altered.  
      
      
 
      
      
SQL> archive log list;  
      
      
Database log mode              Archive Mode  
      
      
Automatic archival             Enabled  
      
      
Archive destination            /u01/arch/physical_logs/  
      
      
Oldest online log sequence     15  
      
      
Next log sequence to archive   17  
      
      
Current log sequence           17  
      
      
 
      
      
 
      
      
备库：  
      
      
SQL> select first_time,next_time,sequence#,applied from v$archived_log where sequence# >= 15;  
      
      
 
      
      
FIRST_TIME          NEXT_TIME            SEQUENCE# APP  
      
      
------------------- ------------------- ---------- ---  
      
      
2011-10-13:14:33:14 2011-10-13:14:38:16         15 YES  
      
      
2011-10-13:14:38:16 2011-10-13:14:42:38         16 YES 
     
     

5：DGMGRL报错如下

     
     

      
      
[oracle@physical u01]$ dgmgrl sys/123456@physical  
      
      
DGMGRL for Linux: Version 10.2.0.1.0 - Production  
      
      
Copyright (c) 2000, 2005, Oracle. All rights reserved.  
      
      
Welcome to DGMGRL, type "help" for information.  
      
      
Connected.  
      
      
DGMGRL> show configuration;  
      
      
 
      
      
Configuration  
      
      
  Name:                DG_YANG  
      
      
  Enabled:             YES  
      
      
  Protection Mode:     MaxPerformance  
      
      
  Fast-Start Failover: DISABLED  
      
      
  Databases:  
      
      
    primary  - Physical standby database  
      
      
    physical - Primary database  
      
      
 
      
      
Current status for "DG_YANG":  
      
      
Warning: ORA-16608: one or more databases have warnings  
      
      
 
      
      
DGMGRL> show database physical statusreport;  
      
      
STATUS REPORT  
      
      
       INSTANCE_NAME   SEVERITY ERROR_TEXT  
      
      
 
      
      
DGMGRL> show database primary statusreport;  
      
      
STATUS REPORT  
      
      
       INSTANCE_NAME   SEVERITY ERROR_TEXT  
      
      
                   *    WARNING ORA-16826: apply service state is inconsistent with the DelayMins property  
      
      
                     
      
      
原因：  
      
      
Oracle Error :: ORA-16826 apply service state is inconsistent with the DelayMins property  
      
      
 
      
      
Cause  
      
      
This warning was caused by one of the following reasons:  
      
      
- The apply service was started without specifying the real-time apply option or without the NODELAY option when the DelayMins property was set to zero.  
      
      
 
      
      
- The apply service was started with the real-time apply option or with the NODELAY option when the DelayMins property was set to a value greater than zero.  
      
      
 
      
      
Action  
      
      
Reenable the standby database to allow the broker to restart the apply service with the apply options that are consistent with the specified value of the DelayMins property.  
      
      
 
      
      
 
      
      
DGMGRL> show database primary DelayMins;  
      
      
  DelayMins = '0' 
      
      
DGMGRL> edit database primary set property DelayMins=1;  
      
      
Property "delaymins" updated          
      
      
 
      
      
DGMGRL> edit database physical set property DelayMins=1;  
      
      
Property "delaymins" updated  
      
      
 
      
      
DGMGRL> show configuration;  
      
      
 
      
      
Configuration  
      
      
  Name:                DG_YANG  
      
      
  Enabled:             YES  
      
      
  Protection Mode:     MaxPerformance  
      
      
  Fast-Start Failover: DISABLED  
      
      
  Databases:  
      
      
    primary  - Physical standby database  
      
      
    physical - Primary database  
      
      
 
      
      
Current status for "DG_YANG":  
      
      
SUCCESS  
      
      
 
      
      
DGMGRL> show database primary statusreport;  
      
      
STATUS REPORT  
      
      
       INSTANCE_NAME   SEVERITY ERROR_TEXT         
     
     

2011年11月2日更新，之前提到在switchover中间会提示连不上数据库，经过排查，发现是tnsnames.ora文件service_name配置不正确导致

本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/686900如需转载请自行联系原作者

ylw6006