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.

调整后正常
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，可以设置为如下：

 

*  soft  memlock    60397977
*   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。