Troubleshooting: Database connectivity for Oracle databases

You can encounter database connectivity issues when you are connecting to an Oracle database to create instances, apply fix packs, or when you run many WebSphere Commerce utilities. To resolve these connectivity issues, validate your configured database connection settings and ensure that you are using the correct JDBC driver configuration. Review the following information about connecting to Oracle databases, including common connectivity errors, to help you validate your settings and troubleshoot problems.

Validate whether you can establish a database connection

If you encounter errors when a WebSphere Commerce utility or process that involves database-related operations runs, use the database connection validation utility to ensure that a database connection established. By default, this validation utility runs as part of the process for some utilities and processes. You can run this utility by itself to validate that a database connection can be properly established with the parameters that are configured for your WebSphere Commerce instance.

When the utility completes running, a detailed report generates that indicates whether a successful database connection can be established with the specified connection parameters. The report also indicates whether any errors occurred. For more information about this utility, see Running the database connection validation utility.

When you run the validation utility from a command-line utility, you can specify your database connection parameters in the command. When you run the utility and want to include your connection parameters in the command, use the following syntax: 
wcsDbValidation.sh -jdbcURL <jdbc_url> -jdbcDriver oracle.jdbc.OracleDriver -dbUserName <user> -dbUserPassword <password>
For example, the following command is for validating a connection that uses a type 4 connection type that includes the Oracle SID value:
wcsDbValidation.sh -jdbcURL jdbc:oracle:thin:@myhostname:1521:orcl -jdbcDriver oracle.jdbc.OracleDriver -dbUserName wcs -dbUserPassword password
The following command is for validation a connection that uses a type 2 connection type:
wcsDbValidation.sh -jdbcURL jdbc:oracle:oci:@orcl -jdbcDriver oracle.jdbc.OracleDriver -dbUserName wcs -dbUserPassword password
Note: You can also use other method to validate your database connection. You can use the SQL*Plus command line and the tnsping utility.
  • To validate a connection with the SQL*Plus command line utility, you can use a command that uses the following format:
    sqlplus user/password@<NET_SERVICE_NAME>
    Where NET_SERVICE_NAME is one of the aliases that are defined in tnsnames.ora. If you are not able to use SQL*Plus with the non-root user profile, you might need to set the Oracle environment variables.
    You can also bypass the tnsnames.ora configuration and specify the connection arguments directly in the SQL*Plus command line:
    • Connecting by SID:
      sqlplus <USER>/<PASSWORD>@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOST>)(PORT=<PORT>))(CONNECT_DATA=(SID=<SID>)))"
    • Connecting by Service:
      sqlplus <USER>/<PASSWORD>@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOST>)(PORT=<PORT>))(CONNECT_DATA=(SERVICE_NAME=<SERVICE>)))"
    It's also possible to try service connections with this abbreviated format:
    sqlplus <USER>/<PASSWORD>@<HOST>:<PORT>/<SERVICE>
  • To validate a connection to the listener with the tnsping utility from a command-line utility, use a command with the following format: 
    [oracle@myhostname 12.1.0]$ tnsping "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))"

Validate the Oracle client environment access

Ensure that you can access your Oracle client environment from WebSphere Commerce. The Oracle client provides the necessary libraries to connect WebSphere Commerce to the Oracle database server, including the required Java JDBC Drivers and database connection configuration.

