How to troubleshoot Oracle remote database connection
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.
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/orcl
or
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
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.
Hi Raghu to test Oracle Net connectivity
– 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
— Kirill
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.
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
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
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
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
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
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
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
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
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
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.
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?
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
My another article about can explain partly this topic
http://www.dadbm.com/consider-security-using-one-big-application-user-model/
— Kirill
can you please help to create database link with load balance database (RAC) what should be host name
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!
Hi Nilesh
Your RAC SCAN IP. It will connect to one or another RAC instance.
— Kirill
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
you have probably 1 listener that manages 2 DB instances. Run netmgr you should probably see it.
— Kirill
Very useful article – pretty easy to follow and understand!!
Thanks for this post.
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
Do not see your exact issue but I suggest to follow the steps in the same post to nail down the issue.
— Kirill
Dear Sir,
Please send me the detailed method how to connect oracle server via any vpn tool like
teamviewer or hamachi
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
Well, if you install Oracle client on Centos and your DB up and running, just follow this guide and you should be ok
— Kirill