11 Replies Latest reply on Feb 24, 2015 12:38 PM by 12cdb

Out of Memory

2888673 Feb 20, 2015 7:53 AM

Dear everyone,

Once in a week our db breaks down with the following error:

ORA-01034: ORACLE not available

ORA-27102: out of memory

Linux-x86_64 Error: 12: Cannot allocate memory

Additional information: 2

Additional information: 786443

Additional information: 1660944384

I am not a oracle crack, so any help is appreciated.

Regards

Tobi

ulimit -a:

core file size (blocks, -c) 0

data seg size (kbytes, -d) unlimited

scheduling priority (-e) 0

file size (blocks, -f) unlimited

pending signals (-i) 63696

max locked memory (kbytes, -l) 64

max memory size (kbytes, -m) unlimited

open files (-n) 1024

pipe size (512 bytes, -p) 8

POSIX message queues (bytes, -q) 819200

real-time priority (-r) 0

stack size (kbytes, -s) 10240

cpu time (seconds, -t) unlimited

max user processes (-u) 16384

virtual memory (kbytes, -v) unlimited

file locks (-x) unlimited

/etc/sysctl.conf:

# Kernel sysctl configuration file for Red Hat Linux

# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and

# sysctl.conf(5) for more details.

# Controls IP packet forwarding

net.ipv4.ip_forward = 0

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing

net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.

# Useful for debugging multi-threaded applications.

kernel.core_uses_pid = 1

# Controls the use of TCP syncookies

net.ipv4.tcp_syncookies = 1

# Disable netfilter on bridges.

net.bridge.bridge-nf-call-ip6tables = 0

net.bridge.bridge-nf-call-iptables = 0

net.bridge.bridge-nf-call-arptables = 0

# Controls the default maxmimum size of a mesage queue

kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 4294967296

# oracle-rdbms-server-12cR1-preinstall setting for fs.file-max is 6815744

fs.file-max = 6815744

# oracle-rdbms-server-12cR1-preinstall setting for kernel.sem is '250 32000 100 128'

kernel.sem = 250 32000 100 128

# oracle-rdbms-server-12cR1-preinstall setting for kernel.shmmni is 4096

kernel.shmmni = 4096

# oracle-rdbms-server-12cR1-preinstall setting for kernel.shmall is 1073741824 on x86_64

# oracle-rdbms-server-12cR1-preinstall setting for kernel.shmmax is 4398046511104 on x86_64

kernel.shmmax = 4398046511104

# oracle-rdbms-server-12cR1-preinstall setting for kernel.panic_on_oops is 1 per Orabug 19642132

kernel.panic_on_oops = 1

# oracle-rdbms-server-12cR1-preinstall setting for net.core.rmem_default is 262144

net.core.rmem_default = 262144

# oracle-rdbms-server-12cR1-preinstall setting for net.core.rmem_max is 4194304

net.core.rmem_max = 4194304

# oracle-rdbms-server-12cR1-preinstall setting for net.core.wmem_default is 262144

net.core.wmem_default = 262144

# oracle-rdbms-server-12cR1-preinstall setting for net.core.wmem_max is 1048576

net.core.wmem_max = 1048576

# oracle-rdbms-server-12cR1-preinstall setting for fs.aio-max-nr is 1048576

fs.aio-max-nr = 1048576

# oracle-rdbms-server-12cR1-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500

1. Re: Out of Memory

12cdb Feb 20, 2015 8:44 AM (in response to 2888673)

Hi,
Can you add DB version and following DB parameter values;

show parameter sga_
show parameter pga_
show parameter memory
show parameter pool

Also provide total memory allocation on the server as below:

$free -g

Also look into the database alert log or more info and copy paste the complete error message.

Regards,
Pradeep
Like Show 0 Likes(0)

Actions
2. Re: Re: Out of Memory

2888673 Feb 20, 2015 9:02 AM (in response to 12cdb)

I am using Oracle 12c (Release 12.1.0.1.0) on Oracle Linux 6

Maybe I also have to say, that I am running two db instances.
And the error occurs since I have modified memory_target and memory_max_target of SID one, because the database seemed to be slow, and I wanted to allocate more memory.

show parameter sga_:
SID1:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 4G
sga_target                           big integer 0
unified_audit_sga_queue_size         integer      1048576

SID2:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1200M
sga_target                           big integer 1200M
unified_audit_sga_queue_size         integer     1048576

show parameter _pga:
SID1:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2848M
pga_aggregate_target                 big integer 0

SID2:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 395M

show parameter memory:
SID1:

NAME                                 TYPE         VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 4G
memory_target                        big integer 4G
shared_memory_address                integer     0

