Oracle9i引入pga_aggregate_target,可以自动对PGA进行调整;Oracle10引入sga_target,可以自动对
SGA进行调整。Oracle11g则对这两部分进行综合,引入memory_target,可以自动调整所有的内存,这就是新引入的自动内存管理特性。
SQL> alter system set memory_target=200m scope=spfile;
System altered.
SQL> alter system set memory_target=200m scope=spfile;
System altered.
SQL> alter system set sga_target=0 scope=spfile;
System altered.
SQL> alter system set pga_aggregate_target=0 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 150998552 bytes
Database Buffers 54525952 bytes
Redo Buffers 2412544 bytes
Database mounted.
Database opened.
设
置memory_target参数后,实际上Oracle会自动设置并调整两个以双下划线开头的隐含参数来分配SGA和PGA的内存,这和
Oracle10g自动设置sga_target后分配db_cache_size和shared_pool_size的机制是一样的
SQL> select a.ksppinm name,b.ksppstvl value
2 from x$ksppi a,x$ksppcv b
3 where a.indx=b.indx
4 and (a.ksppinm like '%sga_target%'
5 or a.ksppinm like '%pga_aggregate_target%');
NAME VALUE
---------------------------------------- ------------------------------
sga_target 0
__sga_target 125829120
pga_aggregate_target 0
__pga_aggregate_target 79691776
如果memory_max_target/memory_target设置过大,可能导致instance无法启动,报ORA-00845错误
SQL> alter system set memory_max_target=300m scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
这
个错误有点误导,实际上这并不是说该平台版本上不支持AMM特性,只是设置的memory_max_target超过了系统中设置的share
memory(/dev/shm)而已。在Oracle11g for linux中似乎使用了一种新的机制来管理共享内存段,而不是传统的sys v
shm了。在alert.ora中可以找到更准确的错误描述
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 314572800
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 209715200 and used is 0 bytes.
memory_target needs larger /dev/shm
所以,这个错误有两种解决方法。第一种自然是减少memory_max_target/memory_target。第二种则是增加/dev/shm
增加/dev/shm有两种方法:
方法一:
[root@localhost ~]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shmfs 204800 0 204800 0% /dev/shm
[root@localhost ~]# umount /dev/shm
[root@localhost ~]# mount -t tmpfs shmfs -o size=300m /dev/shm
[root@localhost ~]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shmfs 358400 0 358400 0% /dev/shm
[root@localhost trace]# ls -l /dev/shm
total 0
增加/dev/shm后再次启动instance
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1299624 bytes
Variable Size 255855448 bytes
Database Buffers 50331648 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.
再来看/dev/shm中的内容
[root@localhost trace]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shmfs 307200 122792 184408 40% /dev/shm
[root@localhost trace]# ls -l /dev/shm
total 122792
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_0
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_1
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_10
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_11
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_12
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_13
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_14
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_15
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_16
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_17
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_18
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_19
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_2
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_20
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_21
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_22
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_23
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_24
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_25
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_26
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_27
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_28
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_29
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_3
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_30
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_31
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_32
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_33
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_34
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_35
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_36
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_37
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_38
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_39
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_4
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_40
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_41
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_42
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_43
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_44
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_45
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_46
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_47
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_48
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_49
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_5
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_50
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_51
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_52
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_53
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_54
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_55
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_56
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_57
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_58
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_59
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_6
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_60
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_61
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_62
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_63
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_64
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_65
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_66
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_67
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_68
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_69
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_7
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_70
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_71
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_72
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_73
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_74
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_75
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_8
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_9
方法二:
增加tmpfs文件系统的容量。修改/etc/fstab中tmpfs对应的行,
将原来的tmpfs /dev/shm tmpfs defaults 0 0
改成tmpfs /dev/shm tmpfs defaults,size=1024M 0 0 ,这样tmpfs增大为1G
size参数也可以用G作单位:size=1G。
重新mount /dev/shm使之生效:
# mount -o remount /dev/shm
马上可以用"df -h"命令检查变化。
[root@localhost oracle]# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
none 1.0G 0 1.0G 0% /dev/shm
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/9466564/viewspace-608883/
,如需转载,请注明出处,否则将追究法律责任。