Issue

Error comes while running Release 1

2.2.5 Release Update Pack (RUP).

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

Investigation done

Upon investigation we found that PMON update listener with information about instance such as load and dispatcher information. And the PROCESS parameter determines the maximum load for dedicated connection in database.

The interval at which PMON provides SERVICE_UPDATE information differs according to the workload of the instance. The maximum interval between these service updates is usuallty 10 minutes. When the threshold exceeds the limit then listener become “Blocked” and no new session can be made since it refusing incoming connection. Hence the error.

Resolution

Simply increase the number of PROCESSES from the current 200 to 300.

Option 1 - If spfile is already being used the follow the below mentioned steps:

STEP-1.

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/VIS/12.1.0/dbs/spfileVIS.ora

STEP-2.

SQL> alter system set PROCESSES=300 scope=spfile;
System altered.

Check the updates:

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 2
log_archive_max_processes integer 4
processes integer 300

Now restart the patch and the above mentioned error will not be thrown.

Option 2 - If spfile is NOT being used then you will get the below error upon execution of the above solution. Hence follow the below mentioned alternate solution:

SQL> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string VIS, ebs_patch.

SQL> alter system set PROCESSES=300 scope=spfile;
alter system set PROCESSES=300 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

Alternate solution

STEP-1.

SQL> CREATE SPFILE FROM PFILE;
File created.

STEP-2.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

STEP-3.

SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 3712904 bytes
Variable Size 486541432 bytes
Database Buffers 1644167168 bytes
Redo Buffers 13062144 bytes
Database mounted.
Database opened.

STEP-4.

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/VIS/12.1.0/dbs/spfileVIS.ora

STEP-5.

SQL> alter system set PROCESSES=300 scope=spfile;
System altered.

Check your updates now

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 2
log_archive_max_processes integer 4
processes integer 300

Environment

+ Oracle E-Business Suite = From R12.2.0 to 12.2.5

+ Operating System = Oracle Linux 5.x (x86-64)