Ошибка ora 12519 tns no appropriate service handler found - Ремонт и установка крупной бытовой техники

Don’t know if this will be everybody’s answer, but after some digging, here’s what we came up with.

The error is obviously caused by the fact that the listener was not accepting connections, but why would we get that error when other tests could connect fine (we could also connect no problem through sqlplus)? The key to the issue wasn’t that we couldn’t connect, but that it was intermittent

After some investigation, we found that there was some static data created during the class setup that would keep open connections for the life of the test class, creating new ones as it went. Now, even though all of the resources were properly released when this class went out of scope (via a finally{} block, of course), there were some cases during the run when this class would swallow up all available connections (okay, bad practice alert — this was unit test code that connected directly rather than using a pool, so the same problem could not happen in production).

The fix was to not make that class static and run in the class setup, but instead use it in the per method setUp and tearDown methods.

So if you get this error in your own apps, slap a profiler on that bad boy and see if you might have a connection leak. Hope that helps.

Источник

Here’s a quick post to share a quick fix to a problem that I faced this morning while trying to setup a proof-of-concept environment for the Oracle Service Bus. If you think I’m writing Greek, then you may skip this rather g(r)eeky post. However, if you’re like me having this problem and wondering why there are so little post online that easily explains what the problem is and how to quickly fix it, then here’s a quick tip to save your time trying to figure out what’s wrong.

Perhaps I should have consulted a more experienced DBA. Afterall, a database to me is just a database, and it should just work. So, when WebLogic Server threw me the “ORA-12519: TNS:no appropriate service handler found” exception, I too wondered if I was reading Greek, or perhaps maybe even Ebonian. I knew it had to do with server’s connection to the Oracle DB XE that I was using for the environment setup, but I just couldn’t figure out what it was, especially since the connection test worked!
Googling “ORA-12519: TNS:no appropriate service handler found” initially didn’t help much. Most of the search results mentions the following:-

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

This is perhaps due to the fact that the Oracle documentation on this error states exactly that. And the database listener is running alright. So that’s not going to solve my problem.

After fumbling about a little lot, and spending hours trying to ‘rambo’ myself over this problem, I figured that it might be due to the rather limited concurrent connections or db processes that was allowed by this license-free Express Edition. So there I went searching for the command to increase something around this area for Oracle Database XE. And sure enough, I found recommendations to do this exact step on this ‘limited’ version of the database.

Fixing the ORA-12519: TNS:no appropriate service handler found error

Cutting the long story short, all you need to do is the few simple steps as the following:-

Run SQL*Plus and login as SYSTEM. You should know what password you’ve used during the installation of Oracle DB XE.

Run the command “alter system set processes=150 scope=spfile;” in the SQL*Plus

VERY IMPORTANT: Restart the database.

ORA-12519: TNS

Once done. you should no longer get the ORA-12519: TNS:no appropriate service handler found error!

So there you go, a simple line of command to save hours of hair pulling trying to figure out what’s wrong. Don’t blame yourself for being incompetent. Just blame poor documentation and console yourself that this is a case where even if you RTFM, it won’t help anyway. But perhaps getting yourself skilled up with a book like the following won’t hurt either. 🙂

Источник

I am trying to insert into Oracle database which has two columns-

ID         Primary Key     varchar2 (4000)
ACCOUNT                    varchar2 (4000)

I wrote a Multithreaded program for that. And each thread is using unique id every time to insert into ID column as ID is primary key.

The only problem that I am facing at some point is- The below code, throws following exception after running for few seconds.

 1) Null Pointer Exception
 2) java.sql.SQLException: Listener refused the connection with the following error:ORA-12519, TNS:no appropriate service handler found

I am not able to find any root cause of this problem in my code as everything is looking good to me. As I am closing each and every connection properly. Then how does this NPE is getting thrown and other exception as well?

    ExecutorService service = Executors.newFixedThreadPool(10);

    try {
        // queue some tasks
        for (int i = 0; i < 100 * 10; i++) {
            service.submit(new ThreadTask());
        }
        service.shutdown();
        service.awaitTermination(Long.MAX_VALUE, TimeUnit.SECONDS);

        while (!service.isTerminated()) {

        }
    } catch (InterruptedException e) {
        LOG.warn("Threw a Interrupted Exception in" + XMPLoadTest.class.getSimpleName()
                + ".XMPLoadTest: boss told me to stop...Not my fault!!");
    }

