ORA-12519, TNS:no appropriate service handler found

oracle hibernate session sessionfactory

31,188

Solution 1

Make sure your oracle listener offers the service 'XE'

  [bbrandt@limbo] ~/ $ lsnrctl status | grep XE
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    Default Service           XE
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
    Service "XE" has 1 instance(s).
      Instance "XE", status READY, has 1 handler(s) for this service...
    Service "XEXDB" has 1 instance(s).
      Instance "XE", status READY, has 1 handler(s) for this service...
    [bbrandt@limbo] ~/ $

Make sure you are able to connect to the service through sqlplus like this before you try any other client applications:

sqlplus username/password@localhost:1521/XE

Note! always connect to an oracle database using the SERVICE_NAME (hostname:1521/SERVICE_NAME). Connecting to an oracle database using the SID (hostname:1521:SID) .... is something oracle discouraged only 15 years ago!

Solution 2

i also faced the same problem. the reasons for this error are:

connections are not closed or idle for long time. due to this, the processes in oracle will exceed the limit.
Non-optimistic pool configuration in context.xml (apache tomcat).

the steps i have followed to fix this issue are:

check current processes count by using select * from v$resource_limit where resource_name = 'processes';
Click PerformGC button in jconsole.exe which is available in bin folder of jdk installed folder.By doing so, idle connections which are not closed will be garbage collected.

If above steps are not solved your problem, you can try by reducing maxIdle (The maximum number of connections that should be kept in the pool at all times.) in context.xml(apache tomcat)

Note : if heap size more , there is a possibility of more consumption of the db processes from the huge tenured Objects.

Solution 3

Try this solution at your end. It worked for me. Close the connection in try/catch block and just after closing the connection, write Thread.sleep(1000);

You can write it as follows:

finally {
    try {
        if (conn != null && !conn.isClosed())
            {
            conn.close();
            Thread.sleep(1000);
             }
        }
    catch (SQLException e) {
        e.printStackTrace();
    }
}

31,188

Author by

dolly

Updated on October 12, 2020

Comments

dolly over 3 years

I am closing the hibernate connection properly but i am getting this error after some clicks or after some page refreshes. whats the problem i cant understand. I have also checked the number of sessions its also increasing by the following command and program code is also given:

Managedbean code:

public List<TrxFile> getFileDetails() {
        //SessionFactory sf = HibernateUtill.HibernateUtil.getSessionFactory();
        Session sess = HibernateUtill.HibernateUtil.getSessionFactory().openSession();
        java.util.List l = null;
        try {
            Query q = sess.createQuery("from Files");
            l = q.list();
            Iterator itr = l.iterator();

            return l;
        } catch (HibernateException hbx) {
            hbx.printStackTrace();
        } finally {
            sess.flush();
            sess.clear();
            sess.close();
            //sf.close();
            HibernateUtill.HibernateUtil.getSessionFactory().close();
        }
        return l;
    }

error:

ERROR:   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

More detailed Error:

WARN:   HHH000342: Could not obtain connection to query metadata : 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

Info:   HHH000400: Using dialect: org.hibernate.dialect.OracleDialect
WARN:   HHH000063: The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead
WARN:   HHH000064: The OracleDialect dialect has been deprecated; use Oracle8iDialect instead
Info:   HHH000422: Disabling contextual LOB creation as connection was null
Info:   HHH000399: Using default transaction strategy (direct JDBC transactions)
Info:   HHH000397: Using ASTQueryTranslatorFactory
WARN:   SQL Error: 0, SQLState: null
ERROR:   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

Info:   HHH000043: Configuring from resource: /hibernate.cfg.xml
Info:   HHH000040: Configuration resource: /hibernate.cfg.xml
WARN:   HHH000223: Recognized obsolete hibernate namespace http://hibernate.sourceforge.net/. Use namespace http://www.hibernate.org/dtd/ instead. Refer to Hibernate 3.6 Migration Guide!
Info:   HHH000221: Reading mappings from resource: Pojos/TrxTender.hbm.xml
Info:   HHH000221: Reading mappings from resource: Pojos/CfgPaths.hbm.xml
Severe:   org.hibernate.exception.GenericJDBCException: Could not open connection
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:56)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1859)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
    at org.hibernate.loader.Loader.doQuery(Loader.java:900)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    at org.hibernate.loader.Loader.doList(Loader.java:2526)
    at org.hibernate.loader.Loader.doList(Loader.java:2512)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
    at org.hibernate.loader.Loader.list(Loader.java:2337)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1269)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at ManagedBean.FileUpload.getFileDetails(FileUpload.java:560)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at javax.el.BeanELResolver.getValue(BeanELResolver.java:363)
    at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:176)
    at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:203)
    at com.sun.el.parser.AstValue.getValue(AstValue.java:140)
    at com.sun.el.parser.AstValue.getValue(AstValue.java:204)
    at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:226)
    at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:109)
    at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194)
    at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182)
    at javax.faces.component.UIData.getValue(UIData.java:732)
    at org.primefaces.component.api.UIData.getDataModel(UIData.java:629)
    at javax.faces.component.UIData.getRowCount(UIData.java:356)
    at org.primefaces.component.api.UIData.calculateFirst(UIData.java:175)
    at org.primefaces.component.datatable.DataTableRenderer.preRender(DataTableRenderer.java:115)
    at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:83)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:924)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1863)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:176)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:894)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1856)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1859)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1859)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:443)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:120)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:219)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:72)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
    at java.lang.Thread.run(Thread.java:745)
Caused by: 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: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
Severe:   :839)
    at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:204)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
    ... 84 more

command to find the current sessions: