Labels

Delphi (23) windows (6) XE3 (5) Delphi 2007 (4) Rad Studio (4) XE (4) XE8 (4) Service (3) windows service application (3) Error (2) IIS (2) Manual (2) SQL (2) TTouchKeyBoard (2) Uninstall (2) XE7 (2) application (2) attach to process (2) batch file (2) cmd (2) date (2) delphi 2009 (2) delphi xe (2) excel (2) no-cache (2) .NET Globalization (1) .res (1) Application Events (1) AutoFit Height (1) AutoFit width (1) Borland.Common.Targets (1) Borland.Delphi.Targets (1) Cache-control (1) Clearing cache on IIS (1) Configure (1) DFM (1) DPR (1) DYNAMIC FORM IN DELPHI (1) Database (1) DateTime Issue (1) DateTime issue in IIS 7 (1) Delete cache (1) Delphi XE3 (1) Disable application from taskbar (1) Duplicate (1) Error 1053: (1) Events (1) Excel columns (1) Excel row (1) Export Excel (1) FD (1) FDDriversFireDac database connection error. (1) File (1) Firedac (1) Format Excel using delphi (1) GNU gettext (1) GPEDIT (1) Get ip address using Delphi (1) GetIPAddress (1) HTTP header (1) Hide Application button from Taskbar (1) Hide Application from TaskBar (1) Hide recycle Bin (1) Hint (1) Hint balloon (1) ICON GROUP (1) ID MAINICON (1) IIS Culture (1) IIS Date issue (1) IIS PHP (1) IIS Set DateTime Format (1) ISAPI extensions (1) Icon (1) Install (1) Install PHP (1) Joomla freezes while installing (1) Joomla taking more time to install (1) MAINICON (1) MYSQL (1) Modify (1) ORA-12516 (1) ORA-12520 (1) ORA-12520: TNS:listener could not find available handler for requested type of server (1) Oracle (1) Out of memory error in TstringList delphi (1) PHP (1) Parameterized queries (1) Php extension (1) Process Name Not Visible (1) Promote Websites (1) Query (1) Remove (1) Remove IIS Temp files (1) Remove Recycle Bin (1) Repair (1) Resource (1) Run php on IIS (1) SQl Injection (1) SQlText (1) Safe (1) Show recycle Bin (1) Simple Ways to Promote Blogs (1) Sortdate format (1) StringList limitations (1) System account setting (1) System locale in IIS (1) TNS listener could not find available handler with matching protocol stack (1) TStrings (1) TTrayIcon (1) TaskBar (1) Tform (1) The service did not respond in a timely fashion (1) TranslateComponent (1) Tray (1) Tray Application (1) TstringList (1) Type 14 (1) UnInstallation (1) Website (1) WinCache (1) Windows Application (1) XE2 (1) batch (1) c++ (1) c++Builder (1) cache (1) cache clear (1) cachecontrol (1) call (1) cannot load library (1) cd (1) cell format (1) clear (1) client (1) close cmd window (1) close program started by cmd (1) cmd.exe (1) command prompt (1) configure a php website on IIS (1) convert delphi color to html color (1) create (1) creating table (1) current (1) data format (1) dbx (1) dbx framework (1) dbx vs vcl (1) debug (1) debugger (1) debugging (1) debugging service application (1) delete IIS temp (1) delete temporary files on IIS (1) delphi color to html color (1) delphi debugger (1) delphi windows service debug (1) directory (1) disable cache (1) dll (1) duplicate resource (1) dynamic form (1) echo off (1) excel cells with mso-number-format (1) exit (1) exit /B (1) form (1) get directory (1) getip (1) group policy (1) hexa color (1) hexa color code (1) hide application (1) hostname (1) how to disable cache in indy (1) html (1) html color (1) html to excel (1) idhttp (1) indy (1) indy not clearing cache (1) inno (1) inno script (1) inno scripts (1) inno setup (1) instant (1) instant client (1) internalize (1) invalid pointer operation (1) invalid pointer operation error in inno (1) invalid pointer operation error in inno setup (1) ip (1) ip address (1) joomla (1) joomla 3 (1) joomla 3 not installing (1) joomla freezes (1) joomla hangs (1) joomla instalation (1) joomla not installing (1) keyboard (1) keystroke (1) libmysql.dll (1) libmysqld.dll (1) localization (1) localize (1) minimize (1) mso-number-format (1) numeric (1) parameters (1) params (1) path (1) pc ip address (1) php in Windows (1) php on IIS (1) prevent against SQL Injection (1) procedure (1) procedure call (1) process (1) program (1) pskill (1) remove cache on IIS (1) resource not found error (1) run as administrator (1) send (1) shortdate format (1) sortdate (1) sqlquery (1) styling excel (1) taskkill (1) tcolor to hexacolor (1) text (1) touchkeyboard (1) tranbslate (1) translate (1) translate component captions (1) unable to load project (1) unc (1) vcl (1) windows 10 (1) windows xp (1) winsock (1) without installing standard oracle (1)

Wednesday, 4 July 2018

ORA-12516: TNS listener could not find available handler with matching protocol stack"

Problem(Abstract)
Users intermittently receive error messages, especially when there are many users using the system.

Symptom
COM Exception (0x80004005) ORA-12516: TNS listener could not find available handler with matching protocol stack

or:

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

Cause
The Oracle database server (hosting the Controller database) has run out of available 'Oracle processes'.

This means that the Oracle database server cannot accept any new connections.

There are several potential causes for the Oracle database server running out of available 'processes':

  • Scenario #1 - Oracle database server's value for "PROCESSES" has been configured too low
  • Scenario #2 - Bug in third party (Oracle) software, causing session leak when JDBC connections are created/used. This means that the used processes are never released when closed.
  • Scenario #3 - Defect in Controller 10.1.0.
  • Scenario #4 - Defect in Controller 10.2 onwards, which causes too many processes to be consumed.

Diagnosing the problem
To find out what the maximum number of Oracle processes your database server is currently configured to allow

Check the value of 'PROCESSES' on the Oracle database server.

Steps:
  1. Launch a SQL command product (for example 'SQL Plus')
  2. Logon as 'system'
  3. Type the following command: show parameter process
 
TIP: By default, the value will be 150. 
To find out how many Oracle processes are currently in use
Run the following script:

SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER'

(or)
SELECT * from v$resource_limit where resource_name in ('processes', 'sessions', 'transactions');

Resolving the problem

Scenario #1

Increase the maximum number of possible processes by increasing the value for PROCESSES on the Oracle database server (typically by default from 150) to a sensibly larger value

In many/most customer's environments, 300 is sufficient
For other customers, they have required 500 or even 1000.


Steps:
Ask your Oracle DBA to consult official Oracle documentation to confirm the best method for your Oracle environment.

For example, some database servers may be configured to use the 'spfile' parameters, but others may not.

Example:

In one real-life example (where it used spfile), the steps to change this parameter were:
  1. Launch 'SQL Plus'
  2. Logon as 'system'
  3. Type the following command (to check that the database is using spfile): show parameter spfile
  4. Assuming that it shows that you ARE using spfile, then type the following command: alter system set PROCESSES=300 scope = spfile
  5. Obtain some downtime (nobody using the databases) and restart the Oracle database server (or simply the relevant Oracle database).

Scenario #2

Apply relevant Oracle patch/upgrade. Specifically, upgrade to either:

Oracle 10.2.0.5 (Server Patch Set)
Or Oracle 11.2


No comments:

Post a Comment