Below is ThreadTask class-

class ThreadTask implements Runnable {

    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String CONNECTION = "jdbc:oracle:thin:@localhost:1521:ORCL";
    private static final String USER = "scott";
    private static final String PASSWORD = "tiger";
    private static Connection dbConnection = null;
    private static PreparedStatement preparedStatement = null;

    private static final AtomicInteger id = new AtomicInteger(1);

    private final static Logger LOG = Logger.getLogger(ThreadTask.class.getName());

    public ThreadTask() {

    }

    @Override
    public void run() {

        try {

            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(Constants.INSERT_ORACLE_SQL);

            preparedStatement.setString(1, String.valueOf(id.getAndIncrement()));
            preparedStatement.setString(2, Constants.A_ACCOUNT);

            preparedStatement.executeUpdate();

        } catch (Exception e) {
            // NPE getting thrown here/And second exception as well
            LOG.error("Threw a SQLException in " + getClass().getSimpleName(), e);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                    preparedStatement = null;
                } catch (SQLException e) {
                    //Oouch...
                    LOG.error("Threw a SQLException in finally block of prepared statement " + getClass().getSimpleName(), e);
                }
            }
            if (dbConnection != null) {
                try {
                    dbConnection.close();
                    dbConnection = null;
                } catch (SQLException e) {
                    //Better go and look for SQL.
                    LOG.error("Threw a SQLException in finally block of dbConnection " + getClass().getSimpleName(), e);
                }
            }
        }
    }

    /**
     * Attempts to establish a connection to the given database URL
     * 
     * @return the db connection
     */
    private Connection getDBConnection() {

        Connection dbConnection = null;

        try {
            Class.forName(XMP_DRIVER);
            dbConnection = DriverManager.getConnection(CONNECTION, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            LOG.error("Threw a ClassNotFoundException in " + getClass().getSimpleName(), e);
        } catch (SQLException e) {
            //DAMN! I'm not....
            LOG.error("Threw a SQLException in " + getClass().getSimpleName(), e);
        } catch (Exception e) {
            LOG.error("Threw a Exception in " + getClass().getSimpleName(), e);
        }

        return dbConnection;
    }
}

Is there any potential problem here with my code? I am more worried about this NPE.

StackTrace:

