DATAGURAD BROKER 初探

最新推荐文章于 2024-12-04 22:49:33 发布

有趣是好的

最新推荐文章于 2024-12-04 22:49:33 发布

阅读量706

点赞数 1

分类专栏： ORACLE DATAGUARD

本文链接：https://blog.youkuaiyun.com/stephen11111/article/details/77912818

版权

ORACLE DATAGUARD 专栏收录该内容

8 篇文章

订阅专栏

介绍一下环境： 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.dat
dg_broker_config_file2           string  /u01/app/oracle/product/11.2.0
                         /dbhome_1/dbs/dr2orcl_prd.dat

备库的配置文件：

SQL> show parameter  DG_broker

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1           string  /u01/app/oracle/product/11.2.0
                         /dbhome_1/dbs/dr1orcl_dg.dat
dg_broker_config_file2           string  /u01/app/oracle/product/11.2.0
                         /dbhome_1/dbs/dr2orcl_dg.dat
dg_broker_start              boolean     FALSE
SQL>

因为这里不是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_dmon
oracle     4163      1  0 21:26 ?        00:00:00 ora_dmon_orcl

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

    [oracle@oraprimary admin]$ 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/Oracle123
Connected.
DGMGRL> help create

Creates a broker configuration

Syntax:

  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 configuration
Enabled.
DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxAvailability
  Databases:
    orcl_prd - Primary database
    orcl_dg  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

查看主备库的相关信息

DGMGRL> show database orcl_dg

Database - 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):
    orcl

Database Status:
SUCCESS

DGMGRL> show database orcl_prd

Database - orcl_prd

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    orcl

Database Status:
SUCCESS

DGMGRL>

在转换的过程中报错了

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 database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
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_prd

Database - 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:
SUCCESS

DGMGRL> 



    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 Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect hdp/123123
Connected.
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" updated

DGMGRL>  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" updated
DGMGRL> 


 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 database
ORA-12154: TNS:could not resolve the connect identifier specified

Failed.
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]$ ls
alert_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_dg
Performing 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_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 database
ORA-12154: TNS:could not resolve the connect identifier specified

Failed.
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 convert

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert             string
log_file_name_convert            string


alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl_dg', '/u01/app/oracle/oradata/orcl_prd' scope=spfile

alter 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_server

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
fal_server               string  orcl_prd
SQL> alter system set fal_server=''
  2  /

System altered.

SQL> show parameter fal_server

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
fal_server               string


SQL> alter system set log_archive_dest_1 ='' scope=spfile
  2  /

System altered.

SQL> alter system set log_archive_dest_2 ='' scope=spfile
  2  /