4 Replies Latest reply: Mar 21, 2013 9:07 AM by oraman RSS

    V$RESOURCE_LIMIT and TNS-12602 contradiction

    oraman
      hi experts,

      I get regularely this error:

      oracle TNS-12602: TNS: Connection Pooling limit reached

      however according to this view there is no limit reached:

      select * from v$resource_limit where resource_name in ('processes','sessions');

      RESOURCE_NAME          CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION               LIMIT_VALUE

      processes                         94     138     600                         600
      sessions                    107     220     1008                         1008


      my environment:
      oracle 11gr2
      redhat

      is my perception somehow wrong?


      thank you
        • 1. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
          sb92075
          oraman wrote:
          hi experts,

          I get regularely this error:

          oracle TNS-12602: TNS: Connection Pooling limit reached

          however according to this view there is no limit reached:

          select * from v$resource_limit where resource_name in ('processes','sessions');

          RESOURCE_NAME          CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION               LIMIT_VALUE

          processes                         94     138     600                         600
          sessions                    107     220     1008                         1008


          my environment:
          oracle 11gr2
          redhat

          is my perception somehow wrong?


          thank you
          does application utilize Connection Pooling?
          12602, 00000, "TNS: Connection Pooling limit reached"
          // *Cause:  The operation failed because maximum active current connections has
          //          been reached. It may not be a real error when the Connection
          //          Pooling feature is enabled. It is possible that the application
          //          later reissues the operation and successfully grabs the connection
          //          pool slot and proceeds.
          // *Action: This is an internal error. Enable tracing and attempt to repeat the
          //          error. If it occurs again, contact Oracle Customer
          //          Support.
          • 2. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
            TSharma-Oracle
            Do you have dedicated server environment OR MTS? If yes, can you post your mts dispatcher setting?
            • 3. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
              oraman
              hello again,

              no I dont use mts:

              $ cat initmydb.ora | grep dispatcher
              *.dispatchers='(PROTOCOL=TCP)(DISPATCHERS=1)(SESSIONS=900)'
              $ cat initmydb.ora | grep mts
              no output here

              and no it seems like there is no Connection Pooling:
              client tnsnames.ora:

              conn1 =
              (description =
              (address = (host = server1)(protocol = tcp)(port = 1521))
              (address = (host = server2)(protocol = tcp)(port = 1521))
              (address = (host = server3)(protocol = tcp)(port = 1521))
              (failover = yes)
              (connect_data =
              (service_name = conn1.webadress.com)
              (failover_mode =
              (type = select)
              (method = basic)
              )
              )
              )

              I plan to change it to shared on all clients and hope that it will solve the issue!? :

              conn1 =
              (description =
              (address = (host = server1)(protocol = tcp)(port = 1521))
              (address = (host = server2)(protocol = tcp)(port = 1521))
              (address = (host = server3)(protocol = tcp)(port = 1521))
              (failover = yes)
              (connect_data =
              (service_name = conn1.webadress.com)
              (failover_mode =
              (type = select)
              (method = basic)
              )
              (SERVER=shared)
              )
              )

              or is something else the core of the issue?
              • 4. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
                oraman
                any acknowledgment or criticism?