oracle process UTILIZATION is increasing gradually

[former_member196171]

Hi Experts ,

Oracle process utilization is gradually increasing in our all systems and finally getting ORA - 00020 error in our all SAP systems (Portal, ECC, BW, other SAP java systems) with maxed out with number oracle processes.

We are using Oracle 11.2.0.3 on AIX 6.1 with NW 7.31 with Kernel 7.20_EXT patch 600 , DBSL patch 522 , saphostexec 720_REL  patch 194

We have increased number of processes couple of times, but slowly getting maxed out again.  We are running these systems for long time, had no issues related to oracle processes before.   Two changes are made recently might affect these systems but not sure , one is we have upgraded the saphostagent with all these systems to patch level 194 , other one is Sol Man system has been upgraded with SP 11.

Just wondering how to troubleshoot oracle processes utilization or the SAP shadow processes, if  oracle processes are getting inactive and not being released, might cause gradual increase of number of processes, is there any relation between host agent or Sol man connection or DBA Cockpit etc. ?

Any thoughts on this will be appreciated

Thanks

Al Mamun

[salome_szanto · Accepted solution]

Hello Al Mamun,
As you noticed already by unsuccessful connections the process list overflows and the Oracle
shadow process is not deleted. This is fixed already by the latest DBSL

1898521 - Process list grows after ORA-01017; ORA-00020  ---> point 2

Kind regards,
Salome

[former_member196171 · Accepted solution]

Hi Salome ,

Thanks for the note , DBSL upgrade to patch 719 did not fix the problem

Still oracle processes are growing , I ran  sql scripts in the DB server ( DB and CI are distributed ) as attached might give some clue

I ran following scripts and got the result as below : SYS@S50>select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in('processes','sessions'); RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION ------------------------------ ------------------- --------------- processes                                      136             139 sessions                                        56              67 SYS@S50>select spid, username, program from v$process where addr not in (select paddr from v$session); SPID                     USERNAME     PROGRAM ------------------------ ------------ ---------------------------------------- 39846010                 oras50       oracle@dbserver1 40697918                 oras50       oracle@dbserver1 40894556                 oras50       oracle@dbserver1 33161236                 oras50       oracle@dbserver1 41549918                 oras50       oracle@dbserver1 35717208                 oras50       oracle@dbserver1 38666418                 oras50       oracle@dbserver1 34799778                 oras50       oracle@dbserver1 32964812                 oras50       oracle@dbserver1 35913940                 oras50       oracle@dbserver1 37421074                 oras50       oracle@dbserver1 41025550                 oras50       oracle@dbserver1 ......... ......... ......... 32309294                 oras50       oracle@dbserver1 33947824                 oras50       oracle@dbserver1 32440414                 oras50       oracle@dbserver1 37027846                 oras50       oracle@dbserver1 41615392                 oras50       oracle@dbserver1 42074246                 oras50       oracle@dbserver1                                       PSEUDO 40566850                 oras50       oracle@dbserver1 36306984                 oras50       oracle@dbserver1 ........ ........ ........ 40960138                 oras50       oracle@dbserver1 34865310                 oras50       oracle@dbserver1 39714970                 oras50       oracle@dbserver1 88 rows selected. SYS@S50>select p.spid, s.username, s.status from v$process p, v$session s where s.paddr=p.addr; SPID                     USERNAME     STATUS ------------------------ ------------ -------- 41812192                              ACTIVE 37158974                              ACTIVE 48038104                              ACTIVE 54132918                              ACTIVE 46858266                              ACTIVE 54657204                              ACTIVE 46596344                              ACTIVE 42664162                              ACTIVE 29360350                              ACTIVE 29032682                              ACTIVE 18415764                              ACTIVE 14024796                              ACTIVE 13828236                              ACTIVE 12845086                              ACTIVE 11731044                              ACTIVE 39387312                 SAPSR3       INACTIVE 52560062                              ACTIVE 48955494                              ACTIVE 10158334                              ACTIVE 5898302                               ACTIVE 39780470                              ACTIVE 40501318                              ACTIVE 10617018                              ACTIVE 38273210                 SAPSR3       INACTIVE 39256088                 SAPSR3       INACTIVE 42336402                 SAPSR3       INACTIVE 56492038                 SAPSR3       INACTIVE 39452794                 SAPSR3       INACTIVE 39321826                 SAPSR3       INACTIVE 38731814                 SAPSR3       INACTIVE 38535216                 SAPSR3       INACTIVE 38076630                 SAPSR3       INACTIVE 40239292                 SAPSR3       INACTIVE 37093466                 SAPSR3       INACTIVE 36831340                 SAPSR3       INACTIVE 37486614                 SAPSR3       INACTIVE 36503762                 SAPSR3       INACTIVE 36438210                 SAPSR3       INACTIVE 36110542                 SAPSR3       INACTIVE 36897002                 SAPSR3       INACTIVE 37617836                              ACTIVE 37552218                              ACTIVE 34603188                              ACTIVE 34275446                 SYS          ACTIVE 35062000                              ACTIVE 34472046                 SAPSR3       INACTIVE 46 rows selected.

[salome_szanto · Accepted solution]

Hello Al Mamun,
Have you upgraded the DBSL in all systems? I'm afraid this should be done if not.

Also have you checked param:rdisp/wp_auto_restart?

2087224 - ORA-00020 although the number of processes/sessions have been increased

Regards,
Salome

[former_member196171 · Accepted solution]

Hi Salmoe,

Thanks for your reply ,

Recent upgrade of the Sol Man to SP 11 with SAP Kernel 720_REL patch 700 and with DBSL patch 623 is the problem .

Here main culprit is  DBSL patch 623

We upgraded DBSL patch with Sol Man only to 719 , all the remote server problem got fixed , though  remote systems are still with DBSL 522.

Thanks for you help , you deserve full points !!

Al Mamun