配置dataguard broker(19C)并主从切换

环境:
OS:CentOS 7
Oracle:19.3.0.0
拓扑结构:一主一从

角色 ip 实例id
主库 192.168.1.102 slnngk
从库 192.168.1.103 slavea
observer机器 192.168.1.104

说明:
(ob机器需要安装同版本的数据库软件)

1 前置条件
已经部署好了一主一从的dataguard，同时ob机器需要安装好同版本的oracle软件

2 主备库和observer机器配置好tns

tnsslnngk =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = slnngk)
    )
  )

tnsslavea =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = slavea)
    )
  )

3 主备库添加静态监听

主库
vi /u01/app/oracle/product/19.3.0.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = slnngk_DGMGRL)
      (ORACLE_HOME =/u01/app/oracle/product/19.3.0.0/db_1)
      (SID_NAME =slnngk)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = slnngk)
      (ORACLE_HOME =/u01/app/oracle/product/19.3.0.0/db_1)
      (SID_NAME =slnngk)
    )
  )

备库
vi /u01/app/oracle/product/19.3.0.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = slavea_DGMGRL)
      (ORACLE_HOME =/u01/app/oracle/product/19.3.0.0/db_1)
      (SID_NAME =slavea)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = slavea)
      (ORACLE_HOME =/u01/app/oracle/product/19.3.0.0/db_1)
      (SID_NAME =slavea)
    )
  )

主备库监听重启动
su - oracle
lsnrctl stop
lsnrctl start
lsnrctl status

若是不配置静态监听器的话需要在observer的机器上进行如下的配置

DGMGRL> edit database slnngk set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit database slavea set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.103)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

4.主备库配置相应参数

主库

alter system set dg_broker_config_file1='/u01/app/oracle/product/19.3.0.0/db_1/dbs/dr1slnngk.dat' scope=both;
alter system set dg_broker_config_file2='/u01/app/oracle/product/19.3.0.0/db_1/dbs/dr2slnngk.dat' scope=both;

主库启用dgbroker
alter system set dg_broker_start=true scope=both;

备库

alter system set dg_broker_config_file1='/u01/app/oracle/product/19.3.0.0/db_1/dbs/dr1slavea.dat' scope=both;
alter system set dg_broker_config_file2='/u01/app/oracle/product/19.3.0.0/db_1/dbs/dr2slavea.dat' scope=both;

文件名称无关紧要,可以随便取,一般放dbs目录下,若是asm的需要放共享磁盘上.

备库启用dgbroker
alter system set dg_broker_start=true scope=both;

6.observer服务器配置/etc/hosts

observer服务器需要配置ip和主机的解析，如下:

[root@19c-slaveb ~]# more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.102 19c
192.168.1.103 19c-slavea

若observer服务器与数据库服务器是部署在一起的话,那么也需要进行配置hosts解析

若不想配置hosts,那么可以在配置文件中写死ip

DGMGRL> edit database slnngk set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk_DGMGRL)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit database slavea set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.103)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea_DGMGRL)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

7.配置dgbroker(在observer机器上操作)

添加主库
在observer机器上执行
[oracle@dg admin]$ dgmgrl sys/oracle@tnsslnngk; ##这里的tnsslnngk是连接主库的tns,也可以连接备库

添加主库

DGMGRL> create configuration slnngktest as primary database is 'slnngk' connect identifier is 'tnsslnngk';
Configuration "slnngktest" created with primary database "slnngk"

slnngktest:配置别名,可以随意取
slnngk:主库的db_unique_name
tnsslnngk:连接主库的tns
#启用配置文件(这个时候生成主库的配置文件dr1slnngk.dat,dr2slnngk.dat)

DGMGRL> enable configuration;
Enabled.

#添加备库

[oracle@dg admin]$ dgmgrl sys/oracle@tnsslnngk;
DGMGRL> add database 'slavea' as connect identifier is 'tnsslavea';
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

这个错误是之前手工部署dataguard的时候配置了相应的的归档路径,如log_archive_dest_2导致的,配置dgbroker后这些配置
都交给DG broker 来管理了，不再需要人为介入设置。
备库执行以下语句,重置参数log_archive_dest_2
SQL> alter system set log_archive_dest_2='' scope=both;

重新执行:

DGMGRL> add database 'slavea' as connect identifier is 'tnsslavea';
Database "slavea" added

DGMGRL>  enable database 'slavea';
Enabled.

8.查看配置信息

[oracle@dg admin]$ dgmgrl sys/oracle@tnsslnngk;
DGMGRL> show configuration

Configuration - slnngktest

  Protection Mode: MaxPerformance
  Members:
  slnngk - Primary database
    slavea - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

9.查看主备库的配置情况

DGMGRL> show database verbose slnngk;
DGMGRL> show database verbose slavea;

10.switchover切换

10.1 连接到其中一个数据库,我这里连接到主库
[oracle@dg admin]$ dgmgrl sys/oracle@tnsslnngk;

连接从库也是可以操作的
[oracle@dg admin]$ dgmgrl sys/oracle@tnsslavea;

10.2 查看配置

DGMGRL> show configuration

Configuration - slnngktest

  Protection Mode: MaxPerformance
  Members:
  slnngk - Primary database
    slavea - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 6 seconds ago)

10.3 切换到slavea

DGMGRL> switchover to slavea;
Performing switchover NOW, please wait...
Operation requires a connection to database "slavea"
Connecting ...
Connected to "slavea"
Connected as SYSDBA.
New primary database "slavea" is opening...
Operation requires start up of instance "slnngk" on database "slnngk"
Starting instance "slnngk"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk_DGMGRL)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))
ORA-12545: Connect failed because target host or object does not exist

Failed.

Please complete the following steps to finish switchover:
        start up instance "slnngk" of database "slnngk"

这里不能正常启动备库是因为在/etc/hosts文件中没有对主机名和ip进行解析导致的，出现这种情况的话，需要在observer机器上配置/etc/hosts解析,同时手工启动备库，手工启动备库会自动应用日志，不需要手工输入alter database recover managed standby database using current logfile disconnect from session

手工启动从库slnngk
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3372217840 bytes
Fixed Size                  8902128 bytes
Variable Size             671088640 bytes
Database Buffers         2684354560 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

从库会自动启动日志应用进程
SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
DGRD      ALLOCATED
DGRD      ALLOCATED
ARCH      CONNECTED
ARCH      CONNECTED
RFS       IDLE
ARCH      CONNECTED
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      APPLYING_LOG

11 rows selected.

posted @ 2023-02-01 15:11 slnngk 阅读(893) 评论(0) 编辑收藏举报

刷新页面返回顶部

配置dataguard broker(19C)并主从切换

公告