McTnsping: a tnsping-like program for Windows [message #648221] |
Thu, 18 February 2016 12:02  |
 |
Michel Cadot
Messages: 68455 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I have posted a tnsping-like program in our tnsping wiki page.
This is a Windows stand-alone program which requires no Oracle client.
Note that this program currently accepts only tnsnames.ora or easy connect strings; ldap is currently not supported.
The help is the following one:
C:\>McTnsping -h
McTnsping Utility by Michel Cadot: Version 2021.12.03 on 03-DÉC.-2021 18:08:02
Copyright (c) Michel Cadot, 2016-2021. All rights reserved.
Usage 1: McTnsping.exe { <tns entry> | <host>:<port> } [<count>]
with
<tns entry> Gives the net service name in the tnsnames.ora file.
<host>:<port> Give the server name or IP and port (which is mandatory).
<count> Sets the number of times the program checks the target, default is 1.
Usage 2: McTnsping.exe
{ -h |
{ -s <host>[:<port>] | -t <tns entry> }
[-v { 8 | 9 | 10 | 11 | 12 | 12.2 | 18 | 19 | 21 }]
[-dir <tnsnames.ora directory>]
[-tim <timeout>] [-c <count>]
[-rc { 0 | 1 | OR | AND }]
}
with
-c <count> Sets the number of times the program checks the target,
default is 1.
-dir <directory> Gives the directory for sqlnet.ora and tnsnames.ora files;
this option is ignored if "-s" option is given.
-h Displays the version and the usage help.
-s <host>[:<port>] Gives the server name or IP and port to check the listener,
default port is 1521.
-rc { 0 | 1 | OR | AND }
Gives how McTnsping return code would be in case of multiple
addresses in the tnsnames entry, default value is AND=1.
-t <tns entry> Gives the net service name in the tnsnames.ora file.
-tim <timeout> Gives the timeout (in seconds) on connection to listener,
default is 5 seconds.
-v { 8 | 9 | 10 | 11 | 12 | 12.2 | 18 | 19 | 21 }
Selects the version of the simulated tnsping, default is 11.
"-s" and "-t" options are exclusive.
If "-t" is choosen, the program searches for sqlnet.ora and tnsnames.ora files:
* if "-dir" option is given, in this directory.
* otherwise, first in TNS_ADMIN directory if set, then in ORACLE_HOME/network/admin.
Note: a program restriction is that a tnsnames.ora entry must be at the beginning of
a line (maybe preceded by spaces).
When there is only one address in the tnsnames.ora entry, McTnsping return code is 0
if the ping was successful and 1 otherwise.
If there are multiple addresses in the tnsnames.ora entry, McTnsping checks all of
them, "-rc" option tells what can be the return code in cases of some addresses work
and others not. "AND" and "1" mean the return should be 1 in this case, "OR" and "0"
the return code should be 0.
So return code is 1, if at least one address is unsuccessful with option "AND" or "1";
and return code is 0, if at least one address is successful with option "OR" or "0".
The program is provided as it is without any guarantees or warranty. Although the
author has attempted to find and correct any bugs in this free program, the author
is not responsible for any damage or losses of any kind caused by the use or misuse
of the program. The author is under no obligation to provide support, service,
corrections, or upgrades to this program.
You can freely use, copy and distribute this program but you can't modify it without
the permission of the author you can contact on http://www.orafaq.com
You can post your comments, ask for improvements, report bugs... on the program at
http://www.orafaq.com/forum/t/200058/
I encourage you to subscribe to this topic (link at top of the page) if you want to
be informed when a new version is released.
Here's an example of its usage compared to the same tnsping output (I have cut the end of entry description lines):
C:\>tnsping mikb 3
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 18-FÉVR.-2016 19:57:44
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
E:\oracle\ora102\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ...
OK (30 msec)
OK (30 msec)
OK (50 msec)
C:\>McTnsping mikb 3
McTnsping Utility by Michel Cadot: Version 2016.02.21 on 21-FÉVR.-2016 21:23:04
Copyright (c) Michel Cadot, 2016. All rights reserved.
Used parameter files:
E:\oracle\ora102\network\admin\sqlnet.ora
E:\oracle\ora102\network\admin\tnsnames.ora
Found tnsnames.ora entry:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=...
Attempting to contact myserver:1530
OK (41 msec) -- listener version 10.2.0.4.0
OK (21 msec) -- listener version 10.2.0.4.0
OK (21 msec) -- listener version 10.2.0.4.0
Note: a small difference with Oracle tnsping is that if the tnsnames.ora entry contains several ADDRESS parts, McTnsping reports the result of all addresses and not only the first one which works.
So if you want to test all your listeners in a single command you can reference all of them in a single tnsnames.ora entry like the following one (it does not even need the tnsnames.ora entry to be complete, it only have to contain the IP/hostname and the port number):
ALL_LISTENERS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1528))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1529))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1530))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1532))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1533))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1538))
)
)
)
Then:
C:\>McTnsping -dir c: -t all_listeners
McTnsping Utility by Michel Cadot: Version 2018.11.27 on 27-NOV.-2018 18:15:04
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.
Used parameter files:
c:\tnsnames.ora
Found tnsnames.ora entry:
(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.7)(PORT=1528))(A
DDRESS=(PROTOCOL=TCP)(HOST=192.168.1.7)(PORT=1529))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.7)(PORT=15
30))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.7)(PORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.9)(P
ORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.3)(PORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.
1.3)(PORT=1532))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.3)(PORT=1533))(ADDRESS=(PROTOCOL=TCP)(HOST=19
2.168.1.3)(PORT=1538)))))
Attempting to contact 192.168.1.7:1528
OK (23 msec) -- listener version 8.1.7.4.0
Attempting to contact 192.168.1.7:1529
OK (17 msec) -- listener version 9.2.0.8.0
Attempting to contact 192.168.1.7:1530
OK (12 msec)
Attempting to contact 192.168.1.7:1531
OK (12 msec)
Attempting to contact 192.168.1.9:1531
TNS-12541: TNS:no listener
Attempting to contact 192.168.1.3:1531
OK (531 msec)
Attempting to contact 192.168.1.3:1532
OK (8 msec)
Attempting to contact 192.168.1.3:1533
OK (19 msec)
Attempting to contact 192.168.1.3:1538
OK (7 msec)
Latest version: 2021.12.10, download in tnsping wiki page
MD5: 5c86b59d4236eb8086095a09747e1eb5
SHA-1: 5a8cfc4425704408bd73e937528a17333881cb0c
[Updated on: Fri, 03 December 2021 11:20] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: McTnsping: a tnsping-like program for Windows [message #671226 is a reply to message #671219] |
Fri, 17 August 2018 08:18   |
 |
Michel Cadot
Messages: 68455 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Here's what I get copying and pasting what you posted in a file:
C:\>type c:\tnsnames.ora
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
ORCLWGA1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Hyper-W7-Ora12c.mysystems.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCLWGA1.world)
)
)
C:\>McTnsping -t orclWGA1 -dir c:
McTnsping Utility by Michel Cadot: Version 2018.01.15 on 17-AOÛT-2018 15:01:30
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.
Used parameter files:
c:\tnsnames.ora
Found tnsnames.ora entry:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Hyper-W7-Ora12c.mysystems.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCLWGA1.world)))
Attempting to contact Hyper-W7-Ora12c.mysystems.com:1521
TNS-12545: Connect failed because target host or object does not exist
Quote: N.B.: the blanks preceding the 2nd+ line of the real entry above were collapsed when this posted.
You can avoid this using tags as explain in How to use [code] tags and make your code easier to read.
[Updated on: Fri, 17 August 2018 08:33] Report message to a moderator
|
|
|
Re: McTnsping: a tnsping-like program for Windows [message #671228 is a reply to message #671226] |
Fri, 17 August 2018 08:30   |
 |
Michel Cadot
Messages: 68455 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I get what you got inserting the commented CONNECT_DATA inside the ORCLWGA1 entry:
C:\>type c:\tnsnames.ora
ORCLWGA1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Hyper-W7-Ora12c.mysystems.com)(PORT = 1521))
)
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
(CONNECT_DATA =
(SERVICE_NAME = ORCLWGA1.world)
)
)
C:\>McTnsping -t orclWGA1 -dir c:
McTnsping Utility by Michel Cadot: Version 2018.01.15 on 17-AOÛT-2018 15:22:08
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.
Used parameter files:
c:\tnsnames.ora
Found tnsnames.ora entry:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Hyper-W7-Ora12c.mysystems.com)(PORT=1521)))#(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orafdb01.mysystems.com)))
Attempting to contact Hyper-W7-Ora12c.mysystems.com:1521
TNS-12545: Connect failed because target host or object does not exist
So sure, there's a bug with these bl... comments, I will fix it as soon as possible (most likely this week-end), thanks for finding it. 
[Updated on: Fri, 17 August 2018 08:31] Report message to a moderator
|
|
|
|
|
|
Re: McTnsping: a tnsping-like program for Windows [message #671235 is a reply to message #671231] |
Fri, 17 August 2018 10:23   |
 |
Michel Cadot
Messages: 68455 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The new version fixing the bug is available (see link in the first post of this topic).
I tested the program with the following humanly unreadable entry (putting your comments everywhere iside your entry ):
C:\>type c:\tnsnames.ora
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
ORCLWGA1 =
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
(DESCRIPTION = #TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
(ADDRESS_LIST =#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
(ADDRESS = (PROTOCOL = TCP)(HOST = Hyper-W7-Ora12c.mysystems.com)(PORT = 1521))
)
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
(CONNECT_DATA =
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
(SERVICE_NAME = ORCLWGA1.world) #TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
)
#TCORACLE.WORLD = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = orafdb01.mysystems.com)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orafdb01.mysystems.com)))
)
# Added blah1, blah2
TEST01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blah1)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = TEST01)))
TEST02 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blah2)(PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = TEST02)))
C:\>McTnsping -t orclWGA1 -dir c:
McTnsping Utility by Michel Cadot: Version 2018.08.17 on 17-AOÛT-2018 17:18:12
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.
Used parameter files:
c:\tnsnames.ora
Found tnsnames.ora entry:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Hyper-W7-Ora12c.mysystems.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCLWGA1.world)))
Attempting to contact Hyper-W7-Ora12c.mysystems.com:1521
TNS-12545: Connect failed because target host or object does not exist
[Updated on: Fri, 17 August 2018 10:24] Report message to a moderator
|
|
|
|
Re: McTnsping: a tnsping-like program for Windows [message #674486 is a reply to message #673613] |
Tue, 29 January 2019 08:44   |
 |
sloc
Messages: 8 Registered: January 2019
|
Junior Member |
|
|
Hi there, i just downloaded this version (Version 2018.11.27). Unfortunately it does not seem to parse the tnsnames.ora.
I have tried setting the tns_admin in advance and also using the -dir Parameter.
In both cases it shows that it would use the correct tnsnames.ora file, but no matter which alias I try it never finds it.
The errors is this:
McTnsping Utility by Michel Cadot: Version 2018.11.27 on 29-JAN-2019 15:38:13
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.
Label not found for "last 2" at script/McTnsping.pl line 576, <FILE> line 3.
I have tried unsetting the path, changing the entry in case it is case sensitive, but no matter what I do I always get the same response.
Our tnsnames.ora has a lot of entries (in the thousands, and these are normally look like this:
test.eng.domain.ch = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(Host = testserver.eng.domain.ch)(Port = 12004 ))
There are also a lots of comments in the file and a header from svn/git, as the file is versioned.
Thank you for your time, let me know if you need more input.
|
|
|
|
Re: McTnsping: a tnsping-like program for Windows [message #674489 is a reply to message #674488] |
Tue, 29 January 2019 11:23   |
 |
Michel Cadot
Messages: 68455 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Program is fixed, new version is available to download.
There was a bug when a right parenthesis is missing in the tnsnames.ora entry (like in your exemple).
Note that, as I used a very basic syntax analyzer, in case of a syntax error in an entry, this one is stated as "not found" and the return code is always:TNS-03505: Failed to resolve name whereas Oracle tnsping will give a clearer error (but not necessary accurate), in this case:TNS-12533: TNS:illegal ADDRESS parameters
[Updated on: Tue, 29 January 2019 11:26] Report message to a moderator
|
|
|
Re: McTnsping: a tnsping-like program for Windows [message #674527 is a reply to message #674489] |
Thu, 31 January 2019 05:47   |
 |
sloc
Messages: 8 Registered: January 2019
|
Junior Member |
|
|
Wow, that was fast 
Sorry to bother you again. I downloaded and tested the new version, unfortunately it is still not working.
c:\Workarea\Oracle_Client_Tests>McTnsping.exe -t test81.eng.domain.ch -dir "c:\Workarea\Oracle_Client_Tests" -db 3
McTnsping Utility by Michel Cadot: Version 2019.01.29 on 31-JAN-2019 12:29:25
Copyright (c) Michel Cadot, 2016-2019. All rights reserved.
DEBUG main: p_tns='test81.eng.domain.ch', p_dir='c:\Workarea\Oracle_Client_Tests', oh='', tnsadmin=''
DEBUG main: sqlnet='c:\Workarea\Oracle_Client_Tests\sqlnet.ora', flag=1, domain='prod.domain.ch'
DEBUG CheckTnsnames: file='c:\Workarea\Oracle_Client_Tests\tnsnames.ora', entry='test81.eng.domain.ch.prod.domain.ch'
DEBUG CheckTnsnames: line='TEST81.eng.domain.ch = (DESCRIPTION = '
DEBUG CheckTnsnames: modified line='TEST81.eng.domain.ch = (DESCRIPTION = '
DEBUG CheckTnsnames: line=' (ADDRESS=(PROTOCOL = TCP)(Host = dbsrv81.eng.domain.ch)(Port = 10945 ))'
DEBUG CheckTnsnames: modified line=' (ADDRESS=(PROTOCOL = TCP)(Host = dbsrv81.eng.domain.ch)(Port = 10945 ))'
DEBUG CheckTnsnames: line=' (CONNECT_DATA=(SERVICE_NAME = TEST81_RW.eng.domain.ch)))'
DEBUG CheckTnsnames: modified line=' (CONNECT_DATA=(SERVICE_NAME = TEST81_RW.eng.domain.ch)))'
DEBUG CheckTnsnames: entry=''
DEBUG CheckTnsnames: end - flag=1, ret=''
DEBUG main: tnsnames='c:\Workarea\Oracle_Client_Tests\tnsnames.ora', flag=1
Used parameter files:
c:\Workarea\Oracle_Client_Tests\sqlnet.ora
c:\Workarea\Oracle_Client_Tests\tnsnames.ora
*** TNS-03505: Failed to resolve name
I reduced the tnsnames.ora to just one entry and it still won't resolve it. I tried also the -v option with all available clients we have (11, 12, 12.2 and 18).
The normal tnsping has no issue with the entry. The entry looks like this:
TEST81.eng.domain.ch = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(Host = dbsrv81.eng.domain.ch)(Port = 10945 ))
(CONNECT_DATA=(SERVICE_NAME = TEST81_RW.eng.domain.ch)))
In the sqlnet.ora there is a domain zone set, it seems that the program concatenates the domain zone to the entry and searchs for it.
Settings in the sqlnet.ora
NAMES.DEFAULT_DOMAIN = prod.domain.ch
NAMES.DEFAULT_ZONE= prod.domain.ch
Thank you
[Updated on: Thu, 31 January 2019 05:56] Report message to a moderator
|
|
|
|
Re: McTnsping: a tnsping-like program for Windows [message #674535 is a reply to message #674527] |
Thu, 31 January 2019 10:57   |
 |
Michel Cadot
Messages: 68455 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Program is fixed, new version is available to download.
Now if a domain name is given with the tnsnames.name entry, sqlnet.ora, if present, is ignored (it is used only to get the default domain name).
Note that a difference with Oracle tnsping is that if you have several times the same entry, McTnsping uses the FIRST one. In the opposite, Oracle tnsping will use the LAST one.
I think the reason Oracle tnsping does that is because "netca" tool writes the new entries at the end of the file, so the most recent, and likely the most correct, entries would be at the bottom of the file.
The reason I don't do the same thing is that, I don't use "netca" but simple editor tool to manage my tnsnames.ora and so if I have to modify an entry I search for the first occurrence of it.
|
|
|
|
|
|
|
|
|
|
|
|