Troubleshooting: Validating the Oracle Environment for use with WebSphere Commerce

WebSphere Commerce uses the Oracle type 2 (OCI) driver for tasks such as Instance Creation. The use of this driver requires the Oracle environment to be properly set for the WebSphere Commerce non-root user.

Problem

You might encounter some of the following error messages if the Oracle environment is not properly configured for use with WebSphere Commerce:

java.sql.SQLException: Closed Connection
java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
java.sql.SQLException: ORA-12154: TNS:could not resolve
java.lang.UnsatisfiedLinkError: /opt/oracle/app/oracle/product/12.1.0/dbhome_1/lib/libocijdbc12.so: load ENOEXEC on shared library(s).

Solution

Validate that the environment is configured properly:

Ensure that the WebSphere Commerce non-root user has read and execute permission on the Oracle libraries.
Ensure that the WebSphere Commerce non-root user's .profile file calls the Oracle installation owner's .profile file. Check that the following line exists in the WebSphere Commerce non-root user's .profile file:
```
Oracle_home_dir/.profile
```
Ensure that you can invoke "sqlplus" by using the non-root user.

Validate the values in the profile for the following environment settings:


ENVIRONMENT VARIABLE	SETTING
ORACLE_HOME	Oracle home directory
PATH	Ensure that the `PATH` includes a line similar to the following example: `$ORACLE_HOME/lib:$ORACLE_HOME/bin`
LIBPATH	Ensure that `LIBPATH` includes a line similar to the following example: `$ORACLE_HOME/lib:WC_installdir/bin`
LD_LIBRARY_PATH	Ensure that the `LD_LIBRARY_PATH` includes a line similar to the following example: `$ORACLE_HOME/lib:WC_installdir/bin`

Validate the connection to the database. Use sqlplus to validate the connection to the database by using the WebSphere Commerce non-root user (OS) and the schema user (Oracle):

Execute sqlplus user/password@net_service_name
Execute the following command: SELECT * FROM V$VERSION
Ensure that there are no errors.

You can also bypass the tnsnames.ora configuration, and specify the parameters directly in the sqlplus command line (for use during Instance Creation, Feature Enablement and utility execution):

sqlplus user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))(CONNECT_DATA=(SID=sid)))

If the WebSphere data source is updated to use a service name, execute the following command:

sqlplus user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))(CONNECT_DATA=(SERVICE_NAME=service_name)))

If the connection fails, consult with your Oracle database administrator for the correct connection parameters.

Oracle notes

For information about the Oracle versions that are supported, see Verify hardware, software, and operating system requirements.

For information about using Oracle RAC, see Using Oracle Real Application Clusters (RAC) with WebSphere Commerce.

Note: Although the WebSphere data source can be updated to use a SERVICE, Instance Creation and Feature Enablement require the use of a SID. For more information, seeInstalling and configuring a new Oracle RAC database.