Troubleshooting
Problem
Symptom
In one real-life test (using Controller 10.2.2039 connected to Oracle 11G release 2):
One user launching Controller client and reaching logon screen...
...created 2 new 'INACTIVE' processes (associated with this database).
After typing in the username/password, and reaching the 'actuality' choice screen...
...there were a total of 20 INACTIVE processes (associated with this database).
After clicking OK (and the Controller client was finally open)...
...there were now 32 INACTIVE processes.
Then after closing Controller client, there were still 32 INACTIVE processes.
Example #2:
In a different real-life test (using Controller 10.2.5130.56 connected to Oracle 11G release 2):
- One user launching Controller client and reaching logon screen created 2 new 'INACTIVE' processes (associated with this database).
- After typing in the username/password, and reaching the 'actuality' choice screen, there were a total of 19 INACTIVE processes (associated with this database).
- After clicking OK (and the Controller client was finally open) there were now 32 INACTIVE processes.
- Then after closing Controller client, there were still 32 INACTIVE processes.
Potential Error message:
Eventually (after a while) the number of free processes may drop to zero. When this happens, the users will get the following error message:
-
- ORA-12520: TNS:listener could not find available handler for requested type of server
Cause
- TIP: See separate IBM Technote #1603472 for more examples.
This Technote specifically relates to the scenario where the cause is a defect (reference APAR PI60308) in Controller 10.2.x & 10.3.x.
- This defect causes a leak of Oracle cursors.
More Information:
Earlier versions (for example Controller 10.1) do not have this behaviour. Specifically:
- Controller 10.1: 3 Oracle processes created (during a single user logon)
- Controller 10.2: 30 Oracle processes created (during a single user logon)
Diagnosing The Problem
To check the maximum value of processes (that your Oracle database server will allow), ask your Oracle administrator (DBA) to run the following Oracle script:
- show parameters processes;
How to check the current number of Processes in use (on your Oracle server):
To check how many Oracle processes currently exist (active or inactive), ask your Oracle administrator (DBA) to run the following Oracle 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'
You will see a screen similar to:
Resolving The Problem
Upgrade to Controller 10.4 (or later).
Workaround:
Increase the value of PROCESSES to a sufficiently high number.
- For most (smaller) customers, 1000 should be sufficient.
- For large customers, 3000 should be sufficient.
Steps:
See separate IBM Technote #1603472.
Is it safe to increase PROCESSES ?
IBM believes that there is very little drawback to increasing PROCESSES.
- Open_cursors will set a maximum number of cursors a process can open. If you set it to 1000 Oracle will actually allocate cursors 64 at a time, so setting this artificially high does not have a negative impact on resource usage.
Related Information
Was this topic helpful?
Document Information
Modified date:
04 March 2019
UID
swg21978834