When Harvest returns the ORA-12154 error message
Article ID: 134606
Updated On:
Products
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
Release : 13.0
Component : CA Harvest Software Change Manager
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:
[[email protected] ~]$ cd $CA_SCM_HOME
[[email protected] 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:
[[email protected] scm]$ cd $ODBC_HOME
[[email protected] caiptodbc]$ 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:
[[email protected] caiptodbc]$ cd $ORACLE_HOME/network/admin
[[email protected] admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = olnx75-orcl12c)(PORT = 1521))
(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.
[[email protected] caiptodbc]$ 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:
/app/oracle/product/12.1.0/client_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = olnx75-orcl12c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[[email protected] caiptodbc]$
In this example, the connection was successful.
5. The final test is to try to connect with sqlplus using the command "sqlplus [email protected]" 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".
[[email protected] admin]$ sqlplus [email protected]
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
[[email protected] admin]$
Feedback
