Printable View
Hi there,
I'm having a strange problem with my Listener file,
I have removed a database (files, instance etc.) from a server, I have amended the listener file and stopped and restarted the OracleTNSListener service.
However when I run the command TNSPING (database name) from my workstation I'm still getting a positive response.
Have I missed something???
NT Sever
Oracle 7.3.3 database.
Many thanks
Alison
Have you updated your tnsnames.ora files too?
Sam
Hi Sam,
Yes the tnsname.ora is also updated.
Cheers
Alison
Do a search for all the tnsnames.ora files, and make sure that they are all up to date. Do you have multiple listeners, if so, make sure that you have modified things there.
If you are sure that you have done the above, try again stoping and starting the listener
lsnrctl stop
lsnrctl start
Even then if it doesn't fix, since it being windows, it might be some issue on releasing the resources and acquiring it againg, so the solution would be to bounce your server, if you can afford.
Sam
Bit of a strange one.
I did all the things you suggested, checked for multiple TNSNAMES and LISTENER files (there are none), and stopped and started the process again using
lsnrctl stop
lsnrctl start
I then renamed the listener.ora file to listener.bak and low and behold I can still tnsping the database with a positive response.
If you have no more suggestions then I guess I will have to bounce the box
Cheers
Alison
I have just sat down and thought about what I was writing.
The listener.ora file is only read while the listener process is starting, therefor renaming the file after the process has started wont mean anything.
Still stuck with why I'm still getting responses from the database though.
Regards
Alison
The tnsping always checks up the listener for making sure that it could establish the connections. On this case, it some or other finds the connection information, i.e it was looking at your old configurations. This is what I could think on on this regard and know that Windows has all kinds of mistry things in it. As a last resolt, check your sqlnet.ora too.
BTW, check making a connection to the database through the sqlplus and see whether it would go through, it should infact complain you now of not finding the tnsname entry. To see that the listener does not have an entry, check
listener services
If it lists your deleted service as one of its entry, then you can think about bouncing your server. If it doesn't then you can ignore every thing.
Sam
What happen lately?
Sam
TNSPING doesn't have anything to do with the actual database connecting. It doesn't tell you if the database is up or down or even present. It doesn't try to connect to the database, after all how would it connect as you can't provide any username/PWD when calling it!
What TNSPING does is it only tries to reach (ping) the listener. So it only checks if the listener on the specified machine is up and if it listens to the specified port.
tnsping only tests if it can PING the server specified in the HOST of your TNSNAMES.ORA and if the PORT specified is listening.
TNSPING is a utility to test that the connection is okay and the port is open; it does not gurantee that the database is up and running.
- Rajeev
That is correct, but the question over here is when you get to remove the listener entry from the listener and bounce the listener, then your ping should just break, isn't it suppose to be the case?
Sam
Hi,
Check your sqlnet.ora file and see if you have TNSNAMES
in NAMES.DIRECTORY_PATH parameter value. If you have it,
and you are using hostname in tnsping, you will get response
even if you don't have Oracle instance running on that host.
e.g. if hostname is TEST1 and you run
tnsping TEST1 from other host
you will get ping response from that host.
HTH
np70
Sam,
if the Listener is running on the server for *some other* services and listening on the PORT defined in the TNSNAMES then the TNSPING will succeed in connection. The only thing it tests is the Server Name (or IP) and the PORT number.
So if your Listener was originally listening for 5 databases and you removed one of the databases and bounced the listener, the PING and TNSPING will still work.
TNSPING is just a test to make sure that the listener is running and listening for *some database* on a particular port and not that it is listening for a *particular* database
- Rajeev
Here is where I got lost:
Extract from Oracle Metalink article : Note:1018169.6
Now what I still don't get is how could tnsping ping a service when it had been removed from the tnsname.ora file. Shouldn't it fail saying
TNS-03505: Failed to resolve name
when you had updated the tnsname.ora file. It could have been the case, the owner of this thread had been trying to give the service name which as mentioned by np70 was geting resolved through sqlnet.ora's Names.prefered_server.
But still interested to know from the poster, whether he/she had been able to solve the issue, if so what way?
Sam
Sorry about the confusion Sam....I completely missed the fact that the user had updated the tnsnames.ora before trying the connection.
If this is true then I find it hard to believe how the connection was made; Without tnsnames.ora where does the PORT number information comes from ???
So back to the original poster of the message...
Are you sure there is only one tnsnames.ora on your local system (from where you are launching tnsping) not on the server.
One way to test will be rename one of the service names in your tnsnames.ora and try connecting with the NEW name; this will make sure that this is the tnsnames.ora in use.
- Rajeev
Hi there,
Sorry for being absent from such an interesting thread.
I think I may have misunderstood what was going on with the TNSNAMES and LISTENER files.
I did remove all entries for the deleted database from the TNSNAMES.ORA & LISTENER.ORA on THAT server.
I didn't remove the entry from the TNSNAMES.ORA file on my workstation (where the TNSPING was originating from).
I have done so now, and am getting "Failed to resolve service name".
Can I just clarify with you, does the LISTENER SERVICE just check for ANY database on that PORT regardless of whether it's still mentioned in the LISTENER.ORA file.
Again thanks you for all your responses, it has been very interesting and informative.
Regards
Alison
No, in fact when you execute TNSPING for some service name, what happens is the following:Quote:
Originally posted by alison
Can I just clarify with you, does the LISTENER SERVICE just check for ANY database on that PORT regardless of whether it's still mentioned in the LISTENER.ORA file.
1. Your local tnsnames.ora is consulted to get the *address* of that service. By address I mean only the IP address (or hostname) and a port on which the listener listens.
2. Then a package is send to that address+port
3. The package is recieved by the listener and a responce is immediately returned to the sender. The listener does not even try to check anything about *any* database. In fact listener need not to have *any* database specified in the listener.ora (SID_DESC entry of the listener.ora), yet it will still return the positive responce to TNSPING.
In short, TNSPING only verifies you are able to reach the listener based on your local TNSNAMES.ORA configuration, nothing more.
Thanks very much,
That has clarified the situation.
Many thanks to everyone who has contibuted to this thread.
Regards
Alison
Voilla !!!
Now it all makes sense. Alison, thank you for replying to your posting.
Sam