19:14:28,372 ERROR ThreadTask:187 - Threw a SQLException in ThreadTask
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at java.sql.DriverManager.getConnection(DriverManager.java:322)
    at java.sql.DriverManager.getConnection(DriverManager.java:358)
    at com.ebay.xmp.lnp.ThreadTask.getDBConnection(XMPLoadTest.java:179)
    at com.ebay.xmp.lnp.ThreadTask.run(XMPLoadTest.java:137)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:452)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
    at java.util.concurrent.FutureTask.run(FutureTask.java:149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
    at java.lang.Thread.run(Thread.java:736)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
    ... 14 more
19:14:28,376 ERROR ThreadTask:139 - Threw a SQLException in ThreadTask
java.lang.NullPointerException
    at com.ebay.xmp.lnp.ThreadTask.run(XMPLoadTest.java:137)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:452)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
    at java.util.concurrent.FutureTask.run(FutureTask.java:149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
    at java.lang.Thread.run(Thread.java:736)

Источник

Don’t know if this will be everybody’s answer, but after some digging, here’s what we came up with.

The fix was to not make that class static and run in the class setup, but instead use it in the per method setUp and tearDown methods.

So if you get this error in your own apps, slap a profiler on that bad boy and see if you might have a connection leak. Hope that helps.

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

This is perhaps due to the fact that the Oracle documentation on this error states exactly that. And the database listener is running alright. So that’s not going to solve my problem.

Cutting the long story short, all you need to do is the few simple steps as the following:-

Run SQL*Plus and login as SYSTEM. You should know what password you’ve used during the installation of Oracle DB XE.

Run the command “alter system set processes=150 scope=spfile;” in the SQL*Plus

VERY IMPORTANT: Restart the database.

ORA-12519: TNS

Once done. you should no longer get the ORA-12519: TNS:no appropriate service handler found error!

Oracle Database is a large and complex system. ORA-12519 Error is a frustrating “TNS No Appropriate Service Handler Found” error in Oracle Database that can be troublesome to debug. It can be caused by a variety of factors including a mismatch in Oracle Home information, or corrupted listener.ora file. In this article, we will discuss troubleshooting techniques and ways to fix the ORA-12519 error.

Step-By-Step Solution

1. Check Oracle Home

The ORA-12519 error may be due to the incorrect version of the installed Oracle Home files, or the presence of mutliple Oracle Homes on your server. To fix this issue:

Make sure that the version of the Oracle Home is correct – i.e. the Oracle Home of the database instance has to match the Oracle Home of the listener.ora file.
If there are multiple Oracle Homes installed, ensure that the right Oracle Home is set in the environment variables. This will prevent the listener.ora file from being picked up from the wrong Oracle Home.

2. Check the Listener.ora File

The listener.ora file must be present and correct in the Oracle Home directory. Oracle Listener must also be properly configured in order to start the listener. If the file is corrupted or not present on the server, then create it manually and ensure that it contains valid entries.

3. Troubleshoot Network Connectivity

If the above steps do not resolve the issue, there may be a problem with the network connectivity. Check the server for any network-related issues and ensure that the proper ports are open. Also, try running the netca utility to re-configure the listener.

4. Check Server Logs

If all else fails, check the server logs for any additional information that may point to the root cause of the error. This may provide clues on where to look for further troubleshooting.

FAQs

What is ORA-12519?

ORA-12519 is an Oracle Database error which indicates that there is a problem connecting to the database instance.

What causes ORA-12519 error to appear?

ORA-12519 error can occur when the Oracle Home information is incorrect, the listener.ora file is corrupted, or there is an issue with the server’s network connectivity.

How do I fix ORA-12519 error?

To fix ORA-12519 error, check the Oracle Home information, ensure that there is a valid listener.ora file, troubleshoot network connectivity, and check the server logs for additional clues.

Sources

Troubleshooting ORA-12519 ERRROR

Fixing ORA-12519 Error

ORA-12519: TNS: No Appropriate Service Handler Found

Expert answers
for every coding challenge.

Get the information you need to solve your programming problems on lxadm.com, the expert-driven alternative to StackOverflow

Lxadm.com

Great! You’ve successfully signed up.

Welcome back! You’ve successfully signed in.

You’ve successfully subscribed to Lxadm.com.

Your link has expired.

Success! Check your email for magic link to sign-in.

Success! Your billing info has been updated.

Your billing was not updated.

I am trying to insert into Oracle database which has two columns-

ID         Primary Key     varchar2 (4000)
ACCOUNT                    varchar2 (4000)

I wrote a Multithreaded program for that. And each thread is using unique id every time to insert into ID column as ID is primary key.

The only problem that I am facing at some point is- The below code, throws following exception after running for few seconds.

 1) Null Pointer Exception
 2) java.sql.SQLException: Listener refused the connection with the following error:ORA-12519, TNS:no appropriate service handler found

    ExecutorService service = Executors.newFixedThreadPool(10);

    try {
        // queue some tasks
        for (int i = 0; i < 100 * 10; i++) {
            service.submit(new ThreadTask());
        }
        service.shutdown();
        service.awaitTermination(Long.MAX_VALUE, TimeUnit.SECONDS);

        while (!service.isTerminated()) {

        }
    } catch (InterruptedException e) {
        LOG.warn("Threw a Interrupted Exception in" + XMPLoadTest.class.getSimpleName()
                + ".XMPLoadTest: boss told me to stop...Not my fault!!");
    }

Below is ThreadTask class-

class ThreadTask implements Runnable {

    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String CONNECTION = "jdbc:oracle:thin:@localhost:1521:ORCL";
    private static final String USER = "scott";
    private static final String PASSWORD = "tiger";
    private static Connection dbConnection = null;
    private static PreparedStatement preparedStatement = null;

    private static final AtomicInteger id = new AtomicInteger(1);

    private final static Logger LOG = Logger.getLogger(ThreadTask.class.getName());

