Oracle Connection Leak

  • Thread starter Thread starter Silly Shen
  • Start date Start date
S

Silly Shen

I'm using System.Data.OracleClient, it works fine when the user name and password are correct.
But when the user name or password is incorrect, it has a process leftover in the oracle server box.
There's no Oracle connection established, but the Oracle process is not cleaned, and finally get
Error: "ORA-00020: maximum number of processes (%s) exceeded".

Did anybody experienced this problem before or have any idea how to solve this issue?
I tried to set the pool to on/off and set the Connection Lifetime, but it didn't help.

I'm using .net 1.1 plus Oracle 817 Client in Win2000 and Oracle 9i Server in Solaris.


Silly Shen


PS: my code is like this:

//Check the user login;
public bool Login(string userName, string password)
{
string genErrorMsg = "Failed to verify user "+userName;

OracleCommand dbComm = null;
OracleDataReader dbReader = null;
OracleConnection dbConn = null;
try
{
string connString = "data source=MyDatabase;user id="+userName+";password="+password+";Pooling=true;Connection Lifetime=30";

dbConn = new OracleConnection();
dbConn.ConnectionString = connString;
dbConn.Open();

dbComm = new OracleCommand();
dbComm.Connection = dbConn;

string strQuery = "select U.USER_ID, U.NAME as FULL_NAME"
+" from USERS U"
+" where upper(U.USER_ID)='"+userName.ToUpper()+"'";

dbComm.CommandText = strQuery;
dbReader = dbComm.ExecuteReader();

if (dbReader.Read())
return true;
else
return false;
}
catch (Exception e)
{
//Log and show error information
return false;
}
finally
{
if (dbReader!=null) dbReader.Close();
if (dbConn!=null)
{ dbConn.Close();
dbConn.Dispose(); }
}
}//End of Login(string userName, string password);
 
I had this problem with Oracle in the past, but it was nothing to do with Windows or .Net, it was a totally Unix system. Orphaned oracle processes were building up over time and not shutting down. We finally created a shell script on the server which killed any oracle processes which had been running for more than a day.

I think that there is a more elegant solution involving enabling Keep-Alives on the server. This tells the server to check whether there is a client active by 'pinging' it periodically. It will increase network traffic a bit, but may solve your problem.

On top of this it looks like there is a problem with the OracleClient in that it should be able to tell the server process to shut down when the login fails, but I can't help you there. You could try Oracle's .Net provider rather than Microsoft's, but in my experience you are likely to find other problems.

Neil.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Neil McKechnie
Microlink Associates Ltd
(e-mail address removed)
I'm using System.Data.OracleClient, it works fine when the user name and password are correct.
But when the user name or password is incorrect, it has a process leftover in the oracle server box.
There's no Oracle connection established, but the Oracle process is not cleaned, and finally get
Error: "ORA-00020: maximum number of processes (%s) exceeded".

Did anybody experienced this problem before or have any idea how to solve this issue?
I tried to set the pool to on/off and set the Connection Lifetime, but it didn't help.

I'm using .net 1.1 plus Oracle 817 Client in Win2000 and Oracle 9i Server in Solaris.


Silly Shen


PS: my code is like this:

//Check the user login;
public bool Login(string userName, string password)
{
string genErrorMsg = "Failed to verify user "+userName;

OracleCommand dbComm = null;
OracleDataReader dbReader = null;
OracleConnection dbConn = null;
try
{
string connString = "data source=MyDatabase;user id="+userName+";password="+password+";Pooling=true;Connection Lifetime=30";

dbConn = new OracleConnection();
dbConn.ConnectionString = connString;
dbConn.Open();

dbComm = new OracleCommand();
dbComm.Connection = dbConn;

string strQuery = "select U.USER_ID, U.NAME as FULL_NAME"
+" from USERS U"
+" where upper(U.USER_ID)='"+userName.ToUpper()+"'";

dbComm.CommandText = strQuery;
dbReader = dbComm.ExecuteReader();

if (dbReader.Read())
return true;
else
return false;
}
catch (Exception e)
{
//Log and show error information
return false;
}
finally
{
if (dbReader!=null) dbReader.Close();
if (dbConn!=null)
{ dbConn.Close();
dbConn.Dispose(); }
}
}//End of Login(string userName, string password);
 
Shen,
This could be bad, I am looking into it and will post here as soon as I find
anything out.

Thanks!

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

I'm using System.Data.OracleClient, it works fine when the user name and
password are correct.
But when the user name or password is incorrect, it has a process leftover
in the oracle server box.
There's no Oracle connection established, but the Oracle process is not
cleaned, and finally get
Error: "ORA-00020: maximum number of processes (%s) exceeded".

Did anybody experienced this problem before or have any idea how to solve
this issue?
I tried to set the pool to on/off and set the Connection Lifetime, but it
didn't help.

I'm using .net 1.1 plus Oracle 817 Client in Win2000 and Oracle 9i Server in
Solaris.


Silly Shen


PS: my code is like this:

//Check the user login;
public bool Login(string userName, string password)
{
string genErrorMsg = "Failed to verify user "+userName;

OracleCommand dbComm = null;
OracleDataReader dbReader = null;
OracleConnection dbConn = null;
try
{
string connString = "data source=MyDatabase;user
id="+userName+";password="+password+";Pooling=true;Connection Lifetime=30";

dbConn = new OracleConnection();
dbConn.ConnectionString = connString;
dbConn.Open();

dbComm = new OracleCommand();
dbComm.Connection = dbConn;

string strQuery = "select U.USER_ID, U.NAME as FULL_NAME"
+" from USERS U"
+" where upper(U.USER_ID)='"+userName.ToUpper()+"'";

dbComm.CommandText = strQuery;
dbReader = dbComm.ExecuteReader();

if (dbReader.Read())
return true;
else
return false;
}
catch (Exception e)
{
//Log and show error information
return false;
}
finally
{
if (dbReader!=null) dbReader.Close();
if (dbConn!=null)
{ dbConn.Close();
dbConn.Dispose(); }
}
}//End of Login(string userName, string password);
 
