Introduction
This project is specially for people who use Oracle and Oracle Instant Client. Some Oracle knowledge is
necessary. You have to know what tnsname, connectstring, and some other things
mean.
Oracle Instant Client is very handy and useful. I like it very much because for example,
you can publish a 32 bit program on a 64 bit server, or publish my program to a machine
which has no Oracle client. And it is very small compared to the "big" Oracle client.
The only shortcoming of Instant Client for me is there is no tnsping utility. It is very useful when
you publish your program in a big company, and some users can connect to an Oracle server
and some cannot. And the tnsping is very useful to trace errors.
First of all, if
you want to know more than the original tnsping utility, read this article:
http://docs.oracle.com/cd/E11882_01/network.112/e10836/connect.htm
Tnsping is an Oracle connection testing tool. You can use my program instead of
the tnsping utility.
Of course, you can complete your instant client with tnsping utility. There are many articles on the web about it. The only problem
is that you have to copy many DLLs form the complete Oracle client to use tnsping, and the Instant Client is no more "instant". That was the idea - to write
your own tnsping utility.
This program version can ping using a direct connectstring, resolve with
tnsnames.ora, and resolve with LDAP.
Background
This article is useful for:
- How to use Oracle Instant Client
- How to parse
a text file without regular expressions
- How to read LDAP
The original tnsping utility inspects the state of an Oracle listener. This is a console application, like ping utility.
It inspects the state of an Oracle listener.
In this program I use a little trick. The
tnsping utility inspects if the Oracle listener working or not. This program only simulates it. I try to connect into Oracle using a connect string with a dummy user and dummy password. I get an error message. If the error message is "ORA-01017
" that
is good for us: that means the listener is good, but the username/password is not. No problem: I don't want to connect, just inspect the listener.
Installation
My source code, what
you download, will not work. Please download the source code and follow these steps:
- First
you have to download Oracle Instant Client from here:
http://www.oracle.com/technetwork/topics/winsoft-085727.html
- Unzip the
DLLs into my source code, into the project/tnshrping directory (one
level below the tnshrping.sln file).
- In Visual Studio right click on
the tnshrping project, and Add existing items. Add the DLLs to your project (tnshrping project).
- In each
DLL's properties set Build Action to Content, Copy to Output to Copy Always
These steps
are necessary to compile my utility. After that, when you use Instant Client, it
is enough to copy tnshrping.exe into your Oracle Instant Client directory.
Using the code
The flow of this program:
- Try to connect direct (the argument is the connect string).
- If successful then OK message and finish.
- If not successful, read sqlnet.ora and set the resolving order.
- Try to resolve with first option (LDAP or TNSNAMES) and try to connect.
- If
successful then OK message and finish.
- If not successful, try to resolve with second option.
- If
successful, echoes the "OK" message.
- If not successful, echoes the error message.
The place of sqlnet.ora and tnsnames.ora
is pointed by tns_admin
environment variable.
First of all let's see how to check for an Oracle connection. This is a very simple Oracle connection, with nothing special. I just try to connect to Oracle with a dummy
username and a dummy password. We need only the answer from the Oracle server and parse it. Trytoconnect
needs an
Oracle connect string, and gives back a message
about the state of the listener. It is not necessary to connect, just parse the listener's error message.
public static string trytoconnect(string connectstring)
{
string ret = String.Empty;
string openstring = "Data Source=" + connectstring +
";User Id= x_dummy; Password = x_dummy;";
string msg = String.Empty;
string Ellapsed = String.Empty;
using (OracleConnection connection = new OracleConnection(openstring))
{
DateTime stTime = DateTime.Now;
try
{
connection.Open();
msg = "OK";
connection.Close();
}
catch (Exception ex)
{
msg = ex.Message.Split(':')[0];
}
DateTime etTime = DateTime.Now;
TimeSpan ts = etTime - stTime;
Ellapsed = ts.Milliseconds.ToString();
}
switch (msg)
{
case "ORA-12154":
ret = "No Oracle listener found";
break;
case "ORA-12514":
ret = "No TNS listener found";
break;
case "ORA-12541":
ret = "No listener ";
break;
case "ORA-01017":
ret = String.Format("OK ({0} millisec)", Ellapsed);
break;
case "OK":
ret = String.Format("OK ({0} millisec)", Ellapsed);
break;
default:
ret = "No Oracle listener found";
break;
}
return ret;
}
To get the connection string from a tnsname there are many methods. This program can use tnsnames.ora, or LDAP
.
The resolving order is important, and it is set in the sqlnet.ora file. This program finds
the sqlnet.ora file in a folder
pointed to by the tns_admin
environment variable.
The program reads sqlnet.ora
, reads the resolving order (see the main method below), and tries to resolve tnsname from
the tnsnames.ora file or LDAP.
First, let's see how to parse tnsnames.ora. The tnsnames.ora file contains tnsnames like this:
TNSNAME1.DOMAIN.COUNTRY=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = myhost.domain.country)
(PROTOCOL = TCP)
(HOST = myhost.domain.country)
(PORT = 1522)
)
)
(CONNECT_DATA =
(SID = MYSID)
)
)
We have to write a method to parse this. We give the resolvetns
method a tnsname (TNSNAME1.DOMAIN.COUNTRY) and wait for the connect string: (DESCRIPTION=....etc...).
This is not an elegant method.
Using regular expressions would be more elegant.
The problem is that, I'm not a regexp guru, and the structure of tnsnames.ora is not so simple.
The idea is to "canonize"
tnsnames.ora and split tnsname and the connect string. I parse tnsnames.ora char by char. I make
a difference between "in brackets" and "out of brackets" characters. If
you inspect tnsnames.ora carefully, you can see that the separator
between tnsname and connectstring is an equation mark "out of brackets". All other eq marks are "in brackets".
I changed "out of bracktes" eq. mark to a hash mark, and split tnsnames.ora's line by hash mark into tnsname and connectstring.
Let's see:
private static string resolvetns(string arg)
{
string ret = String.Empty;
string tnsnamesora = String.Empty;
if (File.Exists(Path.Combine(tns_admin, "tnsnames.ora")))
{
using (var sr = File.OpenText(Path.Combine(tns_admin, "tnsnames.ora")))
{
string line;
while ((line = sr.ReadLine()) != null)
{
if (line.StartsWith("#"))
{
continue;
}
if (line == "")
{
continue;
}
tnsnamesora = tnsnamesora + line.Replace(" ", String.Empty).ToUpper();
}
}
int brackets = 0;
List<char> tnschars = new List<char>();
foreach (char c in tnsnamesora)
{
char replacechar;
if (c == '(') brackets++;
if (c == ')') brackets--;
if (brackets == 0 && c == '=')
replacechar = '#';
else
replacechar = c;
tnschars.Add(replacechar);
if (brackets == 0 && c == ')')
{
tnschars.Add(Environment.NewLine[0]);
tnschars.Add(Environment.NewLine[1]);
}
}
string s = new string(tnschars.ToArray());
tnsnamesora = s;
Dictionary<string,> tnsdict = new Dictionary<string,>();
using (StringReader reader = new StringReader(tnsnamesora))
{
string line;
while ((line = reader.ReadLine()) != null)
{
string[] tnssplit = line.Split('#');
tnsdict.Add(tnssplit[0], tnssplit[1]);
}
}
if (tnsdict.ContainsKey(arg))
{
string value = tnsdict[arg];
ret = value;
}
else
{
ret = "Tns value not found in tnsnames.ora";
}
}
else
{
ret = "Tnsnames.ora not found";
}
return ret;
}
If tns-resolving was not successful, or the LDAP wins on tnsnames in resolving order, then we have to find tnsname in LDAP. This is not so complicated, but some LDAP knowledge is necessary. We just have to connect to
the LDAP server and give a simple search command to an LDAP server in the default LDAP context. This method asks all LDAP servers found in
LDAP.ora file until one is of them is found live, and gives back an answer.
If you want to know more than LDAP or directorysearcher, directory services, or know something about using LDAP queries in C#, than please read this article:
http://stackoverflow.com/questions/1437250/connecting-to-ldap-from-c-sharp-using-directoryservices
And now, let's see, how to resolve tnsname from LDAP. This is a simple LDAP query. This program tries to ask LDAP servers while the LDAP server is answering.
There can be differences between LDAP servers.
In my workplace I have to ask for the orclnetdescstring
property, but maybe other LDAP servers use other names. And the response is a byte array, maybe in another LDAP server,
this can be a string, etc.
So before using you have to rewrite this method according to your expectation.
Let's see the code:
private static string resolveldap(string arg)
{
string ret = String.Empty;
string def_admin_context = String.Empty;
string[] directoy_servers = null;
if (File.Exists(Path.Combine(tns_admin, "Ldap.ora")))
{
using (var sr = File.OpenText(Path.Combine(tns_admin, "Ldap.ora")))
{
string line;
while ((line = sr.ReadLine()) != null)
{
if (line.StartsWith("#"))
{
continue;
}
if (line == "")
{
continue;
}
if (line.StartsWith("DEFAULT_ADMIN_CONTEXT"))
{
int eqpos = line.IndexOf('=');
def_admin_context = line.Substring(eqpos + 1).Replace("\"", "").Trim();
}
if (line.StartsWith("DIRECTORY_SERVERS"))
{
directoy_servers = line.Split('=')[1].Replace("(",
"").Replace(")", "").Split(',');
}
}
}
foreach (string directoy_server in directoy_servers)
{
try
{
DirectoryEntry directoryenty = new DirectoryEntry("LDAP://" +
directoy_server + "/" + def_admin_context, null, null, AuthenticationTypes.Anonymous);
DirectorySearcher directorysearcher = new DirectorySearcher(directoryenty,
"(&(objectclass=orclNetService)(cn=" + arg + "))",
new string[] { "orclnetdescstring" }, System.DirectoryServices.SearchScope.Subtree);
SearchResult searchResult = directorysearcher.FindOne();
byte[] dummy = searchResult.Properties["orclnetdescstring"][0] as byte[];
ret = System.Text.Encoding.Default.GetString(dummy);
break;
}
catch
{
continue;
}
}
if (ret == "")
{
ret = "Tns value not found in LDAP";
}
}
else
{
ret = "Ldap.ora not found";
}
return ret;
}
And last but not least. Lets' speak about the main method of this utility. It is very simple.
It tries to parse the arguments. The arguments can contain spaces, but the program needs only one argument. So first concatenate them into one argument. Then the main method tries to follow the complete flow
of what I wrote before. First, it tries to connect to Oracle and the program's argument
is a connectstring. If successful, then end. Then it reads the path of sqlnet.ora, tnsnames.ora, and ldap.ora from tns_admin
environment variable. If not found, then end. If found, tries to open the sqlnet.ora (simple text) and read the searching order in NAMES.DIRECTORY_PATH line. It looks like this:
NAMES.DIRECTORY_PATH = (LDAP,TNSNAMES)
In this case the search order will be LDAP and then TNSNAMES.
And
it reads the default zone from sqlnet.ora from this line:
NAME.DEFAULT_ZONE=mydomain.mycountry
If the order is set, the main method tries to resolve the tnsname in this case first from LDAP, then TNSNAMES. If LDAP can give back the connect string,
we try to connect into Oracle with the trytoconnect
method. If the resolving
is not successful, it tries to resolve LDAP with tnsname adding the default zone:
tnsname.mydomain.mycountry
If successful, then ends, if not
we try to resolve with tnsnames.ora.... see before, and connect. Then we give back a result message about the resolving attempt.
Points of interest
This is a very useful program, but doesn't contain a
lot of new things. It is worth to examining by everybody who meets this problem.
You can learn to use Oracle Instant Client with this example.
History
This is version 1.0.
License
This program has a CPOL license without the Oracle Instant Client. You have to download it individually, and accept Oracle's
license agreement on Oracle's website.