Oracle memory and /dev/shm

==========================
Oracle memory and /dev/shm
==========================

Consider following real life scenario.

Oracle max_memory and max_memory_target were increased, and Oracle instance restarted.

Following is from alert.log

Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side pfile /software/oracle/111/dbs/initigt.ora
System parameters with non-default values:
  processes                = 600
  spfile                   = "/software/oracle/admin/igt/pfile/spfileigt.ora"
  nls_length_semantics     = "char"
  filesystemio_options     = "asynch"
  memory_target         = 8G
  control_files            = "/oracle_db/db1/db_igt/ora_control_01.ctl"
  control_files            = "/oracle_db/db1/db_igt/ora_control_02.ctl"
  control_files            = "/oracle_db/db1/db_igt/ora_control_03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  log_archive_dest_1       = "location=/oracle_db/db2/db_igt/arch"
  log_archive_format       = "arch%T_%s_%r.arc"
  archive_lag_target       = 1800
  fast_start_mttr_target   = 180
  undo_tablespace          = "UNDOTBS"
  undo_retention           = 3600
  recyclebin               = "OFF"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=igtXDB)"
  session_cached_cursors   = 100
  audit_file_dest          = "/software/oracle/admin/igt/adump"
  open_links               = 12
  audit_trail              = "NONE"
  db_name                  = "igt"
  open_cursors             = 300
  os_authent_prefix        = ""
  diagnostic_dest          = "/software/oracle"
Wed Nov 18 12:22:14 2015
PMON started with pid=2, OS id=17174
Wed Nov 18 12:22:14 2015
VKTM started with pid=3, OS id=17178 at elevated priority
VKTM running at (20)ms precision
Wed Nov 18 12:22:14 2015
DIAG started with pid=4, OS id=17184
Wed Nov 18 12:22:14 2015
DBRM started with pid=5, OS id=17188
Wed Nov 18 12:22:14 2015
DBRM started with pid=5, OS id=17188
Wed Nov 18 12:22:14 2015
PSP0 started with pid=6, OS id=17192
Wed Nov 18 12:22:14 2015
DIA0 started with pid=7, OS id=17202
Wed Nov 18 12:22:14 2015
MMAN started with pid=8, OS id=17215
Wed Nov 18 12:22:15 2015
DBW0 started with pid=9, OS id=17219
Wed Nov 18 12:22:15 2015
DBW1 started with pid=10, OS id=17223
Wed Nov 18 12:22:15 2015
LGWR started with pid=11, OS id=17227
Wed Nov 18 12:22:15 2015
CKPT started with pid=12, OS id=17232
Wed Nov 18 12:22:15 2015
SMON started with pid=13, OS id=17242
Wed Nov 18 12:22:15 2015
RECO started with pid=14, OS id=17253
Wed Nov 18 12:22:15 2015
MMON started with pid=15, OS id=17257
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Nov 18 12:22:15 2015
MMNL started with pid=16, OS id=17261
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /software/oracle
Wed Nov 18 12:22:17 2015
ALTER DATABASE   MOUNT
Setting recovery target incarnation to 1
Successful mount of redo thread 1, with mount id 1134364153
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Wed Nov 18 12:22:21 2015
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Wed Nov 18 12:22:21 2015
ARC0 started with pid=20, OS id=17426
Wed Nov 18 12:22:21 2015
ARC1 started with pid=21, OS id=17430
Wed Nov 18 12:22:21 2015
ARC2 started with pid=22, OS id=17434
Wed Nov 18 12:22:21 2015
ARC3 started with pid=23, OS id=17438
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 441882
  Current log# 3 seq# 441882 mem# 0: /oracle_db/db1/db_igt/ora_redo_03_a.rdo
Successful open of redo thread 1
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
Starting background process FBDA
Wed Nov 18 12:22:23 2015
FBDA started with pid=24, OS id=17481
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Nov 18 12:22:24 2015
QMNC started with pid=25, OS id=17505
Completed: ALTER DATABASE OPEN
...
...
...
...
Wed Nov 18 13:38:35 2015
Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 8589934592 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 3500191744 and used is 5089742848 bytes.
memory_target needs larger /dev/shm
Wed Nov 18 13:39:39 2015
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_pmon_17174.trc:
ORA-56710: DBRM process died unexpectedly
PMON (ospid: 17174): terminating the instance due to error 56710
Instance terminated by PMON, pid = 17174

