DataGuard服务与客户端连接

1. 需求

当我们实施好单实例库的DataGuard后,由于主库IP不一样,很多时侯主备切换完,应用需要修改相关URL连接才能在备机上使用。
当然针对这种情况,Oracle给我们提供了解决方案,是通过startup on database的触发器解决这个问题,如下:

2. 配置过程

2.1 在primary上创建两个服务

SQL> exec dbms_service.create_service('prim_db','prim_db');
SQL> exec dbms_service.create_service('stby_db','stby_db');

2.2 在primary上起动服务

SQL> exec dbms_service.start_service('prim_db');

2.3 在primary上创建startup触发器

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

2.4 把primary上的服务和触发器,同步到standby

SQL> select thread#, sequence# from v$log where status = 'CURRENT';
SQL> alter system archive log current;

2.5 客户端配置

PRIM_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (FAILOVER = ON)
      (LOAD_BALANCE = OFF)
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prim_db)
    )
  )
  
STBY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (FAILOVER = ON)
      (LOAD_BALANCE = OFF)
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby_db)
    )
  )  

3. 其它

这种客户端配置方式,在主备机如果有一台机器停机IP不通的情况下,可能会登录比较缓慢,所以最好还是要配置个VIP什么的,主备切换完成后,VIP手工切换一下。
对于DataGuard,又有多少人会配置成自动切换呢。

关于紫砂壶

感悟技术人生
此条目发表在DataGuard分类目录,贴了, 标签。将固定链接加入收藏夹。