    public ThreadTask() {

    }

    @Override
    public void run() {

        try {

            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(Constants.INSERT_ORACLE_SQL);

            preparedStatement.setString(1, String.valueOf(id.getAndIncrement()));
            preparedStatement.setString(2, Constants.A_ACCOUNT);

            preparedStatement.executeUpdate();

        } catch (Exception e) {
            // NPE getting thrown here/And second exception as well
            LOG.error("Threw a SQLException in " + getClass().getSimpleName(), e);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                    preparedStatement = null;
                } catch (SQLException e) {
                    //Oouch...
                    LOG.error("Threw a SQLException in finally block of prepared statement " + getClass().getSimpleName(), e);
                }
            }
            if (dbConnection != null) {
                try {
                    dbConnection.close();
                    dbConnection = null;
                } catch (SQLException e) {
                    //Better go and look for SQL.
                    LOG.error("Threw a SQLException in finally block of dbConnection " + getClass().getSimpleName(), e);
                }
            }
        }
    }

    /**
     * Attempts to establish a connection to the given database URL
     * 
     * @return the db connection
     */
    private Connection getDBConnection() {

        Connection dbConnection = null;

        try {
            Class.forName(XMP_DRIVER);
            dbConnection = DriverManager.getConnection(CONNECTION, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            LOG.error("Threw a ClassNotFoundException in " + getClass().getSimpleName(), e);
        } catch (SQLException e) {
            //DAMN! I'm not....
            LOG.error("Threw a SQLException in " + getClass().getSimpleName(), e);
        } catch (Exception e) {
            LOG.error("Threw a Exception in " + getClass().getSimpleName(), e);
        }

        return dbConnection;
    }
}

Is there any potential problem here with my code? I am more worried about this NPE.

StackTrace:

19:14:28,372 ERROR ThreadTask:187 - Threw a SQLException in ThreadTask
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at java.sql.DriverManager.getConnection(DriverManager.java:322)
    at java.sql.DriverManager.getConnection(DriverManager.java:358)
    at com.ebay.xmp.lnp.ThreadTask.getDBConnection(XMPLoadTest.java:179)
    at com.ebay.xmp.lnp.ThreadTask.run(XMPLoadTest.java:137)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:452)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
    at java.util.concurrent.FutureTask.run(FutureTask.java:149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
    at java.lang.Thread.run(Thread.java:736)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
    ... 14 more
19:14:28,376 ERROR ThreadTask:139 - Threw a SQLException in ThreadTask
java.lang.NullPointerException
    at com.ebay.xmp.lnp.ThreadTask.run(XMPLoadTest.java:137)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:452)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
    at java.util.concurrent.FutureTask.run(FutureTask.java:149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
    at java.lang.Thread.run(Thread.java:736)

Problem

Superuser runs a database optimize. An error appears.

Symptom

Standard Error
Number: 5
Source: FrangoDirect.Optimize.DeletePeriodZeroValues#ControllerProxyClient
Description: Server was unable to process request. —> ORA-12519: TNS:no appropriate service handler found
at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object Source, Object Description, Object HelpFile, Object HelpContext)
at Cognos.Controller.Common.RaiseErrCli.RaiseError(Int32 lErrNo, String sErrSource, String sErrDesc, String sErrHelpFile, Int32 lErrHelpContext)
at Cognos.Controller.Direct.Optimize.DeletePeriodZeroValues(Int32 nYear)
at Cognos.Controller.Forms.Form.frmOptimize.IRun_DoRun()

Cause

Oracle database server (hosting the Controller database) has run out of available processes (and is therefore stopping new connections to be made to the server).

Resolving The Problem

Increase the maximum number of possible processes by increasing the value for PROCESSES on the Oracle database server (for example from 150 to 300).

Steps:

See Scenario #1 of separate IBM Technote #1603472.

Related Information

