DATAGURAD BROKER 初探

介绍一下环境： oracle 11.2.0.4
192.168.80.135 oraprimary
192.168.80.136 oradg

主库:orcl_prd
备库：orcl_dg

dgmgrl 其实是oracle提供的一个命令，并不需要单独安装什么的

[oracle@oraprimary oracle]$ which dgmgrl/u01/app/oracle/product/11.2.0/dbhome_1/bin/dgmgrl[oracle@oraprimary oracle]$ ls /u01/app/oracle/product/11.2.0/dbhome_1/bin/dgmgrl/u01/app/oracle/product/11.2.0/dbhome_1/bin/dgmgrl

dgmgrl的配置文件
主库的配置文件：

dg_broker_config_file1           string  /u01/app/oracle/product/11.2.0                         /dbhome_1/dbs/dr1orcl_prd.datdg_broker_config_file2           string  /u01/app/oracle/product/11.2.0                         /dbhome_1/dbs/dr2orcl_prd.dat

备库的配置文件：

SQL> show parameter  DG_brokerNAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------dg_broker_config_file1           string  /u01/app/oracle/product/11.2.0                         /dbhome_1/dbs/dr1orcl_dg.datdg_broker_config_file2           string  /u01/app/oracle/product/11.2.0                         /dbhome_1/dbs/dr2orcl_dg.datdg_broker_start              boolean     FALSESQL> 

因为这里不是RAC，也没有启用asm之类的。所以都放在本地了。

我们在主库和备库为 dgmgrl 各配置一个静态监听

主库：

SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = orcl_prd)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = orcl)    )     (SID_DESC =      (GLOBAL_DBNAME = orcl_prd_DGMGRL)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = orcl)    )

备库：

SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = orcl_dg)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = orcl)    )      (SID_DESC =      (GLOBAL_DBNAME = orcl_dg_DGMGRL)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = orcl)    )

当然监听需要重新加载下：

在每个节点上启用dg_broker_start 动态参数，即时生效

 SQL>  alter system set dg_broker_start=true;System altered.SQL> show parameter dg_broker_start NAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------dg_broker_start              boolean     TRUE[oracle@oraprimary admin]$ ps -ef|grep ora_dmonoracle     4163      1  0 21:26 ?        00:00:00 ora_dmon_orcl

在备库或者主库或者单独安装 dgmgrl 的机器上操作

    [oracle@oraprimary admin]$ dgmgrlDGMGRL for Linux: Version 11.2.0.4.0 - 64bit ProductionCopyright (c) 2000, 2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" for information.DGMGRL> connect sys/Oracle123Connected.DGMGRL> help createCreates a broker configurationSyntax:  CREATE CONFIGURATION <configuration name> AS    PRIMARY DATABASE IS <database name>    CONNECT IDENTIFIER IS <connect identifier>;         create configuration dg as primary database is orcl_prd connect identifier  is orcl_prd指定主库DGMGRL> create configuration dg as primary database is orcl_prd connect identifier  is orcl_prd> ;Configuration "dg" created with primary database "orcl_prd"

指定备库

DGMGRL> add database orcl_dg as connect identifier is orcl_dg > maintained as physical> ;Database "orcl_dg" added

我这里的 database name 用的都是数据库的service_name

使配置生效

DGMGRL> enable configurationEnabled.DGMGRL> show configurationConfiguration - dg  Protection Mode: MaxAvailability  Databases:    orcl_prd - Primary database    orcl_dg  - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESSDGMGRL> 

查看主备库的相关信息

DGMGRL> show database orcl_dgDatabase - orcl_dg  Role:            PHYSICAL STANDBY  Intended State:  APPLY-ON  Transport Lag:   0 seconds (computed 1 second ago)  Apply Lag:       0 seconds (computed 1 second ago)  Apply Rate:      859.00 KByte/s  Real Time Query: ON  Instance(s):    orclDatabase Status:SUCCESSDGMGRL> show database orcl_prdDatabase - orcl_prd  Role:            PRIMARY  Intended State:  TRANSPORT-ON  Instance(s):    orclDatabase Status:SUCCESSDGMGRL> 

在转换的过程中报错了

DGMGRL> switchover to orcl_prdPerforming switchover NOW, please wait...Operation requires a connection to instance "orcl" on database "orcl_prd"Connecting to instance "orcl"...Connected.New primary database "orcl_prd" is opening...Operation requires startup of instance "orcl" on database "orcl_dg"Starting instance "orcl"...Unable to connect to databaseORA-12514: TNS:listener does not currently know of service requested in connect descriptorFailed.Warning: You are no longer connected to ORACLE.Please complete the following steps to finish switchover:

我们在监听日志中看到如下报错：

Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oradg)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl_dg_DGB)(CID=(PROGRAM=oracle)(HOST=oraprimary)(USER=oracle))))  VERSION INFORMATION:        TNS for Linux: Version 11.2.0.4.0 - Production        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

    看来我们监听里的监听名称要修改(上文中的配置已经是正确的配置)

Service "orcl_prd_DGB" has 2 instance(s).  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...  Instance "orcl", status READY, has 1 handler(s) for this service...  Service "orcl_dg_DGB" has 2 instance(s).  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...  Instance "orcl", status READY, has 1 handler(s) for this service...

我们查看比较详细的信息