SID2:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0

show parameter pool:
SID1:

NAME                                 TYPE     VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 26843545
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0

SID2:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 14260633
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0

free -g:

             total       used       free     shared    buffers     cached
Mem:             7          5          2          3          0          4
-/+ buffers/cache:          0          7
Swap:            3          0          3

alert-log:

Thu Feb 19 04:29:13 2015
TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P3610 (1876) VALUES LESS THAN (TO_DATE(' 2015-02-20 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P3611 (1876) VALUES LESS THAN (TO_DATE(' 2015-02-20 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Thu Feb 19 05:28:57 2015
Process m000 died, see its trace file
Process m000 died, see its trace file
Process m000 died, see its trace file
Process m001 died, see its trace file
Process m000 died, see its trace file
Process m001 died, see its trace file
Process m000 died, see its trace file
Like Show 0 Likes(0)

Actions
3. Re: Out of Memory

JohnWatson2 Feb 20, 2015 9:18 AM (in response to 2888673)

How big is your tmpfs /dev/shm file system?
Like Show 0 Likes(0)

Actions
4. Re: Out of Memory

John Stegeman Feb 20, 2015 9:26 AM (in response to 2888673)

"database seemed to be slow" so you added more memory.

What facts did you have that indicated adding more memory would solve the issue? Sounds like the classic, "Ready, fire, aim" approach to problem resolution
Like Show 0 Likes(0)

Actions
5. Re: Out of Memory

12cdb Feb 20, 2015 9:37 AM (in response to 2888673)

total host memoery = 7G
SID 1 :
memory_max =4G (Total SGA+PGA)
sga_max_size = 4G
PGA= 2.28G
------------
TOTAL = 6.28G
How can you cross the 4GB max limit by allocating individual components more i.e. 6.28G.
As for as I know you can not do that so you have to reset sga and pag allocation to make sure the total allocation does not cross 4GB limit set by memory_max.
example:
sga_max_size = 2.5G
PGA= 1.5G

Changing sga_max_size will require database bounce.

HTH,
Pradeep
Like Show 0 Likes(0)

Actions
6. Re: Out of Memory

JohnWatson2 Feb 20, 2015 10:04 AM (in response to 12cdb)

Expected behaviour. sga_max_size will default to memory_max_size.
Like Show 0 Likes(0)

Actions
7. Re: Out of Memory

12cdb Feb 20, 2015 10:42 AM (in response to JohnWatson2)

Thanks.
As for as I remeber memory_max is sum of SGA & PGA, and if it defaults sga_max_size = memory_max, what space is allocated to PGA when the instance starts ?
Like Show 0 Likes(0)

Actions
8. Re: Out of Memory

jgarry Feb 20, 2015 9:48 PM (in response to 2888673)

First of all, throwing memory at a system because it is slow is the wrong thing to do. Does it work when you set it back? You need to find out what is slow to be an oracle crack, rather than on crack.
Second of all, running 2 instances means you have to account for both, Oracle doesn't do that for you. It could well be what JohnWatson2 said.
Third, see Oracle Automatic Memory Management | Kevin Closson's Blog: Platforms, Databases and Storage
Like Show 0 Likes(0)

Actions
9. Re: Out of Memory

Osama_Mustafa Feb 22, 2015 9:15 PM (in response to 2888673)

Ora-27102: Out Of Memory: Linux Error: 12: Cannot Allocate Memory with LOCK_SGA=TRUE (Doc ID 401077.1)
Like Show 0 Likes(0)

Actions
10. Re: Out of Memory

2888673 Feb 24, 2015 8:19 AM (in response to Osama_Mustafa)

I have raised my tmpfs to 10 GB.

Ok let see if I understand it right.
I actually could set sga_target and sga_max_target, pga_aggregate_target and memory_max_target to 0.

If I only set memory_target, oracle will automatically use 60% for SGA and 40% PGA correct?

But I still have to make sure that the sum of all memory_targets is not higher than my allocated tmpfs. Correct?

Would that be a solution?
Like Show 0 Likes(0)

Actions
11. Re: Out of Memory

12cdb Feb 24, 2015 12:38 PM (in response to 2888673)

>> If I only set memory_target, oracle will automatically use 60% for SGA and 40% PGA correct?
Not correct asllocation to SGA & PGA will be manged by Oracle automatically and as for as I know not in that ratio you have give.

You can set MEMORY_MAX_TARGET & MEMORY_TARGET to same value and make sure they are set below available memory on the server and tmpfs.

HTH,
Pradeep
Like Show 0 Likes(0)

Actions