[{«Product»:{«code»:»SS9S6B»,»label»:»IBM Cognos Controller»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Controller»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»10.2.0″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Platform

Oracle database 11gR2 (11.2.0.2.2)

Problem

New database connection attempts to Oracle database 11gR2 fail with following Oracle error:

Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found [SQLCode: 12519], [SQLState: 66000] None None

Possible reason

One of the most common reasons for the TNS-12516 and/or TNS-12519 Oracle error being reported on Oracle 11gR2 is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS Listener become “Blocked” and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.

Solution

There are different ways to check if a database instance reaches the maximum number of processes on Oracle 11gR2. One of the quickest SQL see below:

select * from v$resource_limit where resource_name = 'processes';

If this is the case, to fix this Oracle error, increase the PROCESSES parameter value to allow the Oracle database Listener to spawn more server process as required in your environment.

It might be further investigation required though.

Update: 16-Dec-2011

Sometimes when listener throws a TNS error the number of actual processes can be below the limit. See below why this can happen.
When the listener believes the current number of connections has reached maximum load, it may set the state of the service handler for an instance to “blocked” and begin refusing incoming client connections with either of the following errors: ora-12519 or ora-12516. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is then accepting connections connectivity resumes. The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated. Only when PMON updates the listener via SERVICE_UPDATE is the listener informed of current load. Since this can take as long as 10 minutes, there can be a difference between the current instance load according to the listener and the actual instance load.

If you really reached the processes limit and it stays this way for some time you will probably get the following error trying to connect:

ERROR: ORA-00020: maximum number of processes (%s) exceeded

There are a couple of ‘workarounds’ for this issue … but no real solution at this time (even for Oracle 11.2) since you are not capable to connect to the DB even as SYSDBA:

– Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and kill some sessions:
alter system kill session ‘SID, SERAL#’;

– Stop a less critical application, DB monitoring, etc.

– Kill one or more of the client connections on OS level:

* UNIX: kill -9
* WINDOWS: using ORAKILL …

If none above helps and the error occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

Enjoyed this article? Please share it with others using the social site of your choice:

Источник

Don’t know if this will be everybody’s answer, but after some digging, here’s what we came up with.

The fix was to not make that class static and run in the class setup, but instead use it in the per method setUp and tearDown methods.

So if you get this error in your own apps, slap a profiler on that bad boy and see if you might have a connection leak. Hope that helps.

Problem

While importing Contributor applications through the Deployment tool in the CAC, many errors are written to PlanningErrorLog.csv :
Unable to open connection using connection string of Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)))(CONNECT_DATA=(SID=tnsalias)));User ID=username;Password=*********; and timeout of 15 seconds.
ORA-12519: TNS:no appropriate service handler found

Cause

Oracle server tuning is insufficient : there are not enough available processes to handle all requests sent by Planning.

Resolving The Problem

Increase the ‘processes’ parameter on your Oracle database server (for example to 300).
Note: Please consult your Oracle database administrator with respect to the setting of Oracle process parameters.

Steps:
The steps vary depending on whether your Oracle database server is using SPFILE or not.
If not using SPFILE, check the processes parameter in the init<sid>.ora file and increase the value.
Alternatively, if using an SPFILE, then run a command (SQLPLUS) similar to the following:
ALTER SYSTEM SET processes=300 scope=SPFILE;
[The above example sets processes to 300].