The first step in troubleshooting is to ensure that the Oracle environment is configured for the non-root user profile. You should be able to connect to the database by using the SQL*Plus command-line utility. For example, by running the following type of command: 
sqlplus user/password@<NET_SERVICE_NAME> ( The net service name is defined in tnsnames.ora)
If the SQL*Plus (sqlplus) command is not found, the client might not be configured correctly. Ensure that the following environment variables are correct:
Environment Variable Usage
ORACLE_BASE Base path for the Oracle install. For example, /opt/oracle/u01/app/oracle
ORACLE_HOME Base directory to the Oracle database in use: $ORACLE_BASE/product/12.1.0/db_1
PATH Add an Oracle binary file to the path. Oracle_installdir/bin
LD_LIBRARY_PATH Include Oracle libraries in the library path: Oracle_installdir/lib
Other Oracle environment variables are also set from within the WebSphere Commerce scripts (setenv.sh). Run the setenv.sh script and then validate the variables. For example, your script and variables can resemble the following script and variables. 
[wasuser@myhostname bin]$ . ./setenv.sh
[wasuser@myhostname bin]$ set | grep ORACLE
ORACLE_CLASSPATH=/opt/oracle/u01/app/oracle/product/12.1.0/db_1/jdbc/lib/ojdbc6.jar
ORACLE_BASE=/opt/oracle/u01/app/oracle
ORACLE_HOME=/opt/oracle/u01/app/oracle/product/12.1.0/db_1
ORACLE_LIBPATH=/opt/oracle/u01/app/oracle/product/12.1.0/db_1/lib:/opt/oracle/u01/app/oracle/product/12.1.0/db_1/lib
If the variables are incorrect, your Oracle client might not be installed in the expected directory. To view the expected directory, review the configured setting in the WC_installdir/xml/product.xml file. The Oracle client installation directory is typically configured during WebSphere Commerce installation.
Note: If your utilities connect to an Oracle database by using a type 2 (thick) JDBC driver, the utility uses a net service name to identify the database connection configuration. Ensure that the net service name configuration is correct. The database connection configuration is in the Oracle_installdir/network/admin/tnsnames.ora file. In this file, net service names are mapped to a configuration for establishing a database connection. This configuration includes all of the required parameters for connecting to the database, such as the host name, port number, and the system identifier (SID) or the service name. The following command and summary display a sample configuration for a net service name: 
[wasuser@myhostname oracle]$ cat /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Validate that a database connection can be established with the JDBC type 4 and type 2 drivers

WebSphere Commerce supports the use of both the JDBC type 2 (thick) driver and the JDBC type 4 (thin) driver for connecting to a database. Ensure that you can establish a connection with both types of driver.

WebSphere Commerce uses the ojdbc6.jar JDBC type 4 driver for connecting to an Oracle database. This type 4 driver is an upgrade from the type 2 driver and provides improved performance and a richer set of functions for creating database connections. This upgrade includes both the runtime data source connections and the utilities connections, such as staging utilities, loading utilities, dbclean, and acpload.

The type 4 (thin) driver connection is a pure Java implementation of the Oracle client and does not interact or use the locally installed Oracle client. Since this connection type does not access the database directory for your client, ensure that you specify the host name, port number, and the service name or SID parameters that are needed to locate the database. To specify these parameters, use the JDBC URL. The format of the URL depends on whether you use a service name or SID parameter:
  • JDBC URL format when you use the SID:
    jdbc:oracle:thin:@<HOST>:<PORT>:<SID>
  • JDBC URL format when you use the service name:
    jdbc:oracle:thin:@//<HOST>:<PORT>/<SERVICE>

Although you can still use the type 2 connection type, this syntax is converted internally to type 4 syntax and a type 4 database connection is created. The type 2 connection type is more error prone that the type 4 connection type since with the type 2 driver, Java needs access to your native Oracle client. To avoid a potential performance impact due to the conversion, use the type 4 connection type database name syntax.

If you do use the type 2 connection type, ensure that you include the correct net service name to identify the database connection configuration to be used. The net service name is defined in the tnsnames.ora file. The JDBC URL format when you use the type 2 connection type and the service name can resemble the following URL: 
jdbc:oracle:oci:@<NET_SERVICE_NAME>

Validate the database connection acquisition framework configuration

Most WebSphere Commerce utilities that require a database connection are updated to connect to the database through the database connection acquisition framework. With this framework update, you can connect utilities to an Oracle database by using the JDBC URL or the net service name in the command line to identify the database. For example, the following sample commands demonstrate the format to use to include the JDBC URL or service name when you call the database cleanup utility:
  • JDBC URL
    dbclean.sh -dbtype oracle -db jdbc:oracle:thin:@myhostname:1521:orcl -object cacheivl -type obsolete -days 1 -instancexml 
WC_installdir/instances/demo/xml/demo.xml -dbuser wcs -dbpasswd password

20150413-103654| AllDBConnector.connect> Determined database-type from input: [oracle] as: [Oracle]
20150413-103654| AllDBConnector.connectOracle> Attempting to load Oracle JDBC driver: [oracle.jdbc.OracleDriver]
20150413-103654| AllDBConnector.connectOracle> Attempting to acquire Oracle Connection using URL: [jdbc:oracle:thin:@myhostname:1521:orcl]
20150413-103655| AllDBConnector.connectOrFail> Connection acquired
  • Service name
    dbclean.sh -dbtype oracle -db orcl -object cacheivl -type obsolete -days 1 -instancexml 
WC_installdir/instances/demo/xml/demo.xml -dbuser wcs -dbpasswd password

20150413-103444| AllDBConnector.connect> Determined database-type from input: [oracle] as: [Oracle]
20150413-103444| AllDBConnector.connectOracle> Attempting to load Oracle JDBC driver: [oracle.jdbc.OracleDriver]
20150413-103445| AllDBConnector.connectOracle> Attempting to acquire Oracle Connection using URL: [jdbc:oracle:oci:@orcl]
20150413-103445| AllDBConnector.connectOrFail> Connection acquired
This option provides you with more flexibility for establishing a database connection when you run a utility.

To resolve connectivity issues that you encounter if your utilities use the database connection acquisition framework, ensure that the connection settings in the alldbconnector.xml configuration file are correct. When you are reviewing your database connection acquisition configuration, check for any configured overrides that can be causing your connectivity problem. The framework provides you with the capability to set override options that can affect all database connections for utilities. For more information about the settings and overrides in this file, see Database connection acquisition for utilities and Ant tasks.

Validate that the configured settings can connect utilities to your database by using the SQL*Plus command line utility or by using the WebSphere Commerce database validation utility.
  • SQL*Plus with the net service name: 
    sqlplus user/password@<NET_SERVICE_NAME>
  • WebSphere Commerce database validation utility with the JDBC URL:
    wcsDbValidation.sh -jdbcURL <jdbc_url> -jdbcDriver oracle.jdbc.OracleDriver -dbUserName <user> -dbUserPassword <password>

Validate the WebSphere Application Server database connection

Ensure that the database connection parameters in WebSphere Application Server are correct.
  1. In the WebSphere Application Server administration console, expand Resources > JDBC > Data Sources. Review and verify the type 4 driver connection type settings.
    Common and required data source properties
    The console includes a JDBC tester, which can you can use to help you validate your settings by testing the connection that uses the configured JDBC settings.
    Test connection for JDBC properties
    Note: You can change the connection URL if needed. For example, if you want to use a SCAN IP and service, such as jdbc:oracle:thin:@//rac-scan:port/rac-service-name.
  2. Review and verify your user name and password settings that are associated with the connection type settings. For example, if you encounter an error that your user ID and password are invalid, you might need to update your password. The user name and password settings are listed in the JAAS - J2C authentication record that is associated with the selected data source. For more information, see Changing database passwords.
Notes:
  • If you are working within an authoring environment, you can also see a data source for publishing data. This data source typically has the production database connection parameters for publishing data. Review and verify the connection settings for this data source.

Validate the SID or service name parameters

You can use an Oracle system ID (SID) or service name to connect to a database. The JDBC URL format for connecting to the database is different when you use the SID or the service name. If you attempt to establish a connection with an SID formatted URL, but specify a service name, the connection fails. The same result occurs if you attempt to use a service name formatted URL and specify an SID. You can determine whether an SID or a service name is being used to connect to the database by reviewing the alias configuration in the tnsnames.ora file. This configuration uses the following format for SID and service names:
  • SID: 
    ORCL = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=orcl)))
  • Service: 
    ORCL = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))
An Oracle SID uniquely identifies an instance and is required for instance creation and feature enablement tasks. The script for each of these tasks composes a thin JDBC URL, jdbc:oracle:thin:@<HOST>:<PORT>:<SID> from the host name, port number, and SID properties. These tasks then use this JDBC URL to establish a database connection.
You can register multiple instances to use same service name. For example, with Oracle Real Application Clusters (RAC), multiple instances subscribe to the same service name so that you can connect to all nodes in the cluster. You can use connections by service name by updating the WebSphere Commerce data source JDBC URL, the tnsnames.ora file definition, or by using a service name formatted JDBC URL when you run a utility.
Note: If you use Oracle RAC, you still need to use scripts that include an SID to connect to the Oracle database server for instance creation, feature enablement, and other scripts. After the task completes, you can update the WebSphere Commerce data source to use a SCAN host and the service name. For example, you can update the data source to use a URL with the following format:
jdbc:oracle:thin:@//rac-scan:port/rac-service-name
For more information, see the topics Using Oracle Real Application Clusters (RAC) with WebSphere Commerce and Installing and configuring a new Oracle RAC database.

