ORA-12545: Target Host or Object Does Not Exist - Fix TNS

ORA-12545: Connect Failed Because Target Host or Object Does Not Exist

Error Overview

Error Text: ORA-12545: Connect failed because target host or object does not exist

The ORA-12545 error occurs when Oracle Net (TNS) cannot resolve or reach the hostname or IP address specified in the connection descriptor. The transport layer successfully parses the tnsnames.ora entry but fails when attempting to open a network connection to the target host — because the hostname cannot be resolved by DNS, is not present in the local hosts file, the IP address is unreachable, or the HOST value itself contains a typographical error. This error is a network-layer failure that happens before the Oracle listener is even contacted.

Common Causes

1. Hostname Not Resolvable

• Hostname in tnsnames.ora or Easy Connect string does not exist in DNS
• DNS server is unreachable or returning NXDOMAIN for the hostname
• Hostname was recently changed and DNS cache has not been flushed

2. Typographical Error in tnsnames.ora

• HOST entry has a spelling mistake (e.g., dbserver vs. dbsserver)
• Extra space, tab, or invisible character in the HOST value
• Incorrect domain suffix (e.g., .internal vs. .corp)

3. Missing Hosts File Entry

• Client machine does not have a /etc/hosts (Linux) or C:\Windows\System32\drivers\etc\hosts (Windows) entry for the database server
• Environment uses hosts file resolution but the entry was not added for a new server

4. IP Address Changed or Server Moved

• Database server was migrated to a new IP without updating DNS or hosts files
• Cloud instance IP changed after restart (non-static IP assignment)
• Load balancer VIP address changed without updating connection descriptors

5. Network or Firewall Blocking

• The client cannot route to the server’s IP address at all
• Firewall rule blocking outbound connections on port 1521
• VPN not connected in a remote work scenario

Diagnostic Queries

Test Name Resolution from the Database Server

-- From inside the database, test UTL_TCP to validate connectivity
-- (Run after resolving ORA-12545 to confirm fix)
DECLARE
  v_conn UTL_TCP.CONNECTION;
BEGIN
  v_conn := UTL_TCP.OPEN_CONNECTION(
    remote_host => 'target_db_host',
    remote_port => 1521,
    tx_timeout  => 10
  );
  DBMS_OUTPUT.PUT_LINE('Connection successful');
  UTL_TCP.CLOSE_CONNECTION(v_conn);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Connection failed: ' || SQLERRM);
END;
/

Check TNS Configuration

-- View current TNS_ADMIN setting from within the database
SELECT name, value
FROM v$parameter
WHERE name IN ('local_listener', 'remote_listener', 'db_domain');

-- Check listener configuration
-- (Run from OS command line)
-- lsnrctl status
-- lsnrctl services

-- View registered services
SELECT name, network_name, creation_date
FROM v$services
ORDER BY name;

Check Active Database Links (if ORA-12545 from a DBLINK)

-- Find database links and their connection strings
SELECT
  owner,
  db_link,
  username,
  host,
  created
FROM dba_db_links
ORDER BY owner, db_link;

-- Test a specific database link
SELECT * FROM dual@my_db_link;

-- Find open database link sessions
SELECT
  s.sid,
  s.serial#,
  s.username,
  l.db_link,
  l.logged_on,
  l.open_cursors
FROM v$session s
JOIN v$dblink l ON s.saddr = l.session
ORDER BY s.sid;

Step-by-Step Resolution

1. Verify and Fix the Hostname

From the client machine, run these OS-level checks:

# Linux/Mac — test DNS resolution
nslookup dbserver.corp.internal
dig dbserver.corp.internal

# Windows — test DNS resolution
nslookup dbserver.corp.internal
Resolve-DnsName dbserver.corp.internal -Type A

# Ping the host to test basic connectivity
ping dbserver.corp.internal

# Test the specific port
telnet dbserver.corp.internal 1521
# Or on Linux:
nc -zv dbserver.corp.internal 1521

If DNS resolution fails, proceed to the hosts file fix below.

2. Fix tnsnames.ora HOST Entry

Locate and edit the tnsnames.ora file:

%ORACLE_HOME%\network\admin\tnsnames.ora

# Typical locations:
# Linux:   $ORACLE_HOME/network/admin/tnsnames.ora
# Client:  $TNS_ADMIN/tnsnames.ora

