ORACLE内存用尽,ORACLE DB实例因内存耗尽而DOWN

db 进程退出

ORACLE11G 退出.日志报

PMON (ospid: 7028): terminating the instance due to error 471

[Process 0x0x15a7c5008 appears to be hung while dumping

Current time = 439317916, process death time = 439257905 interval =

60000

Attempting to kill process 0x0x15a7c5008 with OS pid = 7067

OSD kill succeeded for process 0x15a7c5008

PMON (ospid: 7028): terminating the instance due to error 471

Instance terminated by PMON, pid = 7028

Thu Nov 09 13:23:03 2017

Starting ORACLE instance (normal)

查BAIDU 说是内存用尽所致

oracle@NY-DB2 trace]$ free -m

total used free shared buffers cached

Mem: 7885 7752 133 0 156 6379

-/+

buffers/cache: 1215 6669

Swap: 7951 4 7947

/var/log/message 日志

Nov 8 11:34:42 YN-DB2A init: tty (/dev/tty5)

main process ended, respawning

Nov 8 11:34:42 YN-DB2A init: tty (/dev/tty5) main

process ended, respawning

Nov 8 12:40:27 YN-DB2A kernel: packagekitd[6376]:

segfault at 29 ip 0000003d9d257dd3 sp 00007fff6c632470 error 4 in

libglib-2.0.so.0.2200.5[3d9d200000+e4000]

Nov 8 12:40:27 YN-DB2A abrt[6381]: abrtd is not

running. If it crashed, /proc/sys/kernel/core_pattern contains a

stale value, consider resetting it to 'core'

Nov 9 03:14:04 YN-DB2A pcscd:

winscard.c:309:SCardConnect() Reader E-Gate 0 0 Not Found

Nov 9 03:14:04 YN-DB2A pcscd:

winscard.c:309:SCardConnect() Reader E-Gate 0 0 Not Found

调整前先查检一下当前大小

$sqlplus sys/oracle@orcl as sysdba

SQL> show parameter memory;

SQL> show parameter SGA;

调整(比原来调小些):

SQL> alter system set sga_max_size=3G scope=spfile;

System altered.

SQL> alter system set memory_max_target=4G scope=spfile;

System altered.

SQL> alter system set memory_target=4G scope=spfile;

System altered.

注意,实际只要调整MEMORY就可以SGA可不用调,如

SQL>alter system set memory_max_target=4G scope=spfile;

System altered.

SQL> alter system set memory_target=4G scope=spfile;

System altered.

总结如下，以后调整sga的顺序是，

先调整tmpfs的大小，即/dev/shm,系统默认情况为内存的一半.

再调整MEMORY_TARGET 的大小，

最后调整sga，

保证tmpfs>MEMORY_TARGET >sga+pga

调整后正常

SQL> ! free

total used free shared buffers cached

Mem: 8075012 7421776 653236 0 170144 6498688

-/+

buffers/cache: 752944 7322068

Swap: 8142840 5952 8136888

一.HugePages 介绍

二． 配置HugePages

关键:

初始化参数MEMORY_TARGET

和MEMORY_MAX_TARGET 为0

配置的/etc/security/limits.conf 中的 memlock

用配置/etc/sysctl.conf

2.1 第一步：

设置memlock

在/etc/security/limits.conf文件中添加memlock的限制，注意该值略微小于实际物理内存的大小。

比如物理内存是64GB，可以设置为如下：

*softmemlock60397977

* hard

memlock 60397977

如果这里的值超过了SGA的需求，也没有不利的影响。

如果使用了Oracle Linux的oracle­-validated包，或者Exadata

DB compute会自动配置这个参数。

2.2 第二步：

验证memlock

使用如下命令查看参数值：

$ ulimit -l

60397977

2.3

第三步：11g中禁用AMM

如果Oracle 是11g以后的版本，那么默认创建的实例会使用Automatic Memory

Management (AMM)的特性，该特性与HugePage不兼容。

在设置HugePage之前需要先禁用AMM。设置初始化参数MEMORY_TARGET

和MEMORY_MAX_TARGET 为0即可。

使用AMM的情况下，所有的SGA 内存都是在/dev/shm

下分配的，因此在分配SGA时不会使用HugePage。这也是AMM 与HugePage不兼容的原因。

另外：默认情况下ASM instance 也是使用AMM的，但因为ASM

实例不需要大SGA，所以对ASM 实例使用HugePages意义不大。