[{«Product»:{«code»:»SSPN2D»,»label»:»Cognos Planning»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Contributor»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»10.2;10.1.1″,»Edition»:»All Editions»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

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

This is perhaps due to the fact that the Oracle documentation on this error states exactly that. And the database listener is running alright. So that’s not going to solve my problem.

Cutting the long story short, all you need to do is the few simple steps as the following:-

Run SQL*Plus and login as SYSTEM. You should know what password you’ve used during the installation of Oracle DB XE.

Run the command “alter system set processes=150 scope=spfile;” in the SQL*Plus

VERY IMPORTANT: Restart the database.

ORA-12519: TNS

Once done. you should no longer get the ORA-12519: TNS:no appropriate service handler found error!

Platform

Oracle database 11gR2 (11.2.0.2.2)

Problem

New database connection attempts to Oracle database 11gR2 fail with following Oracle error:

Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found [SQLCode: 12519], [SQLState: 66000] None None

Possible reason

Solution

There are different ways to check if a database instance reaches the maximum number of processes on Oracle 11gR2. One of the quickest SQL see below:

select * from v$resource_limit where resource_name = 'processes';

If this is the case, to fix this Oracle error, increase the PROCESSES parameter value to allow the Oracle database Listener to spawn more server process as required in your environment.

It might be further investigation required though.

Update: 16-Dec-2011

If you really reached the processes limit and it stays this way for some time you will probably get the following error trying to connect:

ERROR: ORA-00020: maximum number of processes (%s) exceeded

There are a couple of ‘workarounds’ for this issue … but no real solution at this time (even for Oracle 11.2) since you are not capable to connect to the DB even as SYSDBA:

– Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and kill some sessions:
alter system kill session ‘SID, SERAL#’;

– Stop a less critical application, DB monitoring, etc.

– Kill one or more of the client connections on OS level:

* UNIX: kill -9
* WINDOWS: using ORAKILL …

If none above helps and the error occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

Enjoyed this article? Please share it with others using the social site of your choice:

In the context of a Java/Liferay/JackRabbit-Setting developer wants to export all stored files from database to filesystem for migration-purposes. After some uncertain time this always runs repeatedly on errors of this kind:

12:19:31,488 DEBUG [MaintenanceUtil:64] Executing com.liferay.portal.convert.ConvertDocumentLibrary
12:19:31,497 INFO  [ConvertProcess:41] Starting conversion for com.liferay.portal.convert.ConvertDocumentLibrary
12:19:31,537 DEBUG [MaintenanceUtil:64] Migrating 276 document library files
12:21:07,739 ERROR [DatabaseFileSystem:225] failed to initialize file system
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
(stacktrace follows...)

After this error the application goes on until next occurrence of that error.

Monitoring the listener.log while executing the migration-application shows a massive increase of connection-requests.

[oracle@serv1 ~]$ tail -f ./diag/tnslsnr/serv1/listener/trace/listener.log | egrep -i "hostdb|ORA-|TNS-"


28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57601)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57602)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57603)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57604)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57605)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57606)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57607)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57608)) * establish * hostdb * 12519
TNS-12519: TNS:no appropriate service handler found
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57609)) * establish * hostdb * 12519
TNS-12519: TNS:no appropriate service handler found
28-JUN-2013 16:52:32 * service_update * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57610)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57611)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57612)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57613)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57614)) * establish * hostdb * 0

Apparently the app opens a new connection for every to be exported file – and this ~3-8 times per second. Monitoring the “v$session”-view shows no significant increase in the overall number of sessions – still round about 110. So the lifetime of every such connection must be very short. Actually the app uses a connection-pool on the application-server but this seems to be not properly implemented.
Digging the web for that error-message brought up the advice to rise the value for parameter “PROCESSES” on the db.
But as we can see, this limit was never hit until now:

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

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
------------- ------------------- --------------- -------------------- -----------
processes                     109             138        150                  150
sessions                      122             161        248                  248

SQL>

Apart from that I wouldn’t accept to rise that limit, with a corresponding restart of my otherwise well running DB, just because of an untidy implemented use of connectionpooling. And also I guess it would just defer the next appearance of the same error.

At http://www.orafaq.com I finally found a posting that shed some light on the problem. As also stated on MOS (ID 240710.1) the listener counts all incoming connections and rises the “TNS-12519 TNS:no appropriate service handler found” if the sum of connections hit the PROCESSES-Limit of that particular database. Unfortunately the listener’s count is not always accurate as he’s not aware of the disconnects until the next “service_update” from db to listener happens. So if, as in my case, there are very many short connections, it is likely to happen that the listener mistakenly locks access to the DB for new connections.
In another post I found the advice to use a shared-server-configuration to cope with the problem. This works like a connection-pool on the DB-Side and so we would just have a handful of server-processes fed by some dispatchers with user-requests instead of one server-process for every dedicated connection.

If you have XML-DB-Option installed, shared_server should be enabled by default. To find the name of our XDB-dispatcher-service we could query:

SQL> select name, value from v$parameter where name='dispatchers';

NAME        VALUE
----------- ----------------------------------------
dispatchers (PROTOCOL=TCP) (SERVICE=hostdbXDB)

SQL>

If shared_server is not yet configured, don’t worry. To enable shared_server we just have to set the parameter shared_server to a value greater than zero. This can be done dynamically without rebooting the database.

