data guard broker 简单使用

前提: 需要提前搭建好data guard (如果还未搭建，可参考我duplicate建dg，比较简单)

配置主库的dg broker静态监听

 LISTENER=
   (DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.203)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
 SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME=SRM)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME=SRM))
     (SID_DESC=
       (SID_NAME=SRM)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (GLOBAL_DBNAME=SRM_DGB))
     (SID_DESC=
       (SID_NAME=SRM)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (GLOBAL_DBNAME=SRM_DGMGRL)))

配置主库的dg broker静态监听

 LISTENER=
   (DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.204)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
 SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME=SRMDG_DGMGRL)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME=SRMDG))
     (SID_DESC=
       (GLOBAL_DBNAME=SRMDG)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME=SRMDG))
     (SID_DESC=
       (GLOBAL_DBNAME=SRMDG_DGB)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME=SRMDG)))

在主库和备库分别启动dg_broker

 -- show parameter dg_broker_start;
 alter system set dg_broker_start=true;

在主库创建dg_broker配置文件

 [oracle@localhost ~]$ dgmgrl
 DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

 Welcome to DGMGRL, type "help" for information.
 DGMGRL> connect sys/oracle@srm
 Connected.
 DGMGRL> help
 DGMGRL> help create 

 create configuration yg_srm_dgBroker as
   primary database is SRM
   connect identifier is SRM;

添加standby备库

 DGMGRL> add database srmdg as connect identifier is srmdg maintained as physical;

查看配置

 DGMGRL> show CONFIGURATION;

 Configuration - yg_srm_dgbroker

   Protection Mode: MaxPerformance
   Databases:
     srm   - Primary database
     srmdg - Physical standby database (disabled)

 --如果standby未启用，则启用一下
 DGMGRL> enable database srmdg;

再次查看详细配置

 DGMGRL> show configuration verbose

 Configuration - yg_srm_dgbroker

   Protection Mode: MaxPerformance
   Databases:
     srm   - Primary database
     srmdg - Physical standby database

   Properties:
     FastStartFailoverThreshold      = '30'
     OperationTimeout                = '30'
     FastStartFailoverLagLimit       = '30'
     CommunicationTimeout            = '1800'
     ObserverReconnect               = '0'
     FastStartFailoverAutoReinstate  = 'TRUE'
     FastStartFailoverPmyShutdown    = 'TRUE'
     BystandersFollowRoleChange      = 'ALL'
     ObserverOverride                = 'FALSE'
     ExternalDestination1            = ''
     ExternalDestination2            = ''
     PrimaryLostWriteAction          = 'CONTINUE'

 Fast-Start Failover: DISABLED

 Configuration Status:
 SUCCESS

第一次测试switchover
```
 DGMGRL> switchover to srmdg
```

验证

 SQL> set line 200
 SQL> col name for a20
 SQL> col open_mode for a20
 SQL> select name, open_mode, database_role, switchover_status from v$database;

第二次切换回来

DGMGRL> switchover to srm

在本地搭建dg broker过程中备库启用后遇到错误：Error: ORA-16664: unable to receive the result from a database

# trace/drcSRM.log日志一直报错 Physical Standby Database srmdg Error ORA-16664 
# trace/alert_SRM.log日志中一直报错 , Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.204)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srmdg_DGB)(CID=(PROGRAM=oracle)(HOST=localhost.localdomain)(USER=oracle))))

解决：

最后在 监听配置文件中加上 sid_DGB 的静态监听才解决,也就是上面的静态监听中的这段：

(SID_DESC=
(GLOBAL_DBNAME=SRMDG_DGB)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=SRMDG))