What is /dev/shm?

It is an in-memory mounted file system (tmpfs) and is very fast, but non-persistent when Linux is rebooted.

In Oracle 11g, it is used to hold SGA memory by storing the SGA structures in files with the same granule size. 

When these MEMORY_TARGET and MEMORY_MAX_TARGET parameters are set, oracle will create MEMORY_MAX_TARGET/granule size files. 

In this example, MEMORY_TARGET was set to 8Gb.

8*1024*1024*1024/67108864=128

So there are in total 128 files.

The output of ‘ls -lk /dev/shm’, will show you that not all the 128 files are taking the 4M of space.
ls -lk /dev/shm

-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_0
-rw-r----- 1 oracle dba     0 Nov 18 15:39 ora_igt_4915207_1
-rw-r----- 1 oracle dba     0 Nov 18 15:39 ora_igt_4915207_10
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_100
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_101
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_102
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_103
-rw-r----- 1 oracle dba 65536 Nov 19 07:48 ora_igt_4915207_104
-rw-r----- 1 oracle dba 65536 Nov 19 02:06 ora_igt_4915207_105
-rw-r----- 1 oracle dba 65536 Nov 19 07:48 ora_igt_4915207_106
-rw-r----- 1 oracle dba 65536 Nov 19 07:48 ora_igt_4915207_107
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_108
-rw-r----- 1 oracle dba 65536 Nov 19 02:06 ora_igt_4915207_109
-rw-r----- 1 oracle dba     0 Nov 18 15:39 ora_igt_4915207_11......
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_90
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_91
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_92
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_93
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_94
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_95
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_96
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_97
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_98
-rw-r----- 1 oracle dba 65536 Nov 19 07:49 ora_igt_4915207_99

ls -lk /dev/shm | grep 65536 | wc -l
77

In total there are 77 file, each file has size of 67108864 bytes.

67108864*77/1024/1024=4928 Mb

This is the same value, as checking SGA size in V$SGASTAT

SELECT ROUND(SUM(bytes)/1024/1024) AS MB_MEMORY FROM V$SGASTAT;

4827

When you add up all the files that do take space, it will never take more space than MEMORY_TARGET. 

PGA is not stored in shared memory, because this is private memory. MEMORY_MAX_TARGET (used for SGA and PGA) is ‘allocated’ in /dev/shm, but PGA is not stored in /dev/shm. 
This means, when memory for PGA is allocated (and/or pga_aggregate_target is set), not all files in /dev/shm will get used!

What about the error memory_target needs larger /dev/shm

less /etc/fstab

/dev/Volume00/LogVol00  /                       ext3    defaults,acl        1 1

/dev/cciss/c0d0p1       /boot                   ext3    defaults,acl        1 2

devpts                  /dev/pts                devpts  gid=5,mode=620      0 0

tmpfs                /dev/shm              tmpfs  size=8g          0 0

/dev/Volume00/LogVol05  /home                   ext3    defaults,acl        1 2

/dev/Volume00/LogVol10  /kits                   ext3    defaults,acl        1 2

/dev/Volume00/LogVol04  /opt                    ext3    defaults,acl        1 2

/dev/Volume00/LogVol13  /oracle_db              ext3    defaults,acl        1 2

proc                    /proc                   proc    defaults            0 0

/dev/Volume00/LogVol12  /software               ext3    defaults,acl        1 2

/dev/Volume00/LogVol07  /software/oracle        ext3    defaults,acl        1 2

df -h

Filesystem                     Size  Used Avail Use% Mounted on

/dev/mapper/Volume00-LogVol00  2.0G  697M  1.2G  38% /

/dev/cciss/c0d0p1              927M  142M  738M  17% /boot

tmpfs                      8.0G  4.8G  3.3G  60% /dev/shm

/dev/mapper/Volume00-LogVol05  992M   34M  908M   4% /home

/dev/mapper/Volume00-LogVol10  2.5G  961M  1.4G  41% /kits

/dev/mapper/Volume00-LogVol04  2.0G  499M  1.4G  27% /opt

If you increase the MEMORY_MAX_TARGET above the available /dev/shm space, you will receive:

ORA-00845: MEMORY_TARGET not supported on this system

===============
Solution
===============
Option A.
If you have enough memory on your Linux machine, but /dev/shm is mounted to small by default, one can increase this amount of memory by changing /etc/fstab for permanent changes. The default is half of your physical RAM without swap.

For a permanent change, need to edit /etc/fstab file

vi /etc/fstab

modify /dev/shm entry as follows to set size to 8G
none      /dev/shm        tmpfs   defaults,size=8G        0 0

Save and close the file. 

For the changes to take effect immediately remount /dev/shm:
mount -o remount /dev/shm

Option B.

For temporary change, at least to start the database, execute the following.
In this example, /dev/shm is set to 8000Mb:

> umount tmpfs
> mount -t tmpfs shmfs -o size=8000m /dev/shm

Option C.

The other option would be to decrease the memory_targer, as described here.

References

Oracle 11g AMM: MEMORY_TARGET, MEMORY_MAX_TARGET and /dev/shm

Automatic Memory Management 

Oracle connections, processes, sessions, connection pooling.

===================================
About Oracle connections, processes, sessions.
===================================

This is an extract from:
Tom Kyte, What's the difference between connections, sessions and processes? 
JDBC Developer’s Guide and Reference 10.2
Expert Oracle JDBC Programming


A connection
A connection is a network, physical connection between you and Oracle database.  
A connection might be of two types:
DEDICATED server and SHARED server.  

A session
A session encapsulates user interaction with the database, from the moment user was authenticated until the moment the user disconnects.
In most cases, one connection corresponds to one session, but it does not have to be so.
Zero, one or more sessions may be established over a given connection to the database.
A process will be used by a session to execute statements.

A process
A process is a physical process or thread.

On Linux, one can see a process with "ps".  
There are two types of processes in Oracle:
Background processes like SMON, PMON, RECO, ARCH, CKPT, EMNn, DBWR, etc...
User processes like dedicated servers or shared server (multi-threaded server, MTS )

Relationship between CONNECTION->SESSION->PROCESS

You can be connected to a database yet have 0 or 1 or MORE sessions going on that connection.

Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS. This is the regular situation in dedicated server connection.  
Sometimes there is a one to many from connection to 
sessions (eg: like autotrace, one connection, two sessions, one process).  

A process does not have to be dedicated to a specific connection or session.
When using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement.  When the call is over, that process is released back to the pool of processes.

In dedicated server mode.
Each physical connection has one session, and one separate (or dedicated) process (or thread) associated with it.
In this configuration, each entry in V$SESSION would have a unique entry in V$PROCESS, connected by V$SESSION.paddr = V$PROCESS.addr.
V$PROCESS.spid is the actual server pid on the host machine.

Dedicated server is the most commonly used option.
(This is the option SH is using) 

In shared server (multi-threaded server) mode.
A pool of processes is shared by physical connections.
A user has a session.  
A session is your login, it means you are connected and authenticated, and you can do SQL. You have a row in v$session.
When you are not doing SQL, you are inactive and you will NOT be associated with any process.  
When a shared server session becomes active, it is associated with some process.
It only uses a process for the duration of the request/call to the database.

The session does not have a process. A process does not have sessions.  

A process is a resource used by sessions over time. 

===================
Example
===================

[tkyte@tkyte-pc-isdn tkyte]$ ps -auxww | grep oracleora920 | grep -v grep
[tkyte@tkyte-pc-isdn tkyte]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 10:36:03 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> !ps -auxww | grep oracleora920 | grep -v grep
tkyte    19971  0.0  0.1  2196  916 pts/1    S    10:36   0:00 /bin/bash -c ps -auxww | 
grep oracleora920 | grep -v grep