如果我们要使用HugePage，那么就必须先确保没有设置MEMORY_TARGET/

MEMORY_MAX_TARGET参数。

2.4

第四步：计算vm.nr_hugepages的建议值

确保所有的数据库实例都已经启动，包括ASM 实例。使用hugepages_settings.sh

脚本获取thevm.nr_hugepages内核参数的建议值。

$ ./hugepages_settings.sh

...

Recommended setting: vm.nr_hugepages = 1496

$

也可以根据自己的经验来计算该值。

脚本如下：

#!/bin/bash # #hugepages_settings.sh # #

Linux bash scriptto compute values for the #

recommendedHugePages/HugeTLB configuration # #

Note: This scriptdoes calculation for all shared

memory

#

segmentsavailable when the script is run, no matter

it

#

is an OracleRDBMS shared memory segment or not. # #

This script isprovided by Doc ID 401749.1 from My Oracle

Support #http://support.oracle.com #

Welcome text

echo

"

This

script isprovided by Doc ID 401749.1 from My Oracle

Support (http://support.oracle.com)where

it is intended to compute values

for the

recommendedHugePages/HugeTLB configuration for the current

shared memory

segments.Before proceeding with the execution please note

following:

*

For ASMinstance, it needs to configure ASMM instead of

AMM.

*

The'pga_aggregate_target' is outside the SGA

and

youshould accommodate this while calculating SGA

size.

*

In case youchanges the DB SGA

size,

as thenew SGA will not fit in the previous HugePages

configuration,

it haer disable the whole

HugePages,

startthe DB with new SGA size and run the script

again.

And

make sure that:

*

OracleDatabase instance(s) are up and running

*

OracleDatabase 11g Automatic Memory Management (AMM) is not

setup

(SeeDoc ID 749851.1)

*

The sharedmemory segments can be listed by

command:

#

ipcs -m

Press

Enter toproceed..."

read #

Check for thekernel version KERN=`uname

-r |awk -F. '{ printf("%d.%d\n",$1,$2); }'`

#

Find out theHugePage size HPG_SZ=`grepHugepagesize

/proc/meminfo | awk '{print $2}'` if

[ -z"$HPG_SZ" ];then

echo"The hugepages may not be supported

in the system where the script isbeing executed."

exit

1

fi #

Initialize thecounter NUM_PG=0 #

Cumulative numberof pages required to handle the running shared

memory segments for

SEG_BYTES in`ipcs -m | cut -c44-300 | awk '{print $1}' | grep

"[0-9][0-9]*"` do

MIN_PG=`echo

"$SEG_BYTES/($HPG_SZ*1024)" | bc -q`

if

[ $MIN_PG -gt 0 ]; then

NUM_PG=`echo

"$NUM_PG+$MIN_PG+1" | bc -q`

fi done RES_BYTES=`echo"$NUM_PG

* $HPG_SZ * 1024" | bc -q`

#

An SGA less than100MB does not make sense #

Bail out if thatis the case if

[ $RES_BYTES -lt100000000 ]; then

echo

"***********"

echo

"** ERROR **"

echo

"***********"

echo

"Sorry! There are not enough total of shared memory

segmentsallocated for HugePagesconfiguration.

HugePages can only be used for shared memory

segments that

you can listby command: #

ipcs -m

of

a size that canmatch an Oracle Database SGA. Please make sure

that:

*

OracleDatabase instance is up and

running

*

OracleDatabase 11g Automatic Memory Management (AMM) is not

configured"

exit

1

fi #

Finish withresults case

$KERN in

'2.4')

HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc

-q`;

echo

"Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL"

;;

'2.6')

echo "Recommended setting: vm.nr_hugepages = $NUM_PG"

;;

*)

echo "Unrecognized kernel version $KERN. Exiting."

;;

esac #

End

2.5 第五步：

在/etc/sysctl.conf文件中设置vm.nr_hugepages参数

...

vm.nr_hugepages = 1496

...

2.6

第六步：停止所有实例，并重启服务器

2.7

验证配置

在重启系统之后，确保所有的数据库实例都已经启动，使用如下命令检查HugePage的状态：

# grep HugePages /proc/meminfo

HugePages_Total:1496

HugePages_Free:485

HugePages_Rsvd:446

HugePages_Surp:0

为了确保HugePages配置的有效性，HugePages_Free值应该小于HugePages_Total的值，并且应该等于HugePages_Rsvd的值。

Hugepages_Free和HugePages_Rsvd的值应该小于SGA

分配的gages。