解决Linux上11g的ORA-00845错误

下午想把一个11.2.0.2实例的memory_target AMM内存初始化参数修改到2000M,改好后重启发现实例起不来了,出现了ORA-00845错误:

[oracle@rh2 ~]$ 
[oracle@rh2 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 3 19:43:50 2011

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

Connected to an idle instance.

SQL> startup nomount;
ORA-00845: MEMORY_TARGET not supported on this system

======================= ALERT.LOG======================

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 2097152000 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 1871466496 and used is 200790016 bytes. 
Ensure that the mount point is /dev/shm for this directory.
memory_target needs larger /dev/shm

看起来是因为采用了Automatic Memory Management的特性,所以Oracle采用了一种基于POSIX的共享内存使用风格,在AMM中Oracle不像之前版本那样利用”一整块”内存作为SGA,而是使用一块块小的”chunk”,以满足SGA与PGA之前交换内存的目的,具体可以参考下图:
AMM1

针对上述ORA-00845错误,我们需要修改映射内存文件夹/dev/shm的装载参数,针对正在运行的Linux操作系统,可以remount这个tmpfs装载点,如:

[root@rh2 ~]# umount /dev/shm
umount: /dev/shm: device is busy
umount: /dev/shm: device is busy

[root@rh2 ~]# ls /dev/shm
JOXSHM_EXT_0_PROD1_8323079   JOXSHM_EXT_25_PROD1_8323079  ora_+ASM1_7569414_11  
JOXSHM_EXT_10_PROD1_8323079  JOXSHM_EXT_26_PROD1_8323079  ora_+ASM1_7569414_12  
............

/* 可以看到如果ASM实例也使用AMM的话同样会在/dev/shm目录下产生内存映像 
    为了umount该装载点,我们不得不首先shutdown ASM instance ! */

[root@rh2 ~]# su - grid
[grid@rh2 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 3 20:05:24 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> shutdown immediate;
ORA-01031: insufficient privileges
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[grid@rh2 ~]$ sqlplus  / as sysasm

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 3 20:05:32 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> shutdown immediate;
ORA-15097: cannot SHUTDOWN ASM instance with connected client (process 1576)
SQL> shutdown abort;
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

[root@rh2 ~]# umount /dev/shm

[root@rh2 ~]# mount -t tmpfs shmfs -o size=4000m /dev/shm

/* 这里的size指定了shm目录的装载大小,4000m可能对你的系统仍然不够,
    那么你有理由设一个更大的值  */


[root@rh2 ~]# mount|grep shmfs
shmfs on /dev/shm type tmpfs (rw,size=4000m)

Startup ASM...........

[root@rh2 ~]# su - oracle
[oracle@rh2 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 3 20:09:40 2011

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size                  2228072 bytes
Variable Size            1476395160 bytes
Database Buffers          603979776 bytes
Redo Buffers                5177344 bytes

/* 实例启动成功! */

上述umount/mount的方式只在操作系统的此次生命周期中生效,如果重启的话shmfs目录仍会以默认方式装载;如果需要永久生效的话,我们需要修改系统参数文件/etc/fstab中的shm条目:

/* 修改前的shm装载参数 */

[root@rh2 ~]# grep shm /etc/fstab 
tmpfs                   /dev/shm                tmpfs   defaults        0 0

/* 修改后的shm装载参数 */

[root@rh2 ~]# grep shm /etc/fstab 
tmpfs                   /dev/shm                tmpfs   rw,size=4000m   0 0

11g内存管理新特性的internal表现

11g中自动内存管理(Automatic Memory Management ,amm), 令dba在数据库内存配置的相关工作更加简单. AMM现在将SGA与PGA整合到一起管理,而您只需要设置memory_target参数即可限定Oracle将使用到的内存尺寸,Oracle将自动分配这些内存空间.

您一定很困惑Oracle在unix平台上是如何对共享的sga内存空间与私有的pga内存空间进行切换的?这意味着Oracle需要经常释放sga中的部分内存以便允许pga去使用它们.传统的sys V 使用的共享内存shm接口不具备如此的灵活性.我们来看看Oracle是如何做到的?

先来获取我们需要的11g实例共享内存id(shared memory id)

[oracle@rh2 ~]$ sysresv                // 该命令需要设置了正确的LD_LIBRARY_PATH
IPC Resources for ORACLE_SID “T11” :
Shared Memory:
ID              KEY
65537           0x95c84bb8
Semaphores:
ID              KEY
327681          0xdf521034
Oracle Instance alive for sid “T11”

试着找出对应的sys V共享内存段:

[oracle@rh2 ~]$ ipcs -m

—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x95c84bb8 65537      oracle    660        4096       0

对应的存在着共享内存段,但该段很小只有 4096 byte哦,既然Oracle不再把sga放到共享段中,那藏到哪里去了呢?

我们接下来检查Oracle实例进程的内存影射状况.

[oracle@rh2 ~]$  pmap `pgrep -f lgwr`|less
14889:   ora_lgwr_T11
0000000000400000 155016K r-x–  /usr/oracle/product/11g/db_1/bin/oracle
0000000009c62000  12404K rw—  /usr/oracle/product/11g/db_1/bin/oracle
000000000a87f000    732K rwx–    [ anon ]
0000000060000000      4K r–s-  /dev/shm/ora_T11_65537_0
0000000060001000  16380K rw-s-  /dev/shm/ora_T11_65537_0
0000000061000000  16384K rw-s-  /dev/shm/ora_T11_65537_1
0000000062000000  16384K rw-s-  /dev/shm/ora_T11_65537_2
0000000063000000  16384K rw-s-  /dev/shm/ora_T11_65537_3
0000000064000000  16384K rw-s-  /dev/shm/ora_T11_65537_4
0000000065000000  16384K rw-s-  /dev/shm/ora_T11_65537_5
0000000066000000  16384K rw-s-  /dev/shm/ora_T11_65537_6
0000000067000000  16384K rw-s-  /dev/shm/ora_T11_65537_7
0000000068000000  16384K rw-s-  /dev/shm/ora_T11_65537_8
0000000069000000  16384K rw-s-  /dev/shm/ora_T11_65537_9
000000006a000000  16384K rw-s-  /dev/shm/ora_T11_65537_10
000000006b000000  16384K rw-s-  /dev/shm/ora_T11_65537_11
000000006c000000  16384K rw-s-  /dev/shm/ora_T11_65537_12
000000006d000000  16384K rw-s-  /dev/shm/ora_T11_65537_13
000000006e000000  16384K rw-s-  /dev/shm/ora_T11_65537_14
000000006f000000  16384K rw-s-  /dev/shm/ora_T11_65537_15
0000000070000000  16384K rw-s-  /dev/shm/ora_T11_65537_16
0000000071000000  16384K rw-s-  /dev/shm/ora_T11_65537_17
0000000072000000  16384K rw-s-  /dev/shm/ora_T11_65537_18
0000000073000000  16384K rw-s-  /dev/shm/ora_T11_65537_19
0000000074000000  16384K rw-s-  /dev/shm/ora_T11_65537_20
0000000075000000  16384K rw-s-  /dev/shm/ora_T11_65537_21
0000000076000000  16384K rw-s-  /dev/shm/ora_T11_65537_22
0000000077000000  16384K rw-s-  /dev/shm/ora_T11_65537_23
0000000078000000  16384K rw-s-  /dev/shm/ora_T11_65537_24
0000000079000000  16384K rw-s-  /dev/shm/ora_T11_65537_25
000000007a000000  16384K rw-s-  /dev/shm/ora_T11_65537_26
000000007b000000  16384K rw-s-  /dev/shm/ora_T11_65537_27
000000007c000000  16384K rw-s-  /dev/shm/ora_T11_65537_28
000000007d000000  16384K rw-s-  /dev/shm/ora_T11_65537_29
000000007e000000  16384K rw-s-  /dev/shm/ora_T11_65537_30
000000007f000000  16384K rw-s-  /dev/shm/ora_T11_65537_31
0000000080000000  16384K rw-s-  /dev/shm/ora_T11_65537_32
0000000081000000  16384K rw-s-  /dev/shm/ora_T11_65537_33
0000000082000000  16384K rw-s-  /dev/shm/ora_T11_65537_34
0000000083000000  16384K rw-s-  /dev/shm/ora_T11_65537_35
0000000084000000  16384K rw-s-  /dev/shm/ora_T11_65537_36
0000000085000000  16384K rw-s-  /dev/shm/ora_T11_65537_37
0000000086000000  16384K rw-s-  /dev/shm/ora_T11_65537_38
0000000087000000  16384K rw-s-  /dev/shm/ora_T11_65537_39
0000000088000000  16384K rw-s-  /dev/shm/ora_T11_65537_40
0000000089000000  16384K rw-s-  /dev/shm/ora_T11_65537_41
000000008a000000  16384K rw-s-  /dev/shm/ora_T11_65537_42

............

0000003e79109000      4K rw—  /lib64/tls/librt-2.3.4.so
0000003e7910a000     64K rw—    [ anon ]
0000003e79600000     84K r-x–  /lib64/libnsl-2.3.4.so
0000003e79615000   1020K —–  /lib64/libnsl-2.3.4.so
0000003e79714000      4K r—-  /lib64/libnsl-2.3.4.so
0000003e79715000      4K rw—  /lib64/libnsl-2.3.4.so
0000003e79716000      8K rw—    [ anon ]
0000007fbfff3000     52K rwx–    [ stack ]
ffffffffff600000      4K r-x–    [ anon ]
total          2497724K

pmap工具诠释了进程相关共享内存的情况,可以看到许多个16MB的"文件"对应了Oracle服务进程的空间地址.这是linux上POSIX风格的共享内存管理模式,使用"文件"形式包含共享内存段.

借助于将sga分割成许多块,Oracle可以很容易地把sga部分内存返回给OS,而服务器进程即可以利用到这些内存.(当memory_max_target>1024时,颗粒为16MB,否则为4MB).

接下来我们测试下Oracle是如何释放部分sga内存的.

对比实例启动前后:

启动前:

[oracle@rh2 ~]$ ls -l /dev/shm
总用量 0

启动后:
[oracle@rh2 ~]$ ls -l /dev/shm
总用量 1373704
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_0
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_1
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_10
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_100
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_101
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_102
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_103
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_104
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_105
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_106
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_107
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_108
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_109
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_11
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_110
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_111
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_112
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_113
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_114
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_115
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_116
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_117
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_118
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_119
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_12

可以看到启动后出现的16MB文件形式共享内存中部分大小为0,这些块被选出当发生内存交换时来被’destory’.使用pmap工具仍可以看到该部分影射,而实际上已经被Oracle释放了.

现在我们加大pga,观察其交换情况.

SQL> alter system set pga_aggregate_target=1900M ;

System altered.

[oracle@rh2 ~]$ ls -l /dev/shm
总用量 289984
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_0
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_1
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_10
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_100
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_101
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_102
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_103
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_104
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_105
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_106
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_107
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_108
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_109
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_11
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_110
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_111
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_112
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_113
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_114
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_115
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_116
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_117
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_118
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_119
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_12
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_120
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_121

可以看到出现了大量size为0的"文件",期许的交换出现了.

可见在11g中Oracle采用了新的共享内存实现方式,区别于旧的"一块式"共享段,更为灵活了.

沪ICP备14014813号

沪公网安备 31010802001379号