ORA-12154: TNS:could not resolve service name
R3trans or work processes terminate with the following errors:
C *** ERROR => OCI-call 'olog' failed: rc = 12154
C *** ERROR => CONNECT failed with sql error '12154'
Brbackup or brarchive ends with the following error:
BR301E SQL error -12154 at location BrDbConnect-1
A tnsping call fails with:
TNS-03505: Failed to resolve name
This reveals the following possible causes of error
The SAP kernel and tools such as tp, R3trans or saplicense create a link to the Oracle database via SQLNetV2 (Oracle 7.x) or Net8 (Oracle 8.x). In the first step, you use suitable entries in the configuration file tnsnames.ora to determine the destination of the connect (host, port, SID), that is where the listener accepting the connection requests is running. If, for some reason, the relevant section in tnsnames.ora cannot be determined, the system returns the error ORA-12154.
This reveals the following possible causes of error:
1. Use of the incorrect tnsnames.ora or sqlnet.ora
2. Incorrect or missing entries in tnsnames.ora or sqlnet.ora
3. Incorrect DBSWPATH for tp
4. Incorrect or missing DBCONFPATH for tp
5. NT: Incorrect OCI-DLLs
6. Remote connection: Incorrect entry in DBCON
7. Database passwords with "@" in the name
8. The OS parameter "locale" is set to tr_TR.ISO8859-9, as is the ABAP parameter abap/locale_ctype.
The SAP release is 4.6C or lower.
1. Check Note 445038, particularly with reference to tnsnames.ora and sqlnet.ora.
2. To enable the setting up of a Net8 connection via tnsnames.ora, the following parameter must exist in sqlnet.ora:
NAMES.DIRECTORY_PATH = (TNSNAMES)
If this parameter is set to another value (for example, "HOSTNAME"), the connection is not set up via tnsnames.ora. If this is the case, correct the entry.
The correct section in tnsnames. ora is determined by using an alias. If R/3 connects to the database, the content of the variable dbs_ora_tnsname is usually used as an alias. In rare cases, ORACLE_SID is also used. Usually, both dbs_ora_tnsname and ORACLE_SID should contain the SID of the database to be called (exception: RAC). For more information, see Notes 602843 (UNIX) and 556232 (WINDOWS). Also check the settings in the environment, and on NT also in the registry. In addition, the R/3 profile parameter dbs/ora/tnsname must not be set differently.
If you use a connect string for a connect (for example, using svrmgrl or sqlplus), you can specify the alias behind the user/password entry, separated by "@". For example:
These types of connect strings are also often contained in trace files immediately before the ORA-12154 error occurs. Through this, you can determine whether the correct alias has been used.
In addition to the alias, the default domain WORLD is also used by default. Therefore, sqlnet.ora should contain the following entry:
NAMES.DEFAULT_DOMAIN = WORLD
If this entry is missing in sqlnet.ora or if it contains a typing error, with SQLPLUS and
an ORA-12154 error is returned, whilst
Tnsnames.ora now contains entries such as the following:
<alias>.<default_domain> = ...
A sample entry for the alias C11 and default domain WORLD could be, for example:
(ADDRESS = (PROTOCOL = TCP) (HOST = c11host) (PORT = 1527)
(SID = PRD)
With regard to ORA-12154, only the first line is important. tnsnames.ora must contain this type of line, comprising the alias used and the default domain. If this entry is missing, ORA-12154 is issued. Even if the line <sid>.WORLD is indented (that is, starting with blank spaces or tabs), ORA-12154 occurs.
In addition, the syntactic accuracy of tnsnames.ora must be ensured (for example, each opening parenthesis must have a closing parenthesis). If the file is syntactically incorrect, the existing correct entries are not found, causing an ORA-12154. One solution here (apart from manual checking) would be to set up the Net8 configuration by using the Net8 assistant.
3. See Note 324714.
4. If the error occurs only if tp is called, check whether you can solve the problem by including the following parameters in the tp configuration file (TP_DOMAIN_<sid>.PFL or TPPARAM):
<sid>/dbconfpath = <path_to_net8_files>
here, <path_to_net8_files> is the path to the Net8 configuration files that are used (for example, /oracle/<sid>/920_64/network/admin).
5. See Note 520233.
6. If you want to set up a connection from R/3 to a remote Oracle database, the table DBCON must be maintained accordingly (Note 339092). The entry CON_NAME (connection name) must contain the alias that is also in tnsnames.ora. If the alias differs, ORA-12154 occurs.
7. Avoid using the "@" character in passwords for database users. Otherwise, ORA-12154 errors may occur because Oracle may interpret the part behind "@"as a TNS alias. This is particularly difficult to analyze if the password defined in the SAPUSER table contains an "@" for SAPR3 or SAP<sid>.
8. See Note 39048.
Due to the incorrect conversion of the character code, an incorrect connectstring may occur which, in turn, means that the service in tnsnames.ora is not found.