Oracle RAC Connection String

[james nuzzo]

Have a SB application that uses Placeholder Properties file crm.properties

# Placeholders crm.*
#    for ORACLE 10g:
crm.jdbc.driver=oracle.jdbc.driver.OracleDriver
crm.jdbc.url=jdbc:oracle:thin:@(description=(load_balance=on)(address=(protocol=tcp)(host=crmdb3a.isus.emc.com)(port=1521))(address=(protocol=tcp)(host=crmdb3b.isus.emc.com)(port=1521))(connect_data=(service_name=CRM03.isus.emc.com)))
#crm.jdbc.url=jdbc:oracle:thin:@crmdb3a.isus.emc.com:1521:crm03a
crm.jdbc.user=SPRINGBATCH
crm.jdbc.password=supersafe
crm.schema=
crm.schema.script=

Note the commented out crm.jdbc.url line specifies a single node in the RAC and works as expected ... correctly.


to populate my dbcp BasicDataSource for my ItemReader

	<beans:bean id="accnTeamdataSource"  class="org.apache.commons.dbcp.BasicDataSource">
		<beans:property name="driverClassName" value="${crm.jdbc.driver}" />
		<beans:property name="url" value="${crm.jdbc.url}" />
		<beans:property name="username" value="${crm.jdbc.user}" />
		<beans:property name="password" value="${crm.jdbc.password}" />
	</beans:bean>

I get the following error ...only fragment shown.

Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Io exception: The Network Adapter could not establish the connection)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
	at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:210)
	at $Proxy12.prepareStatement(Unknown Source)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:494)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:176)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
	... 65 more
Caused by: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection

This should work. Bug? or am I missing something (again!).

[james nuzzo]

So I am missing nothing ... Oracle RAC connection String does not work in Spring Batch?

[Andreas Senft]

If the URL is valid then it rather seems that DBCP could not handle it. Did you try to write a simple standalone Java app that just opens a connection with that URL?
If it's a DBCP problem you might try C3P0 instead.

Regards,
Andreas

[hailspring]

I am using class="org.springframework.jdbc.datasource.DriverM anagerDataSource" rather than BasicDataSource and I could successfully connect to my Oracle RAC server. I am not sure whether that could be the issue though.

As mentioned above, I would try to connecting to the databse as standalone java application first and then go from there. Here is my connection string if you would like to try:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
		<property name="url" value="jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYSERVICE.WORLD)))"/>
		<property name="username" value="myuser"/>
		<property name="password" value="mypassowrd"/>
	</bean>

[james nuzzo]

hailspring & Andreas,

Thanks ... switched to "org.springframework.jdbc.datasource.DriverManager DataSource" and it works.

Looks like the short-coming is in dbcp

Thanks ..

[trisberg]

The DriverManagerDataSource doesn't provide any pooling so it's not a long term solution. Why don't you try using the pool that Oracle provides with their JDBC driver. I know that it does support RAC. Try using the "oracle.jdbc.pool.OracleDataSource" in your configuration. Here is a basic example that I have been using:

    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
      <property name="URL" value="${jdbc.racurl}"/>
      <property name="user" value="${jdbc.username}"/>
      <property name="password" value="${jdbc.password}"/>
      <property name="connectionCachingEnabled" value="true"/>
      <property name="fastConnectionFailoverEnabled" value="true"/>
      <property name="ONSConfiguration" value="${jdbc.racons}"/>
    </bean>

[PShahood]

The DriverManagerDataSource doesn't provide any pooling so it's not a long term solution. Why don't you try using the pool that Oracle provides with their JDBC driver. I know that it does support RAC. Try using the "oracle.jdbc.pool.OracleDataSource" in your configuration. Here is a basic example that I have been using:

    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
      <property name="URL" value="${jdbc.racurl}"/>
      <property name="user" value="${jdbc.username}"/>
      <property name="password" value="${jdbc.password}"/>
      <property name="connectionCachingEnabled" value="true"/>
      <property name="fastConnectionFailoverEnabled" value="true"/>
      <property name="ONSConfiguration" value="${jdbc.racons}"/>
    </bean>

Just to close this out (I'm on Nuzzo's team). The suggestion above was applied and has been working great.

Thanks!

[Dave Syer]

I used DBCP with RAC this week as well though, so don't assume that is the problem (or try the latest version). Anyway, nothing to do with Batch.

[wasan_th]

Dear All
I used DBCP and RAC the same Mr. Dev Syer as I implemented many site for that using both DBCP and RAC as well krub. I think RAC will used some feature as Database clustering.