关于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 9.2.x thin driver (type IV) to
connect to an 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.

【责编:Amy】

--------------------next---------------------