How to troubleshoot Oracle remote database connection

June 7, 2013 by 28 Comments 

Many Oracle DBAs, developers and just end-users often encounter an issue when they can not connect remotely to an Oracle database. There can be different reasons of the connection problems. I give below a short cookbook on resolving those database connection issues. But first let’s explore a bit Oracle connectivity concepts and terminology.

Oracle NET Client Listener tnsnames

Oracle NET Client Server configuration

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. The service name is included in the connect data part of the connect descriptor. To connect to a database service, clients use a connect descriptor that provides the location of the database and the name of the database service. The address portion of the connect descriptor is actually the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server. The listener receives incoming client connection requests and hands these requests to the database server. Once the connection is established, the client and database server communicate directly.

Ping database host IP

ping 11.222.333.44

If it works, go to the next step;
If not, check the server availability

Ping database hostname

ping orcl.dadbm.com

if it works, go to the next step;
if not, something wrong with DNS /ACTIVE directory => try using hosts file

Test if listener port is reachable/opened for your remote connection

For that use telnet utility which is available on Unix and can be enabled on Windows.

1. Linux example:
$ telnet orcl1.dadbm.com 1522
Trying 10.126.247.11...
Connected to orcl.dadbm.com (12.222.333.44).
Escape character is '^]'.
get
Connection closed by foreign host.

2. Windows example

1) First enable telnet on Windows 7 if it’s not there:

pkgmgr /iu:"TelnetClient"

2) Check the database port

telnet orcl1.dadbm.com 1523
=> no output in case of a port opened; in case of a failure – see below Oracle error:
Could not open connection to the host, on port 1523: Connect failed

In case the database listener port is not reachable you face potentially a firewall issue. There are at least two solutions of this problems.
Both described in one of my presentations on SlideShare and in Demos on DaDBm YouTube channel.

Test Oracle Net connectivity – tnsping

The Oracle Net Listener is the gateway to the Oracle instance for all nonlocal user connections. A single listener can service multiple database instances and thousands of client connections. tnsping is the Oracle Net equivalent of the TCP/IP ping utility. It offers a quick test to verify that the network path to a destination is good. The utility validates that the host name, port, and protocol reach a listener. It does not actually check whether the listener handles the service name or a database is up and running

tnsping orcl.dadbm.com:1521/orclor
tnsping orcl

1) If it works, you will get a following message:

OK (10 msec)

2) In case of issues or errors, verify that the database listener is configured properly and/or troubleshoot the client side (see the chapter below)

Test database connection

1) With EZCONNECT bypassing tnsnames.ora (you can omit default port 1521)
sqlplus user@'//orcl.dadbm.com:1521/orcl'
sqlplus user@'//orcl.dadbm.com/orcl'

2) With TNS alias using tnsnames.ora file:

sqlplus user@orcl

Troubleshoot the client side

Before trying to solve a particular Oracle error on client side, ensure the following on client side:

– Your Oracle client is installed and configured properly
– Identify your Oracle environment
– Identify current ORACLE_HOME
– Identify a location of tnsname.ora file (if used)
– Verify that you have correctly entered the service name of the database that you want to reach
– If you are connecting from a login dialog box, verify that you are not placing an at symbol (@) before your connection service name.

You can use Oracle Universal Installer (OUI) and OS commands to achieve all above steps. For example, on Windows following sqlplus commands can be useful in identifying your Oracle environment:

sqlplus /nolog
@%ORACLE_HOME%
@%TNS_ADMIN%

The following error codes are related to problems on the client side

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

Couse and Action:
Usually this error indicate that a connect identifier / tns alias you use in your connection can not be recognized or found somewhere. Cross check your tnsnames.ora if it exists there.

ORA-12198: “TNS:could not find path to destination” and
ORA-12203: “TNS:unable to connect to destination”

Cause: The client cannot find the desired database.
Action:
1. Verify that the service name ADDRESS parameters in the connect descriptor of your TNSNAMES.ORA file are correct.
2. Verify that the listener on the remote node has started and is running. If not, start the listener by using the Listener Control utility.