ALTER SYSTEM SET SHARED_SERVER=1 SCOPE=BOTH;

This starts one shared_server-process, what should be enough for testing. For information on suggested numbers of shared_server-processes in relation to the expected number of concurrent sessions, please dig the web.

If shared_server is configured, a dispatcher is implicitly started. To configure our dispatcher to respond on a named service we do:

ALTER SYSTEM SET DISPATCHER='(PROTOCOL=TCP) (SERVICE=hostdbSS)' SCOPE=BOTH;

Now we have to configure the JDBC-Connect-String to use this service-name. To use Oracle Service-Names in JDBC we must use this syntax (from Oracle-Docu):

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

@//host_name:port_number/service_name

For example:

jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

If this doesn’t work you should try to use the fully qualified service-name in the format “servicename.domainname” (e.g. “hostdbXDB.private.domain.com”).

If we want every (TCP-)connection, that doesn’t explicitly request a dedicated_server, to use the shared_server, we simply omit the service_name in our dispatcher’s config and set the dispatcher to respond on all TCP-connections:

ALTER SYSTEM SET DISPATCHER='(PROTOCOL=TCP)' SCOPE=BOTH;

If we now restart our application to make it use the new setting, we should see it using the dispatcher-process when querying “lsnrctl service”:

[oracle@serv1 ~]$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-JUL-2013 10:07:23

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serv1.private.domain.com)(PORT=1522)))
Services Summary...
Service "hostdb.private.domain.com" has 1 instance(s).
  Instance "hostdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:14179 refused:0 state:ready
         LOCAL SERVER

Service "hostdbXDB.private.domain.com" has 1 instance(s).
  Instance "hostdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:5649 refused:0 current:74 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=serv1.private.domain.com)(PORT=32829))

In the line ” “D000″ established:5649 refused:0 current:74 max:1022 state:ready” we see the dispatcher “D000” that has served 5649 connections until now and currently holds 74 sessions connected.

More info about our shared-server-(usage) can be found by querying the following views:

select * from V$SHARED_SERVER;
select * from V$CIRCUIT;
select * from V$DISPATCHER;
select * from V$SHARED_SERVER_MONITOR;
select * from V$DISPATCHER_CONFIG;

E.g. the view “V$CIRCUIT” shows all current connections to the dispatcher.

Querying “select count(*), server from v$session group by server;” would show us the number of connections per access-path:

DEDICATED: dedicated user-connections to DB
SERVER: shared_server-connections doing some stuff
NONE: shared_server-connections on idle

If a session is inactive, it is just connected to it’s dispatcher-process and no shared_server-process is active on behalf of that session – hence the “NONE”.

While solving this problem I came across a listener-parameter new to me, with which it is possible to limit the connection-rate per second (e.g. in defense of DDoS): “CONNECTION_RATE_listener name“

May 5, 2020

Hi,

Sometimes You can get “ORA-12519: TNS:no appropriate service handler found ” error.

Details of error are as follows.

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.

This error is related with out of available Oracle processes.

If processes parameters are insufficient, then you should increase the PROCESSES parameter as follows.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 1500
SQL>
SQL>
SQL> alter system set processes=2000 scope=spfile;

System altered.

SQL>

Restart database after this operation.

Do you want to learn more details about RMAN, then read the following articles.

https://ittutorial.org/rman-backup-restore-and-recovery-tutorials-for-beginners-in-the-oracle-database/

2,187 views last month, 1 views today

Intermittent TNS-12516 or TNS-12519 or TNS-12520 errors when connecting via Oracle Net (Listener) (Doc ID 240710.1)

To Bottom

In this Document

APPLIES TO:

Oracle Net Services – Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 27-NOV-2013***

SYMPTOMS

Client connections may fail intermittently with any of the following errors:

TNS-12516 TNS: listener could not find instance with matching protocol stack
ORA-12516 TNS: listener could not find instance with matching protocol stack

TNS-12519 TNS: no appropriate service handler found
ORA-12519 TNS: no appropriate service handler found

Any or all of these errors might appear in the listener.log and may accompany an ORA-12520:

ORA-12520 TNS:listener could not find available handler for requested type of server