no process, no nothing

idle> connect /
Connected.
idle> !ps -auxww | grep oracleora920 | grep -v grep
ora920   19974  1.5  2.2 230976 11752 ?      S    10:36   0:00 oracleora920 
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

got my process now...

idle> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
idle> !ps -auxww | grep oracleora920 | grep -v grep
ora920   19974  0.6  2.3 230976 11876 ?      S    10:36   0:00 oracleora920 
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

idle> select 1 from dual;
SP2-0640: Not connected

Still have connection, process, but no session. 
The message is a little "misleading". Technically there is a connection, but no session


further, autotrace in sqlplus can be used to show that you can have 
a) a connection
b) that uses a single process
c) to service two sessions:


SELECT username FROM V$PROCESS WHERE username is not null;

USERNAME
--------------
OPS$TKYTE

one session

SELECT username, program FROM V$PROCESS;

USERNAME        PROGRAM
--------------- ------------------------------------------------
                PSEUDO
ora920          oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte           oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)

14 rows selected.

To get number of non backgrouond processes:
SELECT count(*) FROM V$PROCESS WHERE background IS NOT NULL;

Background processes and one dedicated server process.

set autotrace on statistics;

Autotrace for statistics uses ANOTHER session so it can query up the stats for your 
CURRENT session without impacting the STATS for that session!


SELECT username FROM V$PROCESS WHERE username IS NOT NULL;

USERNAME
--------------
OPS$TKYTE
OPS$TKYTE

Two sessions but....

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SELECT username, program FROM V$PROCESS;

USERNAME        PROGRAM
--------------- ------------------------------------------------
                PSEUDO
ora920          oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte           oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)

14 rows selected.

same 14 processes...

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1095  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

ops$tkyte@ORA920.US.ORACLE.COM>

A connection pooling
Connection pooling is maintained by Application server.
Creating new connection involves creating a new process or a thread in OS, and is both time consuming and exhausting for the host OS .
Rather connection pooling, is a better option.
The application server takes a connection from a pool, marks it as "used", and hands it over to the application.
The application is actually receiving a reference to a Logical Connection, rather than to a Physical Connection, so when application does close() to the connection, it just clear the connection state, and return it to the pool of Physical connections.

See also
ORA-00020: No more process state objects available and ORA-00020: maximum number of processes 0 exceeded

ORA-00020: No more process state objects available and ORA-00020: maximum number of processes 0 exceeded

===============================
Error ORA-00020 in alert log
===============================
Mon Nov 09 23:22:03 2015
Archived Log entry 83763 added for thread 1 sequence 83774 ID 0x36162634 dest 1:
Mon Nov 09 23:42:04 2015
Thread 1 advanced to log sequence 83776 (LGWR switch)
  Current log# 1 seq# 83776 mem# 0: /oracle_db/db1/db_igt/ora_redo_01_a.rdo
Mon Nov 09 23:42:05 2015
Archived Log entry 83764 added for thread 1 sequence 83775 ID 0x36162634 dest 1:
Mon Nov 09 23:50:04 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_cjq0_21012.trc:

Process J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_cjq0_21012.trc:

Mon Nov 09 23:51:13 2015

ORA-00020: maximum number of processes 0 exceeded

ORA-20 errors will not be written to the alert log for

 the next minute. Please look at trace files to see all

 the ORA-20 errors.

Mon Nov 09 23:51:56 2015

Shutting down instance (abort)

License high water mark = 270

USER (ospid: 5241): terminating the instance

Mon Nov 09 23:51:57 2015

ORA-1092 : opiodr aborting process unknown ospid (30859_47851384046032)

Mon Nov 09 23:51:57 2015

ORA-1092 : opitsk aborting process

Mon Nov 09 23:51:57 2015

ORA-1092 : opiodr aborting process unknown ospid (10833_47625099882960)

Instance terminated by USER, pid = 5241

Mon Nov 09 23:51:59 2015

Instance shutdown complete

===============================
Error ORA-00020 in trace file
===============================

 /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_cjq0_21012.trc:

Trace file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_cjq0_21012.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /software/oracle/111

