Common TNS/Database Connectivity Issuses and Possible solutions

By Jag - August 20, 2013

you must check the following criteria shoulde be satisfied
If you are using the Oracle tool like TOAD, SQL Developer,Discoverer Desktop or Report Builder,Form Builder to work properly

1.    The  environmental variable (TNS_HOME) sould be set properly I mean it should point to the
 oracle home  directory where you placed your TNS file only.
Ex: %ORACLE_HOME%/network/admin

2.    The TNS Entry should be in the proper format
SID=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=DATABASE_SERVER_NAME/IP)(PORT=PORT_NUMBER))
            (CONNECT_DATA=
                (SID=DATABASE SID)
            )
        )

3.    Verify  Oracle Database version 10g or 11g is installed on your computer.

Go to Start->Run->type “cmd” and press enter

enter the command  "TNSPING" it shold return a message like



If the command returns no output: it means  database is not installed , or the PATH variable is not set.


Required Environmental Variables for Oracle

Make Sure that the Following environmental variables are added at the SYSTEM Level:

ORACLE_HOME-  C:\DEVSUITE_HOME  # The location where Database is installed

PATH - %ORACLE_HOME%\bin

TNS_ADMIN - %ORACLE_HOME%/network/admin


                                                          Common TNS Errors
                                                        
                                                TNS-03505 Failed to resolve name
Cause:
 SID is not present in the tnsnames.ora file.

 Possible Solution:
 Go to Start->Run->type “cmd” and press enter
 enter the command "tnsping SID_NAME"

Make sure that you have added the tns entry on the correct tnsnames file.
the tnsnames file should be in oracle home specified location Ex..C:\%ORACLE_HOME%\network\admin)

                              TNS-12545: Connect failed because target host or object does not exist
Causes:
1.    The server is not reachable.
2.    Wrong TNSNAMES entry.
3.    A wrong SID is mentioned in the TNSNAMES file.

 Possible Solution:
 1.    Do a telnet on the database server and the database port.

If the TNS Entry is like

ODEV=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=TEST)(PORT=1521))
(CONNECT_DATA=
(SID = ODEV)
)
)

Here, the Database server name is TEST and the port is 1521.
Go to Start->Run->type “cmd” and press enter
 enter the command "telnet TEST 1521"

The command should return a blank window.IF not , check the server name and connectivity to the server.

2.    Check the TNS Entry also.

Normally  TNS Entry should be the specified format which I mentioned at the statring of this post.
Please use the Following as a template and create your own TNS Entry and replace it with the one in the TNSNAMES.ora File.

                                    ORA-12154: TNS: Could not resolve service name
Cause:      
The problem seems related to the listner means the  client was able to connect to the listener,
but the listener rejected the connection because the Servie_name/SID in the TNS entry is not recognized.
                      
Possible Solution:
Check the TNS Entry as mentioned in the above step. If you are getting the error in a tool like toad or report builder or pl/sql developer,
form bilder do a tnsping and confirm if you get a OK message or not!!.
  • Share:

You Might Also Like

0 comments