Oracle 10g RAC Issues

[exwhyz]

I am using AppFuse framework (Spring JDBC and Commons DBCP) for an application that currently support 500 concurrent users running on Linux/Apache2/Tomcat5

We recently moved to Oracle 10g RAC and I have implemented the modified connection string for the commons DBCP to use RAC. I am running into issues when I restart the application server I cannot access the database but after refreshing the jsp page a few times (sometimes more) it starts working. And once it starts working I dont see any issues.

I have done some legwork in trying to identify the problem, it seems like when this problem happens I just see a DEBUG message as follows:

DataSourceUtils.doGetConnection(145) | Opening JDBC connection

and nothing else; or I see the following 2 DEBUG messages in succession

DataSourceUtils.doGetConnection(145) | Opening JDBC connection
DataSourceUtils.doCloseConnectionIfNecessary(290) | Closing JDBC connection

It never shows the StatementCreatorUtils.setParameterValue that should normally be the case after opening the connection. It starts working fine after a while and since I am not able to pinpoint the cause of the problem I am posting some information below: (you can see that the first query executes successfully and you see the parameters generated, however the second query executes but nothing happens no error messages at all.


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



/***********************SPRING BEANS CONFIGURATION***********************/
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryB ean">
<property name="jndiName"><value>java:comp/env/jdbc/paneldb</value></property>
</bean>

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource"><ref bean="dataSource"/></property>
</bean>

<bean id="txProxyTemplate" abstract="true" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager"><ref bean="transactionManager"/></property>
<property name="transactionAttributes">
<props>
<prop key="save*">PROPAGATION_REQUIRED</prop>
<prop key="update*">PROPAGATION_REQUIRED</prop>
<prop key="remove*">PROPAGATION_REQUIRED</prop>
<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
</props>
</property>
</bean>


/***********************DBCP POOL CONNECTION URL***********************/
<value>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DEV.synovate)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))</value>


/***********************DEBUG STATEMENTS FROM CONSOLE***********************/

