当前位置： 首页 > java >正文

rac-rac dg 用role自动启动service

java 2025/8/14 4:26:40

1 需求

主库和DG都是RAC.客户端通过pri_service连接到主库。（连接到DG做查询的类似）
当主备切换时，pri_service自动在新的主库启动， 客户端不需要改动，自动重联到新的主库的pri_service 。

在11.2可以实现自动根据database role启动定义的服务。（ -l PRIMARY 指定只有在primary时才启动这个服务） 但是这需要使用dg broker 才能实现自动启动。如果没有使用dg broker,只能用以前版本的方法，数据库触发器。

本文介绍使用数据库触发器实现在11g随着DG Role切换自动启动相应的service.

2 环境

2.1客户端配置的连接符

  • failover = ON 当发生dg切换时， 旧的主库没有pri_service服务，尝试第二个IP,即新主库的SCAN IP。
  • load_balance=OFF, 只有主库运行pri_service,所以不能在2个RAC之间 load balance.
    必须为OFF.
pri =(DESCRIPTION =(ADDRESS_LIST =(FAILOVER = ON)(LOAD_BALANCE = OFF)(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.78)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.205)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = pri_service)))

2.2 设置

在这里插入图片描述

3 参考文档：

How To Configure Client Failover For Data Guard Connections Using Database Services (Doc ID 1429223.1)

How to create a RAC Database Service With Physical Standby Role Option? (Doc ID 1129143.1)

https://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf

4 在主rac设置pri_service

命令

srvctl add service -d ctp -s pri_service -l PRIMARY -e SESSION -m BASIC -w 10 -z 10 -r 'ctp1,ctp2'
srvctl start service -d ctp -s pri_service
srvctl status service -d ctp -s pri_service
srvctl stop database -d ctp  
srvctl start database -d ctp 
srvctl status service -d ctp -s pri_service

输出

[oracle@rac1 trace]$ srvctl add service -d ctp -s pri_service -l PRIMARY -e SESSION -m BASIC -w 10 -z 10 -r 'ctp1,ctp2'
[oracle@rac1 trace]$ srvctl start service -d ctp -s pri_service
[oracle@rac1 trace]$ srvctl status service -d ctp -s pri_service
Service pri_service is running on instance(s) ctp1,ctp2
[oracle@rac1 trace]$ srvctl stop database -d ctp
[oracle@rac1 trace]$ srvctl start database -d ctp
[oracle@rac1 trace]$ srvctl status service -d ctp -s pri_service
Service pri_service is running on instance(s) ctp1,ctp2  # primary role, so this pri_service is started with database.
[oracle@rac1 trace]$

5 在DG添加 pri_service

命令

srvctl add service -d ctpdgrac -s pri_service -l PRIMARY -e SESSION -m BASIC -w 10 -z 10 -r 'ctp1,ctp2'

输出

oracle@rac1-target ~ $ srvctl add service -d ctpdgrac -s pri_service -l PRIMARY -e SESSION -m BASIC -w 10 -z 10 -r 'ctp1,ctp2'
oracle@rac1-target ~ $

6 在主RAC添加sby_service

命令

srvctl add service -d ctp -s sby_service -l physical_standby  -e SESSION -m BASIC -w 10 -z 10 -r "ctp1,ctp2"
srvctl start service -d ctp -s sby_service
srvctl status service -d ctp -s sby_service
srvctl stop service -d ctp 
srvctl status service -d ctp -s sby_service
srvctl stop database -d ctp
srvctl start database -d ctp
srvctl status service -d ctp -s sby_service

输出

[oracle@rac1 trace]$ srvctl add service -d ctp -s sby_service -l physical_standby  -e SESSION -m BASIC -w 10 -z 10 -r "ctp1,ctp2"
[oracle@rac1 trace]$ srvctl start service -d ctp -s sby_service
[oracle@rac1 trace]$ srvctl status service -d ctp -s sby_service
Service sby_service is running on instance(s) ctp1,ctp2
[oracle@rac1 trace]$  srvctl stop service -d ctp
[oracle@rac1 trace]$  srvctl status service -d ctp -s sby_service
Service sby_service is not running.
[oracle@rac1 trace]$  srvctl stop database -d ctp
[oracle@rac1 trace]$  srvctl start database -d ctp
[oracle@rac1 trace]$ srvctl status service -d ctp -s sby_service
Service sby_service is not running. # primary database, so dby_service is not started with database.
[oracle@rac1 trace]$

7 主库建立trigger

命令

CREATE OR REPLACE TRIGGER startDgServices after startup on database
DECLAREdb_role VARCHAR(30);db_open_mode VARCHAR(30);
BEGINSELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE;IF db_role = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('pri_service'); END IF;IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN DBMS_SERVICE.START_SERVICE('sby_service'); END IF;
END;
/

