Re: QueuePool and ORA-12519

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 16 Aug 2010 06:11:06 -0700 (PDT)
Message-ID: <766433bd-a329-446e-9588-2e61c6b486a1_at_t20g2000yqa.googlegroups.com>

On Aug 16, 8:42 am, sajuptpm <sajup..._at_gmail.com> wrote:
> I got following exceptions.
>
> -----------------------
>
> Exception-1
>
>     raise exc.DBAPIError.instance(None, None, e)
> DatabaseError: (DatabaseError) ORA-12519: TNS:no appropriate service
> handler found
>  None None
>
> Solution i found in the net:
>
>        1. Run SQL*Plus and login
>        2. Run the command “alter system set processes=150
> scope=spfile;” in the SQL*Plus
>        3. Restart the database.
>
> Question:
>         Is the correct way to solve this problem?
>
> -----------------------
>
> Exception -2
>
> QueuePool limit of size 10 overflow 10 reached, connection timed out,
> timeout 30
> Traceback (most recent call last):
>     raise exc.TimeoutError("QueuePool limit of size %d overflow %d
> reached, connection timed out, timeout %d" % (self.size(),
> self.overflow(), self._timeout))
> TimeoutError: QueuePool limit of size 10 overflow 10 reached,
> connection timed out, timeout 30
>
> Question:
>         How can i find the Pool size to be used?

Based on the Oracle error message information the posted solution does would only be useful if the reason the listener was backed up and being flooded with commands was if the reason for the backup was that the Oracle database parameter value for processes was being exceeded and your license allowed you to have more connections to the database than the value processes was set to. This could be a side effect from Network issues, OS process limit issues, or database issues. The fix would depend on the root cause of the listener not being able to hand off connections.

$ oerr ora 12519
12519, 00000, "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.

By any chance did this error happen while there was some type of error occurring on the database? Or right after a database restart? What does the alert log for the target database show?

HTH -- Mark D Powell -- Received on Mon Aug 16 2010 - 08:11:06 CDT