How to solve ORA-27102: out of memory on startup

You want to increase your SGA to a larger value, and you have taken care to increase the value of shmmax in the /etc/sysctl.conf file.

When starting up the database with increased values for sga_max_size and sga_target, you hit

SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: 1543503872
Additional information: 1

Solution:
Increase the value of shmall as well. Shmall indicates the total amount of shared memory that the system can use at one time (measured in pages).

To find the page size:

getconf PAGE_SIZE
4096

Make sure that the shmall is larger, not identical, than the setting of your SGA.
For example, if you want a 12G SGA, do not set shmall to 3145728 which would be the number of pages for 12G

Set it to a value larger instead, for example 3670016, which is the number of pages equivalent to 14G:

3670016*4096=15032385536
15032385536/1024/1024/1024 = 14G

As root, open /etc/sysctl.conf in an editor.
Search for the string kernel.shmall
Change the value
Save and exit.

Then, still as root, execute

sysctl -p

Check that the new settings are in place:

/sbin/sysctl -a | grep shm

The database should now be possible to open.

How to work around ORA-00845: MEMORY_TARGET not supported on this system during startup

On Linux platforms, you need to adjust the size of the shared memory file system (/dev/shm) to match the size of your memory_target or memory_max_target. If not you will see the following error:

SQL> startup mount

ORA-00845: MEMORY_TARGET not supported on this system

To adjust the size, open the file /etc/fstab in an editor:

vi /etc/fstab

At the line where you see the tmpfs file system specified, add the size after the keyword "defaults". In the example below, I would like to use a memory_target of 10g, so I add "10g". Do not use a capital letter G, but simply a "g":

tmpfs                   /dev/shm                tmpfs   defaults,size=10g        0 0

Then, remount the file system:

mount –o remount /dev/shm

Login storm against database caused exhausted library cache

One of our databases experienced massive contention in the shared pool, in form of wait events alerted as "library cache locks".

The database was very small indeed, so my natural instinct was to throw some more memory at the virtual host, and rearrange the memory parameters.

This turned out to be a misconception; the resources were sufficient for the instance to work properly.

The problem was caused by an incorrect password configuration on the application server.

What we could observe was:

A totally exhausted shared pool, caused by "library cache lock"

The SQL that seemed to be repeatedly executed was

SELECT /*+ connect_by_filtering */
          privilege#, LEVEL
      FROM sysauth$
CONNECT BY grantee# = PRIOR privilege# AND privilege# > 0
START WITH grantee# = :1 AND privilege# > 0;


SELECT privilege#
  FROM sysauth$
