Oracle DG--客户端TAF 配置实现IP漂移

Oracle DG之--客户端TAF 配置

系统环境：

操作系统：RedHat EL5

Oracle： Oracle 11gR2

系统架构图

如图所示：

主数据库（生产库）： BJDB

备库（容灾库）： SHDB

DG的架构模式为Physical DG,数据保护模式为：Max Protection。

1、系统架构

主库：

[email protected] TestDB12>show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string TestDB12

db_unique_name string bjdb

instance_name string TestDB12

service_names string bjdb

[email protected] TestDB12>select name,dbid,database_role,protection_mode from v$database;

NAME DBID DATABASE_ROLE PROTECTION_MODE

------------------------------ ---------- ---------------- --------------------

TESTDB12 2811829300 PRIMARY MAXIMUM PERFORMANCE

主库归档配置：

log_archive_dest_1 string LOCATION=/dsk4/arch_bj

VALID_FOR=(ALL_LOGFILES,ALL_

ROLES)

DB_UNIQUE_NAME=bjdb

log_archive_dest_2 string SERVICE=shdb ASYNC

VALID_FOR=(ONLINE_LOGFILES,P

RIMARY_ROLE)

DB_UNIQUE_NAME=shdb

备库：

[email protected] shdb>show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string TestDB12

db_unique_name string shdb

instance_name string shdb

service_names string shdb

[email protected] shdb>select name,dbid,database_role,protection_mode from v$database;

NAME DBID DATABASE_ROLE PROTECTION_MODE

--------- ---------- ---------------- --------------------

TESTDB12 2811829300 PHYSICAL STANDBY MAXIMUM PERFORMANCE

备库归档日志：

log_archive_dest_1 string LOCATION=/dsk4/arch_sh

VALID_FOR=(ALL_LOGFILES,ALL_

ROLES)

DB_UNIQUE_NAME=shdb

log_archive_dest_2 string SERVICE=bjdb ASYNC

VALID_FOR=(ONLINE_LOGFILES,P

RIMARY_ROLE)

DB_UNIQUE_NAME=bjdb

主备库网络配置：

BJDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = bjsrv)(PORT = 1521))

)

(CONNECT_DATA = (SERVER = DEDICATED)

(SERVICE_NAME = bjdb)

)

SHDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = shsrv)(PORT = 1521))

)

(CONNECT_DATA = (SERVER = DEDICATED)

(SERVICE_NAME = shdb)

)

2、客户端TAF 配置

1）首先在主库上配置一个TAF的service

此服务在数据库出现故障时会发送通知给客户端，允许查询语句在故障转移发生后继续运行。

[email protected] TestDB12>begin

DBMS_SERVICE.CREATE_SERVICE

(service_name=> 'dg_sw',

network_name => 'dg_sw',

aq_ha_notifications =>TRUE,

failover_method => 'BASIC',

failover_type =>'SELECT',

failover_retries => 30,

failover_delay => 5);

end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

2）建立一个存储过程，用于调用service，确保只在主库运行

我们创建一个存储过程来实现此目的，如果当前数据库是主库它就启动此服务，如果是备库就停止。

[email protected] TestDB12>create or replace procedure dg_taf_proc

v_role VARCHAR(30);

if v_role = 'PRIMARY'

begin

select DATABASE_ROLE into v_role from V$DATABASE;

if v_role = 'PRIMARY'

then

DBMS_SERVICE.START_SERVICE('dg_sw');

else

DBMS_SERVICE.STOP_SERVICE('dg_sw');

end if;

end;

Procedure created.

3）创建2个触发器来确保服务可以运行

创建两个触发器，让数据库在启动和角色转换时运行此存储过程。

[email protected] TestDB12>

create or replace TRIGGER dg_taf_trg_startup

after startup on database

begin

dg_taf_proc;

end;

Trigger created.

Elapsed: 00:00:00.08

用于当数据库open时，如果是主库则执行存储过程。

[email protected] TestDB12>

[email protected] TestDB12>create or replace TRIGGER dg_taf_trg_rolechange

after db_role_change on database

begin

dg_taf_proc;

end;

Trigger created.

当数据库切换后，如果是主库则执行存储过程。

4）客户端tnsnames 配置

DG_SW =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.120)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.130)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg_sw)

(FAILOVER_MODE =

(TYPE = session)

(METHOD = basic)

(RETRIES = 180)

(DELAY = 5)

)

注：8.120 是主库，8.130 是备库

3、验证客户端的TAF

1）首先在主库上执行该存储过程

04:19:09 [email protected] TestDB12>execute dg_taf_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

04:22:55 [email protected] TestDB12>show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string TestDB12

db_unique_name string bjdb

instance_name string TestDB12

service_names string dg_sw

注：service_name 变为dg_sw

04:23:01 [email protected] TestDB12> alter system switch logfile;

在主库做日志的切换，将变化应用到备库

2）在备库查询

[email protected] shdb>select trigger_name,trigger_name from dba_triggers

where trigger_name like '%DG%';

TRIGGER_NAME TRIGGER_NAME

------------------------------ ------------------------------

DG_TAF_TRG_STARTUP DG_TAF_TRG_STARTUP

DG_TAF_TRG_ROLECHANGE DG_TAF_TRG_ROLECHANGE

3）客户端连接测试

C:\Documents and Settings\tiany>sqlplus sys/[email protected]_sw as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 23 16:35:28 2014

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL>

此时应连接到主库！

4、主备库切换后的TAF测试

1）主备库切换

主库切换后：

[email protected] TestDB12>select database_role ,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY RECOVERY NEEDED

备库切换后：

[email protected] shdb>select database_role ,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY NOT ALLOWED

Elapsed: 00:00:00.02

2）客户端连接测试

C:\Documents and Settings\tiany>sqlplus sys/[email protected]_sw as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 23 16:43:49 2014

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string TestDB12

db_unique_name string shdb

instance_name string shdb

service_names string dg_sw

切换后，在备库上存储过程启动了TAF的service ，客户端再连接时，自动连接到了当前的主库（原备库）。

@至此，在DG环境下客户端的TAF配置基本完成。

Oracle DG--客户端TAF 配置实现IP漂移

相关推荐