Oracle error code 12154



ORA-12154: TNS:could not resolve the connect identifier specified

hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.

could anyone help me on this?

ORA-12154: TNS:could not resolve the connect identifier specified

Answers

here is my tns.ora

GISDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gisdb)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

here is my listener .ora

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
)
)

here is my sqlnet.ora

# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install «Software Only», this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Hi,
I have some guidelines for you,can you please go through the details below:

Error: ORA-12154 / TNS-12154
Text: TNS:could not resolve service name
——————————————————————————-
Cause: The service name specified is not defined correctly in the
TNSNAMES.ORA file.
Action: Make the following checks and correct the error:
— Verify that a TNSNAMES.ORA file exists and is in the proper
place and accessible. See the operating system specific manual
for details on the required name and location.
— Check to see that the service name exists in one of the
TNSNAMES.ORA files and add it if necessary.
— Make sure there are no syntax errors anywhere in the file.
Particularly look for unmatched parentheses or stray characters.
Any error in a TNSNAMES.ORA file makes it unusable. See
Chapter 4 in the SQL*Net V2 Administrator’s Guide. If
possible, regenerate the configuration files using the Oracle
Network Manager.

*** Important: The notes below are for experienced users — See Note:22080.1

Explanation:
The SQL*Net layer cannot find a definition for the alias supplied
(as in «sqlplus scott/[email protected]»).

Diagnosis:
1) Check the tnsnames.ora file you are using, verify that it is accessible.
Eg: On Unix:
— Check if you have a $HOME/.tnsnames.ora file — This will be
used in addition to ‘tnsnames.ora’.
— Check TNS_ADMIN is set in your environment.
— There is a readable tnsnames.ora file in $TNS_ADMIN

2) Ensure that the tnsnames.ora file contains a line of the form
‘alias=(. )’ for the alias you are specifying.
Aliases are NOT case sensitive.

3) Make sure that there are no mismatched parentheses in the
tnsnames.ora file.

4) Even if TNS_ADMIN is set SQL*Net looks in other locations for
configuration files. Check the default directories for old (or bad)
copies of TNS_NAMES.ORA. Eg: /etc, /var/opt/oracle,
$ORACLE_HOME/network/admin

5) Check the default domain name being used, and the path used to
locate aliases, in the SQLNET.ORA file.
The default domain is specified in the NAMES.DEFAULT_DOMAIN
parameter — this is appended to the alias specified in the
connect string if there is no domain given.
Eg: If NAMES.DEFAULT_DOMAIN=mydom.uk
and a connect to «scott/[email protected]» is requested
SQL*Net will look for the alias «mydb.mydom.uk»
If NAMES.DIRECTORY_PATH is also specified this determines where
SQL*Net looks for the alias expansion.

6) If none of these show an error enable client side tracing
at level 16 and see what has been written to the client trace
file. There list of aliases in the trace file under the heading
‘TNS.NAMES.ORA TABLE HAS THE FOLLOWING CONTENTS’.

7) If ORA-12154 is returned when selecting over a database link from a
client check that the alias in the link can be resolved in the
tnsnames.ora file ON THE SERVER.

8) If you are connecting from a login dialog box, verify that you are
not placing an «@» symbol before your connect net service name.

9) When going from Windows to Linux/Unix platforms,
you can see characters at the
end of each line instead of . Be sure you
use ascii mode when ftp’ing this between Windows and Linux/Unix

please can you post the output from tnsping GISDB executed on the host from where you are trying to connect to the DB?

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 — Production on 29-APR-2
010 17:02:17

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = alias-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = gisdb)))
OK (20 msec)

the output of tnsping shows that you are able to connect to the listener listening on alias-PC on port 1521 and the listener knows of service gisdb.

Does an sqlplus /

@gisdb work on the host were you executed the tnsping command?

What does an lsnrctl status executed on alias-PC telling you?

pimpom wrote:
hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.

could anyone help me on this?

ORA-12154: TNS:could not resolve the connect identifier specified

=================================
ORA-12154: TNS:could not resolve the connect identifier specified

This error means one thing, and one thing only. The client could not find the specified entry in the tnsnames.ora file being used.

As a follow-on to that statement, remember that when you use a dblink, the database in which the link is defined is acting as a client to the database that is the target of the link. So in this case, the tnsnames.ora file on the host of your source should have an entry for your target db, as defined in the db_link.

