DG Broker配置

发表于 | 分类于 ， ， ， | | 阅读次数

前言

Dgmgrl表示Data Guard Manager Command Line Interface，用来管理维护Dataguard，而且该命令系统自带不需要额外安装，命令简单易上手，容易学习，比sqlplus用来更加简单一些.

本文参考了如下几篇文档：

https://community.oracle.com/docs/DOC-1007327

http://blog.csdn.net/u011364306/article/details/50523117

http://blog.csdn.net/u011364306/article/details/50534654

配置

注：本文的配置是基于我的另一篇文章Oracle11g-dataguard配置搭建出来的dg环境进行的配置

配置监听

主备库都需要配置监听，需添加如下监听内容

主库(srmcloud)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srmcloud)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = srmcloud)
      (ORACLE_HOME = /home/oracle/opt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = srmcloud)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = srmcloud_dgmgrl)
      (ORACLE_HOME = /home/oracle/opt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = srmcloud)
    )
  )

其中

1
2
3
4
5
(SID_DESC =
      (GLOBAL_DBNAME = srmcloud_dgmgrl)
      (ORACLE_HOME = /home/oracle/opt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = srmcloud)
    )

为新添加的代码，GLOBAL_DBNAME一定要以dgmgrl结尾，备库一样，修改为如下内容

备库(srmclouddg)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srmclouddg)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = srmclouddg)
      (ORACLE_HOME = /home/oracle/opt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = srmcloud)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = srmclouddg_dgmgrl)
      (ORACLE_HOME = /home/oracle/opt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = srmcloud)
    )
  )

注：添加完成后，重启主备库的监听

配置DG Broker multiplexed configuration files

如下操作在主备库都需要执行

1
2
3
4
5
SQL> alter system set dg_broker_config_file1='/home/oracle/opt/oracle/oradata/srmcloud/dg_config1.dat' scope=both;
System altered.
SQL> alter system set dg_broker_config_file2='/home/oracle/opt/oracle/oradata/srmcloud/dg_config2.dat' scope=both;
System altered.
SQL>

start the Broker

如下操作在主备库都需要执行

1
2
3
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL>

这时可以查看后台启动了一个dmon进程

1
2
3
4
[oracle@srmcloud ~]$ ps -ef | grep dmon
oracle   20464     1  0 05:54 ?        00:00:00 ora_dmon_srmcloud
oracle   20579 18643  0 06:16 pts/1    00:00:00 grep dmon
[oracle@srmcloud ~]$

配置Broker Configuration

1
2
3
4
5
6
7
8
[oracle@srmcloud ~]$ dgmgrl sys/handhand
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.
Connected.
DGMGRL> create configuration srmcloud_srmclouddg_config as primary database is srmcloudconnect identifier is 'srmcloud';
Configuration "srmcloud_srmclouddg_config" created with primary database "srmcloud"
DGMGRL>

注：

  • 其中identifier后面的内容是tns中配置的tnsname
  • 有文章说在oracle12c中，由于配置了LOG_ARCHIVE_DEST_n的参数，导致报如下错误，Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added，这种问题在11g中不会出现，至于12c是否真的会出现，可自行测试，解决办法就是将LOG_ARCHIVE_DEST_n参数置为空，等配置好之后再改回去。

Add the standby to the configuration

1
2
3
4
5
6
7
8
9
10
11
12
DGMGRL> add database srmclouddg as connect identifier is 'srmclouddg';
Database "srmclouddg" added
DGMGRL>show configuration
Configuration - srmcloud_srmclouddg_config
  Protection Mode: MaxPerformance
  Databases:
    srmcloud   - Primary database
    srmclouddg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>

Enable that Configuration.

1
2
3
4
5
6
7
8
9
10
11
12
DGMGRL> enable configuration;
Enabled.
DGMGRL>  show configuration
Configuration - srmcloud_srmclouddg_config
  Protection Mode: MaxPerformance
  Databases:
    srmcloud   - Primary database
    srmclouddg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

验证

验证切换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DGMGRL> switchover to srmclouddg;
Performing switchover NOW, please wait...
Operation requires a connection to instance "srmcloud" on database "srmclouddg"
Connecting to instance "srmcloud"...
Connected.
New primary database "srmclouddg" is opening...
Operation requires startup of instance "srmcloud" on database "srmcloud"
Starting instance "srmcloud"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "srmclouddg"
DGMGRL> show configuration;
Configuration - srmcloud_srmclouddg_config
  Protection Mode: MaxPerformance
  Databases:
    srmclouddg - Primary database
    srmcloud   - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

验证手动关闭，并启动备机，自动启用日志应用

之前需要手动启动日志应用才可以。

这里使用的是上一步切换过的主备库，主库为srmclouddg，备库为srmcloud

备库：

1
2
3
4
5
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

主库：

1
2
3
4
5
6
7
8
9
10
11
12
13
DGMGRL> show database srmcloud
Database - srmcloud
  Role:            PHYSICAL STANDBY
  Intended State:  OFFLINE
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    srmcloud
Database Status:
SHUTDOWN
DGMGRL>

备库：

1
2
3
4
5
6
7
8
9
10
SQL> startup;
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
Database mounted.
Database opened.
SQL>

主库：

1
2
3
4
5
6
7
8
9
10
11
12
tabase - srmcloud
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    srmcloud
Database Status:
SUCCESS
DGMGRL>

可以看出，自动启用了日志应用，不需要在sqlplus中输入alter database recover managed standby database …类似的语句，并且备库自动启动为PHYSICAL STANDBY的角色，不需要手动指定，OPEN_MODE自动为READ ONLY WITH APPLY模式，也不需要手动指定