ORA-12154 error in Harvest
search cancel

ORA-12154 error in Harvest

book

Article ID: 134606

calendar_today

Updated On:

Products

CA Harvest Software Change Manager CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

When trying to connect to the Harvest database, we're getting the following error:

HServer | 20190708 16:10:09 | ERROR: E0302004d: Cannot connect to the datasource <datasource name> as user <userid> .
HServer | 20190708 16:10:09 | ERROR: [CA Harvest SCM][ODBC 20101 driver][20101]ORA-12154: TNS:could not resolve the connect identifier specified


What is the best way to resolve this problem?

Environment

Harvest Software Change Manager all versions with Oracle DBMS

Cause

When trying to connect to the database, Harvest will follow a trail to find the Oracle database it needs to connect to. 

  1. It will look in the HServer.arg to find the data source name
  2. It will then look in the odbc.ini file to find the tns service name associated with the data source name from HServer.arg
  3. It will then look in the Oracle tnsnames.ora file to find the connection details associated with the tns service name it found in the odbc.ini file

The idea will be to follow the same trail to find where the problem is.

Resolution

Harvest will follow a trail to find the Oracle database it needs to connect to. The idea here will be to follow the same trail to find where the problem might be. If it's in steps 1 or 2 that's more of a Harvest configuration problem.  If it's in steps 3, 4 or 5 that's more of an Oracle configuration problem. At some point along this path you should encounter the same “ORA-12154” error message. Follow these steps to find the problem

  1. Harvest will look in the HServer.arg to find the name of the data source:

    [<userid>@<servername> ~]$ cd $CA_SCM_HOME
    [<userid>@<servername> scm]$ cat HServer.arg | grep datasource
    -datasource=harvest
    .
  2. It will then navigate to ODBC_HOME and look in the odbc.ini file to find the "SERVERNAME" configured for that data source definition. We're looling for a "paragraph" inside the odbc.ini file that begins with the name of the datasource we found in step 1, enclosed in square brackets:

    [<userid>@<servername> scm]$ cd $ODBC_HOME
    [<userid>@<servername> odbc]$ cat odbc.ini
    [ODBC]
    Trace=0
    TraceFile=odbctrace.out
    TraceDll=odbctrace.so
    [harvest]
    DESCRIPTION=CA SCM
    SERVERNAME=orcl
    Driver=ptora13.so
    [ODBC Data Sources]
    harvest=CAI/PT ORACLE

    In this example, our data source name is "harvest" so we're looking in the "[harvest]" paragraph for the SERVERNAME. In this case it's "orcl"

  3. The next step is to locate your tnsnames.ora file and look for a "paragraph" in that file that begins with the servername we found in odbc.ini:

    [<userid>@<servername> odbc]$ cd $ORACLE_HOME/network/admin
    [<userid>@<servername> admin]$ cat tnsnames.ora
    ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <servername>)(PORT = <port>))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )


    In this example we do find a paragraph that begins with the same SERVERNAME (or normally called the "service name") we found in the odbc.ini file (could be upper case or lower case - either will work). This paragraph will specify the hostname of the computer that Oracle server is running on and the port number on which the Oracle "Listener" is listening.
    .
  4. We can try the "tnsping" command to see if we are able to connect to the Oracle listener using this service name.

    [<userid>@<servername> odbc]$ tnsping orcl
    TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 09-JUL-2019 10:00:44
    Copyright (c) 1997, 2013, Oracle. All rights reserved.
    Used parameter files:
    <oracle home folder>/network/admin/sqlnet.ora
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <servername>)(PORT = <port>)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
    OK (0 msec)
    [<userid>@<servername> odbc]$

    In this example, the connection was successful.
    .
  5. The final test is to try to connect with sqlplus using the command "sqlplus userid@servicename" where "userid" is the "schema owner's userid" for the Harvest database, and "servicename" is the tns service name we found in step 3. It will prompt for a password and you will want to provide the password for the "schema owner's userid".

    [<userid>@<servername> admin]$ sqlplus <userid>@<servicename>
    SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 9 10:30:21 2019
    Copyright (c) 1982, 2013, Oracle. All rights reserved.
    Enter password:
    Last Successful login time: Tue Jul 09 2019 09:42:57 -04:00
    Connected to:

    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    SQL> exit

    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    [<userid>@<servername> admin]$

Once all these tests complete successfully, then Harvest should be able to connect to your Oracle database.

Powered by Wolken Software