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 :
(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 :)
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 :
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.
