Alternative tnsping utility

• Download project - 11.2 KB

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;
 
  //open string with a dummy username and a dummy password
  string openstring = "Data Source=" + connectstring + 
     ";User Id= x_dummy; Password = x_dummy;";

    //return message from oracle connect
    string msg = String.Empty;

    //Ellapsed time in string
    string Ellapsed = String.Empty;

    //Create new oracle connection and safely close the resource
    using (OracleConnection connection = new OracleConnection(openstring))
    {
        //Timestamp for ellapsed time counting
        DateTime stTime = DateTime.Now;
        try
        {
           // Try to connect. If succeed, then close, and create
           // an OK message. x_dummy user and password is improbabile.
            // If connection not successfull, there issued an Oracle
            // exception. We need to parse the exception.
            connection.Open();

            // If succeeded...
            msg = "OK";
            //.. then cloe.
            connection.Close();
   
        }
        // If connection is not succesfull, we get the errormessage.
        catch (Exception ex)
        {
            //We need the first part of message, this is an Oracle message like this: ORA-01017
            msg = ex.Message.Split(':')[0];
        }
        //Timestamp for ellapsed time counting
        DateTime etTime = DateTime.Now;

        //Calculate the ellapsed time
        TimeSpan ts = etTime - stTime;
        //Get the millisecs from ellapsed time
        Ellapsed = ts.Milliseconds.ToString();

    }

    //Parsing the ORA errormessage. If Listener is OK, we will get
    //ORA-01017. It means a bad username and password. Any else
    //response means a kind of listener problem. I tried to parse some of them.
    switch (msg)
    {
        //kind os listener answers 
        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;
        //ORA-01017 is only good response for us. It means
        //that listener is good, but username and passwor is not.
        //This procedure gives back OK message and ellapsed time in this case
        case "ORA-01017":
            ret = String.Format("OK ({0} millisec)", Ellapsed);
            break;
        case "OK":
            ret = String.Format("OK ({0} millisec)", Ellapsed);
            break;
        //In default case this procedure doesn't found listener.
        default:
            ret = "No Oracle listener found";
            break;
    }
    //returns the result of connection                        
    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)
{
    //return string
    string ret = String.Empty;
    //dummy string for parsing
    string tnsnamesora = String.Empty;
 
    //If tnsnames.ora  exists, the tries to parse
    if (File.Exists(Path.Combine(tns_admin, "tnsnames.ora")))
    {
        //Opens safely tnsnames.ora file resource
        using (var sr = File.OpenText(Path.Combine(tns_admin, "tnsnames.ora")))
        {
            string line;
            //reads tnsnames.ora line by line
            while ((line = sr.ReadLine()) != null)
            {
                //if the line starts with # then ignore
                if (line.StartsWith("#"))
                {
                    continue;
                }

                //if the line doesn'contain any data then ignore
                if (line == "")
                {
                    continue;
                }

                //If lie contain data, then add to tnsnamesora dummy strig, after cutting the spaces.
                tnsnamesora = tnsnamesora + line.Replace(" ", String.Empty).ToUpper();
            }
        }

        //Now tnsnamesora variable contains full tnsnames.ora file,
        //without empty lines, and without spaces.

        //variable for counting brackets. If left bracket found,
        //then increase, if right bracket found then decrease
        int brackets = 0;

        //char array  for converting tnsnamesora
        List<char> tnschars = new List<char>();

        //Iterate on tnsnamesora char by char.
        foreach (char c in tnsnamesora)
        {
            char replacechar;

            //If left bracket found then increase brackets, if right bracket found then decrease
            if (c == '(') brackets++;
            if (c == ')') brackets--;

            //Make difference in text "out of brackets" and "between brackets"
            //If equatation mark is  out of brackets then change it to hash mark. 
            //After then we can split line by hash mark.
            if (brackets == 0 && c == '=')
                replacechar = '#';
                //if the cycle is between brackets, then remain the original char.
            else
                replacechar = c;

            //Add the new char to char array
            tnschars.Add(replacechar);

            //If the cycle is out of brackets, and found the last bracket, then add a newline char.
            if (brackets == 0 && c == ')')
            {
                tnschars.Add(Environment.NewLine[0]);
                tnschars.Add(Environment.NewLine[1]);
            }
        }
        //In this moment the tnsnames.ora looks like this:
        //NAME1.DOMAIN.COUNTRY#(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=COMMUNITY)
        //(PROTOCOL=TCP)(HOST=MYDBHOST1.DOMAIN.COUNTRY)(PORT=MYPORT1)))
        //(CONNECT_DATA=(SID=MYSID1)(GLOBAL_NAME=MYGLOBALNAME1.MYDOMAIN.COUNTRY)))
        //NAME2.DOMAIN.COUNTRY#(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=COMMUNITY)
        //(PROTOCOL=TCP)(HOST=MYDBHOST2.DOMAIN.COUNTRY)(PORT=MYPORT2)))
        //(CONNECT_DATA=(SID=MYSID2)(GLOBAL_NAME=MYGLOBALNAME2.MYDOMAIN.COUNTRY)))
        // etc....

        //So in fact in this moment the tnsname.ora is "canonized". The splitting char is a hash mark.

        //converts it to a string
        string s = new string(tnschars.ToArray());

        //Puts back to original string. 
        tnsnamesora = s;

        //Creates a dictionary. The key will be the tnsname, the value will be the connectstring.
        Dictionary<string,> tnsdict = new Dictionary<string,>();

        //converts a string to a dictionary with using stringreader resource
        //Using it like the string would be a textfile.
        using (StringReader reader = new StringReader(tnsnamesora))
        {
            //dummy line
            string line;
            //reads tnsnamesora multiline string line by line.
            while ((line = reader.ReadLine()) != null)
            {
                //slits the line by hash mark. Remeber: the only hash mark
                //is the equatation mark "out of brackets"
                string[] tnssplit = line.Split('#');

                //Adds the key to the dictionary, and adds the value
                tnsdict.Add(tnssplit[0], tnssplit[1]);
 
            }
        }

        //Finds the connectstring is dictionary by key.
        //If founds then returns the connectionstring
        if (tnsdict.ContainsKey(arg))
        {
            string value = tnsdict[arg];
            ret = value;
        }
        //If not founds then return an error message.
        else
        {
            ret = "Tns value not found in tnsnames.ora";
        }
    }
    //If tnsnames.ora is not found, then returns error message.
    else
    {
        ret = "Tnsnames.ora not found";

    }
    //Returns connectsring orr error message
    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)
{
    //returning answer
    string ret = String.Empty;
    //default admin context i.e "dc=company,dc=country"
    string def_admin_context = String.Empty;

    //Ldap.ora can contain many LDAP servers.
    string[] directoy_servers = null;

    //If LDAP.ora file exists
    if (File.Exists(Path.Combine(tns_admin, "Ldap.ora")))
    {
        //safely opens ldap.ora file
        using (var sr = File.OpenText(Path.Combine(tns_admin, "Ldap.ora")))
        {
            string line;

            //reads ldap.ora line by line
            while ((line = sr.ReadLine()) != null)
            {
                //if line starts with # then ignore
                if (line.StartsWith("#"))
                {
                    continue;
                }
                //if line contains no data then ignore. Maybe not necessary.
                if (line == "")
                {
                    continue;
                }

                //if line starts with DEFAULT_ADMIN_CONTEXT then splits
                //by equatation mark, and parse the second part.
                //The "Split" method does not working,
                //because the line contains many equatation marks.
                if (line.StartsWith("DEFAULT_ADMIN_CONTEXT"))
                {
                    int eqpos = line.IndexOf('=');
                    //get substring fom = mark to the end 
                    def_admin_context = line.Substring(eqpos + 1).Replace("\"", "").Trim();
                }

                //If line starts with DIRECTORY_SERVERS then reads
                //LDAP servesr, splitted by commas, after an equuatation mark.
                if (line.StartsWith("DIRECTORY_SERVERS"))
                {
                    //Split by = mark, get the second part
                    //(means [1]) remove the brackets, and split by commas
                    //the result is a string array, contains LDAP servers
                    directoy_servers = line.Split('=')[1].Replace("(", 
                      "").Replace(")", "").Split(',');
                }
            }
        }

        //Iterate on each LDAP server, and tries to connect
        foreach (string directoy_server in directoy_servers)
        {
            //Tries to connect to LDAP server with using default admin contact
            try
            {
                //LDAP connect
                DirectoryEntry directoryenty = new DirectoryEntry("LDAP://" + 
                  directoy_server + "/" + def_admin_context, null, null, AuthenticationTypes.Anonymous);
                //LDAP searcher, search orclnetservice, searchin for orclnetdescstring property
                DirectorySearcher directorysearcher = new DirectorySearcher(directoryenty, 
                  "(&(objectclass=orclNetService)(cn=" + arg + "))", 
                  new string[] { "orclnetdescstring" }, System.DirectoryServices.SearchScope.Subtree);

                //find only one
                SearchResult searchResult = directorysearcher.FindOne();

                //The result will be a byte array, tries to cast

                byte[] dummy = searchResult.Properties["orclnetdescstring"][0] as byte[];

                //Converts a bytearray to string with default encoding.
                //If casting was not successfull, the result will be null.
                ret = System.Text.Encoding.Default.GetString(dummy);

                //If the connection was successfull, then no necessary to try another LDAP servers.
                break;
            }
            //If the connection to LDAP server not successfull,try to connect to next
            catch
            {
                continue;
            }
        }

        //If casting was not successfull, or not found
        //any tns value, then result is an error message.
        if (ret == "")
        {
            ret = "Tns value not found in LDAP";
        }
    }
    // if LDAP.ora not exists, then returns error message.
    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.