How to troubleshoot Oracle remote database connection

Data and Database Management DBMS Blog

June 7, 2013 byKirill Loifman25 Comments

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

Oracle NET Client Server configuration

AnOracle databaseis 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.

If not, check the server availability

if not, something wrong with DNS /ACTIVE directory = try using hosts file

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

Connected to orcl.dadbm.com (12.222.333.44).

1) First enable telnet on Windows 7 if its not there:

= 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 ofmy presentations on SlideShareand in Demos onDaDBm YouTube channel.

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

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

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)

1) With EZCONNECT bypassing tnsnames.ora (you can omit default port 1521)

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

2) With TNS alias using tnsnames.ora file:

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:

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

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.

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.

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:

(ADDRESS = (PROTOCOL = TCP)(HOST = orcl1.dadbm.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = orcl2.dadbm.com)(PORT = 1522))

(CONNECT_DATA = (SERVICE_NAME = ORCL2))

(ADDRESS = (PROTOCOL = TCP)(HOST = orcl3.dadbm.com)(PORT = 1523))

Presentation:Live adventure From my PC to Oracle remote database

How to configure / identify Oracle client on Windows

Firewall SSH tunneling to Oracle database

Oracle Listener refused connection ORA-12519 troubleshooting

Enable Oracle database to send emails via SMTP server

How to fix ORA-12547 TNS lost contact when try to connect to Oracle

From my PC to Oracle remote database at DOAG 2014

Please share it with others using the social site of your choice:

Filed underHow-toOracleTagsclientdatabaseerrorNETORA-OracleserviceTNStnsnames

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.

Ensure you connect to service_name and not to SID

If its 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:

Service ORCL has 1 instance(s).

Instance ORCL, status UNKNOWN, has 1 handler(s) for this service

You can install oracle client and try tnspingto test Oracle Net connectivity

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.

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

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

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.

/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)))

but still i got same error and cant connect to the DataBase from any client.

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

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

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:

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:

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

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

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 )

It definitely should work this way, at least on Windows client.

Ive 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.

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 64bit Production

See my presentationFrom my PC to Oracle remote database

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:

If you have same error, fix your listener first.

Check my new presentation on this topic:From my PC to Oracle remote database

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.

I am able to ping the remote host DB.

Telnet is not happening and not able to connect.

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

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.

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.

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?

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

My another article about can explain partly this topic

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

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 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.

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!

Your RAC SCAN IP. It will connect to one or another RAC instance.

There is no obvious answer to the error ORA-12170. That depends on several factors.

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.

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

Very useful article pretty easy to follow and understand!!

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

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

We welcome thoughtful and constructive comments from readers.

If you want your own picture to show with your comment?Go get aGlobally Recognized Avatar!

Mail (will not be published) (required)

Notify me of followup comments via e-mail

Being an Oracle Certified Professional database administrator I have more than a decade full-time DBA experience.

Enjoy reading my DBMS Blog and feel free to hire me as an Oracle consultant.

onHow to fix ORA-12547 TNS lost contact when try to connect to Oracle

onOracle RMAN full backup script for EMC NetWorker

onOracle database client silent installation without response file

onHigh Availability and SLA requirements for Oracle database

onHow to fix ORA-12547 TNS lost contact when try to connect to Oracle

RSS:Subscribe to ArticlesSubscribe to CommentsReceive site updates via email

Over 17 years of full-time Oracle DBA experience

Expert in Oracle performance, security and troubleshooting

Oracle certified professional DBA (OCP) 9i, 10g, 11g, 12c

Certified in ITIL, Project & Service management

Database Solution Architecture

Oracle database health-check