8 主库切换日志

检查当前DG库实时apply

可以检查备库apply的instance. 看到如下内容，说明在实时同步：

Recovery of Online Redo Log: Thread 2 Group 6 Seq 129 Reading mem 0Mem# 0: +DATADG/ctpdgrac/onlinelog/group_6.759.1200613107
Media Recovery Waiting for thread 1 sequence 483 (in transit)
Recovery of Online Redo Log: Thread 1 Group 7 Seq 483 Reading mem 0Mem# 0: +DATADG/ctpdgrac/onlinelog/group_7.761.1200613109

然后主库切换日志，把建立的service, trigger传导DG库。

alter system archive log current;
alter system archive log current;

输出

SYS@ctp1>alter system archive log current;System altered.SYS@ctp1>alter system archive log current;System altered.SYS@ctp1>

9 在DG库建sby_service

在主库日志传到dg并apply后，可以在dg添加sby_service

srvctl add service -d ctpdgrac -s sby_service -l physical_standby -e SESSION -m BASIC -w 10 -z 10 -r "ctp1,ctp2"
srvctl start service -d ctpdgrac -s sby_service
srvctl status service -d ctpdgrac -s sby_service
#restart db the sby_service should start automatically.
srvctl stop database -d ctpdgrac
srvctl start database -d ctpdgrac
srvctl status service -d ctpdgrac -s sby_service 
#sby_service should be started automatically with database.

输出

oracle@rac1-target ~ $ srvctl add service -d ctpdgrac -s sby_service -l physical_standby -e SESSION -m BASIC -w 10 -z 10 -r "ctp1,ctp2"
oracle@rac1-target ~ $  srvctl start service -d ctpdgrac -s sby_service
oracle@rac1-target ~ $  srvctl status service -d ctpdgrac -s sby_service
Service sby_service is running on instance(s) ctp1,ctp2
oracle@rac1-target ~ $  srvctl stop database -d ctpdgrac
oracle@rac1-target ~ $  srvctl start database -d ctpdgrac
oracle@rac1-target ~ $ srvctl status service -d ctpdgrac -s sby_service
Service sby_service is running on instance(s) ctp1,ctp2  -- sby_service is automatically started with database.
oracle@rac1-target ~ $

10 关闭其它instance

主库只保留instance 1, 其它instance都关闭。

dg库只保留instance 1, 其它instance都关闭。

用sqlplus关闭其它instance 或者 srvctl

--with sqlplus
shut  immediate;
-- with srvctl
srvctl stop instance -d ctp -i ctp2
srvctl stop instance -d ctpdgrac -i ctp2

输出

--primary RAC ctp2SYS@ctp2>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ctp2>--DG RAC ctp2SYS@ctp2>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ctp2>

11 切换

主库切换为DG

set linesize 200
select name,database_role,switchover_status from v$database;
--返回switchover_status值为 TO STANDBY or SESSIONS ACTIVE,可以进行转换
alter database commit to switchover to physical standby with session shutdown;
srvctl stop database -d ctp
srvctl start database -d ctp
srvctl status service -d ctp
用srvctl重启数据库后，触发器根据role启动相应的service.

输出

[oracle@rac1 scripts]$ srvctl stop instance -d ctp -i ctp2
[oracle@rac1 scripts]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed May 21 22:39:51 2025Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing optionsUSERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  ctp1                 rac1                        1 60    97       11.2.0.4.0 20250521 23983      54    23982           000000008F79B768 000000008F4DCDC8SYS@ctp1>set linesize 200
select name,database_role,switchover_status from v$database;
--返回switchover_status值为 TO STANDBY or SESSIONS ACTIVE,可以进行转换
alter database commit to switchover to physical standby with session shutdown;SYS@ctp1>
NAME      DATABASE_ROLE    SWITCHOVER_STATUS
--------- ---------------- --------------------
CTP       PRIMARY          FAILED DESTINATIONSYS@ctp1>SYS@ctp1>Database altered.SYS@ctp1>
SYS@ctp1>
SYS@ctp1>
SYS@ctp1>
SYS@ctp1>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 scripts]$ srvctl stop database -d ctp
PRCC-1016 : ctp was already stopped
[oracle@rac1 scripts]$ srvctl start database -d ctp
[oracle@rac1 scripts]$ srvctl status database -d ctp
Instance ctp1 is running on node rac1
Instance ctp2 is running on node rac2
[oracle@rac1 scripts]$ srvctl status service -d ctp
Service pri_service is not running.
Service sby_service is running on instance(s) ctp1,ctp2
Service testlb is not running.
[oracle@rac1 scripts]$

