Sunday, 21 February 2016

ORA-12543: TNS:destination host unreachable

Connectivity errors are common questions on Oracle related forums and in many cases they are highly misunderstood by the poster of the questions and some responders.
This aim of this document is to show a method for diagnosis and resolution to a scenario in which an ORA-12543 is raised during the connection to a database.



Problem:
You attempt a connection to database through SQL*Plus and you receive the following error.
1
2
3
4
5
6
7
8
9
10
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 30 11:06:18 2010
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
SQL> connect hr@proddb
Enter password:
ERROR:
ORA-12543: TNS:destination host unreachable
SQL>
Diagnosis process:
If you are unfamiliar with the error use the oerr utility to get more information on the error.
1
2
3
4
5
[oracle@ora2 ~]$ oerr ora 12543
12543, 00000, "TNS:destination host unreachable"
// *Cause: Contact can not be made with remote party.
// *Action: Make sure the network driver is functioning
 and the network is up.
[oracle@ora2 ~]$
An ORA-12543 most commonly indicates a problem either resolving the host name given in the ADDRESS parameter of thetnsnames entry or that the network is unavailable. In some cases it could also be that the database server down.
If you were to trace the connection attempt you would see something similar to the following.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
(3086649024) niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)
(HOST=prod)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=
(PROGRAM=sqlplus)
(HOST=ora2.localdomain)(USER=oracle))))
(3086649024) nscall: connecting...
(3086649024) nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=192.168.56.130)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=(PROGRAM=sqlplus)
(HOST=ora2.localdomain)
(USER=oracle))))
(3086649024) nttbnd2addr: using host IP address: 192.168.56.130
(3086649024) nsopen: opening transport...
(3086649024) nttcni: Tcp conn timeout = 60000 (ms)
(3086649024) nttcni: trying to connect to socket 8.
(3086649024) ntt2err: soc 8 error - operation=1, ntresnt[0]=513,
 ntresnt[1]=101,  ntresnt[2]=0
(3086649024) nserror: nsres: id=0, op=65, ns=12543, ns2=12560; nt[0]=513, 
nt[1]=101, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(3086649024) nsopen: unable to open transport
(3086649024) nioqper:  error from nscall
(3086649024) nioqper:    ns main err code: 12543
(3086649024) nioqper:    ns (2)  err code: 12560
(3086649024) nioqper:    nt main err code: 513
(3086649024) nioqper:    nt (2)  err code: 101
(3086649024) nioqper:    nt OS   err code: 0
(3086649024) niqme: reporting NS-12543 error as ORA-12543
(3086649024) niotns: Couldn't connect, returning 12543
Looking at the trace we can see that TNS was unable to open transport to the host.
Below are the following possible issues ranging from least probable to most probable.
The database server is down– Possible and can be easily verified.
The network is down – Probable as simple ping test to another server that is known to be up can narrow down the issue some.
The IP address is incorrect in either the host file or the DNS– Most likely the case. Even if you do not know the IP address, if you factor out the other two possibilities all you are left with is this one.
Resolution
Correct the incorrect IP address in the hosts file/DNS or resolve the network outage.

No comments:

Post a Comment