System name:    Linux

Node name:      tha-ais-2-aps-5

Release:        2.6.18-308.24.1.el5

Version:        #1 SMP Wed Nov 21 11:42:14 EST 2012

Machine:        x86_64

Instance name: igt

Redo thread mounted by this instance: 1

Oracle process number: 274

Unix process pid: 21012, image: oracle@tha-ais-2-aps-5 (CJQ0)

*** 2015-11-09 23:50:04.616

*** SESSION ID:(83.28755) 2015-11-09 23:50:04.616

*** CLIENT ID:() 2015-11-09 23:50:04.616

*** SERVICE NAME:(SYS$BACKGROUND) 2015-11-09 23:50:04.616

*** MODULE NAME:() 2015-11-09 23:50:04.616

*** ACTION NAME:() 2015-11-09 23:50:04.616

ORA-00020: No more process state objects available

ORA-00020: No more process state objects available

ORA-00020: No more process state objects available

*** 2015-11-09 23:50:09.627

ORA-00020: No more process state objects available

===============================

Check in Database
===============================

COL RESOURCE_NAME FOR A20

COL LIMIT_VALUE FOR A20

SELECT resource_name, current_utilization, limit_value

  FROM V$RESOURCE_LIMIT

 WHERE resource_name in ('sessions','processes','transactions');

RESOURCE_NAME        CURRENT_UTILIZATION LIMIT_VALUE

-------------------- ------------------- -----------

processes                           1041       2000

sessions                            1015       3072

transactions                           7  UNLIMITED

SELECT *
  FROM V$RESOURCE_LIMIT 

 WHERE resource_name in ('sessions','processes','transactions');

RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE

-------------------- ------------------- --------------- -------------------- -----------

processes                            288             296        300                   300

sessions                             296             319        335                   335

transactions                  4294965549      4294967295        368             UNLIMITED

SET LINESIZE 120

SET PAGESIZE 1000

COL MACHINE FOR A30

COL USERNAME FOR A30

COL PROGRAM FOR A30

SELECT MACHINE, USERNAME, PROGRAM, COUNT(*) 

  FROM V$SESSION 

GROUP BY MACHINE, USERNAME, PROGRAM 

HAVING count(*) > 5 

ORDER BY COUNT(*) DESC;

MACHINE           USERNAME            PROGRAM                                   COUNT(*)
----------------- ------------------- ---------------------------------------- ---------
esp-vod-9-aps-1   IRL_VODAF_SPARX     JDBC Thin Client                                37
esp-vod-9-aps-1   ZAF_VODAC_SPARX     JDBC Thin Client                                32
esp-vod-9-aps-1   NZL_VODAF_SPARX     JDBC Thin Client                                31
esp-vod-9-aps-1   NZL_VODAF_SFIQQ     JDBC Thin Client                                18
esp-vod-9-aps-1   IRL_VODAF_SFIQQ     JDBC Thin Client                                18
esp-vod-9-aps-1   ZAF_VODAC_SFIQQ     JDBC Thin Client                                18
esp-vod-9-aps-1   ROM_VODAF_SPARX     JDBC Thin Client                                16
esp-vod-9-aps-1   CZE_VODAF_SBQQQ     proxy@esp-vod-9-aps-1 (TNS V1-V3)               12
jdbcclient        MLT_VODAF_MOCOQ     JDBC Thin Client                                11
esp-vod-9-aps-1   IRL_VODAF_SPARX     notification@esp-vod-9-aps-1 (TNS V1-V3)         6
esp-vod-9-aps-1   ZAF_VODAC_SPARX     notification@esp-vod-9-aps-1 (TNS V1-V3)         6

===============================
Solution
===============================

The computed number for PROCESSES is derived form SESSIONS number.
In 10g, use following formula:

(1.1 * PROCESSES) + 5

In 11g R1 onward it changed to


(1.5 * PROCESSES) + 22

When increasing PROCESSES parameter, one should also increase SESSIONS and TRANSACTIONS parameters.

For Oracle 10g: the default formula for determining  these parameter values is as follows:

    