And for the umpteenth time . this error has nothing to do with the status of a listener. The connection request never got far enough to reach a listener. If anyone tells you to check a listener, they are not paying attention, or do not understand how TNS works. This error is the equivelent of not being able to place a telephone call because you don’t know the number of the party you want to reach. You wouldn’t debug that situation by going to the other guy’s house and testing his telephone, or by going to the phone company and testing the switchboard. And you don’t debug a ORA-12154 by checking the listener. If I had a top ten list of «Incredibly Simple Concepts ™» that should be burned into the brain of everyone who claims to be an Oracle DBA, it would include «ORA-12154 Has Nothing To Do With The Listener».

Читайте также:  Iis config error cannot read configuration file due to insufficient permissions

Assume you have the following in your tnsnames.ora:
Now, when you issue a connect, say like this:
tns will look in your tnsnames.ora for an entry called ‘larry’. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).

Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name ‘myhost’ will get resolved to an IP address, either via a local ‘hosts’ file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.

Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you’ll be connected.

A couple of important points.

First, the listener is a server side only process. It’s entire purpose in life is the receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn’t sustain the connection. One listener, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners. That would be like the telephone company building a separate switchboard for each customer.

Second, the tnsnames.ora file is a client side issue. It’s purpose is for addressess resolution — the tns equivelent of the ‘hosts’ file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.

What can go wrong?

First, there may not be an entry for ‘larry’ in your tnsnames. In that case you get «ORA-12154: TNS:could not resolve the connect identifier specified» No need to go looking for a problem on the host, with the listener, etc. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory (tnsnames.ora) or can’t find the party you are looking for listed in it (no entry for larry)) you don’t look for problems at the telephone switchboard.

Maybe the entry for larry was found, but myhost couldn’t be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in «ORA-12545: Connect failed because target host or object does not exist»

Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in «ORA-12545: Connect failed because target host or object does not exist»

Maybe the IP was good, but there is no listener running: «ORA-12541: TNS:no listener»

Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. «ORA-12560: TNS:protocol adapter error»

Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn’t know about SERVICE_NAME = curley. «ORA-12514: TNS:listener does not currently know of service requested in connect descriptor»

Источник

Oracle error code 12154

Oracle Net Services provides methods for understanding, testing and resolving network problems. Oracle Database includes utilities, and log and trace files for testing and diagnosing network connection and problems. The TNSPING and TRCROUTE utilities test connectivity. The log and trace files keep track of the interaction between network components as errors occur. Evaluating this information helps to diagnose and troubleshoot network problems.

Understand the common testing procedures and network errors, and outline procedures for resolving problems. Also, learn methods for logging and tracing error information to diagnose and troubleshoot more complex network problems.

  • Understanding Automatic Diagnostic Repository
  • Diagnosing Oracle Net Services
  • Resolving the Most Common Error Messages for Oracle Net Services
  • Troubleshooting Suggestions for Oracle Net Services
    These are the suggestions for diagnosing network problems and troubleshooting Oracle Connection Manager in Traffic Director Mode.
  • Example of Troubleshooting a TNS-12154 Error
  • Logging Error Information for Oracle Net Services
  • Tracing Error Information for Oracle Net Services
  • Contacting Oracle Support Services

16.1 Understanding Automatic Diagnostic Repository

The Automatic Diagnostic Repository (ADR) (ADR) is a systemwide tracing and logging central repository. The repository is a file-based hierarchical data store for depositing diagnostic information, including network tracing and logging information.

The ADR home is the unit of the ADR directory that is assigned to an instance of an Oracle product. Each database instance has its own ADR home. Similarly, each listener, Oracle Connection Manager, and client instance has its own ADR home.

In case of a process failure, an incident is generated. The incident dump files are located in the ADR_BASE/ADR_HOME/incident/ directory, By default, ADR_BASE is ORACLE_BASE if the ORACLE_BASE variable is set. If the variable is not set, then ADR_BASE is ORACLE_HOME/log . ADR_BASE can be set to any location.

The incident dump file location can be found inside the process trace file.

The location of an ADR home is given by the following path, which starts at the ADR base directory:

Читайте также:  Illegal character error python

The following table lists the values of the path components for an Oracle Net Listener instance.

Table 16-1 ADR Home Path Components for an Oracle Net Listener Instance

listener alias name

The following figure illustrates the directory hierarchy of the ADR for an Oracle Net Listener instance. Other ADR homes for other Oracle products or components, such as Automatic Storage Management (ASM) or Oracle Database, can exist within this hierarchy, under the same ADR base.

Figure 16-1 Directory Structure for an Oracle Net Listener Instance


Description of «Figure 16-1 Directory Structure for an Oracle Net Listener Instance»

The following table lists the values of the path components for an Oracle Connection Manager instance.

Table 16-2 ADR Home Path Components for an Oracle Connection Manager Instance

Path Component Value for Oracle Net Listener

Oracle Connection Manager instance name