ORA-12533: “TNS:illegal ADDRESS parameters”

Cause: The protocol-specific parameters in the ADDRESS section of the designated
connect descriptor in your tnsnames.ora file are incorrect.
Action: For more information about protocol-specific keywords, refer to the Oracle
operating system documentation for your platform.

TNS-12541: TNS:no listener

Cause: The listener on the remote node cannot be contacted.
Action: Verify that the listener on the remote node has been started. You can check its status with the STATUS command of the Listener Control utility and start it with the START command if necessary. Verify that the database listener is configured properly using the following commands:
tnslsnr status
tnslsnr status
tnslsnr services

tnsnames.ora file example


ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl1.dadbm.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL1)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl2.dadbm.com)(PORT = 1522))
(CONNECT_DATA = (SERVICE_NAME = ORCL2))
)
ORCL3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl3.dadbm.com)(PORT = 1523))
)
(CONNECT_DATA = (SID = ORCL3))
)

More information

Presentation:  Live adventure – From my PC to Oracle remote database
Demo videos:
How to configure / identify Oracle client on Windows
Firewall SSH tunneling to Oracle database

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

28 Responses to “How to troubleshoot Oracle remote database connection”
  1. Raghu says:

    Hi Kirill, your article is very informative.

    I have an oracle12c database setup on my windows 8 machine & I am trying to access this database through JDBC from ubuntu(Linux) machine present in local area network. When am trying to establish connection through JDBC, getting the following exception

    The network adapter could not establish connection.

    I have ensured the following. 1. Disabled the firewalls between both the machines. 2. the URL is proper & I tired using both hostname as well as ip address. 3. listener is up and running on port 1521.(lsnrctl stat shows the listener is READY)

    I have done extensive google search about this. Please help. Thanks in advance. Please let me know if you need more details.

  2. Kirill Loifman says:

    Hi Raghu
    – Ensure you connect to service_name and not to SID
    – If it’s not the case, you probably have to fix listener issue
    – Ensure you your listener properly registers your instance with the proper service you try to connect to. Example is below:
    $ lsnrctl status

    Services Summary…
    Service “ORCL” has 1 instance(s).
    Instance “ORCL”, status UNKNOWN, has 1 handler(s) for this service…

    – You can install oracle client and try tnsping to test Oracle Net connectivity
    — Kirill

  3. Fantastic article and very well explained. After fighting for 3 days to get a link between MS SQL Server and Oracle, I finally stumbled across your article. This was the information I needed to get our setup to work.

  4. Samer says:

    Dear Sir,

    i have DataBase Oracle 10g installed on linux RedHat Server and i restarted the server then got the same error ORA-15260 on my DataBase, i tried the below steps

    1- Ping and got below result

    ping 10.10.151.15
    PING 10.10.151.15 (10.10.151.15) 56(84) bytes of data.
    64 bytes from 10.10.151.15: icmp_seq=1 ttl=64 time=0.040 ms
    64 bytes from 10.10.151.15: icmp_seq=2 ttl=64 time=0.046 ms
    64 bytes from 10.10.151.15: icmp_seq=3 ttl=64 time=0.035 ms

    2- use tnsping

    IT-RBT:oracle:/report/oracle$ tnsping 10.10.151.15:1521/orcl

    TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 10-JUL-2014 09:39:51

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

    Used parameter files:
    /report/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

    Used EZCONNECT adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orcl))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.151.15)(PORT=1521)))
    OK (0 msec)

    but still i got same error and can’t connect to the DataBase from any client.

    Best Regards
    SHM

  5. Samer says:

    Sorry i forgot to inform you that also i test the below command

    3- lsnrctl status

    and got the below results

    Services Summary…
    Service “REPORT” has 1 instance(s).
    Instance “REPORT”, status READY, has 1 handler(s) for this service…
    Service “REPORTXDB” has 1 instance(s).
    Instance “REPORT”, status READY, has 1 handler(s) for this service…
    The command completed successfully

    Best Regards
    SHM

  6. Kirill Loifman says:

    Well, something is wrong in your test.
    you connect with 10.10.151.15:1521/orcl to service orcl which is not there in your “lsnrctl status”.
    You have only REPORT DB service registered.
    – ensure you test your connection (step 1,2) from the same remote client from where you try to connect
    – use proper DB service that is registered in the listener. Potentially It can be:
    10.10.151.15:1521/report
    .. but I’m not cirtain.
    — Kirill

  7. Ammar says:

    Hi Kirill,

    Your article is very really helpful. I have “TNS-12541: TNS:no listener” error;

    (1) I have installed oracle 11g on one of the VMware virtual machine with orcl.abcd.local database, and orcl as connect descriptor in its tnsname.ora file.

    (2) I have installed weblogic 10.3.6, forms and reports 11g on another virtual machine, where I put the tnsname.ora file of above database folder to forms 11g machine at C:\oracle\Middleware\asinst_1\config

    (3) when I run forms 11g and try to connect database through login screen, it through an error that:

    TNS-12541: TNS:no listener

    (4) when I tried to “tnsping orcl ” , it shows the connect descriptor lines but again same error ie:

    TNS-12541: TNS:no listener

    Kindly guide where is the problem in connecting from forms 11g machine to database machine.

    Thanks
    Ammar

  8. Aurelio Rommel says:

    Hi Sir Kirill ,
    I found a problem with your EZCONNECT example

    sqlplus user@’//orcl.dadbm.com:1521/orcl’

    Because of its usefulness I like EZCONNECT but I never was able to connect to a database without put the password in the connection string like your example.

    I made my tests using databases servers from 10.2.0.2 to 11.2.0.3.8 and full client version 11.2.0.1. I think that both client and server side TNS configuration are ok because if I try to connect using EZ and providing the password works. If I use your example (EZ without password ) SQL*Plus blames me and prints its the ‘USAGE’ message like when you mistype some keyword .

    I have to avoid to use the password to establish a db session because of an internal rule of ours (i.e. no one should see any passwords on the shell history )

    Best Regards,

    Aurelio

  9. Kirill Loifman says:

    Hi Aurelio
    It definitely should work this way, at least on Windows client.
    I’ve just. Below is an example from my recent demo and presentation on DOAG2014 conference:
    C:\”\>sqlplus dadbm@’//127.0.0.1:8822/orcl’
    SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 22 21:49:42 2014
    Copyright (c) 1982, 2010, Oracle. All rights reserved.
    Enter password:
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
    With the Partitioning option
    SQL>

    See my presentation From my PC to Oracle remote database

  10. Kirill Loifman says:

    Hi Ammar
    You have everything is in the article. If you can ping and telnet the host and DB port.
    Most probably you have an issue in your tnsnames connection string or on listener side.
    Try to connect on DB server side locally using local TNS alias from local tnsnames.oare and listener like this:
    sqlplus user/pass@
    If you have same error, fix your listener first.
    Check my new presentation on this topic: From my PC to Oracle remote database

    10
    — Kirill

  11. Pritiranjan says:

    Hi,

    I am facing one issue while trying to connect to database using TNSPING utility.

    Error : ORA-12535: TNS:operation timed out

    I have increased the timeout parameter in SQLNET.ORA file. Also from other DBs i am able to connect using the same TNSNAMES.ORA file.

    What could be the issue?

    I am able to ping the remote host DB.
    Telnet is not happening and not able to connect.

    Regards,
    Pritiranjan

  12. Kirill Loifman says:

    Hi Pritiranjan
    I doubt that the timeout parameter in SQLNET.ORA can help here.
    In case you can not reach the host and the DB port with telnet, you have a potential firewall issue. If this is the case open firewall port or establish firewall tunnel. Check out my presentation and demo on this topic:
    From my PC to Oracle remote database;
    Firewall SSH tunneling to Oracle database – Demo 2
    — Kirill

  13. Ganesh@ says:

    Very nice article sir 🙂

    I have created 2 database db1 and db2 on window server for my database db1 i have one listener file on location “E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN” but for db2 its not available on dbhome_2 path

    And i m able to access both the databsae there is no issue with listner.
    just need to set oracle sid every time.

    please explain me why if happened ?

    Thanks in advance.

  14. Rashid says:

    i want to know what security issues should be considered while giving some one to access the database tables.
    how we can set the users rights to read only through command.

  15. pramod says:

    i have install 2 redhat linux in one vmware and one of them linux has database up .
    and from 2nd linux on same vmware in same machine i want to connect to this database what should i do?

  16. Kirill Loifman says:

    Hi Pramod
    Not sure what you mean by 2 Linx in one VMware. If they are different machines in terms of network (have diff. IPs) then the connection way should be similar to any remote DB connection
    — Kirill

  17. Kirill Loifman says:

    My another article about can explain partly this topic
    http://www.dadbm.com/consider-security-using-one-big-application-user-model/
    — Kirill

  18. Nilesh says:

    can you please help to create database link with load balance database (RAC) what should be host name

  19. Suresh says:

    Dear Kirill,

    Am getting error when testing a DB link to a remote server – ORA-12170: TNS:Connect timeout occurred

    But ping, telnet, tnsping are successful. But when i create a db link & test the connectivity it fails. 🙁

    When I try ‘sqlplus username@connection_name’ then same error.

    tnsnames.ora has entry for the target system, by using the same am able to connect to target system from other DB server.

    Server from where i tried DB Link:
    ———————————————
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    TNS FOR IBM/AIX RISC SYSTEM/6000: VERSION 11.2.0.4.0 – PRODUCTION

    Hope you can give me a possible root cause. Thanks in advance!

  20. Kirill Loifman says:

    Hi Nilesh
    Your RAC SCAN IP. It will connect to one or another RAC instance.
    — Kirill

  21. Kirill Loifman says:

    Hi Suresh
    There is no obvious answer to the error ORA-12170. That depends on several factors.
    It can be:
    – network/firewall issue
    – TNS configuration on client side
    – Connection time outes on server side
    The easiest way to go is to eliminate components to trouble shoot.
    Check if this is only one single client problem (check another one) or you have similar error from other clients from same network segment.
    DB Alert.log and listener.log can be also helpful.
    — Kirill

  22. Kirill Loifman says:

    you have probably 1 listener that manages 2 DB instances. Run netmgr you should probably see it.
    — Kirill

  23. Balaji Venkatachalam says:

    Very useful article – pretty easy to follow and understand!!

    Thanks for this post.

  24. Mouli says:

    Krill,
    Informative article.Below is my issue. I am connecting to Oracle database through c3p0 from Java application.
    We have as many as 18 servers and the below issue occurs in 4 servers.

    Even when we kill our application , still the DB connections are not going down from the server.
    We are not seeing any connection from application end. What settings I need to check for this.
    Will it be a Network/Firewall issue, If so how to conclude this and what settings I need to check in the Linux Server

    Thanks,
    Mouli

  25. Kirill Loifman says:

    Do not see your exact issue but I suggest to follow the steps in the same post to nail down the issue.
    — Kirill

  26. Burhan says:

    Dear Sir,

    Please send me the detailed method how to connect oracle server via any vpn tool like
    teamviewer or hamachi

  27. Nikhil says:

    Hi Kirill,

    I installed oracle database 11g on one server which has Centos7 . Now i have another client machine with same os as Centos7 and i want to access the oracle database on first server from this client machine. I could telnet, ping between 2 servers and only port 1521 seems open. Kindly help me with step by step process to connect to oracle database remotely via sqlplus command so that i can access and create tables from client machine.

    Eagerly waiting for response.

    Thanks,
    Nikhil

  28. Kirill Loifman says:

    Well, if you install Oracle client on Centos and your DB up and running, just follow this guide and you should be ok
    — Kirill

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email