processes=x

sessions=x*1.1+5

transactions=sessions*1.1

for example:
 300, 335, 370    
 500, 555, 610    
 600, 665, 730
 900, 995,1095
1000,1105,1215

These parameters can't be modified in memory. 

You have to modify the spfile only (scope=spfile) and bounce the instance.

ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE;

ALTER SYSTEM SET SESSIONS=555 SCOPE=SPFILE;

ALTER SYSTEM SET TRANSACTIONS=610 SCOPE=SPFILE;

SHUTDOWN ABORT;

STARTUP;





===============================
Preliminary session option.
===============================
The -prelim option. 
Sometimes, because the threashold of session is reached, a DBA cannot login to the database in order to perform a SHUTDOWN command. 
In this case, the solution would be to use the -prelim option. 
When opening a session in Preliminary mode, it will not try to create private session structures in the SGA. 
This allows you to connect to perform debugging or shutdown operations. 

For Example:
sqlplus /nolog

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 @ SQL> connect /
 ERROR:
 ORA-00020: maximum number of processes (1000) exceeded

exit

sqlplus -prelim "/ as sysdba"

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 SQL> shutdown abort
 ORACLE instance shut down.
 SQL> exit

 Disconnected from ORACLE


===============================
Checking Current Situation
===============================

SET lines 140 pages 1000
COL RESOURCE_NAME FOR A20

SELECT *
  FROM v$resource_limit
 WHERE resource_name='processes';


RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------- ------------------- --------------- ------------------ --------------------
processes                     249             255                300                  300

SET linesize 140

COL run_date FOR A20

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:mi:ss') AS run_date,

       username AS user_name,

       count(*) AS sessions

FROM V$SESSION 

WHERE username IS NOT NULL 

GROUP BY username

ORDER BY count(*) DESC;

RUN_DATE             USER_NAME                        SESSIONS

-------------------- ------------------------------ ----------

20170308 16:57:10    AUT_TMOQQ_USERA                       149

20170308 16:57:10    AUT_TMOBI_USERB                        57

20170308 16:57:10    AUT_MAXMO_USERC                        11

20170308 16:57:10    AUT_TMOBI_USERD                        10

20170308 16:57:10    SYS                                     1

20170308 16:57:10    SYSTEM                                  1

===============================

Checking Historic Situation
===============================

SET lines 200 pages 1000

COL begin_interval_time FOR A20

SELECT HIST_SNAPSHOT.snap_id,

       HIST_SNAPSHOT.begin_interval_time,

       HIST_RESOURCE_LIMIT.current_utilization,   

       HIST_RESOURCE_LIMIT.max_utilization,

       HIST_RESOURCE_LIMIT.initial_allocation

 FROM DBA_HIST_RESOURCE_LIMIT HIST_RESOURCE_LIMIT, 

      SYS.DBA_HIST_SNAPSHOT   HIST_SNAPSHOT

WHERE HIST_RESOURCE_LIMIT.resource_name='processes'

  AND HIST_RESOURCE_LIMIT.snap_id=HIST_SNAPSHOT.snap_id

ORDER BY HIST_SNAPSHOT.snap_id DESC;

SELECT parsing_schema_name,COUNT(*) 

FROM DBA_HIST_SQLSTAT 

WHERE snap_id=51158 

GROUP BY parsing_schema_name;

===============================
Monitoring from Database
===============================
When answering "Why did ORA-00020 occur, first option would be to query historical data.
If that data is not available, or not meaningful, it is possible to log sessions count to a table every N minutes.
Following example used Oracle JOB mechanism to call every 5 minutes a procedure.
The Procedure would log data to a table



---------------------------------------
Create Tablespace
---------------------------------------
CREATE TABLESPACE MONITOR_TBS DATAFILE '/oracle_db/db1/db_igt/ora_monitor_table_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M EXTENT MANAGEMENT LOCAL;

---------------------------------------
Create Sequence
---------------------------------------
CREATE SEQUENCE MONITOR_SEQ INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE;

