Troubleshooting connection failed when running SM with Oracle DB on Linux / Unix

database connection down

While working on a case with customer, I got one issue that the connection between HP Service ManagerĀ and Database is failed, and that customer’s using Linux and Oracle. Here is the flow of troubleshooting process that I followed until the issue is resolved

The error appeared in the sm.log (smconfig.log) is :

RTE E Error: SQL code=12514 message=ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
RTE E Failure connecting to dbtype [oracle11], using Oracle TNS ‘SM’, user ‘ideasm’
RTE E sql errors: SQL code=12541 message=ORA-12541: TNS:no listener
RTE I Thread termination in progress
RTE E sql errors: SQL code=12541 message=ORA-12541: TNS:no listener
RTE E sql errors: SQL code=12541 message=ORA-12541: TNS:no listener
RTE E Failure opening ‘dbdict’ file, terminating.

 

Possible cause: The connection request could not be completed because the listener is not running\

ACTION:

  1. Get the detail version of SM App, DB , DB type (Oracle, SQL).
  2. Files need to get from customer’s machine: sm.ini, sm.cfg, sm.log, tnsnames.ora, listener.ora
  3. Compare the TNSNAMES.ORA entry with the appropriate LISTENER.ORA file (or
    TNSNAV.ORA if the connection is to go by way of an Interchange). Start the listener on
    the remote machine

Here is the result:

1. Ping is working
2. tnsping <listener_name> is working on both machines (App and DB)
3. Telnet is working also

Next action:

– Get the configuration files: sm.ini, sm.cfg, listener.ora (/network/admin), tnsnames.ora
1. Compare the Listener name in sm.ini and listener.ora: below entry must be same
sm.ini: sqldb:<listener_name>
listener.ora:
listener_name =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = IPFMSIMSTND49.IDEACONNECT.COM)(PORT = 1521))

)

2. in listener.ora, add the following entry if not have:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasvr01)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

Note: the Host value is the hostname of DB server , then restart the listener service.

3. Check the DNS/ hostname resolution:
$ host oracle-server-ip
$ host oracle-server-hostname
Make sure /etc/resolv.conf and /etc/hosts and /etc/host.conf configured properly.

– Check the Oracle Client , must use 32 bit version.
1. Oracle ODBC driver 32 bit
2. Oracle client 32 bit
3. Copy the library file from 32 bit library to RUN:libclntsh.so.11.1: cannot open shared object file: No such file or directory
4. Change the environment variables, set it to 32 bit path:

export ORACLE_HOME=/usr/lib/oracle/11.2/client
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin

FINAL RESULT:

The connection was working fine, now we can connect and login to DB from Service Manager

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s