Have anyone encounter this error before? I tried to refer to this link: http://www.ardentperf.com/2007/04/02/local_listener-and-ora-12545/
But it doesn’t really resolve our issue. Our scenario is that we are able to connect to the database however we will encounter this error when we try to select data from a view.
I have enabled Client-side sqlnet trace but i am unable to interpret what is the exact cause of the issue.
Any ideas anyone?
Thanks
OMG Ponies
326k82 gold badges523 silver badges502 bronze badges
asked Jan 26, 2011 at 2:35
4
For me the problem was the HOST was not being detected by name in the TNSNAMES.ora, using the IP address instead resolved it (I think its due to a domain controller issue):
XYZD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.45.67.89)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = XYZD))
)
Do a command: “ping HOST” to find the servers IP address.
- PING HOST
- TELNET HOST PORT
- TNSPING TNS_ALIAS
Edit:
Just ran into this again, this time it was a Firewall blocking TCP via the port.
answered Sep 28, 2016 at 2:40
Jeremy ThompsonJeremy Thompson
62.1k36 gold badges195 silver badges322 bronze badges
1
This issue can be multiple things:
1. Your TNSNAMES.ora isn’t up to date
Fix: Find your Oracle Home
Find Directory: /network/ADMIN/
TNSNAMES.ora should be in there if you’re experiencing this issue on a local machine
2. Create TNS_ADMIN Environmental Variable
In my case:
Variable Name: TNS_ADMIN
Value: C:\Programs\Ora10g\network\ADMIN
For testing purposes, try connecting to the Oracle DB using sqlplus (you may already be trying this).
answered May 30, 2012 at 16:39
I also had this issue, and since I was not using a tnsnames.ora file, I almost gave up hope, when I stepped on this link.
So, now my code looks like this:
import cx_Oracle
connection_string = '''username/password@(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=<host_name>)
(PORT=<port_numer>)
)
)
(CONNECT_DATA=
(SID=<your_SID>)
)
)'''
db = cx_Oracle.connect(connection_String)
Now you can create a cursor and write your query.
Note: This is not a recommended practice, but I used it just for testing.
answered Mar 13, 2019 at 11:56
i’ve got this error and the reason for me was that I was trying to connect on the wrong PORT.
answered Sep 18 at 11:52
ORA-12545 and TNS-12545
ORA-12545 is the same as TNS-12545, they all indicate that the destination you provide in the connection string or description is not valid.
Let’s see how we reproduce TNS-12545.
C:\Users\ed>tnsping compdb
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 21-JUL-2014 19:10:59
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
TNS-12545: Connect failed because target host or object does not exist
TNS-12545 complains about hostname can not be resolve. As we can see, the network service name points to a host called primary01 which could be the source of problem.
Let’s resolve the hostname by nslookup through DNS 10.10.10.10.
C:\Users\ed>nslookup primary01 10.10.10.10
Server: 10.10.10.10
Address: 10.10.10.10#53
** server can't find primary01: NXDOMAIN
Obviously, DNS has no record about primary01. Next, you should check /etc/hosts, it may be a typo there or you misspelled the hostname in tnsnames.ora.
The solution is easy. You can add the IP-Hostname mapping record to either /etc/hosts or DNS.
C:\Users\ed>notepad %SystemRoot%\System32\drivers\etc\hosts
...
10.1.2.123 primary01 primary01.example.com
Then we test the result.
C:\Users\ed>ping primary01
Pinging primary01 [10.1.2.123] with 32 bytes of data:
Reply from 10.1.2.123: bytes=32 time<1ms TTL=64
Reply from 10.1.2.123: bytes=32 time<1ms TTL=64
Reply from 10.1.2.123: bytes=32 time<1ms TTL=64
Reply from 10.1.2.123: bytes=32 time<1ms TTL=64
Ping statistics for 10.1.2.123:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
That’s how we resolve TNS-12545.
Further reading: TNSPING Errors Collections
For more connection troubleshooting, you may refer to Oracle 19c Net Services Administrator’s Guide: 15 Testing Connections.
Problem
A Data Manager catalog is held in an Oracle database. Attempting to connect to the catalog fails with an error.
Symptom
1. ERROR
DS-DBMS-E402: UDA driver reported the following:
UDA-SQL-0031 Unable to access the ‘ALIAS_03E43FD8’ database.
UDA-SQL-0532 Data Source is not accessible: ‘TNS:MYORCL’.
ORA-12545: Connect failed because target host or object does not exist
Cause
The NAMES.DIRECTORY_PATH= option in the sqlnet.ora file contained an entry for EZCONNECT:-
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Usually, this entry only contains
NAMES.DIRECTORY_PATH= (TNSNAMES)
With easy connect it is possible to connect to a database using the following information:
server_name:port/service_name.
This means that Oracle now sees «TNS:MYORCL» as server_name and service_name «TNS» and
port «MYORCL».
Resolving The Problem
Remove the EZCONNECT option in the NAMES.DIRECTORY_PATH= setting in the sqlnet.ora file.
Steps:
- Open sqlnet.ora in a text editor
- Search for NAMES.DIRECTORY_PATH=
- Remove the EZCONNECT option
[{«Product»:{«code»:»SSEP7J»,»label»:»Cognos Business Intelligence»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud \u0026 Data Platform»},»Component»:»Data Manager»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»8.1;8.2″,»Edition»:»»,»Line of Business»:{«code»:»»,»label»:»»}}]
Historical Number
1030720
This is a revised version of an article about ORA-12545 that I originally published while using Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.
Introduction to ORA-12545
Continuing our discussion of “Why can’t I connect to my database”, I want to focus on “ORA-12545: Connect failed because target host or object does not exist”.
To recap what we’ve covered so far, when an oracle client requests a connection to a database, it has to provide a “connect identifier”, which sqlnet then translates to a “connect descriptor”. The connect descriptor specifies the transport protocol (usually tcp), the ip address of the database server, the port being used by the listener, and the service name the database has registered with the listener. This name resolution is usually done by looking up the connect identifier in the client’s tnsnames.ora file, though there are other methods available as well. Failure to find an entry from which to derive the connect descriptor will result in an ORA-12154: TNS:could not resolve the connect identifier specified, which I explained here.
Once the connect descriptor is determined, that information becomes part of the packet that is passed to the transport layer of the standard OSI network stack and is thus used for standard network routing. This is important to understand. At this point, the routing of the packet to its destination is handled entirely by standard network transport layers.
The Setup
For this demonstration, I am using an Oracle 10.2.0.4 client on Windows XP, connecting to an Oracle 10.2.0.4 database on Oracle Enterprise Linux 5. Name resolution is through tnsames.ora, which looks like this:
fred =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vlnxora1)
)
)
Listing 1.
(By the way, I like to use “fred” as an alias name in demonstrations because it should be obvious that it is a totally artificial name that doesn’t have any inherent relationship to anything. As in the above tnsnames example, in a production system I would use the service name (vlnxora1) as the connect identifier, but in demonstrations I want the distinction to be crystal clear.)
The key information we are focusing on is the “HOST = vmlnx01” on line 4.
First, let’s make a good connection to prove that everything is working correctly, then we will break it.
C:\>sqlplus scott/tiger@fred SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 5 09:45:17 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
Listing 2.
The Demonstration
We know that in order for a message to be routed across a network, we need an ip address. This is like placing a telephone call. If I want to call Moe, I can’t just pick up my phone and dial “Moe”. The telephone company switchboards don’t know anything about Moe. There has to be some mechanism to translate “Moe” to 1-555-123-4567. Likewise the network routers don’t know anything about “vmlnx01”. For your telephone you would have some sort of directory to tell you (or your smart phone!) what Moe’s number is. In a corporate environment, you probably have a DNS server to tell the network stack that “vmlnx01” is 192.168.160.101. The other mechanism, and the one that trumps a DNS lookup, is a file on the client, named simply “hosts”. On unix, this will be at /etc/hosts. On my Windows XP machine it is at C:\WINDOWS\system32\drivers\etc\hosts. Given Microsoft’s propensity for reshuffling the deck with each new release of Windows, I can’t promise that is where the file should be located on your machine!
My hosts file looks like this:
127.0.0.1 localhost 192.168.160.101 vmlnx01 vmlnx01.vmdomain
Listing 3.
For those not familiar with this file, the format is
ipaddress alias1 alias2 .... aliasN
Listing 4.
All hosts files should have the same first line, equating ip address 127.0.0.1 to the alias “localhost”. All other entries typically have two aliases, one with the unqualified server name, the other with the fully qualified servername.domain. That is by convention and for everyone’s convenience, but the fact is these are just aliases for the ip address, and like any alias can actually be anything you want. I will demonstrate that after taking care of the business at hand.
At this point, we know that we told the network to route our request to ‘vmlnx01’, and by using the local hosts file, it was able to translate ‘vmlnx01’ to ‘192.168.160.101’. Let’s set it so that it can’t make that translation, and see what results. We will do that by removing the entry for vmlnx01 from the hosts file:
127.0.0.1 localhost 192.168.160.101 fubar.vmdomain fubar
Listing 5.
Then test:
C:\>sqlplus scott/tiger@fred SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 5 10:13:45 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-12545: Connect failed because target host or object does not exist
Listing 6.
And we have our error. Please notice that we did NOT touch our tnsnames.ora file, which we’ve already proven to be good. This error is simply oracle reporting what the OS network returned. Of course, since it resulted from a mis-match between the HOST parameter in tnsnames and the entries in the hosts file, the proper fix is could be in either file. It is often suggested to avoid this problem by hard-coding the IP address in the tnsnames (HOST=192.168.160.101), but I consider that to be a hack taken by those who do not understand how net name resolution works. It is certainly poor practice to hard-code an IP address any place an alias can be used. Just think of the problems caused by hard-coded IP addresses when the network administrator restructures the net.
Conclusion
This particular error is exactly analogous to “ORA-12154: TNS:could not resolve the connect identifier specified”. With ORA-12154, sqlnet couldn’t find a way to translate “fred” to a complete connect descriptor. With ORA-12545, the OS network layer couldn’t find a way to translate ‘vmlnx01’ to an ip address.
“Film at eleven . . .”
ORA-12545: Connect failed because target host or object does not exist
Cause: The address specified is not valid, or the program being connected to does not exist.
Action: Ensure the ADDRESS parameters have been entered correctly; the most likely incorrect parameter is the node name. Ensure that the executable for the server exists (perhaps «oracle» is missing.) If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again.
Reference: Oracle Documentation
C:\Users\nimish.garg>sqlplus scott/tiger@MY_DATABASE
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 25 16:38:30 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Enter user-name:
Understanding ORA-12545:
Oracle Client connects a Database using «connect identifier», which is defined in client’s tnsnames.ora file. SQLNET then translate «connect identifier» to «connect descriptor» which specifies how to connect to What Server (HOST) using which PROTOCOL and which SERVICE is listener is serving on WHAT port. After this process, this «connect descriptor» information is passed to Network Layer to connect to the Database Server. ORA-12454 occurs usually when TNS is unable to reach HOST defined in ADDRESS parameter of TNSNAMES.ORA.
This error may be due to a network glitch or may be due to some typo error in HOST value in ADDRESS parameter of TNSNAMES.ORA.
Resolving ORA-12545:
We can debug ORA-12545 by following steps:
1) Verify the TNSNAMES.ORA Entry and validate Host Name
MY_DATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_database.mydomain.co.in)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.mydomain.co.in)
)
)
2) Test Connectivity to the HOST by
— PING HOST
— TELNET HOST PORT
— TNSPING TNS_ALIAS
3) Use IP instead of HOST Name in ADDRESS (HOST) of TNSNAMES.ORA and retry connecting.
4) If you are able to reach HOST, validate Oracle and Listener exist and are running.
Related Posts:
— SQLNET: How does Oracle Client connect with Oracle Server
— ORA-12154: TNS:could not resolve the connect identifier specified
— ORA-12560: TNS:protocol adapter error
— ORA-12514: TNS:listener does not currently know of service requested in connect descriptor