Common connection errors

The following errors are examples of common connection problems that you might encounter. To help resolve these problems and other connectivity problems, review any error message that generates when you encounter a problem.
  • The JDBC driver class cannot be found.
    You can encounter a connectivity error if Java cannot find the ojdbc6.jar JAR file for the JDBC driver. Java needs to find the JAR file to use the oracle.jdbc.OracleDriver driver class. This error can resemble the following error message:
    java.lang.ClassNotFoundException:oracle.jdbc.OracleDriver
    at java.lang.Class.forName(Class.java:185)
    at com.ibm.commerce.config.ant.WcsDbValidation.testJdbcDriverClass(WcsDbValidation.java:665)
    Ensure that the JAR file is in the expected directory. The JAR file is expected to be in the Oracle_installdir/jdbc/lib directory. You can encounter this error when the JAR file is not in the expected directory. For example, if the Oracle client is not installed in the location that was specified during WebSphere Commerce installation. Review the WC_installdir/xml/product.xml file to identify the expected installation location for the Oracle client.
  • The native Oracle client libraries cannot be found.
    If you use the Oracle JDBC type 2 driver, you can encounter an error if Java cannot locate the native Oracle client libraries. This error can resemble the following error message:
    Caused by: java.lang.UnsatisfiedLinkError: ocijdbc11 (Not found in java.library.path)
    at java.lang.ClassLoader.loadLibraryWithPath(ClassLoader.java:1035)
    at java.lang.ClassLoader.loadLibraryWithClassLoader(ClassLoader.java:999)
    Ensure that the LD_LIBRARY_PATH and PATH environment variables are correct and that the non-root user has read-access to the Oracle client directories.
  • The net service name cannot be resolved.
    If you use a type 4 JDBC driver, you can encounter the following error message if the net service name that you specify is invalid: 
    -jdbcURL jdbc:oracle:thin:@/myhostname:1521/invalid
    You can also encounter the following error message that references a specific Oracle error message code:
    Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
      at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
    This code, ORA-12514, returns when the specified service name is unknown. Ensure that you specify the correct net service name and other database parameters when you run a utility or script.
    If you use a type 2 JDBC driver, you can encounter the following error message if the net service name that you specify is invalid: 
    -jdbcURL jdbc:oracle:oci:@invalid
    You can also encounter the following error message that references a specific Oracle error message code:
    java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified
      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:765)
      at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:414)
    This code, ORA-12154, identifies that the error is that the net service name cannot be found in the tnsnames.ora file for your Oracle client. Update the file for your client to include the correct net service name parameters.
  • The Oracle SID cannot be resolved.
    If you use a type 4 JDBC driver, you can encounter the following error message if the SID that you specify is invalid: 
    -jdbcURL jdbc:oracle:thin:@myhostname:1521:invalid
    You can also encounter the following error message that references a specific Oracle error message code, ORA-12505, when the SID that you specify is unknown:
    Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
      at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
      at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
    This error can occur when you specify a net service name instead of the SID. Ensure that you include the correct SID and other database parameters when you run a utility or script.
  • A database connection cannot be established during instance creation, feature enablement, or database update.
    During instance creation, feature enablement, database update with the updatedb utility, or any other Ant tasks, WebSphere Commerce uses the db parameter that is specified when you run a utility to compose a type 2 JDBC URL for connecting to the database. This JDBC URL is formatted to use the SID. If you do not specify the SID, the connection fails and an error message that is similar to the following message can display:
    WC_installdir/components/common/xml/enableFeature.xml:89: The following error occurred while executing this line:
WC_installdir/components/common/xml/checkPrerequisites.xml:93: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
    at org.apache.tools.ant.ProjectHelper.addLocationToBuildException(ProjectHelper.java:539)
    at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:384)
    Ensure that you do specify the SID when you run a utility or script that requires an SID formatted JDBC URL to connect to the database.
    Note: This error is common with Oracle RAC environments when you specify a service name instead of the SID to connect to the database. For more information, see Installing and configuring a new Oracle RAC database.
  • di-buildindex utility fails, which is Caused by: oracle.net.ns.NetException.

    Oracle does not support an attribute value that is configured in the wc-data-config.xml. To fix the issue, see Full import failed during di-buildindex utility.