WAS v8.5 > Reference > Troubleshooting tips

Data access problems for Oracle data sources

This topic provides troubleshooting tips for accessing Oracle data sources.


What error do you see when we try to access your Oracle-based data source?


Invalid Oracle URL is specified

This error might be caused by an incorrectly specified URL on the URL property of the target data source.

Examine the URL property for the data source object in the dmgr console to ensure that it is correct.

Examples of Oracle URLs:


"DSRA0080E: Exception was received by the data store adapter. See original exception message: ORA-00600"

A possible reason for this exception is the version of the Oracle JDBC driver being used is older than the Oracle database. It is possible that more than one version of the Oracle JDBC driver is configured on the WebSphere Application Server.

Examine the version of the JDBC driver. Sometimes we can determine the version by looking at the class path to determine what directory the driver is in.

If we cannot determine the version this way, use the following program to determine the version. Before running the program, set the class path to the location of your JDBC driver files. 

import java.sql.*;
     import oracle.jdbc.driver.*;
     class JDBCVersion
     {
         public static void main (String args[])
         throws SQLException
         {
             // Load the Oracle JDBC driver              DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
             // Get a connection to a database              Connection conn = DriverManager.getConnection
             ("jdbc:oracle:thin:@appaloosa:1521:app1","sys","change_on_install");
             // Create Oracle DatabaseMetaData object              DatabaseMetaData meta = conn.getMetaData();
             // gets driver info:
             System.out.println("JDBC driver version is"  + meta.getDriverVersion());
         }
     }

If the driver and the database are at different versions, replace the JDBC driver with the correct version. If multiple drivers are configured, remove any that occur at the incorrect level.


DSRA8100E: Unable to get a {0} from the data source. Explanation: See the linkedException for more information.

When using an oracle thin driver, Oracle creates a java.sql.SQLException: invalid arguments in call error if no user name or password is specified when getting a connection. If you see this error while running WAS, the alias is not set.

To remove the exception, define the alias on the data source.


"Error while trying to retrieve text for error"

The likely cause of this error is the Oracle OCI driver is being used with an ORACLE_HOME property that is either not set or is set incorrectly.

To correct the error, examine the user profile that WAS is running under to verify the $ORACLE_HOME environment variable is set correctly.


Class loader errors occur when using an Oracle OCI driver as your JDBC provider

When you configure an Oracle OCI driver as your JDBC provider, specify the path to where the native libraries are stored. If you did not specify a native library path, the first time you try to connect using this provider, class loader errors occur.

To correct this problem, in the dmgr console, click Resources > JDBC > JDBC Providers, select the Oracle OCI driver, and then specify the path to the native libraries in the Native library path field.


"java.lang.UnsatisfiedLinkError:" connecting to an Oracle data source

The environment variable LIBPATH might not be set or is set incorrectly, if your data source creates an UnsatisfiedLinkError error, and the full exception indicates the problem is related to an Oracle module.

To correct the problem:

  1. Examine the user profile under which the WAS is running to verify the LIBPATH environment variable includes Oracle libraries.


Errors occur when referencing Oracle classes or when connecting to an Oracle data source

Receive java.lang.NullPointerException errors when referencing Oracle classes, or "internal error: oracle.jdbc.oci. OCIEnv" errors when connecting to an Oracle data source

The problem might be the OCI driver is being used on an AIX machine, the LIBPATH is set correctly, but the ORACLE_HOME environment variable is not set or is set incorrectly. We can encounter an exception similar to either of the following when the application attempts to connect to an Oracle data source:

To correct the problem, examine the user profile that WAS is running under to verify that it has the $ORACLE_HOME environment variable set correctly, and the $LIBPATH includes $ORACLE_HOME/lib.


WSVR0016W: Class path entry for the Oracle JDBC Thin Driver has an invalid variable

This error occurs when there is no environment variable defined for the property, ORACLE_JDBC_DRIVER_PATH.

Verify this problem in the dmgr console. Go to Environment > Manage WebSphere Variables to verify the variable ORACLE_JDBC_DRIVER_PATH is defined.

To correct the problem, click New and define the variable. For example, name: ORACLE_JDBC_DRIVER_PATH, value : c:\oracle\jdbc\lib. Use a value that names the directory in your operating system containing the ojdbc6.jar file (or the ojdbc6_g.jar file to enable Oracle trace.)


Transaction recovery failure (for XA data sources)

Problem

When WAS attempts to recover Oracle database transactions, the transaction service issues the following exception: 

WTRN0037W: The transaction service encountered an error on an xa_recover operation. 
The resource was com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl@1114a62. 
The error code was XAER_RMERR. The exception stack trace follows: 
javax.transaction.xa.XAException
at oracle.jdbc.xa.OracleXAResource.recover(OracleXAResource.java:726)
at com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.recover(WSRdbXaResourceImpl.java:954)
at com.ibm.ws.Transaction.JTA.XARminst.recover(XARminst.java:137)
at com.ibm.ws.Transaction.JTA.XARecoveryData.recover(XARecoveryData.java:609)
at com.ibm.ws.Transaction.JTA.PartnerLogTable.recover(PartnerLogTable.java:511)
at com.ibm.ws.Transaction.JTA.RecoveryManager.resync(RecoveryManager.java:1784)
at com.ibm.ws.Transaction.JTA.RecoveryManager.run(RecoveryManager.java:2241)

Cause

Oracle requires services such as the transaction service to have special permissions for performing transaction recoveries.

Solution

As user SYS, run the following commands on your Oracle server: 

grant select on pending_trans$ to public;
grant select on dba_2pc_pending to public;
grant select on dba_pending_transactions to public;
grant execute on dbms_system to <user>;
User is a user ID in the application server that is authorized to perform transaction recovery for the XA data source. If we have not authorized any user IDs to perform transaction recovery, the application server uses the login alias for the data source as the user ID.

This problem is mentioned under Oracle bug: 3979190. Running the preceding commands solves the problem.


Application server receives an error in the SystemOut.log file when it registers the Oracle JDBC Diagnosability MBean

The following error is displayed in the SystemOut.log file when WAS attempts to connect to an Oracle database: 

E Error while registering Oracle JDBC Diagnosability MBean. 
javax.management.MalformedObjectNameException: Invalid character '' in value part of property

This error occurs during the initial connection to an Oracle database because the diagnosis for the MBean is not properly initialized.

We can ignore this error. However, we can apply patch number 6362104 from Oracle to prevent future occurrences of this error. Check with Oracle if we have any other patches applied, as this patch might not work with other patches.

IBM recommends using the HPEL log and trace infrastructure. With HPEL, one views logs using the LogViewer command-line tool in PROFILE/bin.


Related concepts:

Troubleshooting and Support


Related


Develop data access applications
Troubleshoot applications with HPEL


Reference:

Data access problems
Data access problems for DB2 databases
Data access problems for Microsoft SQL Server data sources
Data access problems for Apache Derby databases
Data access problems for Sybase data sources


+

Search Tips   |   Advanced Search