---------------------------------------
Create Table
---------------------------------------
-- Create table
create table MONITOR_SESSIONS
(
  seq_id                    NUMBER,
  run_date                  VARCHAR2(30),
  user_name                 VARCHAR2(30),
  sessions                  NUMBER
)
tablespace MONITOR_TBS;


---------------------------------------
Grants as sysdba
---------------------------------------
GRANT SELECT ON SYS.V_$SESSION TO AUT_TMOQQ_USERA;


---------------------------------------
Code
---------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_MONITOR IS

-----------------------------------------------------------
   PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS
     PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     INSERT INTO SGA_W_LOG( procedure_name,  data ,ts_last_modified)
       VALUES ( p_module_name, p_msg_text, SYSDATE);
     COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
   END;
-----------------------------------------------------------
  PROCEDURE monitor_sessions  IS
     v_module_name VARCHAR2(30);
     v_seq_id      NUMBER;
  BEGIN
    
    v_module_name := 'monitor_sessions';
    SELECT MONITOR_SEQ.nextval INTO v_seq_id FROM DUAL;
    
    INSERT INTO MONITOR_SESSIONS (seq_id,run_date, user_name, sessions)
    SELECT v_seq_id, run_date, user_name, sessions FROM (
         SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:mi:ss') AS run_date,
                username AS user_name,
                count(*) AS sessions
            FROM V$SESSION
           WHERE username IS NOT NULL
        GROUP BY username ) SESSIONS_COUNT;

     COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
       write_sga_w_log(v_module_name, 'Unexpected Error: '||SQLERRM);
   END monitor_sessions;

END ADMIN_MONITOR;

-----------------------------------------------------------
CREATE OR REPLACE PACKAGE ADMIN_MONITOR IS
  PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2);
  PROCEDURE monitor_sessions;
END ADMIN_MONITOR;



---------------------------------------
Job - to run every 5 minutes
---------------------------------------
DECLARE
   v_job_number NUMBER(10);
BEGIN
 DBMS_JOB.SUBMIT (JOB => v_job_number, 
                  WHAT => 'ADMIN_MONITOR.monitor_sessions;', 
                  NEXT_DATE => SYSDATE + 5/1440, 
                  INTERVAL => 'SYSDATE + 5/1440'
 );
 COMMIT;
END;
/

Sample output from the monitoring table:

COL run_date FOR A30
COL user_name FOR A30
SET LINESIZE 120
SET PAGESIZE 400

SQL> select * FROM MONITOR_SESSIONS ORDER BY seq_id desc, sessions desc;

    SEQ_ID RUN_DATE                       USER_NAME                        SESSIONS
---------- ------------------------------ ------------------------------ ----------
         3 20170308 17:03:27              AUT_TMOQQ_SPARX                       152
         3 20170308 17:03:27              AUT_TMOBI_SHARB                        58

         3 20170308 17:03:27              AUT_MAXMO_IPNQQ                        12

         3 20170308 17:03:27              AUT_TMOBI_OVMDQ                        10

         3 20170308 17:03:27              SYSTEM                                  1
         2 20170308 16:58:26              AUT_TMOQQ_SPARX                       150
         2 20170308 16:58:26              AUT_TMOBI_SHARB                        57
         2 20170308 16:58:26              AUT_MAXMO_IPNQQ                        11
         2 20170308 16:58:26              AUT_TMOBI_OVMDQ                        10
         2 20170308 16:58:26              SYSTEM                                  1
         2 20170308 16:58:26              SYS                                     1
         1 20170308 16:53:26              AUT_TMOQQ_SPARX                       150
         1 20170308 16:53:26              AUT_TMOBI_SHARB                        57
         1 20170308 16:53:26              AUT_MAXMO_IPNQQ                        11
         1 20170308 16:53:26              AUT_TMOBI_OVMDQ                        10

         1 20170308 16:53:26              SYSTEM                                  1

===============================
Monitoring from Linux
===============================

#/bin/bash

. /etc/sh/orash/oracle_login.sh igt

sqlplus / << EOF

select username, count(username) from v\$session group by username having count(username)>10 order by 2;

select count(*) from v\$session;

exit

EOF