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

-

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


Shell Prompt>sqlplus scott@orcl
.....................
.....................
Enter password: *****
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

When you try to connect to a remote database from your client machine, you provide a
connect identifier in your connection command, which get resolved into a connect descriptor
depending on the parameter's value in Oracle Net profile (SQLNET.ORA)

connect identifier can be in the form of Net Service Name or Easy Connect or other,
which depends upon the value of NAMES.DIRECTORY_PATH parameter
in the Oracle Net profile (SQLNET.ORA)

  @[<net_service_name> | [//]Host[:Port]/<service_name>]

    <net_service_name> is a simple name for a service that resolves
    to a connect descriptor.

    Example: Connect to database using Net Service Name and the
             database net service name is ORCL.

       sqlplus myusername/mypassword@ORCL

    Host specifies the host name or IP address of the database
    server computer.

    Port specifies the listening port on the database server.

    <service_name> specifies the service name of the database you
    want to access.

    Example: Connect to database using Easy Connect and the
             Service name is ORCL.

       sqlplus myusername/mypassword@Database_Host/ORCL



Reason for ORA-12154: TNS:could not resolve the connect identifier specified
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A connection to a database or other service was requested using a connect identifier,
and the connect identifier specified could not be resolved into a connect descriptor
using one of the naming methods configured. For example, if the type of connect
identifier used was a net service name then the net service name could
not be found in a naming method repository, or the repository could not be located or reached.


Solution for ORA-12154: TNS:could not resolve the connect identifier specified
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are using local naming (TNSNAMES.ORA file):

- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter
in the Oracle Net profile (SQLNET.ORA)

- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched
parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

- If problem still persists, try to re-create TNS entry in tnsnames.ora

You can find these files in ORACLE_HOME/NETWORK/ADMIN Directory.
Basically this file(s) are to be reside on the client machine as they are required by the
client process to resolve the connect_identifier to connect descriptor.

Sample sqlnet.ora
~~~~~~~~~~~~~~~~~~~~~~~~
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Sample TNS Entry in tnsnames.ora
~~~~~~~~~~~~~~~~~~~~~~~~~~
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

If you are using directory naming:

- Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the
Oracle Net profile (SQLNET.ORA).

- Verify that the LDAP directory server is up and that it is accessible.

- Verify that the net service name or database name used as the connect identifier is
configured in the directory.

- Verify that the default context being used is correct by specifying a fully qualified net
service name or a full LDAP DN as the connect identifier

If you are using easy connect naming:

- Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

- Make sure the host, port and service name specified are correct.

- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators
Guide or the Oracle operating system specific guide for more information on naming.


One more Point...........

There is an optional parameter that can be set in sqlnet.ora and that is NAMES.DEFAULT_DOMAIN. Before looking in tnsnames.ora,
sqlnet will check to see if the parameter NAMES.DEFAULT_DOMAIN has been set.
If it is then it’s value will be appended to the connect_identifier supplied
by the user – before searching tnsames.ora for the result.

(SQLNET.ORA)
NAMES.DEFAULT_DOMAIN=mydomain.com

So either remove this from sqlnet.ora OR add the NAMES.DEFAULT_DOMAIN's to connect descriptor
in tnsnames.ora


Sample TNS Entry in tnsnames.ora
~~~~~~~~~~~~~~~~~~~~~~~~~~~

orcl.mydomain.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more