可以看到新的DG库启动了sby_service.

DG切换为主库

如果当前dg不在apply,需要先开启apply, 追平后再cancel

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.

set linesize 200
select name,open_mode,database_role,switchover_status from v$database;
alter database commit to switchover to primary with session shutdown ;
alter database open;
srvctl stop database -d ctpdgrac
srvctl start database -d ctpdgrac
srvctl status service -d ctpdgrac
用srvctl重启数据库后，触发器根据role启动相应的service.

输出

oracle@rac1-target ~/scripts $ srvctl stop instance -d ctpdgrac -i ctp2
oracle@rac1-target ~/scripts $ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed May 21 22:41:05 2025Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing optionsUSERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  ctp1                 rac1                        1 51    17       11.2.0.4.0 20250521 30228      32    30227           000000008F7B6F48 000000008F4C5DF8SQL> set linesize 200
select name,open_mode,database_role,switchover_status from v$database;SQL>NAME                        OPEN_MODE                                                    DATABASE_ROLE                                    SWITCHOVER_STATUS
--------------------------- ------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
CTP                         READ ONLY                                                    PHYSICAL STANDBY                                 NOT ALLOWEDSQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> set linesize 200
select name,open_mode,database_role,switchover_status from v$database;SQL>NAME                        OPEN_MODE                                                    DATABASE_ROLE                                    SWITCHOVER_STATUS
--------------------------- ------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
CTP                         READ ONLY                                                    PHYSICAL STANDBY                                 TO PRIMARYSQL> alter database commit to switchover to primary with session shutdown ;Database altered.SQL> alter database open;Database altered.SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@rac1-target ~/scripts $ srvctl stop database -d ctpdgrac
oracle@rac1-target ~/scripts $ srvctl start database -d ctpdgrac
oracle@rac1-target ~/scripts $ srvctl status database -d ctpdgrac
Instance ctp1 is running on node rac1
Instance ctp2 is running on node rac2
oracle@rac1-target ~/scripts $ srvctl status service -d ctpdgrac
Service pri_service is running on instance(s) ctp1,ctp2
Service sby_service is not running.
oracle@rac1-target ~/scripts $

可以看到新的主库启动了pri_service.

12 其它

客户端

在切换后，客户端连接会断开。重连时连接到新的主库。

删除service

oracle@rac1-target /u01/app/oracle/product/11.2.0/dbhome_1/dbs $ srvctl remove service -s pri_service -d ctpdgrac
oracle@rac1-target /u01/app/oracle/product/11.2.0/dbhome_1/dbs $ srvctl remove service -s sby_service -d ctpdgrac[oracle@rac1 admin]$ srvctl remove service -s pri_service -d ctp
[oracle@rac1 admin]$ srvctl remove service -s sby_service -d ctp
http://www.xdnf.cn/news/8336.html

相关文章：

  • 青少年编程与数学 02-020 C#程序设计基础 03课题、开始编程
  • 【Python正则表达式终极指南】从零到工程级实战
  • Spring Boot与Kafka集成实践：从入门到精通
  • AOP的代理模式
  • 非关系型数据库NoSQL
  • CMA软件实验室体系建设中的测试方法部分
  • 【计网】五六章习题测试
  • arcgis js统计FeatureLayer的椭球面积、平面面积
  • 如果请求体不是JSON格式，UserController层会怎样？
  • PCA例题
  • UG903 (v2024.2)的中文版
  • 楼宇自控系统赋予建筑智慧动能，加速智慧低碳建筑建设进程
  • 代码随想录算法训练营第四十八天
  • 昆仑芯超节点创新设计：1U 4 卡高密算力，无缝适配各类机房环境
  • Linux之Ext系列文件系统（含动静态库）
  • ansible剧本和角色的使用，部署lnmp
  • 搭建自己的语音对话系统：开源 S2S 流水线深度解析与实战
  • 李宏毅《深度学习》：Self-attention 自注意力机制
  • redis 进行缓存实战-18
  • 第J2周：ResNet50V2 算法实战与解析
  • Python爬虫（35）Python爬虫高阶：基于Docker集群的动态页面自动化采集系统实战
  • 内网渗透——红日靶场四
  • 从逻辑视角学习信息论：概念框架与实践指南
  • 127. 单词接龙
  • WDS 无线桥接
  • 交安安全员：交通工程安全领域的关键角色
  • 无人机桥梁检测如何通过数据存储、边缘AI、无线通讯等技术路线，提升检测效率
  • Seata分布式事物案例及详解
  • R语言开始绘图--柱状图
  • 业务场景中使用 SQL 实现快速数据更新与插入