2009-01-04

Configuring Oracle RAC with Oracle WebLogic Server

Oracle RAC is the solution to provide high-avaibility to your database. It's based on a active-active cluster.

Even before BEA was bought by Oracle, some documentation to use RAC with WLS was provided.

 

First of all : activating JDBC debug mode on WebLogic

 

This step is very important since it will enable us to see how WebLogic behaves.

To do so, enable the verbose debug by clicking "JDBC" on the Debug window :

 

image 

 

(And don't forget to set the log level to DEBUG on your server)

Then, you should see (with a connection pool composed of 5 connections) :

 

<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:test (10) sqlQuery = SELECT 1 FROM DUAL>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < < CE:test (40) returns 1>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:test (10) sqlQuery = SELECT 1 FROM DUAL>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < < CE:test (40) returns 1>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:test (10) sqlQuery = SELECT 1 FROM DUAL>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < < CE:test (40) returns 1>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:test (10) sqlQuery = SELECT 1 FROM DUAL>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < < CE:test (40) returns 1>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:test (10) sqlQuery = SELECT 1 FROM DUAL>

 

Choosing the configuration

 

BEA | Oracle recommendation : Multipools / Multidatasources

 

As exposed by this picture, the principle of Multidatasources is simple : one pool per database.

And one multidatasource to rule them all :)

 

 

clip_image002

 

 

But I had a recent discussion with one of my colleague about a behavior I found not normal.

Let's take a concrete example : if you have a connection pool configured like :

 

image

 

The interesting options here are :

  • => Initial Capacity : 5
  • => Test Frequency : 10 (seconds)

It means that when you start your server, 5 connections will be created. Fine

And the "Test Frequency" allows to test periodically if the connections in the pool are OK.

 

Note : the other test option is to enable the "Test Connections On Reserve" option which will test a connection

prior to be given to a client : be aware that it will increase dramatically the number of hits on the database !

 

If the database crashes, the pool will automatically destroy the bad connections, thanks to the pool testing options.

What when the database comes back ? Well, your connection pool will recreate only one connection.

 

<30 oct. 2008 16 h 03 CET> <Error> <JDBC> <BEA-001112> <Test "SELECT 1 FROM DUAL" set up for pool "JDBC Data Source-0"

failed with exception: "java.sql.SQLRecoverableException: Il n'y a plus de données à lire dans le socket".>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < <* CE:test (30) returns -1>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCConn> <BEA-000000> <ConnectionEnv.cleanup, jconn=oracle.jdbc.driver.T4CConnection@131e51d,

isXA=false, isJTS=false, jconn.isolationLevel=2, initialIsolationLevel=2, dirtyIsolationLevel=false>
<30 oct. 2008 16 h 03 CET> <Info> <JDBC> <BEA-001128> <Connection for pool "JDBC Data Source-0" closed.>
<30 oct. 2008 16 h 03 CET> <Info> <JDBC> <BEA-001067> <Connection for pool "JDBC Data Source-0" refreshed.>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:initialize (10)>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < < CE:initialize (20) returns>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:test (10) sqlQuery = SELECT 1 FROM DUAL>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < > CE:test (10) sqlQuery = SELECT 1 FROM DUAL>
<30 oct. 2008 16 h 03 CET> <Debug> <JDBCInternal> <BEA-000000> < < CE:test (40) returns 1>

 

It's not a bug, it's a feature :)

Seriously, this behavior is WAD (works as designed) : the reason is that a simultaneaous connection creations can be very power consuming.

Then if a rush is in progress, that creation could impact the working users. BEA decided that was not acceptable.

Connections are then recreated when requested.

 

TAF

 

As TAF requires the use of an OCI Driver, which is NOT supported by WebLogic, the use of that method is impossible.

 

CTF / FCF

 

I personnally have a preference for Fast Connection Failover.

First, you have to configure ONS (Oracle Name Service) since it's not (yet) shipped within WLS (as it's the case with IAS).

You will have to download and install it. (Metalink 553806.1).

 

With that connection mode, you simply have to configure the connection URL like :

 

jdbc:oracle:thin:@(DESCRIPTION=
                    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
                    (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))
                    (FAILOVER=off)(LOAD_BALANCE=on)
                    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC)))

 

 

And the properties like :

 

ods.ONSConfiguration("nodes=host1:5000,host2:5000")

 

But if you set a property like that, as stated in the official documentation, you will have a little surprise.

You may end with a configuration file like that :

 

<property>
  <name>ods.ONSConfiguration("nodes</name>
  <value>host1:5000,host2:5000")</value>
</property>

 

In order to have a proper value, type instead : ods.ONSConfiguration=nodes=machine01:port,machine02:port

And it should give the following result in your configuration file :

 

<property>
  <name>ods.ONSConfiguration</name>
  <value>nodes=host1:5000,host2:5000</value>
</property>

 

Configuration Choice

 

Well, there is no good answer to that question, since each architecture & associated needs are different.

You will have to experiment it by yourself. Play with your configuration, make some load tests.

Crash the database, keep an eye on transactions, check if the way failover is handled fits your needs, etc.

Then you should be able to determine which one fits best your architecture.

 

Tips for testing the behavior

 

From an Oracle point of view, simply type the following command in your favorite SQL prompt :

 

select sid, serial#, schemaname, program from v$session where schemaname = 'SAMPLE' (where SAMPLE is the name of your schema)

 

It should display something like :

 

SID                    SERIAL#                SCHEMANAME                     PROGRAM                                                         
---------------------- ---------------------- ------------------------------ ----------------------------------------------------------------
19                     2                      SAMPLE                         SQL Developer                                                   
21                     2                      SAMPLE                         JDBC Thin Client                                                
22                     4                      SAMPLE                         JDBC Thin Client                                                
24                     3                      SAMPLE                         JDBC Thin Client                                                
26                     7                      SAMPLE                         JDBC Thin Client                                                
27                     4                      SAMPLE                         JDBC Thin Client                                                

 

Note : if you want to kill a session, you have to type that command : alter system kill session 'sid,serial#' IMMEDIATE;

 

Note : If during your stress test you encountered an error saying the number of database connections is too high, just type the command :

 

ALTER SYSTEM SET SESSIONS = 226 SCOPE = SPFILE
(SCOPE = SPFILE / MEMORY / BOTH) (SPFile has to be binary) 

and where 226 is the number of simultaneous connections you want

More information at page 28 of this pdf.

 

Happy New Year !!!

I wish you all the best for this new year !