WHERE (grantee# = :1 OR grantee# = 1) AND privilege# > 0;

The V$EVENT_NAME view showed that the wait event was accompanied by the additional information found in the columns parameter1 through parameter3, which turned out to be helpful further on:

select  name, wait_class,parameter1,parameter2,parameter3
from v$event_name
where wait_class = 'Concurrency'
and name = 'library cache lock';

NAME	WAIT_CLASS	PARAMETER1	PARAMETER2	PARAMETER3
library cache lock	Concurrency	handle address	lock address	100*mode+namespace

Further research showed that the problem was due to a built-in delay between failed login attempts in Oracle 11g:

"The 'library cache lock' wait is seen due to the fact that the account status gets updated due to incorrect login.
To prevent password guessing attack, there's a sleep() in the code when incorrect login attempts exceed count of 3.
And because of this sleep() you see a wait on library cache, as the process is yet to release the lock."

In release 11.1.0.7, patch 7715339 was released to remove this delay.

In release 11.2.X, the DBA must set an event to remove the delay, as follows:

alter system set events '28401 trace name context forever, level 1'; 

According to Oracle, the purpose of the built-sleep is to make it harder to succeed in a "password guessing attack", particularly in cases where FAILED_LOGIN_ATTEMPTS is set to UNLIMITED. Oracle Development is pointing out that disabling the sleep-function is not recommended. A better solution is to set the FAILED_LOGIN_ATTEMPTS to a reasonable value.
When the number of failed login attempts for a session hits the limit, the account will be locked. Subsequent logon attempts with incorrect password will then be rejected immediately without any contention in the library cache.

See Bug 15882590 : 'LIBRARY CACHE LOCK' DURING WRONG PASSWORD LOGON ATTEMPTS on My Oracle Support (MOS) for further information.

How to deal with ORA-00020: maximum number of processes (%s) exceeded

I recently had a situation where access to the database was completely blocked because of the infamous error message

ORA-00020: maximum number of processes (%s) exceeded

Facts:

The database had processes set to 1000.

The Cloud Control agent was spawning hundreds of processes (obviously an error to troubleshoot as a separate action)

Connecting through sqlplus with os authentication (sqlplus / as sysdba) didn't work due to the same reason

At that time, the database had to become available to the users again ASAP.

When I have encountered these situations in the past, I have had to kill all the operating system processes and restart the instance. A brut-force method that is not particularly pretty, but sometimes necessary:

for a in $(ps -ef |grep $ORACLE_SID | grep -v grep | awk '{ print $2}'); do 
>kill -9 $a; 
>done

It normally does the job when you really have no other option.
This time however, after having killed all the processes, Oracle still rejected connections to the database using sqlplus:

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

I then found the page by tech.e2sn.com that showed how to use sqlplus with the "preliminary connection".

Simply by using

sqlplus -prelim "/as sysdba"

I was able to connect and shutdown the database with the abort option.

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

After this point the database could once again be restarted:

sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:10:38 2014
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 Connected to an idle instance.
SQL> startup
 ORACLE instance started.

 Total System Global Area 2137886720 bytes
 Fixed Size                  2248080 bytes
 Variable Size            1258291824 bytes
 Database Buffers          855638016 bytes
 Redo Buffers               21708800 bytes
 Database mounted.
 Databasen opened.

The article referred to above is worth reading, but in short, the -prelim option will not try to create private session structures in the SGA. This allows you to connect to perform debugging or shutdown operations.

How to recreate the control file and rename the database and the datafiles

To change a database name or file names, it can be practical to recreate the control file, and in some cases your only option.

In this example, I will rename a database and its file structure from "OID11UI" to "OID11U1".

Start by generating a file which holds the current layout of all the database's files:

SQL> alter database backup controlfile to trace as '/u01/oracle/product/11204/dbs/cntr_trace.sql' resetlogs;

Database altered.

The generated file typically look like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OID11UI" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11UI/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11UI/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11UI/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11UI/system01.dbf',
  '/u02/oradata/OID11UI/sysaux01.dbf',
  '/u02/oradata/OID11UI/undotbs01.dbf',
  '/u02/oradata/OID11UI/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11UI/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Exchange the word "REUSE" against the word "SET":

CREATE CONTROLFILE SET DATABASE "OID11U1"

Optionally, on the first line, add a pointer to a correct parameter file:

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'

Change all references to "OID11UI" (the old name):

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'
CREATE CONTROLFILE SET DATABASE "OID11U1" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11U1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11U1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11U1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11U1/system01.dbf',
  '/u02/oradata/OID11U1/sysaux01.dbf',
  '/u02/oradata/OID11U1/undotbs01.dbf',
  '/u02/oradata/OID11U1/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11U1/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Change the folder name that oracle uses:

usu0oid01:OID11UI>cd /u02/oradata
usu0oid01:OID11UI>mv OID11UI OID11U1

Change the control_files directive in the parameter file:
Before

*.control_files='/u02/oradata/OID11UI/control01.ctl','/u02/oradata/OID11UI/control02.ctl'

After:

*.control_files='/u02/oradata/OID11U1/control01.ctl','/u02/oradata/OID11U1/control02.ctl'

Remove the old controlfiles physically from disk:

cd /u02/oradata/OID11U1
rm *.ctl

Shutdown the database:

SQL> shutdown abort
ORACLE instance shut down.

Change the oracle profile in the operating system:

cd
vi .profile

Before:

export ORACLE_SID=OID11UI

After:

export ORACLE_SID=OID11U1

Source the new profile, and check that the ORACLE_SID environmental variable is correctly set:

. .profile
usu0oid01:OID11U1>echo $ORACLE_SID
OID11U1

Start sqlplus and run script:

usu0oid01:OID11UI>sqlplus / as sysdba @cntr_trace.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 28 14:00:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2247072 bytes
Variable Size            1107297888 bytes
Database Buffers          603979776 bytes
Redo Buffers                6803456 bytes

Control file created.

Database altered.

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Check the location of the data files after the change:

SQL> select file_name from dba_data_files union select member from v$logfile  union  select name from v$controlfile;

FILE_NAME
--------------------------------------------------
/u02/oradata/OID11U1/control01.ctl
/u02/oradata/OID11U1/control02.ctl
/u02/oradata/OID11U1/redo01.log
/u02/oradata/OID11U1/redo02.log
/u02/oradata/OID11U1/redo03.log
/u02/oradata/OID11U1/sysaux01.dbf
/u02/oradata/OID11U1/system01.dbf
/u02/oradata/OID11U1/undotbs01.dbf
/u02/oradata/OID11U1/users01.dbf

Lag en spfile (anbefales):

SQL> create spfile from pfile; 

File created.

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

Startup the database for normal use:

SQL> startup

SQL: view memory parameter

set lines 200
set pages 100
col name format a30
col value format a40
select name from v$database
/
select name, value/1024/1024 "MB"
from v$parameter
where name in ('java_pool_size',
                'db_cache_size',
                'shared_pool_size',
                'large_pool_size',
                'streams_pool_size',
                'sga_max_size',
                'sga_target',
                'memory_max_target',
                'memory_target',
                'pga_aggregate_target')
/
exit

Components and Granules in the SGA

The SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests.
Examples of memory components include

* the shared pool (used to allocate memory for SQL and PL/SQL execution)
* the java pool (used for java objects and other java execution memory)
* the buffer cache (used for caching disk blocks)

All SGA components allocate and deallocate space in units of granules.
Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size.
Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB.
For SGAs larger than 1 GB, granule size is 16 MB.

You can query the V$SGAINFO view to see the granule size that is being used by an instance.
The same granule size is used for alldynamic components in the SGA.

Source: Oracle Documentation