RE: 1-45348091 | JDBC Adapter Connection Errors with Oracle

, ,
1

Hi,

I’ve raised a service request Ref above with the full details.
Using Oracel 9.2 in RAC with Active/Active Oracle configuration, and IS 6.1 with webMethods JDBC Adapter Version 6.0.3 to try and connect on IBM AIX 5.2 Platform.
Using either Oracle client through SQLplus on a remote server or a Java based application with command line overrides we can attach to the RAC using a single Database Alias we have set up in tnsnames, rather than each alias on each node.
When we try the same using the JDBC Adapter it fails unless we use the individual node database Alias’s.

Any Help would be welcome.

Javed

2

What is the exact error and stack trace ?
Which JDBC driver are you using ? Where is it copied in your IS dir tree ?
Are you using LOCAL_TRANSACTION ? That’s a condition in using RAC with JDBC adapter.

I believe Connect JDBC driver version 3.3 supports RAC by specifying alternate servers in the JDBC URL, but don’t know if that’s ok from the JDBC adapter (TN 6.1 does it)

(I suppose you use driverType=oci8 in the JDBC connection properties ?)

3

The Error is:
Error encountered
[ART.118.5042] Adapter Runtime (Connection): Unable to enable connection resource testOracleDB:testOracleConnection.
[ART.118.5036] Adapter Runtime (Connection): Unable to configure connection manager.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
ORA-12505: TNS:listener could not resolve SID given in connect descriptor

If I use the specific SID for one Node it works fine, but using the Generic SID set up in RAC/TAF it fails with this error.
The Oracle version is 9.2.0.5.0
I am using the ojdbc14.jar from Oracle version - 9.0.2.0.0 for use with JDK1.4

I have tried driverType oci8 and oci, both work using the SID for one node,
but not for the Generic SID.

A Java Application can connect using the same adapter and Generic SID, but uses a command line override
-Dsun.boot.library.path=/usr/java14/jre/bin:/oraclient/ora92/lib32

How does one specify alternate servers in the JDBC URL?

Javed

4

maybe this is of help, we managed to connect to a loadbalanced oracle database using oci

Transaction Type LOCAL_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
serverName
user wm
password ******
databaseName
portNumber
networkProtocol
Other Properties url=jdbc:oracle:oci8:@(DESCRIPTION=(address_list=(ADDRESS=(PROTOCOL=TCP)(Host=host1)(Port=1526))(ADDRESS=(PROTOCOL=TCP)(Host=host2)(Port=1526))(load_balance=yes))(CONNECT_DATA=(service_name=CRM)(server=dedicated)))

this was working but we still had some problems with connections that were not removed from the database

5

Many thanks Bart.
This works well with either Oracle ojdbc14.jar driver or the DataDirect driver.

We are using Oracle 9.2 RAC with Transparent Application Failover (TAF)
The URL we used was:

url=jdbc:oracle:oci:@(DESCRIPTION=(failover=on)(failover_mode=(type=session)(method=basic))(address_list=(ADDRESS=(PROTOCOL=TCP)(Host=host1)(Port=1521))(ADDRESS=(PROTOCOL=TCP)(Host=host2)(Port=1521))(load_balance=on))(CONNECT_DATA=(service_name=CRM)))

Where CRM is the single SID we use, rather than specific SID1 & SID2 for each server.

If you put this info into the Oracle tnsnames.ora on the Oracle Servers this can be simplified to:

url=jdbc:oracle:oci:@CRM

The only other fields required are:
Transaction Type LOCAL_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
user wm
password ********

All other fields are blank.

I will request webMethods put this in the Adapter Manual.

Javed

6

This does not work so well for the Data Direct driver if you are using Oracle for the logs.

The OCI driver is not loaded unless you create an adapter using it on the IS.
It produces errors from the Connection Pools, until the driver gets loaded for the Adapter.

[9693]2004-06-19 17:04:12 AST [ISS.0095.0003C] AuditLogManager Runtime Exception: java.sql.SQLException:No suitable driver processing log entry .

Once the driver is loaded it gives this message:

[9695]2004-06-19 17:04:13 AST [SCC.0126.0001E] SCC ConnectionManager Pool Started

Then no more errors.

Javed

7

Okay the info or OCI is valuable and I can get it work. Now the issue is can it work with Oracle JDBC thin drivers? I’ve been doing reading elsewhere (i.e, JAVARANCH) and they suggest that thin driver performance and reliablity is better than OCI drivers. So two questions how to? and What is webMethods opinion on thin vs OCI for 9i RAC adapters?

8

… I’ll answer myself …

You follow the same Adapter definition attribute entries as you would with the OCI definition but for the url you would use the following form:

url=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))(ADDRESS=(PROTOCOL=TCP)(HOST=<HOST 2>)(PORT=))(CONNECT_DATA=(SERVICE_NAME=)))

This seems to work … don’t know if there are any issues but just started running it.

9

JDBC adapter does not support thin drivers for Oracle 9i RAC. At the moment, no type 4 drivers are supported for RAC. Only OCI drivers are supported for Oracle 9i RAC.

10

I have used the following settings to connect to Oracle 9i RAC successfully

Transaction Type LOCAL_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
serverName
user user
password pass
databaseName
portNumber
networkProtocol tcp
Other Properties driverType=oci;url=jdbc:oracle:oci:@abc

TNS entry

abc =
(DESCRIPTION =
(ADDRESS_LIST =
(load_balance = on)
(failover = on)
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = abc)
(failover_mode =
(type = select)
(method = basic)
)
)
)

11

Hi Javed,
Could you please delete and re-enter info in the JDBC Pools. It could be just a simple typo or extra space in the entry which causes this.

Thanks,
WM