10 Replies Latest reply: Mar 31, 2006 7:35 AM by 500322 RSS

    ORA-12519 using thin JDBC client

    greenec
      This error has popped up today after things had been working. I'm still trying to figur eout what I have changed that caused the problem, but am hoping to get some pointers from here.

      Here is the error text received running my Java app:
      ORA-12519, TNS:no appropriate service handler found
      The Connection descriptor used by the client was:
      localhost:1521:XE


      Or from within Eclipse debug:
      java.sql.SQLException: Cannot get connection for URL jdbc:oracle:thin:@localhost:1521:XE : Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
           at org.enhydra.jdbc.standard.StandardDataSource.getConnection(StandardDataSource.java:158)
           at org.enhydra.jdbc.standard.StandardPooledConnection.<init>(StandardPooledConnection.java:57)
           at org.enhydra.jdbc.standard.StandardXAConnection.<init>(StandardXAConnection.java:70)


      Looking up this error online tells me this:
      ORA-12519: TNS:no appropriate service handler found
      Cause: The listener could not find any available service handlers that are appropriate for the client connection.
      Action: Run "lsnrctl services" to ensure that the instance(s) have registered with the listener, and are accepting connections.

      So, if I run "lsnrctl services" then I get this:
      LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Beta on 16-DEC-2005 12:07:23
      
      Copyright (c) 1991, 2005, Oracle.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      Services Summary...
      Service "CLRExtProc" has 1 instance(s).
        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
      Service "PLSExtProc" has 1 instance(s).
        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
      Service "XEXDB" has 1 instance(s).
        Instance "xe", status READY, has 1 handler(s) for this service...
          Handler(s):
            "D000" established:26 refused:0 current:0 max:1002 state:ready
               DISPATCHER <machine: MSPPCXX4609, pid: 4172>
               (ADDRESS=(PROTOCOL=tcp)(HOST=msppcxx4609)(PORT=1374))
      Service "XE_XPT" has 1 instance(s).
        Instance "xe", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:43 refused:0 state:blocked
               LOCAL SERVER
      Service "xe" has 1 instance(s).
        Instance "xe", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:43 refused:0 state:blocked
               LOCAL SERVER
      A lot of "UNKNOWN"s and "blocked" states.
      My tnsnames.ora entry looks like this:
      XE =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
           )
         )
      The sqlnet.ora is empty (all defaults). The Listener.ora looks like this:
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oracle\express10g\app\oracle\product\10.2.0\server)
            (PROGRAM = extproc)
          )
          (SID_DESC =
            (SID_NAME = CLRExtProc)
            (ORACLE_HOME = C:\oracle\express10g\app\oracle\product\10.2.0\server)
            (PROGRAM = extproc)
          )
        )
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          )
        )
      
      DEFAULT_SERVICE_LISTENER = (XE)
      My specialties don't include DBA, only Java, so I can't tell if the db is ok or not. This is all on my dev laptop, which previously had 9i that I uninstalled to then install XE.

      The HTMLDB works fine. Sqlplus and TOAD work fine. Yesterday they were intermittant until I figured out to turn off the WinXP firewall (still off). I have only ever used the thin client from my Java apps. I have tried re-downloading the recent 10.2.0 driver, as well as the -g version. Am I correct that the OCI (type 2) version will not work with XE?
        • 1. Re: ORA-12519 using thin JDBC client
          greenec
          Still haven't figured this out. I've tried various 1.4.2 jdk (Sun, IBM) with no luck. It appears slightly intermittant, as about 5% of the time, the JDBC call will work. I haven't yet been able to figure out what is different that 5% of the time.

          A longer version of the same stack trace:
          ORA-12519, TNS:no appropriate service handler found
          The Connection descriptor used by the client was:
          localhost:1521:XE
          
               at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
               at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:380)
               at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
               at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:441)
               at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
               at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
               at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:839)
               at java.sql.DriverManager.getConnection(DriverManager.java:539)
               at java.sql.DriverManager.getConnection(DriverManager.java:189)
          After a little more testing, I think it may have something to do with the number of JVMs that I have connecting to the DB. I will do some research on that.

          Message was edited by:
          greenec
          • 2. Re: ORA-12519 using thin JDBC client
            greenec
            It is related to the number of JVMs I have running. I have various "container" VMs that have DAOs that use JDBC to access XE, plus an OpenJMS JVM that uses XE as a datastore. I've reduced the number of container VM's from 3 to 1 (plus OpenJMS) and I no longer receive any error.

            If there is a hard-limit built into XE that is causing this that I can configure around, please let me know. Otherwise, if I find out something unusual that my code is doing that might have caused this, I will update this thread.
            • 3. Re: ORA-12519 using thin JDBC client
              241066
              Can you send me an exact description of what you are doing ? There is some resource management control that is done in XE so that no more than 1 CPU of work is being done at any one time, but I doubt that this is causing you a problem. However .... email me direct at mark.townsend@oracle.com
              • 4. Re: ORA-12519 using thin JDBC client
                473737
                I have the same problem. This is the code that produces the error:
                public class Test
                {
                  public static void main(String[] args) throws Exception
                  {
                    DriverManager.registerDriver((Driver) Class.forName(
                        "oracle.jdbc.driver.OracleDriver").newInstance());
                
                    int i = 0;
                    while (true)
                    {
                      System.out.println("Connection "+(i++));
                
                      //connect
                      Connection c = DriverManager.getConnection(
                          "jdbc:oracle:thin:@localhost:1521:XE", "admercucio", "q");
                
                      //do something
                      Statement stm=c.createStatement();
                      stm.execute("select * from dual");
                      stm.close();
                
                      //close
                      c.close();
                    }
                  }
                }
                This is the output:
                -----------------------------------------------------------------------
                Connection 0
                Connection 1
                Connection 2
                Connection 3
                Connection 4
                Connection 5
                Connection 6
                Connection 7
                Connection 8
                Connection 9
                Connection 10
                Connection 11
                Connection 12
                Connection 13
                Connection 14
                Connection 15
                Connection 16
                Connection 17
                Connection 18
                Connection 19
                Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:
                ORA-12519, TNS:no appropriate service handler found
                The Connection descriptor used by the client was:
                localhost:1521:XE

                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
                at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
                at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
                at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
                at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
                at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
                at java.sql.DriverManager.getConnection(DriverManager.java:512)
                at java.sql.DriverManager.getConnection(DriverManager.java:171)
                at com.quaerens.mercucio.kernel.misc.Updater.main(Updater.java:20)
                -----------------------------------------------------------------------

                My software: Linux Fedora Core 4 (64 bits), with Sun j2sdk1.4.2_04
                My hardware: Dual AMD Opteron (246)
                • 5. Re: ORA-12519 using thin JDBC client
                  473737
                  I've tried to get connections adding a pause at the end of the loop:
                      while (true)
                      {
                        System.out.println("Connection "+(i++));
                   
                        //connect
                        Connection c = DriverManager.getConnection(
                            "jdbc:oracle:thin:@localhost:1521:XE", "admercucio", "q");
                   
                        //do something
                        Statement stm=c.createStatement();
                        stm.execute("select * from dual");
                        stm.close();
                   
                        //close
                        c.close();
                  
                        //pause
                        Thread.sleep(1000);
                      }
                  The counter reached over 300 connections without problems.

                  I've tried to get connections adding a pause at the end of the loop AND without closing connections. Same results: over 300 connections without problems.

                  I think it may have something to do with the number of connection requests within a short period of time.
                  • 6. Re: ORA-12519 using thin JDBC client
                    473737
                    I've tried to run serveral instances of the test program (using a pause at the end of each loop). It fails. The older instances fail (ORA-12519) when new instances are started. Sometimes the new instances fails and older instances continue.
                    • 7. Re: ORA-12519 using thin JDBC client
                      473737
                      Ok, this seems to be a workarround to this kind of logon storm problem:
                        public static void main(String[] args) throws Exception
                        {
                          DriverManager.registerDriver((Driver) Class.forName(
                              "oracle.jdbc.driver.OracleDriver").newInstance());
                      
                          int i = 0;
                          while (true)
                          {
                            System.out.println("Connection "+(i++));
                            Connection c=null;
                            while(c==null)
                            {
                              try
                              {
                                c = DriverManager.getConnection(
                                    "jdbc:oracle:thin:@localhost:1521:XE", "admercucio", "q");
                              }
                              catch(SQLException x)
                              {
                                if(x.getMessage().indexOf("ORA-12519")!=-1)
                                {
                                  //sleep for a while
                                  System.out.println("Sleeping...");
                                  Thread.sleep(500);
                                }
                                else
                                {
                                  throw x;
                                }
                              }
                            }
                            
                            Statement stm=c.createStatement();
                            stm.execute("select * from dual");
                            stm.close();
                            
                            Thread.sleep(250);
                          }
                        }
                      If a run a single instance of this test, everything is OK. However, running serveral instances it starts to sleep. I'm not sure but it seems that oracle can't handle more than seven or eight connections requests at (almost) the same time. This connection requests could come from the same application (jvm) or could come from different applications.
                      It would be great if the listener service (or the JDBC driver) could handle this naps.

                      This is the output of one of the five instances I've started at the same time:

                      (.....)
                      Connection 15
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      <<<----------------- (here I killed one of the instances)
                      Connection 16
                      Connection 17
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      <<<----------------- (killed other)
                      Connection 18
                      Connection 19
                      Connection 20
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      Sleeping...
                      <<<----------------- (killed other)
                      Connection 21
                      Connection 22
                      Connection 23
                      Connection 24
                      Connection 25
                      Connection 26
                      Connection 27
                      Sleeping...
                      Sleeping...
                      Connection 28
                      Connection 29
                      Connection 30
                      Connection 31
                      Connection 32
                      Connection 33
                      Connection 34
                      Connection 35
                      Connection 36
                      Connection 37
                      Sleeping...
                      Connection 38
                      Connection 39
                      Connection 40
                      <<<----------------- (killed last)
                      Connection 41
                      Connection 42
                      Connection 43
                      Connection 44
                      Connection 45
                      Connection 46
                      Connection 47
                      Connection 48
                      ...
                      (no more naps)

                      NOTE: I don't want to make some kind of "connection eater" application. I'm just trying to simulate a few concurrent or almost concurrent connection request situation (from the same or different jvms).
                      • 8. Re: ORA-12519 using thin JDBC client
                        241066
                        According to the developers this is expected behavior - here are their comments

                        "What he is observing is expected behavior. He has the "processes"
                        init.ora parameter set to 40 on the server side, which on my XE
                        install allows 19 new connections (taking the backgrounds into
                        account). PMON provides this information to the listener as part
                        of it's periodic load updates to the listener, and whenever
                        something changes on the server side, it will keep the listener
                        updated by sending load updates. So, if someone performs 19
                        connect/disconnect in a tight loop, listener will observe the
                        19 slots used up for the connects, and until the next update from
                        PMON comes, it won't know that the 19 servers have exited (only
                        the connects go through the listener, the disconnects don't).
                        The update should come right away, but obviously there is a window
                        of time when listener thinks all slots are currently occupied,
                        and hence, it will refuse connections for that interval. This is
                        only a short race-condition, which is un-avoidable."
                        • 9. Re: ORA-12519 using thin JDBC client
                          486190
                          I had the same problem with a jdbc thin client.

                          The workaround i used: change the xe parameter: PROCESSES. I changed it with 150.

                          I created first the spfile from the pfile and execute the sql 'ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE'

                          Now, the problem is closed.

                          Emmanuel
                          • 10. Re: ORA-12519 using thin JDBC client
                            500322
                            "Note: The use of the DriverManager class to establish a connection to a database is deprecated." --
                            http://download-uk.oracle.com/docs/cd/B25329_01/doc/appdev.102/b25320/getconn.htm

                            To open a connection use something like this:

                            OracleDataSource ods = new OracleDataSource();
                            ods.setUser("username");
                            ods.setPassword("password");
                            ods.setURL("jdbc:oracle:thin:@localhost:1521:XE");
                            ods.getConnection();

                            Instead of this:

                            Class.forName("oracle.jdbc.OracleDriver");
                            DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "username", "password");