8 Replies Latest reply: Dec 30, 2004 4:57 PM by Alex.Keh .Product.Manager-Oracle RSS

    ORA-02396: exceeded maximum idle time, please connect again

    119014
      I feel like I might have read about this before, but I can't find a reference now.

      We are using ODP.NET in out ASP.NET web site. Our DBAs mandated that we have a seperate oracle account for each web user "because it was good database policy." Recently, they decided to apply max idle timeouts to all of those users.

      Now I'm having the following problem. When a user logs in initially, everything is fine. They log into the web site, use it, and go away happy. Now, the same user comes back later (after the max idle timeout) and tries to log in again. During that time, the web server ODP.NET has held onto that connection in the connection pool but the connection has been idle. The Oracle server has decided that the connection should be disconnected because it was idle too long. The user gets this error message "ORA-02396: exceeded maximum idle time, please connect again". If the user tries to log in again they recieve this message "ORA-01012: not logged on"

      Two things appear to be happening. One, ODP.NET is holding on to the connections longer than the max idle timeout. How long does or should ODP.NET normally hold onto an idle connection in the connection pool? How can I adjust this? Two, ODP.NET is not removing the closed connection from the pool.

      How can I overcome this problem?

      Thanks.
        • 1. Re: ORA-02396: exceeded maximum idle time, please connect again
          182042
          ODP.NET does not check the connection status of the connections while they are in the pool. ODP.NET checks the status before putting the connection in the pool so that bad connections are not placed in the pool but does not check the status after getting the connection from the pool. You can execute a light weight command, e.g. alter session, to check the connection status after you get it from the pool.
          • 2. Re: ORA-02396: exceeded maximum idle time, please connect again
            119014
            I don't see how this could work. ODP.NET continues to give me back the invalid connection. I believe that is why subsuquent Open()s give a connection that produces the "ORA-01012: not logged on" error when you try to use it.

            Besides, what am I supposed to do once I figure out the connection is not good? I can't get it out of the pool. Am I supposed to do this every time I call Connection.Open()? That sounds like a reusable bit of code that ought to go in ODP.NET, not in my app. :)

            • 3. Re: ORA-02396: exceeded maximum idle time, please connect again
              182042
              Checking the connection status comes at a cost (one server round trip to the database). Every ODP.NET application would have to pay that cost if ODP.NET does have to check the connection status everytime a connection is pulled from the connection pool and not all the applications use max idle time out.

              As a work around, an application can perform this check by itself and close the connection if the connection is not valid anymore.
              • 4. Re: ORA-02396: exceeded maximum idle time, please connect again
                119014
                I have seen other people posting problems where the database dropped one of their connections and that connection got returned from the pool. This seems to be a common dilema.

                Even still, the problem remains that I cannot get the connection removed from the pool. I did some testing and you can apparently get different messages from the database when you get disconnected. I'm going to try and post my code and the output below, but if it cuts it off I'll post multiple times.

                Please note in the code that I close and dispose, but the connection is sometimes added back to the pool anyway.

                Also, I did come up with a workaround, but I'm not happy with it. I'm still trying to consider if there might be any possible problems with the workaround. Please let me know what you think. In the code below, ConnectionTest1() is the "control". I get it to work "correctly" by setting the Max Pool Size to zero. I'm hypothesizing that this allows the pool to slowly shrink back to zero (insted of 1) when ODP.NET removes un-needed connections every 3 minutes (or so the documentation says).

                Output from several tests.
                The database max idle timeout was set to 60 minutes.

                C:\TEMP>ConsoleApplication5
                USAGE: ConsoleApplication5 <username> <password> <database> <timeout in minutes>

                C:\TEMP>ConsoleApplication5 aaa bbb ccc 65
                1: 7/28/2003 3:37:38 PM
                1: 7/28/2003 3:37:39 PM
                2: 7/28/2003 3:37:41 PM
                2: 7/28/2003 3:37:41 PM
                Timeout Expired.
                1: 7/28/2003 4:42:43 PM
                1: 7/28/2003 4:42:43 PM
                1: 7/28/2003 4:42:43 PM
                2: ORA-03113: end-of-file on communication channel
                2: 7/28/2003 4:43:12 PM
                2: 7/28/2003 4:43:12 PM

                C:\TEMP>ConsoleApplication5 ddd bbb ccc 65
                1: 7/28/2003 3:37:40 PM
                1: 7/28/2003 3:37:41 PM
                2: 7/28/2003 3:37:43 PM
                2: 7/28/2003 3:37:43 PM
                Timeout Expired.
                1: 7/28/2003 4:42:45 PM
                1: 7/28/2003 4:42:45 PM
                1: 7/28/2003 4:42:45 PM
                2: ORA-03113: end-of-file on communication channel
                2: 7/28/2003 4:43:14 PM
                2: 7/28/2003 4:43:14 PM

                C:\TEMP>ConsoleApplication5 eee bbb ccc 65
                1: 7/28/2003 3:37:56 PM
                1: 7/28/2003 3:37:57 PM
                2: 7/28/2003 3:37:58 PM
                2: 7/28/2003 3:37:58 PM
                Timeout Expired.
                1: 7/28/2003 4:42:59 PM
                1: 7/28/2003 4:43:00 PM
                1: 7/28/2003 4:43:00 PM
                2: ORA-02396: exceeded maximum idle time, please connect again
                2: ORA-01012: not logged on
                2: ORA-01012: not logged on

                C:\TEMP>ConsoleApplication5 fff bbb ccc 65
                1: 7/28/2003 3:38:26 PM
                1: 7/28/2003 3:38:26 PM
                2: 7/28/2003 3:38:28 PM
                2: 7/28/2003 3:38:28 PM
                Timeout Expired.
                1: 7/28/2003 4:43:29 PM
                1: 7/28/2003 4:43:29 PM
                1: 7/28/2003 4:43:30 PM
                2: ORA-02396: exceeded maximum idle time, please connect again
                2: ORA-01012: not logged on
                2: ORA-01012: not logged on

                Code:
                using System;
                using System.Threading;
                using System.Text;
                using System.Data;
                using Oracle.DataAccess.Client;
                using Oracle.DataAccess.Types;
                using A400;

                namespace ConsoleApplication6
                {
                     /// <summary>
                     /// Summary description for Class1.
                     /// </summary>
                     class Class1
                     {
                          /// <summary>
                          /// The main entry point for the application.
                          /// </summary>
                          [STAThread]
                          static void Main(string[] args)
                          {
                               Class1 c=new Class1();

                               c.ConnectionTest("a400_tuser_afsoc","development","A400DB");

                               c=null;
                          }

                          void ConnectionTest(string uid,string pwd,string db)
                          {
                               StringBuilder cs=new StringBuilder(100);
                               cs.AppendFormat("User Id={0};Password={1};Data Source={2};Min Pool Size=0;Decr Pool Size=10",uid,pwd,db);

                               OracleConnection con=new OracleConnection(cs.ToString());

                               try
                               {
                                    con.Open();

                                    Util utl=new Util();
                                    utl.Connection=con;
                                    
                                    User usr=utl.GetUser("TMOORE");

                                    Console.WriteLine(usr.ToString());                    
                               }
                               catch(OracleException oe)
                               {
                                    Console.WriteLine(oe.Message);
                               }
                               catch(UserNotFoundException ex)
                               {
                                    Console.WriteLine("User not found: {0}",ex.Message);
                               }
                               finally
                               {
                                    con.Close();
                                    con.Dispose();
                               }
                          }
                     }
                }
                • 5. Re: ORA-02396: exceeded maximum idle time, please connect again
                  119014
                  Sorry, I posted the wrong piece of code.

                  Here is the correct code:
                  using System;
                  using System.Threading;
                  using System.Text;
                  using System.Data;
                  using Oracle.DataAccess.Client;
                  using Oracle.DataAccess.Types;

                  namespace ConsoleApplication5
                  {
                       /// <summary>
                       /// Summary description for Class1.
                       /// </summary>
                       class Class1
                       {
                            /// <summary>
                            /// The main entry point for the application.
                            /// </summary>
                            [STAThread]
                            static void Main(string[] args)
                            {
                                 if(args.Length==0)
                                 {
                                      Console.WriteLine("USAGE: ConsoleApplication5 <username> <password> <database> <timeout in minutes>");
                                      return;
                                 }

                                 Class1 objMe=new Class1();
                                 string username=args[0];
                                 string password=args[1];
                                 string database=args[2];
                                 int minutes=int.Parse(args[3]);

                  // open and close a connection
                                 objMe.ConnectionTest(username,password,database);
                                 objMe.ConnectionTest(username,password,database);
                                 objMe.ConnectionTest2(username,password,database);
                                 objMe.ConnectionTest2(username,password,database);

                                 // wait until connection has timed out
                                 TimeSpan timeOut=new TimeSpan(0,0,minutes,0,0);
                                 Thread.Sleep(timeOut);
                                 Console.WriteLine("Timeout Expired.");

                                 // try connection again
                                 objMe.ConnectionTest(username,password,database);
                                 objMe.ConnectionTest(username,password,database);
                                 objMe.ConnectionTest(username,password,database);

                                 // try test 2 multiple times to see if we get the same message or if the
                                 // connection goes back to the pool after calling close
                                 objMe.ConnectionTest2(username,password,database);
                                 objMe.ConnectionTest2(username,password,database);
                                 objMe.ConnectionTest2(username,password,database);
                                 
                            }

                            void ConnectionTest(string uid,string pwd,string db)
                            {
                                 StringBuilder cs=new StringBuilder(100);
                                 cs.AppendFormat("User Id={0};Password={1};Data Source={2};Min Pool Size=0;Decr Pool Size=10",uid,pwd,db);

                                 OracleConnection con=new OracleConnection(cs.ToString());

                                 try
                                 {
                                      con.Open();

                                      OracleCommand cmd=new OracleCommand("select sysdate from dual",con);
                                      OracleDataReader rdr=cmd.ExecuteReader();

                                      if(rdr.Read())
                                      {
                                           Console.WriteLine("1: {0}",((DateTime)rdr["SYSDATE"]).ToString());
                                      }

                                      rdr.Close();
                                      rdr.Dispose();
                                 }
                                 catch(OracleException oe)
                                 {
                                      Console.WriteLine("1: {0}",oe.Message);
                                 }
                                 finally
                                 {
                                      con.Close();
                                      con.Dispose();
                                 }
                            }

                            void ConnectionTest2(string uid,string pwd,string db)
                            {
                                 StringBuilder cs=new StringBuilder(100);
                                 cs.AppendFormat("User Id={0};Password={1};Data Source={2};",uid,pwd,db);

                                 OracleConnection con=new OracleConnection(cs.ToString());

                                 try
                                 {
                                      con.Open();

                                      OracleCommand cmd=new OracleCommand("select sysdate from dual",con);
                                      OracleDataReader rdr=cmd.ExecuteReader();

                                      if(rdr.Read())
                                      {
                                           Console.WriteLine("2: {0}",((DateTime)rdr["SYSDATE"]).ToString());
                                      }

                                      rdr.Close();
                                      rdr.Dispose();
                                 }
                                 catch(OracleException oe)
                                 {
                                      Console.WriteLine("2: {0}",oe.Message);
                                 }
                                 finally
                                 {
                                      con.Close();
                                      con.Dispose();
                                 }
                            }

                       }
                  }
                  • 6. Re: ORA-02396: exceeded maximum idle time, please connect again
                    119014
                    Have you taken a look at my test case?
                    • 7. Re: ORA-02396: exceeded maximum idle time, please connect again
                      435398
                      Dear Thomas,
                      I have the same problem that you've experienced. Please how did you solve it?
                      Tks.
                      Devair
                      • 8. Re: ORA-02396: exceeded maximum idle time, please connect again
                        Alex.Keh .Product.Manager-Oracle
                        Beginning in ODP.NET 9.2.0.4, there's a new connection string attribute "validate connection" that should be set to true in order to validate connections coming out of the pool. By default, this property is set to false.

                        Keep in mind, this will produce a server round trip every time a you grab a connection from the pool. As such, if the likelihood of an invalid connection is rare, you should write your own error handling code that will grab a new connection from the pool.

                        In the next version of Oracle, ODP.NET will have a way to clear your connection pool if you know all your connection pool connections are invalid, such as what happens if you reboot the DB server machine.