中银国际证券无法使用service_name连接数据库

【处理时间】

【客户名称】

中银国际证券

【主机信息】

Dell3850, oracle10.2.0.5 rac

使用JDBC以及SQLPLUS连接数据库

【处理人员】

孟东

【问题说明】

现象：

在客户端使用TAF模式连接数据库时，发现无法连接，报错如下：

事 件 分 析

这套系统为CRM系统，而且已经上线，后来添加别的应用时，才发现无法使用TAF方式进行连接。

首先查看了下集群状态正常，如下

[oracle@rac10g1 ~]$ crs_stat -t

名称 类型 目标 状态 主机

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

ora....G1.lsnr application ONLINE ONLINE rac10g1

ora....0g1.gsd application ONLINE ONLINE rac10g1

ora....0g1.ons application ONLINE ONLINE rac10g1

ora....0g1.vip application ONLINE ONLINE rac10g1

ora....G2.lsnr application ONLINE ONLINE rac10g2

ora....0g2.gsd application ONLINE ONLINE rac10g2

ora....0g2.ons application ONLINE ONLINE rac10g2

ora....0g2.vip application ONLINE ONLINE rac10g2

** application ONLINE ONLINE rac10g2

ora....b1.inst application ONLINE ONLINE rac10g1

ora....b2.inst application ONLINE ONLINE rac10g2

本次排查主要想通过四方面来看：

1、监听状态

2、tnsname配置

3、数据库相关参数

4、客户端版本

1、开始时检查监听状态，都没有问题

[oracle@rac10g1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 09-1月 -2017 06:54:55

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias LISTENER_RAC10G1

Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date 09-1月 -2017 06:06:14

Uptime 0 days 0 hr. 48 min. 40 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac10g1.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.10)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Services Summary...

Service "testdb" has 2 instance(s).

Instance "testdb1", status READY, has 2 handler(s) for this service...

Instance "testdb2", status READY, has 1 handler(s) for this service...

Service "testdb_XPT" has 2 instance(s).

Instance "testdb1", status READY, has 2 handler(s) for this service...

Instance "testdb2", status READY, has 1 handler(s) for this service...

****pleted successfully

[oracle@rac10g1 ~]$

后来发现监听文件的权限为root，将权限更改后重启监听，发现还是无法通过TAF连接，后来发现单独连接一个节点而且指明SID，是可以连接的，但是单独使用service_name无法连接。

2、检查客户端的tns配置，配置如下：

客户端配置为我们标准的TAF配置方式，而且使用相同客户端相同TNS配置连接其他的同版本RAC都没问题，看来只能从其他方面入手了。

#jdbc配置方式

TESTDB3 =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

)

(load_balance = no)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

(FAILOVER_MODE=

(TYPE = select)

(METHOD = preconnect)

)

)

)

testdb1 =

(DESCRIPTION =

(ADDRESS_LIST =

(address =(protocol = tcp)(host = 192.168.56.11)(port = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb)

(INSTANCE_NAME = testdb1)

(failover_mode =

(backup = testdb2)

(type = select)

(method = preconnect)

)

)

)

testdb2 =

(DESCRIPTION =

(ADDRESS_LIST =

(address =(protocol = tcp)(host = 192.168.56.13)(port = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb)

(INSTANCE_NAME = testdb2)

(failover_mode =

(backup = testdb1)

(type = select)

(method = preconnect)

)

)

)

3、数据库参数配置：

remote_listener设置正常

local_listener未配置

service_names也正常

Tnsnames配置如下：

LISTENERS_TESTDB =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac10g1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac10g2-vip)(PORT = 1521))

)

TESTDB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac10g2-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

(INSTANCE_NAME = testdb2)

)

)

TESTDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac10g1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

(INSTANCE_NAME = testdb1)

)

)

TESTDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac10g1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac10g2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

)

)

这里我们发现没有设置local_listener（默认不配置）没有设置，由于使用client-side TAF时，需要设置local_listener来进行处理连接请求，所以，这里我们配置下local_listener参数，并在服务器端tnsnames.ora中配置一条(节点一节点二都要设置)

testdb1_local =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac10g1-vip)(PORT = 1521))

)

然后修改参数：

Alter system set local_listener='testdb1_local' scope=both sid='testdb1;

Alter system set local_listener='testdb2_local' scope=both sid='testdb2;

设置好之后，重启下监听（不重启也能可以）

此时通过客户端连接数据库，发现可以正常连接（关闭节点1，自动切换到节点二）：

4、客户端版本

后来我又测试了下11g的客户端来进行TAF连接，貌似不用设置local_listener也能正常连接。