DGMGRL> show database verbose orcl_prdDatabase - orcl_prd  Role:            PRIMARY  Intended State:  TRANSPORT-ON  Instance(s):    orcl  Properties:    DGConnectIdentifier             = 'orcl_prd'    ObserverConnectIdentifier       = ''    LogXptMode                      = 'SYNC'    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)'    ApplyLagThreshold               = '0'    TransportLagThreshold           = '0'    TransportDisconnectedThreshold  = '30'    SidName                         = 'orcl'    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraprimary)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_prd_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'    StandbyArchiveLocation          = '/u01/app/archive_log/'    AlternateLocation               = ''    LogArchiveTrace                 = '0'    LogArchiveFormat                = '%t_%s_%r.dbf'    TopWaitEvents                   = '(monitor)'Database Status:SUCCESSDGMGRL>     StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_dg_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

无语 dg配置里面的静态参数又变了（其实这应该是正确的吧）

分别是：
orcl_dg_DGMGRL
orcl_prd_DGMGRL

不想修改监听的话 那我们就修改dgmgrl的配置 那我们用命令的方式给它该回来。

[oracle@oraprimary admin]$ dgmgrl DGMGRL for Linux: Version 11.2.0.4.0 - 64bit ProductionCopyright (c) 2000, 2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" for information.DGMGRL> connect hdp/123123Connected.DGMGRL> edit database orcl_prd set property StaticConnectIdentifie='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraprimary)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_prd_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';Property "staticconnectidentifier" updatedDGMGRL>  edit database orcl_dg set property StaticConnectIdentifier='DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_dg_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'> ;Property "staticconnectidentifier" updatedDGMGRL>  edit database orcl_prd set property StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraprimary)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_prd_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

然后在
show database verbose orcl_prd 看下结果即可

不过转换还是报错

DGMGRL> switchover to orcl_prd;Performing switchover NOW, please wait...Operation requires a connection to instance "orcl" on database "orcl_prd"Connecting to instance "orcl"...Connected.New primary database "orcl_prd" is opening...Operation requires startup of instance "orcl" on database "orcl_dg"Starting instance "orcl"...Unable to connect to databaseORA-12154: TNS:could not resolve the connect identifier specifiedFailed.Warning: You are no longer connected to ORACLE.Please complete the following steps to finish switchover:    start up instance "orcl" of database "orcl_dg"

我们只好来查看日志[oracle@oraprimary trace]$ pwd

/u01/app/oracle/diag/rdbms/orcl_prd/orcl/trace[oracle@oraprimary trace]$ lsalert_orcl.log  drcorcl.log Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oraprimary)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl_prd_DGB)(CID=(PROGRAM=oracle)(HOST=oradg)(USER=oracle))))

发现其实还是这个链接的问题,在Prd 设置中 INSTANCE_NAME=ocrl出了错

DGMGRL> switchover to orcl_dgPerforming switchover NOW, please wait...New primary database "orcl_dg" is opening...Operation requires startup of instance "orcl" on database "orcl_prd"Starting instance "orcl"...ORACLE instance started.Database mounted.Database opened.Switchover succeeded, new primary is "orcl_dg"DGMGRL> 

可是 往orcl_prd转的时候还是报错了

DGMGRL> switchover to orcl_prdPerforming switchover NOW, please wait...Operation requires a connection to instance "orcl" on database "orcl_prd"Connecting to instance "orcl"...Connected.New primary database "orcl_prd" is opening...Operation requires startup of instance "orcl" on database "orcl_dg"Starting instance "orcl"...Unable to connect to databaseORA-12154: TNS:could not resolve the connect identifier specifiedFailed.Warning: You are no longer connected to ORACLE.Please complete the following steps to finish switchover:    start up instance "orcl" of database "orcl_dg"

检查主库发现，两个参数没有设置

DbFileNameConvert               = ''    LogFileNameConvert              = ''    模仿orcl_dg库       DbFileNameConvert               = '/u01/app/oracle/oradata/orcl_prd, /u01/app/oracle/oradata/orcl_dg'    LogFileNameConvert              = '/u01/app/oracle/oradata/orcl_prd, /u01/app/oracle/oradata/orcl_dg'

转变成

  DbFileNameConvert               = '/u01/app/oracle/oradata/orcl_dg, /u01/app/oracle/oradata/orcl_prd'    LogFileNameConvert              = '/u01/app/oracle/oradata/orcl_dg, /u01/app/oracle/oradata/orcl_prd'     edit database orcl_prd set property  DbFileNameConvert = '/u01/app/oracle/oradata/orcl_dg, /u01/app/oracle/oradata/orcl_prd'      edit database orcl_prd set property  LogFileNameConvert= '/u01/app/oracle/oradata/orcl_dg, /u01/app/oracle/oradata/orcl_prd'

  show configuration        Warning: ORA-16792: configurable property value is inconsistent with database setting

可能spfile的值也要相应该过来

检查下

   SQL> show parameter convertNAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------db_file_name_convert             stringlog_file_name_convert            stringalter system set db_file_name_convert='/u01/app/oracle/oradata/orcl_dg', '/u01/app/oracle/oradata/orcl_prd' scope=spfilealter system set log_file_name_convert='/u01/app/oracle/oradata/orcl_dg', '/u01/app/oracle/oradata/orcl_prd' scope=spfile

修改正确后没有报错了，不过dg往prd转的时候还是报错

看网上的帖子，还有几个参数要去除，fal_server,log_archive_dest_1
log_archive_dest_2

SQL> show parameter fal_serverNAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------fal_server               string  orcl_prdSQL> alter system set fal_server=''  2  /System altered.SQL> show parameter fal_serverNAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------fal_server               stringSQL> alter system set log_archive_dest_1 ='' scope=spfile  2  /System altered.SQL> alter system set log_archive_dest_2 ='' scope=spfile  2  /

最后这个问题用来很了时间，还没有解决，那么这个问题就待续吧！