?
Solved

TNSPING working SQLPLUS not working

Posted on 2003-12-01
27
Medium Priority
?
7,374 Views
Last Modified: 2012-06-27
Hi there,

I'm baffled with a problem in connecting a client to a server(remote connection).

The entries in tnsnames.ora on the server side are working fine(when connected from the server).I use the same configuration on my client side,but it doesn't work.

TNSPING for the connect strings are OK.

I get a couple of errors from SQLPLUS,like:

1)ORA-12545 : Connect failed because of target host or object does not exist
2)Oracle not available
3)Shared realm does not exist.

Thanks for your inputs...!!!
0
Comment
Question by:catchmeifuwant
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 7
  • +2
27 Comments
 
LVL 13

Accepted Solution

by:
anand_2000v earned 75 total points
ID: 9849086
1)is PING to the server working?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849088
can you see any difference between the sqlnet.ora file in the client and server?
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849098
1)Ping is also working.

2)There was no sqlnet.ora on the client side.So i added a copy of it on the client side.Still no use...

NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.EXPIRE_TIME=15
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849106
ok
1)is the database up?
2) are the tnsnames.ora values specified with the domain?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849113
are you trying with computer name or ipaddress?
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849122
1)DB is up and running.I can connect to the remote server(I login to the server and from terminal there ...possible).

2)No TNSNames are not specified with the domain

3)IP address
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849147
are the server and the remote  sun machines? if yes I can give you an exact solution otherwise you will have to find similiar options with your OS

>>I can connect to the remote server(RSERVER)(I login to the server(YOUR_SERVER) and from terminal there ...possible).


in YOUR_SERVER give the command

redir --lport=9021 --rport=1521 --ipaddress=<RSERVER ADDRESS>&

in the client machine change the TNSNAMES configuration with
host=YOUR_SERVER
port=9021

regards

0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849154
or on windows I believe there is a command called as "route"
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849165
Client is Win 2000 and Server is HP-UX 11

If I check v$dispatcher,then the port that appears is 49674...does this have anything to do with it?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849171
sorry it was
redir --lport=9021 --cport=1521 --caddr=<RSERVER ADDRESS>&
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849172
no.... that's the dispatcher....
remote machine is in which OS?
0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 75 total points
ID: 9849180
Can you check the c:\winnt\system32\drivers\etc\hosts file and add the entry with host name, fully qualified domain name , IP address for the server. Try with host name in tnsnames.ora with fully qualified domain name
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9849185
let me see if I am understanding your problem correctly

You have a server, let's say SERV1, from which when you try to connect to a remote DB present in server SERV2 the connection works perfectly.

However when you try the same from a client machine CLNT1, which is connected to SERV1 it does not work.

therefore you have to us the command
redir --lport=9021 --cport=1521 --caddr=SERV2&

and change the tnsnames in CLNT1 to
host=SERV1
port=9021

instead of what is present now.
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849193
Oh...sorry if my responses were misleading..the scenario is:

I have a DB installed on Serv1(HP-UX).I connect to the DB from Serv1 (using tnsnames configured in Serv1),the connection is successful.

I now have a client Clin1(Win 2000).This client is in a remote location.I have the replica of tnsnames as on Serv1 and sqlnet.ora as well.

When I try connecting from Clin1 to DB on Serv1,thats where I face the problem.

0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 75 total points
ID: 9849194
tnsping only checks if the Listener on that server is alive.

The messages
1)ORA-12545 : Connect failed because of target host or object does not exist
2)Oracle not available
3)Shared realm does not exist.

show that:
1. You gave a wrong host name and/or service name
2. You gave correct host name/service name but the Oracle Instance doesn' started.

Make sure that the Oracle Instance on the server is started (look at the processes)
(on the server as sys: select status from v$instance;)
Look at the host name and service name and correct them:
as sys: select instance_name, host_name from v$instance;
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849208
schwertner,

1)The host name and service name is right,as I'm using the same configuration from the server.
2)Instance is started.

a)V$instance -> status is open
b)Host_Name is the name of the host (not it's IP)
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9849219
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

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).

solution:

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).
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849236
when I add an entry in hosts file(on client side),sqlplus hangs and aborts with a timeout error.

BTW,do you want sqlnet.ora to be modified on the server side(not possible due to restrictions), or the client side?

0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9849260
It should be on the client side.

Can you try with this command from command from:

c:\nslookup <your_host_name>

to see if your ip and host is correct
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9849283
The trace file is in: $ORACLE_HOME\bin
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849295
baonguyen1,

OK,here are the results.

1)First time the trace showed,"hostname lookup failure"

2)Added an entry in etc/hosts.This time the error was 12535 TNS Operation timed out.It could resolve the host properly.

REgarding,nslookup...errors again:

*** Can't find server name for address <IP>: Non-existent domain
*** Default servers are not available
Server:  UnKnown
Address:  <IP>
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9849343
OK, so your client can not lookup the server as it is an HP-UX.

Try this:

c:\ping <your_server_name>
Pinging <your_server_name>.<your_domain> (IP) with 32 bytes of data
Reply from ....

Use the host name and domain here. Try to tnsping fisrt when you have changed the hostname in the tnsnames.ora
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9849369
well unfortunately,when ping is used,

ping n4004_4

I get the message

Pinging n4004_n [IP] with 32 bytes of data

...no domain name
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9849415
I think there is problem with DNS resolution. My solution is:

1. Try with to add the hostname without domain name to the etc\hosts file to see if it works
2. Use nslookup with your client to see what is your domain or if you already know, use it:

c:\nslookup <your_client_name>

3. Ping your cleint itself to find the domain
0
 

Assisted Solution

by:jbeckstrom
jbeckstrom earned 75 total points
ID: 9851112
Instead of using a host name, try using an ip address
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9855160
yes, you can use IP but sometime it does not work.

I forgot, you can use

c:\ipconfig /all

to check your IP configuration. The domain of your organization should be here
0
 
LVL 12

Author Comment

by:catchmeifuwant
ID: 9864695
Hi all,

Thanks for all your support.It was some network configuration problem,that's been fixed.

I'm splitting up the points !!!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question