[gopanel] DEBUG [http-7080-Processor23] RdbmsOperation.compile(307) | RdbmsOperation with SQL [SELECT C.PANEL_ID, C.QUESTIONNAIRE_ID, C.SECTION_ID, C.LANGUAGE_ID, A.ATTR_ID, A.LOV_ID, C.ATTR_QUESTION_ID, A.OPTIONAL_FLAG, A.ALLOW_UPDATE_FLAG, C.WIDGET_TYPE, C.WIDGET_SIZE, C.QUESTION_ORDER_NO, C.QUESTION_TEXT, C.PARENT_ATTR_QUESTION_ID, C.QUESTION_TIP, C.QUESTION_TIP_DESCR, C.VALIDATION_MESSAGE, C.VALIDATION_LOGIC, C.VALIDATION_FUNCTION, B.PAGE_DESCR FROM PANEL_ATTRIBUTE A, PANEL_SECTION B, PANEL_ATTR_QUESTION C, PANEL_COUNTRY_LANG D WHERE (A.PANEL_ID = C.PANEL_ID AND A.ATTR_ID = C.ATTR_ID AND B.PANEL_ID = C.PANEL_ID AND B.QUESTIONNAIRE_ID = C.QUESTIONNAIRE_ID AND B.SECTION_ID = C.SECTION_ID AND D.LANGUAGE_ID = C.LANGUAGE_ID AND C.PANEL_ID = ? AND C.QUESTIONNAIRE_ID = ? AND C.SECTION_ID = ? AND D.LANGUAGE_CODE = ?) ORDER BY C.QUESTION_ORDER_NO] compiled
[gopanel] DEBUG [http-7080-Processor23] JdbcTemplate.query(490) | Executing SQL query [SELECT C.PANEL_ID, C.QUESTIONNAIRE_ID, C.SECTION_ID, C.LANGUAGE_ID, A.ATTR_ID, A.LOV_ID, C.ATTR_QUESTION_ID, A.OPTIONAL_FLAG, A.ALLOW_UPDATE_FLAG, C.WIDGET_TYPE, C.WIDGET_SIZE, C.QUESTION_ORDER_NO, C.QUESTION_TEXT, C.PARENT_ATTR_QUESTION_ID, C.QUESTION_TIP, C.QUESTION_TIP_DESCR, C.VALIDATION_MESSAGE, C.VALIDATION_LOGIC, C.VALIDATION_FUNCTION, B.PAGE_DESCR FROM PANEL_ATTRIBUTE A, PANEL_SECTION B, PANEL_ATTR_QUESTION C, PANEL_COUNTRY_LANG D WHERE (A.PANEL_ID = C.PANEL_ID AND A.ATTR_ID = C.ATTR_ID AND B.PANEL_ID = C.PANEL_ID AND B.QUESTIONNAIRE_ID = C.QUESTIONNAIRE_ID AND B.SECTION_ID = C.SECTION_ID AND D.LANGUAGE_ID = C.LANGUAGE_ID AND C.PANEL_ID = ? AND C.QUESTIONNAIRE_ID = ? AND C.SECTION_ID = ? AND D.LANGUAGE_CODE = ?) ORDER BY C.QUESTION_ORDER_NO]
[gopanel] DEBUG [http-7080-Processor23] DataSourceUtils.doGetConnection(145) | Opening JDBC connection
[gopanel] DEBUG [http-7080-Processor23] StatementCreatorUtils.setParameterValue(83) | Setting SQL statement parameter value: columnIndex 1, parameter value [3], valueClass [java.math.BigDecimal], sqlType 3
[gopanel] DEBUG [http-7080-Processor23] StatementCreatorUtils.setParameterValue(83) | Setting SQL statement parameter value: columnIndex 2, parameter value [1], valueClass [java.math.BigDecimal], sqlType 3
[gopanel] DEBUG [http-7080-Processor23] StatementCreatorUtils.setParameterValue(83) | Setting SQL statement parameter value: columnIndex 3, parameter value [1], valueClass [java.math.BigDecimal], sqlType 3
[gopanel] DEBUG [http-7080-Processor23] StatementCreatorUtils.setParameterValue(83) | Setting SQL statement parameter value: columnIndex 4, parameter value [en_GB], valueClass [java.lang.String], sqlType 12
[gopanel] DEBUG [http-7080-Processor23] RdbmsOperation.compile(307) | RdbmsOperation with SQL [SELECT TVIN.TYPE_VALUE_ID, TV.LOV_ID, TV.LIST_ORDER_NO, TVIN.LIST_VALUE_DESCR FROM TYPE_VALUE TV INNER JOIN TYPE_VALUE_IN_NLS TVIN ON TVIN.TYPE_VALUE_ID = TV.TYPE_VALUE_ID WHERE TV.LOV_ID = ? AND TVIN.LANGUAGE_ID = ? ORDER BY TO_NUMBER(LIST_ORDER_NO)] compiled
[gopanel] DEBUG [http-7080-Processor23] JdbcTemplate.query(490) | Executing SQL query [SELECT TVIN.TYPE_VALUE_ID, TV.LOV_ID, TV.LIST_ORDER_NO, TVIN.LIST_VALUE_DESCR FROM TYPE_VALUE TV INNER JOIN TYPE_VALUE_IN_NLS TVIN ON TVIN.TYPE_VALUE_ID = TV.TYPE_VALUE_ID WHERE TV.LOV_ID = ? AND TVIN.LANGUAGE_ID = ? ORDER BY TO_NUMBER(LIST_ORDER_NO)]
[gopanel] DEBUG [http-7080-Processor23] DataSourceUtils.doGetConnection(145) | Opening JDBC connection
?????????????????????????????NOT CALLING THE StatementCreatorUtils.setParameterValue HERE???????????????????????????????????
[gopanel] DEBUG [http-7080-Processor23] DataSourceUtils.doCloseConnectionIfNecessary(290) | Closing JDBC connection


/***********************ERROR FROM BROWSER***********************/

javax.servlet.jsp.JspTagException: 500 /WEB-INF/pages/registerForm.jsp
at org.apache.taglibs.standard.tag.common.core.Import Support.acquireString(ImportSupport.java:311)
at org.apache.taglibs.standard.tag.common.core.Import Support.doEndTag(ImportSupport.java:161)
at org.apache.jsp.register_jsp._jspx_meth_c_import_1( register_jsp.java:291)
at org.apache.jsp.register_jsp._jspService(register_j sp.java:146)
at org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:802)

[irbouho]

What Spring / Jdbc Driver version do you use?

[neila29]

The following blog has some more detail and talks about TAF + RAC: http://db360.blogspot.com/2007/01/is...overproof.html

The following is suggested 'with TAF, connection failover is transparent (in case of node failure) however, you have to programmatically rollback ongoing transactions (TAF requires an ACK from the application, in order to proceed)'

Does anyone know if Spring will handle this out of the box? If so which version? Specifically does 1.2.9 handle this?

Cheers
Neil

[trisberg]

There is no special handling of RAC failover in Spring JDBC currently. It's been discussed and will be addressed at some point in the future. Considering Oracle's interest in Spring, there might already be solutions out there that would work with the current Spring releases. Check out the Oracle Spring page - http://www.oracle.com/technology/tec...ing/index.html

Acording to the link you provided TAF relies on OCI driver:

Driver-type dependency: TAF is in fact a OCI failover mechanism exposed to Java through JDBC-OCI. FCF is driver-type independent (i.e., works for both JDBC-Thin and JDBC-OCI).

I would start by switching to the OCI driver and also try using the oracle.jdbc.pool.OracleDataSource for the connection pool.