# BAD: hostname typo
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbsserver.corp.internal)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = MYDB)))

# GOOD: corrected hostname
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver.corp.internal)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = MYDB)))

After editing, test with tnsping:

tnsping MYDB

3. Add or Correct Hosts File Entry

When DNS is unavailable or for local overrides:

# Linux/Mac: edit /etc/hosts (requires sudo/root)
sudo vi /etc/hosts
# Add:
192.168.1.100   dbserver dbserver.corp.internal

# Windows: edit C:\Windows\System32\drivers\etc\hosts (as Administrator)
# Add:
192.168.1.100   dbserver dbserver.corp.internal

# Flush DNS cache after editing hosts file
# Linux:
sudo systemctl restart nscd
# Mac:
sudo dscacheutil -flushcache; sudo killall -HUP mDNSResponder
# Windows:
ipconfig /flushdns

4. Fix Easy Connect String

If using Easy Connect (not tnsnames.ora):

# BAD: wrong hostname in Easy Connect
sqlplus user/[email protected]:1521/MYDB

# GOOD: corrected hostname
sqlplus user/[email protected]:1521/MYDB

# Test with explicit IP if hostname resolution is the problem
sqlplus user/[email protected]:1521/MYDB

5. Update a Database Link with Wrong HOST

-- Drop and recreate the database link with the correct host
-- (Capture existing definition first)
SELECT
  'CREATE DATABASE LINK ' || db_link ||
  ' CONNECT TO ' || username ||
  ' IDENTIFIED BY <password>' ||
  ' USING ''' || host || ''';' AS recreate_ddl
FROM user_db_links
WHERE db_link = 'MY_DB_LINK';

-- Drop the broken link
DROP DATABASE LINK my_db_link;

-- Recreate with correct tnsnames alias or connection descriptor
CREATE DATABASE LINK my_db_link
  CONNECT TO remote_user IDENTIFIED BY remote_pass
  USING 'MYDB';  -- This alias must resolve in tnsnames.ora

-- Or with full descriptor:
CREATE DATABASE LINK my_db_link
  CONNECT TO remote_user IDENTIFIED BY remote_pass
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver.corp.internal)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYDB)))';

-- Test
SELECT * FROM dual@my_db_link;

6. Use IP Address as a Temporary Workaround

If name resolution is broken but the IP is known and reachable:

# Temporary tnsnames.ora using IP instead of hostname
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = MYDB)))

This is a temporary measure. Fix DNS resolution as the permanent solution.

Prevention Strategies

1. Use IP Addresses in tnsnames.ora for Critical Connections

# For production connections where DNS reliability is critical,
# consider using IP addresses directly (trade-off: harder to maintain)
PROD_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.100)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.101)(PORT = 1521)))
    (FAILOVER = ON)
    (CONNECT_DATA = (SERVICE_NAME = PRODDB)))

2. Monitor tnsnames.ora With Version Control

# Track tnsnames.ora in git to detect unauthorized changes
git -C /etc/oracle diff HEAD tnsnames.ora

# Validate tnsnames.ora syntax
tnsping -v MYDB

3. Automate Connectivity Testing

-- Scheduled job to test database links proactively
CREATE OR REPLACE PROCEDURE test_db_links AS
  v_dummy VARCHAR2(1);
  v_error VARCHAR2(4000);
BEGIN
  FOR lnk IN (SELECT db_link FROM user_db_links) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT dummy FROM dual@' || lnk.db_link
        INTO v_dummy;
      DBMS_OUTPUT.PUT_LINE('OK: ' || lnk.db_link);
    EXCEPTION
      WHEN OTHERS THEN
        v_error := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('FAIL: ' || lnk.db_link || ' - ' || v_error);
        -- Send alert here
    END;
  END LOOP;
END;
/

4. Document All Connection Endpoints

• Maintain a CMDB or wiki entry mapping TNS alias names to server IPs and hostnames
• Keep tnsnames.ora in version control and deploy changes through a change management process
• Test all database links after network changes, IP reassignments, or server migrations

Related Errors

• ORA-12541 - TNS no listener
• ORA-12154 - TNS could not resolve the connect identifier
• ORA-12170 - TNS connect timeout
• ORA-12560 - TNS protocol adapter error