lizongbo at 618119.com 工作,生活,Android,前端,Linode,Ubuntu,nginx,java,apache,tomcat,Resin,mina,Hessian,XMPP,RPC

2007年11月20日

用JDBC连接Oracle 10g RAC遇到的问题

Filed under: DbVisualizer,Oracle,数据库 — 标签:, , , , , , , , , — lizongbo @ 08:56

数据库升级为Oracle 10g RAC,原有的连接方式下,出现如下错误:

出错信息:
– Servlet.service() for servlet jsp threw exception
javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: “o
rg.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFacto
ry (Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12505)(
ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))))”
at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnect
ion(QueryTagSupport.java:276)
at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag
(QueryTagSupport.java:159)

于是根据网上的资料修改了JDBC的URL,正确jdbc的url为:

jdbc:oracle:thin:@(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.104)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = mydb.618119.com) ) )

或者:

jdbc:oracle:thin:@(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = db01.618119.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)
(HOST = db02.618119.com)(PORT = 1521))
(LOAD_BALANCE = yes)
(failover = yes)
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = mydb.618119.com) ) )

结果过依然存在问题:

偶尔会出现错误:

An error occurred while establishing the connection:
Type: java.sql.SQLException Error Code: 17002
Message:
Io 异常: Connection refused(DESCRIPTION=(ERR=1153)(VSNNUM=169869568)(ERROR_STACK=(ERROR=(CODE=1153)(EMFI=4)(ARGS='(ADDRESS=(PROTOCOL=TCP)(HOST=RAC02)(PORT=1521))’))(ERROR=(CODE=305)(EMFI=1))))

试了将驱动替换为10g的ojdbc14.jar.

然后再在DbVisualizer中试了很多次断开连接和重新链接,都还没出错.
但是在web页面中测试,还是经常出现获取不到数据库连接的情况.

在网上查询了很多资料,仍未能解决这个问题, 不得已,只好换成oci方式连接.
在jdbc客户端所在地服务器中,将TNSNAMES.ORA里本地服务名配置为
mydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.104)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb.618119.com)
)
)

oci方式的jdbc url=”jdbc:oracle:oci:@mydb”;

获取数据库连接正常.

主要参考了以下资料 : http://www.ixdba.net/hbcms/article/97/244.html

http://dev2dev.bea.com.cn/bbs/thread.jspa?forumID=123&threadID=44660&messageID=264712

http://article.51edu.com/computer/shujuku/Oracle/200512/42391.html

http://www.dba-oracle.com/art_oramag_rac_taf.htm

http://www.koorka.com/download/free/Oracle_RAC_For_Windows_install.pdf

http://www.koorka.com/download/free/Oracle_RAC_For_Linux_install.pdf

update:

在使用oci模式进行连接时,整个虚拟机内只能有一份oracle的jdbc 驱动,并且所在的机器必须安装了oracle 的client,oracle client必须与驱动版本一致,即,如果是使用10g的jdbc驱动,则必须安装10g的 oracle client,需要Orace client的bin目录需要添加到环境变量的path中,例如:

Path=d:\oracle\product\10.2.0\client_1\bin;D:\Java\apache-ant-1.7.0\bin;D:\J ava\
jdk1.6.0_03\bin;D:\WINDOWS\system32;D:\WINDOWS;D:\WINDOWS\System32\Wbem;D:\Progr
am Files\Microsoft Network Monitor 3\;D:\PROGRA~1\F-Secure\Ssh;

因此在web应用中Oracle jdbc的驱动不能放在WEB-INF/lib目录下,而是要放到 server的lib目录下(例如Tomcat6的D:\Java\apache-tomcat-6.0.14\lib ,Tomcat5的D:\Java\apache-tomcat-5.5.25\common\lib) .

如果出现重复的oracle jdbc驱动文件(或者是多个不同版本的oracle jdbc驱动在一起) ,

则可能出现以下错误信息:

[code]

java.lang.UnsatisfiedLinkError: make_c_state
java.lang.UnsatisfiedLinkError: make_c_state
at oracle.jdbc.oci8.OCIDBAccess.make_c_state(Native Method)
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:309)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:307)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:442)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:321)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)

[/code]

[code]

java.lang.UnsatisfiedLinkError: Native Library D:\oracle\ora92\bin\ocijdbc9.dll
already loaded in another classloader
at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1716)
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1676)
at java.lang.Runtime.loadLibrary0(Runtime.java:822)
at java.lang.System.loadLibrary(System.java:992)
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:294)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:307)

at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va:442)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:321)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

[/code]

udate:

http://lhylj2.spaces.live.com/Blog/cns!7E417A90C3FE44A9!229.entry
这里提到换成 Oracle 9.0.2.4的jdbc驱动可以解决时而连不上的问题,我没有测试过 .

Powered by WordPress