The output of the lsnrctl services command may show that the service handler
is in a “blocked” state.

e.g. ‘”DEDICATED” established:1 refused:0 state:blocked’

CHANGES

It is likely a significant increase in load has occurred.

CAUSE

By way of instance registration, PMON is responsible for updating the listener
with information about a particular instance such as load and dispatcher
information. Maximum load for dedicated connections is determined by the
PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE
information varies according to the workload of the instance. The maximum
interval between these service updates is 10 minutes.

The listener counts the number of connections it has established to the instance
but does not immediately get information about connections that have terminated.
Only when PMON updates the listener via SERVICE_UPDATE is the listener
informed of current load. Since this can take as long as 10 minutes, there can be
a difference between the current instance load according to the listener
and the actual instance load.

When the listener believes the current number of connections has reached maximum
load, it may set the state of the service handler for an instance to “blocked”
and begin refusing incoming client connections with either of the following
errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Additionally, an ORA-12520 error may appear in the listener log.

The output of the LSNRCTL services command will likely show that the service handler is “blocked”.

e.g. ‘”DEDICATED” established:1 refused:0 state:blocked’

Oracle Listener refused connection ORA-12519 troubleshooting

November 11, 2011 by Kirill Loifman12 Comments

Platform

Oracle database 11gR2 (11.2.0.2.2)

Problem

New database connection attempts to Oracle database 11gR2 fail with following Oracle error:

Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found [SQLCode: 12519], [SQLState: 66000] None None

Possible reason

Solution

There are different ways to check if a database instance reaches the maximum number of processes on Oracle 11gR2. One of the quickest SQL see below:

select * from v$resource_limit where resource_name = ‘processes’;

If this is the case, to fix this Oracle error, increase the PROCESSES parameter value to allow the Oracle databaseListener to spawn more server process as required in your environment.

It might be further investigation required though.

Update: 16-Dec-2011

If you really reached the processes limit and it stays this way for some time you will probably get the following error trying to connect:

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

There are a couple of ‘workarounds’ for this issue … but no real solution at this time (even for Oracle 11.2) since you are not capable to connect to the DB even as SYSDBA:

– Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and kill some sessions:
alter system kill session ‘SID, SERAL#’;

– Stop a less critical application, DB monitoring, etc.

– Kill one or more of the client connections on OS level:

* UNIX: kill -9
* WINDOWS: using ORAKILL …

If none above helps and the error occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

Posted by on Sunday, January 21, 2018, at 1:53 pm. Filed under Uncategorized. Tagged oracle listener 12516 12519.
Both comments and trackbacks are currently closed.

ORA-12519 in Enterprise Manager

Found ORA-12519 when trying to connect to the database by using dbconsoleAgent Connection to Instance showed failed.

ORA-12519 in Agent Connection to Instance (English Version)

ORA-12519 in Agent Connection to Instance (Chinese Version)

The very first guess in my mind was that an unmatched service name between tnsnames.ora and the listener. But no, the truth is that PMON notified the listener to accept no more connections because of the maximum number of processes or sessions has been reached.

Solution

To resolve the problem, you can raise the number of processes, and it will raise the number of sessions as well.

SQL> alter system set processes=1000 scope=spfile;


  System altered.

Of course, you can also kill some idle sessions manually to release some processes and try to connect again. Killing shared processes should be taken carefully because there’re could be other active sessions still using them.

For more about managing database server processes, please check Oracle 19c Database Administrator’s Guide: Managing Processes.

Источник

Fixing the ORA-12519: TNS:no appropriate service handler found error

Step-By-Step Solution

1. Check Oracle Home

2. Check the Listener.ora File

3. Troubleshoot Network Connectivity

4. Check Server Logs

FAQs

What is ORA-12519?

What causes ORA-12519 error to appear?

How do I fix ORA-12519 error?

Sources

Expert answers for every coding challenge.

Problem

Symptom

Cause

Resolving The Problem

Related Information

Problem

Cause

Resolving The Problem

Do you want to learn more details about RMAN, then read the following articles.

APPLIES TO:

SYMPTOMS

CHANGES

CAUSE

ORA-12519 in Enterprise Manager

Solution

Expert answers
for every coding challenge.