Shen,
We have not quite gotten to the bottom of this, but it is clear that the
problem is with Garbage Collection.

Try adding GC.Collect(); to your code wherever you are catching the invalid
log in exception and you should not run into the ORA-00020 exception again
(you may still see max of two leaked processes, investigating)
 
Shen,
This is actually a complicated issue, your best bet is to contact PSS
directly and file a QFE request with all that that entails.

GC.Collect is not a great workarround, it is expensive and you would have to
go through your code looking for everyplace where you open a connection...
Sorry I could not help more,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Angel Saenz-Badillos said:
Shen,
We have not quite gotten to the bottom of this, but it is clear that the
problem is with Garbage Collection.

Try adding GC.Collect(); to your code wherever you are catching the invalid
log in exception and you should not run into the ORA-00020 exception again
(you may still see max of two leaked processes, investigating)

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Angel Saenz-Badillos said:
Shen,
This could be bad, I am looking into it and will post here as soon as I find
anything out.

Thanks!

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

I'm using System.Data.OracleClient, it works fine when the user name and
password are correct.
But when the user name or password is incorrect, it has a process leftover
in the oracle server box.
There's no Oracle connection established, but the Oracle process is not
cleaned, and finally get
Error: "ORA-00020: maximum number of processes (%s) exceeded".

Did anybody experienced this problem before or have any idea how to solve
this issue?
I tried to set the pool to on/off and set the Connection Lifetime, but it
didn't help.

I'm using .net 1.1 plus Oracle 817 Client in Win2000 and Oracle 9i
Click to expand...
Server
in
Solaris.


Silly Shen


PS: my code is like this:

//Check the user login;
public bool Login(string userName, string password)
{
string genErrorMsg = "Failed to verify user "+userName;

OracleCommand dbComm = null;
OracleDataReader dbReader = null;
OracleConnection dbConn = null;
try
{
string connString = "data source=MyDatabase;user
id="+userName+";password="+password+";Pooling=true;Connection Lifetime=30";

dbConn = new OracleConnection();
dbConn.ConnectionString = connString;
dbConn.Open();

dbComm = new OracleCommand();
dbComm.Connection = dbConn;

string strQuery = "select U.USER_ID, U.NAME as FULL_NAME"
+" from USERS U"
+" where upper(U.USER_ID)='"+userName.ToUpper()+"'";

dbComm.CommandText = strQuery;
dbReader = dbComm.ExecuteReader();

if (dbReader.Read())
return true;
else
return false;
}
catch (Exception e)
{
//Log and show error information
return false;
}
finally
{
if (dbReader!=null) dbReader.Close();
if (dbConn!=null)
{ dbConn.Close();
dbConn.Dispose(); }
}
}//End of Login(string userName, string password);
Click to expand...
Click to expand...
 
Thank you so much for you help.
I'm trying the GC.Collect solution, and trying to schedule it during low
peak period, hope it would help.

--Silly

Angel Saenz-Badillos said:
Shen,
This is actually a complicated issue, your best bet is to contact PSS
directly and file a QFE request with all that that entails.

GC.Collect is not a great workarround, it is expensive and you would have to
go through your code looking for everyplace where you open a connection...
Sorry I could not help more,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Angel Saenz-Badillos said:
Shen,
We have not quite gotten to the bottom of this, but it is clear that the
problem is with Garbage Collection.

Try adding GC.Collect(); to your code wherever you are catching the invalid
log in exception and you should not run into the ORA-00020 exception again
(you may still see max of two leaked processes, investigating)

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Shen,
This could be bad, I am looking into it and will post here as soon as
Click to expand...
I
find
anything out.

Thanks!

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

I'm using System.Data.OracleClient, it works fine when the user name and
password are correct.
But when the user name or password is incorrect, it has a process leftover
in the oracle server box.
There's no Oracle connection established, but the Oracle process is not
cleaned, and finally get
Error: "ORA-00020: maximum number of processes (%s) exceeded".

Did anybody experienced this problem before or have any idea how to solve
this issue?
I tried to set the pool to on/off and set the Connection Lifetime, but it
didn't help.

I'm using .net 1.1 plus Oracle 817 Client in Win2000 and Oracle 9i
Click to expand...
Server
in
Solaris.


Silly Shen


PS: my code is like this:

//Check the user login;
public bool Login(string userName, string password)
{
string genErrorMsg = "Failed to verify user "+userName;

OracleCommand dbComm = null;
OracleDataReader dbReader = null;
OracleConnection dbConn = null;
try
{
string connString = "data source=MyDatabase;user
id="+userName+";password="+password+";Pooling=true;Connection Lifetime=30";

dbConn = new OracleConnection();
dbConn.ConnectionString = connString;
dbConn.Open();

dbComm = new OracleCommand();
dbComm.Connection = dbConn;

string strQuery = "select U.USER_ID, U.NAME as FULL_NAME"
+" from USERS U"
+" where upper(U.USER_ID)='"+userName.ToUpper()+"'";

dbComm.CommandText = strQuery;
dbReader = dbComm.ExecuteReader();

if (dbReader.Read())
return true;
else
return false;
}
catch (Exception e)
{
//Log and show error information
return false;
}
finally
{
if (dbReader!=null) dbReader.Close();
if (dbConn!=null)
{ dbConn.Close();
dbConn.Dispose(); }
}
}//End of Login(string userName, string password);
Click to expand...
Click to expand...
Click to expand...
 
Back
Top