The following figure illustrates the directory hierarchy of the ADR for an Oracle Connection Manager instance. Other ADR homes for other Oracle products or components, such as Oracle ASM or Oracle Database, can exist within this hierarchy, under the same ADR base.

Figure 16-2 Directory Structure for an Oracle Connection Manager Instance


Description of «Figure 16-2 Directory Structure for an Oracle Connection Manager Instance»

Within the ADR home directory are subdirectories where each instance, such as the database, listener, Oracle Connection Manager, or client, stores diagnostic data. The following table lists all the subdirectories shown in the preceding figure and their contents.

Table 16-3 ADR Home Subdirectories

Path Component Value for Oracle Connection Manager

The XML-formatted log named log.xml .

Multiple subdirectories, in which each subdirectory is named for a particular incident, and each contains dumps pertaining only to that incident.

Background and server process trace files, SQL trace files, and text version of the log.xml file in the alert directory.

Other subdirectories of ADR home, which store incident packages, health monitor reports, and other information.

The ADR_BASE directory is the physical location in which one or more ADR homes are placed. Conceptually, it is the root directory of ADR.

Non-ADR (meaning that the DIAG_ADR_ENABLED parameter is set to OFF ) diagnostic and tracing methods are still current and applicable but the parameters are ignored if ADR is enabled. ADR is enabled by default.

Diagnostic parameters are found in the following configuration files:

sqlnet.ora for clients

listener.ora for listeners

cman.ora for Oracle Connection Managers

The following table compares usage of diagnostic parameters found in the sqlnet.ora file used in both ADR-based and non-ADR-based diagnostics.

Table 16-4 sqlnet.ora File Diagnostic Parameter Comparison

Subdirectory Name Contents

The following table compares usage of diagnostic parameters found in the listener.ora file used in both ADR-based and non-ADR-based diagnostics.

Table 16-5 listener.ora File Diagnostic Parameter Comparison

Parameter DIAG_ADR_ENABLED=ON DIAG_ADR_ENABLED=OFF

Table 16-6 compares usage of diagnostic parameters found in the cman.ora file used in both ADR-based and non-ADR-based diagnostics.

Table 16-6 cman.ora File Diagnostic Parameter Comparison

Parameter DIAG_ADR_ENABLED=ON DIAG_ADR_ENABLED=OFF

Oracle Call Interface Programmer’s Guide for additional information about the location of the client ADR Home

Oracle Database Net Services Reference for descriptions of the diagnostic parameters

16.1.1 ADRCI: ADR Command Interpreter

ADRCI is a command-line tool that is part of the fault diagnosability infrastructure. ADRCI enables you to do the following:

View diagnostic data within ADR

Package incident and problem information into a zip file for transmission to Oracle Support Services

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and so on.

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can run scripts of ADRCI commands in the same way that SQL*Plus runs scripts with SQL and PL/SQL commands.

To view trace files using ADRCI, enter ADRCI at a command line. The following are common ADRCI commands used to check a client:

In the preceding commands, the SHOW ALERT command shows the log.xml file in a text editor, such as VI. The SHOW BASE -product client command displays the value of the ADR_BASE directory for the client. Use that value for client in the SET BASE command.

The following are common ADRCI commands used to check a server:

Other ADRCI command options are available for a more targeted Oracle Net trace file analysis. Type HELP at the ADRCI prompt for help documentation.

Oracle Database Utilities for additional information about ADRCI

16.2 Diagnosing Oracle Net Services

Any underlying fault, noticeable or not, is reported by Oracle Net Services with an error number or message. The error number and message provide useful information for diagnosing the problem, but may not always identify the actual problem. The tasks in this section help determine which parts of Oracle Net Services do function properly rather than the parts which do not work. They also help to determine in which of the following categories the fault belongs:

  • Oracle software
  • Operating system layer
  • Other network layers

Testing the various network layers progressively should, in most cases, uncover any problem.

Starting with Oracle Database 21c, a connection identifier is available for each network connection. The connection identifier uniquely identifies a connection in trace and logs of different network elements and helps in correlating diagnostic data from these elements.

When a SQL*Net connection has multiple hops, such as from a client to Oracle Connection Manager (CMAN) and then to a server, correlating diagnostic information from the existing logs and traces becomes difficult. However, with the availability of a connection identifier, you can now easily correlate diagnostics, track network data traffic, and resolve connectivity errors.

The connection identifier consists of two components, namely, CONNECTION_ID and CONNECTION_ID_PREFIX . The CONNECTION_ID parameter contains a unique value, which is generated when the connection originates at the client. The CONNECTION_ID_PREFIX is an application specific prefix parameter that is added to the connection identifier.

