Oracle的memory_max_target和memory_target修改和ORA-00845: MEMORY_TARGET not supported on this system错误解决

修改Oracle的memory_max_target和memory_target

最初安装Orale11g时，采用默认自动内存管理，使用1/2Mem。后来想增大最大内存使用值。
系统内存16G Mem，想修改为3/4Mem: 16*3/4=12G=12288M。

# vim /etc/sysctl.conf
kernel.shmmax = 12884901888
# sysctl -p
再修改ORACLE 参数
sql> show parameter target;
sql> alter system set memory_max_target=12288M scope=spfile;
sql> alter system set memory_target=12288M scope=spfile;

需要重启数据库才能生效。

sql> startup 时报错：
ORA-00845: MEMORY_TARGET not supported on this system
查找资料后发现在oracle 11g中新增的内存自动管理的参数MEMORY_TARGET，它能自动调整SGA和PGA，
这个特性需要用到/dev/shm共享文件系统，而且要求/dev/shm必须大于MEMORY_TARGET，如果/dev/shm比MEMORY_TARGET小，就会报错。

解决方案：

1.初始化参数MEMORY_TARGET或MEMORY_MAX_TARGET不能大于共享内存(/dev/shm),为了解决这个问题，可以增大/dev/shm
# mount -t tmpfs shmfs -o size=12288M /dev/shm

但如果之前已经mount过了，则执行remount

# mount -o remount,size=12288M /dev/shm

2.为了确保操作系统重启之后能生效，需要修改/etc/fstab文件

tmpfs /dev/shm tmpfs defaults,size=12288M 0 0

3.如果/dev/shm没有挂载也会报上面的错，所认需要确保已经挂载，执行目录查看：

# df -h

下面是我的操作流程：

[root@localhost ~]# df -h

Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 184G 24G 160G 13% /
devtmpfs 7.8G 0 7.8G 0% /dev
tmpfs 7.8G 80K 7.8G 1% /dev/shm
tmpfs 7.8G 8.8M 7.8G 1% /run
tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
/dev/sda1 497M 109M 389M 22% /boot
tmpfs 1.6G 12K 1.6G 1% /run/user/42
tmpfs 1.6G 0 1.6G 0% /run/user/0
[root@localhost ~]# 
[root@localhost ~]# cat /etc/fstab 


#
# /etc/fstab
# Created by anaconda on Wed Feb 22 19:26:05 2017
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/centos-root / xfs defaults 0 0
UUID=2fafd7f5-0ceb-46ea-9d57-165f82e04dfe /boot xfs defaults 0 0
/dev/mapper/centos-swap swap swap defaults 0 0
[root@localhost ~]# 
[root@localhost ~]# mount -o remount,size=12288M /dev/shm
[root@localhost ~]# 
[root@localhost ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 184G 24G 160G 13% /
devtmpfs 7.8G 0 7.8G 0% /dev
tmpfs 12G 80K 12G 1% /dev/shm
tmpfs 7.8G 8.8M 7.8G 1% /run
tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
/dev/sda1 497M 127M 370M 26% /boot
tmpfs 1.6G 12K 1.6G 1% /run/user/42
tmpfs 1.6G 0 1.6G 0% /run/user/0
[root@localhost ~]# 
[root@localhost ~]# vim /etc/fstab 

# /etc/fstab
# Created by anaconda on Wed Feb 22 19:26:05 2017
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/centos-root / xfs defaults 0 0
UUID=2fafd7f5-0ceb-46ea-9d57-165f82e04dfe /boot xfs defaults 0 0
/dev/mapper/centos-swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults,size=12288M 0 0

启动数据库还是报错：
$ sqlplus / as sysdba

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

网上搜索，发现是/dev/shm还是不够大，/dev/shm必须大于MEMORY_TARGET，等于也会报错。
所以继续调整/dev/shm为13G：
# mount -o remount,size=13G /dev/shm

启动数据库成功。

持久化修改

# vim /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=13G 0 0

修改成功，查看：
SQL> show parameter target;

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
archive_lag_target integer
0
db_flashback_retention_target integer
1440
fast_start_io_target integer
0
fast_start_mttr_target integer
0
memory_max_target big integer

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
12G
memory_target big integer
12G
parallel_servers_target integer
32
pga_aggregate_target big integer
0
sga_target big integer
0
SQL> 

已经变成12G了。