jdbc 负载均衡连接mysql_关于JDBC客户端如何连接ORACLE数据库RAC的负载均衡

这篇文章主要介绍如何使用jdbc配置连接数据库(oracle的RAC配置的数据库)达到负载均衡的情况.该例子是以2个NODE的情况说明,希望对大家有所帮助.

我的问题是我需要设置oracle9.2.x的thin客户端连接到oracle的RAC环境上.

注:这样连接可以通过RAC自动平衡负载.

--原文如下:

Hi Tom,

I couldn't find this information easily on the net.  So I'm submitting it here

and hope you make it available for anyone else looking for this information.

My problem was I needed to configure the Oracle 9.2.x thin driver (type IV) to

connect to an Oracle Real Application Cluster (RAC) environment.

For example, assuming you have a database called RAC_DB with two nodes - node1

and node2.

You would need to configure your tnsnames.ora with the following information in

the following way:

RAC_DB =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

(LOAD_BALANCE = yes)

(FAILOVER = on)

)

(CONNECT_DATA =

(SERVICE_NAME = RAC_DB)

(FAILOVER_MODE = (TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 20)

(DELAY = 10)

)

)

)

Now if you start a SQL*PLUS session, then you should see a connection on the

node1 instance.  If you start another SQL*PLUS session, then Oracle should

connect you to the node2 instance (automatic load balancing).  It's actually

pretty cool to see the first time.

To pass this same information to the Oracle's JDBC thin driver, you essentially

concatenate a shorten version of the tnsnames information above and pass this to

the driver.

String userid = "scott";

String password = "tiger";

String tnsnames_info =

"(DESCRIPTION=(ADDRESS_LIST=" +

"(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521))" +

"(ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1521))" +

"(LOAD_BALANCE=yes)(FAILOVER=on))" +

"(CONNECT_DATA=(SERVICE_NAME=rac_db)))" ;

String url = "jdbc:oracle:thin:@" + tnsnames_info;

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection dbConnection = DriverManager.getConnection(url, userid, password);

That's it.  If your application creates multiple connection to the database,

then you should see these connections load balance across the two instances.

One last note, Oracle only supports connection to a RAC configuration with the

9i drivers, so you should try to get the latest 9.2.0.4 Oracle JDBC thin driver.

HTH,

-Peter

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

and we said...

you made it really hard.  you just needed the service!  the load balancing and

all could be/should be setup on the listener side!

you have one listener, both databases register with it as a service.

that would be another option.

Reviews

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

I think we tried that and it didn't work...  September 28, 2003

Reviewer:  Peter Tran  from Houston, TX USA

Hi Tom,

I'm pretty sure we tried that but it didn't work with the thin driver.  That

approach will work if you use the OCI driver, but not with the thin driver.

Please send me an example of what you mean or what files I should configure to

test it out.

I'm always opened to easier options.

Thanks,

-Peter

Followup:

you need to set up mts and a single listener, thats it.

pmon on each of the rac instances will tell the listener about the load and away

it goes.  you might not see the round robin right off (both are "not yet

loaded") so it'll be an unbalanced load balance initially but as the system

ramps, it'll balance out.

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

Unknown territory...  September 28, 2003

Reviewer:  Peter Tran  from Houston, TX USA

I'm sorry, but I really lost you with that last recommendation.  Rather than

frustrate you with my ignorance, can you recommend the Oracle documentation that

I should read to brush up on this information?

I'll read this first and come back with questions if I'm still lost.

For example, I don't understand why you want me to setup the database as MTS.

Why can't I use dedicated server mode?

Thanks for the quick response.

-Peter

Followup:

in order for a single listener to service many instances -- on different

machines -- the listener must be servicing shared server connections -- the

listener cannot "fork/exec" a dedicated server since the listener may well not

be running on the machine the instance is on.  it needs to know dispatcher

addresses to redirect the client request to.

it is just inherit in the architecture.