16.2.1 Diagnosing Server Problems

To start diagnosing server problems, you should answer the following questions:

Is any other system such as a workstation or server able to connect to the server using Oracle Net?

Has the server, database, or listener configuration remained the same for some time?

If you answered yes to either of the preceding questions, then go to «Diagnosing Client Problems» .

If you are unsure, or answered no to any of the preceding questions, then use the tasks in this section to diagnose the problem. Diagnosing Oracle Net Services on the server involves the following tasks:

Task 1 Verify the Database is Running

To check that the database is up, log in to the database and connect with a valid username and password. For example:

A message appears, confirming that you are connected with the database. If you receive the following errors, then ask the database administrator to assist you:

ORA-1017: invalid U/P

ORA-1034: Oracle not available

Task 2 Perform a Loopback Test

A loopback test uses Oracle Net to go from the database server back to itself, bypassing the Interprocess Communication (IPC) protocol. Many network protocols provide a means of testing network connections. The PING utility can be used with a TCP/IP network. Performing a successful loopback verifies that Oracle Net is functioning on the database server.

The following procedure describes how to perform a loopback test from the server to the database:

Ensure that the listener.ora , tnsnames.ora , and sqlnet.ora files exist in the correct locations, as described in «Using Localized Management» .

Start Oracle Net Manager.

In the navigator, expand the Directory or Local option.

Expand Service Naming to view the available network service and database names.

Select the network service name or database service.

Choose Command , and then select Test Net Service .

Testing assumes the listener and database are running. If they are not, then see «Starting Oracle Net Listener and the Oracle Database Server» to start components.

During testing, a Connection Test dialog box appears, providing status and test results. A successful test results in the following message:

If the test was successful, then proceed to step 7.

If the test was not successful, then do the following:

Ensure the database and listener are running, and then click Test .

Click Change Login to change the username and password for the connection, and then click Test .

If the loopback test passes, then go to «Diagnosing Client Problems» .

If the loopback test continues to fail, then contact Oracle Support Services.

Click Close to close the Connect Test dialog box.

16.2.2 Diagnosing Client Problems

Verify at least one of the following statements. This will help you decide if it is a client problem.

The database server passed a loopback test, showing the connection worked.

Other computers connect using Oracle Net Services to the same database.

Connections from this workstation worked before making changes on this computer, such as the installation of a new product or modification to the network configuration.

The following procedure describes how to perform diagnostics on the client:

Check that you have installed the same protocol support that was installed on the database server.

On Linux and UNIX platforms you can use the ADAPTERS utility to verify protocol support. On the database server, run the following command from the ORACLE_HOME/bin directory to display the protocol support, naming methods, and security options linked with the oracle executable:

The adapters utility displays output similar to the following:

On the client, run the adapters command from the ORACLE_HOME/bin directory to display the configured Oracle protocol support, naming methods, and security options. The ADAPTERS utility displays output similar to the following:

The DES , DES40 , 3DES 112 , 3DES 168 , RC4 40 , RC4 56 , RC4 128 , RC4 256 , and MD5 algorithms are deprecated in this release.

To transition your Oracle Database environment to use stronger algorithms, download and install the patch described in My Oracle Support note 2118136.2.

RAW is an internal protocol used by Oracle Net.

Oracle Database Administrator’s Reference for additional information about the adapters utility

Check base connectivity for underlying network transport. Oracle Net technology depends on the underlying network for a successful connection.

Table 16-7 Verify Base Connectivity for Network Transport

Parameter DIAG_ADR_ENABLED=ON DIAG_ADR_ENABLED=OFF

Use terminal emulation or file transfer utilities, (PING, FTP, TELNET) from the client to the database server.

See other computers or servers on the Microsoft network.

Ensure that you are able to share drives within the network.

Ensure that the Oracle Net foundation layer and the appropriate Oracle protocol support are present by verifying that all Oracle Net Services software has been installed for the client.

Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files in the correct locations.

If any other working client computers are connecting to the selected Oracle Database, then back up your existing files and copy both the working tnsnames.ora and sqlnet.ora files from the working client computer to the non-working clients. This eliminates the possibility of errors in the files.

Test the Oracle Net foundation layer. You can test using the following command to connect to SQL*Plus:

Do not use the TNSPING utility. The TNSPING utility works like the TCP/IP ping utility and does not create and open a socket, nor does it connect with the listener. It only shows that the listener is present on the database server.

If the connection still fails, then do the following:

Check the Oracle Support Services website for a specific diagnostics bulletin on the error received.

Источник

Оцените статью
toolgir.ru
Adblock
detector
Protocol Verify that you can.