[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

ORA-12545

Hi all

I am having a problem with my test database. I am trying to restore the production databases cold backup over the test database. However, I am getting ORA-12545: Connect failed because target host or object does not exist, when I try to connect. I have created a password file on this environment too. I am using Oracle9 and AIX 5. I am ble to ping to the host name and ip address from other machines. The hostname is also present in the host file. I do not understand what could be wrong. My tnsnames is below

MCSPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aix-scratch)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MCSPROD.xxxxxxx.com)
)
)

Thanks
0
novicedbaUK
Asked:
novicedbaUK
2 Solutions
 
awking00Commented:
From Oracle documentation
ORA-12545: TNS:name lookup failure

Cause: The listener on the remote node cannot be contacted.

Action: Perform these steps:

Verify that the ADDRESS in the tnsnames.ora file and the listener.ora file is correct.
Verify that the listener on the remote node has been started. Enter:
lsnrctl
LSNRCTL> STATUS [listener_name]


listener_name is the name of the listener defined in the listener.ora file. It is not necessary to identify the listener if you are using the default listener, named LISTENER.

If the output indicates the listener is not running, try starting it with the command:

LSNRCTL> START [listener_name]
0
 
mohammadzahidCommented:
Are you using local or host authentication?

If you are using hostname to resolve connect descriptor then you need to modify listener.ora with hostname that is hosting database.

I can provide more information if you post sqlnet.ora and listener.ora file here.

0
 
novicedbaUKAuthor Commented:
Hello

My tnsnames is below

MCSPRD01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = aix-scratch)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MCSPRD01.midlandscoop.com)
    )
  )


My listener is below

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = aix-scratch)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /orascratch/oracle/oui)
      (PROGRAM = extproc)
    )
  )


My sqlnet.ora is below
# SQLNET.ORA Network Configuration File: /orascratch/oracle/oui/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

I really hope that someone can help

Thanks



0
 
hamitaCommented:
The TNS-12545 error normally occurs when the TCP/IP protocol cannot resolve a
given hostname either when initiating a connection, or when resolving a hostname
from a redirect packet. (NOTE: SQL*Net does not resolve hostnames).
TNS-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.)

Diagnosing the Error

1. Create a level 16 trace file and reproduce the problem

a. edit the sqlnet.ora file
b. add the parameter TRACE_LEVEL_CLIENT=16
c. reconnect in order to receive the TNS-12545 error again
d. exit the connecting application (i.e. SQL*Plus)

2. Open the client trace (probably cli.trc) in a text editor

a. search for the line:
   "nttbnd2addr:  *** hostname lookup failure! ***"
b. The line above this line reveals the hostname trying to be resolved
   by TCP/IP (NOTE: SQL*Net does not resolve hostnames).

3. Solutions:
a. Verify this hostname is spelled correctly
b. Verify this hostname actually exists by executing the OS command
   "nslookup [hostname]" where [hostname] is the value of the HOST parameter
   defined for the Net service name being used for the connection.
c. Add the hostname, returned by the trace, to the local hosts file and assign it
   the correct IP address (NOTE: this hostname may be a secondary network card
   on the server or in some cases you need to add the 'Hostname.DomainName'
   of the server in the etc/Hosts file at the client side).
d. Using the IP address of the server for the HOST value in the tnsnames.ora
   file may also be a solution.  This avoids Host to IP resolution.
[Optional] Further Diagnosis
4. Search for a redirect packet

a. From within the client trace file at the line "nttbnd2addr:  *** hostname
   lookup failure! ***", search upward for the word "NSPTRD"
b. If "NSPTRD" is found, then the failure is due to a Multi-threaded Server
   dispatcher returning the hostname of an alternate network card on the server.
   This is not a bug as dispatchers listen on what is defined as the primary
   network card bound to the operating system.

5. Solutions:

a. Force MTS dispatchers to listen on the correct network card on the server -
   thereby returning a valid redirect address.  Redefine the MTS syntax in the
   init.ora file (NOTE: this example is for Oracle9i):
dispatchers="(address=(protocol=tcp)(host=[correct_host/ip]))(dispatchers=3)"

b. Add a hosts file entry for the hostname being returned from the redirect packet.

TNS-12545 when starting the listener
The hostname value for the HOST parameter in the listener.ora file could not be resolved
by TCP/IP (NOTE: SQL*Net does not resolve hostnames).  Verify it's accuracy, or replace the
hostname value with the IP address, and retry the operation.

Resolving a TNS-12545 when TCP/IP is not being used

TNS-12545 errors can occur when making a local Bequeath connection on the Oracle server.
A TNS-12545 can occur if the ORACLE_HOME is not set correctly, if the ORACLE_SID is not set
correctly, or if the application (such as SQL*Plus) is not linked with correctly.  See the
appropriate product documentation on linking Oracle correctly (usually /usr/ccs/bin/make
is the correct linker to use).
 

Other Situations that Produce a TNS-12545

Verify the oracle_home/bin 'oracle' executable exists.  If it is not there, relink or reinstall Oracle.
On Unix, ensure that the oracle user has access rights to etc/hosts.
On Unix, the permissions on resolv.conf may be incorrect. Login as root and change the permissions:
        % chmod 755 /etc/resolv.conf
With the 3rd party tool CATII, add a \ in front of the @ symbol in the connect string:
        For example:  user/password\@alias
On NT, verify LOCAL does not exist in registry.
System Key: [HKEY_LOCAL_MACHINE\Software\Oracle\Home(x)]
Value Name: LOCAL
This note also pertains to
WinNT error 1067
WinNT error 2140
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now