Some tricky quick way to validate oracle db connection

Question

My WCF service need to check is connection available now and can we work with it. We have many remote dbs. Their connection are weird sometimes and can't be used to query data or smth else. So, for example this is regular connection string used:

User Id=user;Password=P@ssw0rd;Data Source=NVDB1;Connection Timeout=30

Here is service method, used for getting

    public List<string> GetAliveDBs(string city)
    {
        if (String.IsNullOrEmpty(city))
            return null;                        

        List<string> cityDbs = (from l in alldbs where !String.IsNullOrEmpty(l.Value.city) && l.Value.city.ToUpper() == city.ToUpper() select l.Value.connString).ToList();            

        // There is no such city databases
        if (cityDbs.Count == 0)
            return null;

        ReaderWriterLockSlim locker = new ReaderWriterLockSlim();

        Parallel.ForEach(cityDbs, p =>
        {
            if (!IsConnectionActive(p.connString))
            {
                locker.EnterWriteLock();
                try
                {
                    cityDbs.RemoveAt(cityDbs.IndexOf(p));
                }
                finally
                {
                    locker.ExitWriteLock();
                }
            }
        });

        return cityDbs;
    }

    static public bool IsConnectionAlive(string connectionString)
    {
        using (OracleConnection c = new OracleConnection(connectionString))
        {
            try
            {                    
                c.Open();
                if ((c.State == ConnectionState.Open) && (c.Ping()))
                    return true;
                else
                    return false;
            }
            catch (Exception exc)
            {
                return false;                 
            }
        }
    }

I use devart components to communicate with Oracle DB. Hope for your help, guys! Thanks in advance!

Answer 1

Try just executing a very low cost operation that should work no matter what schema you are connected to, e.g.

SELECT 1

(that statement works on MS SQL and MySQL... should work on Oracle too but I can't confirm that).

If you get the result you expect (in this case one row, with one column, containing a "1") then the connection is valid.

At least one connection pool manager uses this strategy to validate connections periodically.

UPDATE:

Here's a SQL Server version of your method. You can probably just replace "Sql" with "Oracle".

static public bool IsConnectionAlive(string connectionString)
{
    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT 1", conn))
            {
                int result = (int)cmd.ExecuteScalar();
                return (result == 1);
            }

        }
    }
    catch (Exception ex)
    {
        // You need to decide what to do here... e.g. does a malformed connection string mean the "connection isn't alive"?
        // Maybe return false, maybe log the error and re-throw the exception?
        throw;
    }
}

Answer 2

If the goal is to simply determine if a server lives at the IP Address or host name then I'd recommend Ping (no 3 way handshake and has less overhead than a UDP message). You can use the System.Net.NetworkInformation.Ping class (see its documentation for an example) for this.

If you're looking to prove that there is actually something listening on the common Oracle port, I would suggest using either the System.Net.Sockets.TcpClient or System.Net.Sockets.Socket class (their documentation also provides examples) to provide this.

The simplest way to do this (by far) is to just open a connection using the Oracle API for C#. There is a very good tutorial that includes code here. It covers more than just the connection but you should be able to strip out the connection portion from the rest to fit your needs.

Answer 3

Oracle has products and software specifically for helping maintain high availability that can allow you to have dead connections removed from you connection pool through a setting called HA Events=true on the connection string. Your Oracle DBA will need to determine if your installation supports it.