oracle数据库内存调整之增加内存

模拟操作系统内存从2G增加为8G后，调整数据库内存参数，示例中参数不作为实际生产环境参考，因为因需所取，调整参数需要根据数据库相应调整，避免小牛拉大车，大牛拉小车的现象。

查看原始配置数据
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 7 18:42:30 2017

Copyright © 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter sga;

NAME                                 TYPE        VALUE

---

lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 700M
sga_target                           big integer 0
SQL> show parameter pga

NAME                                 TYPE        VALUE

---

pga_aggregate_target                 big integer 0
SQL> show parameter mem

NAME                                 TYPE        VALUE

---

hi_shared_memory_address             integer     0
memory_max_target                    big integer 1G
memory_target                        big integer 1G
shared_memory_address                integer     0
SQL> ho cat /etc/sysctl.conf | grep shmmax

oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4398046511104 on x86_64

oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4294967295 on i386

kernel.shmmax = 4398046511104
kernel.shmmax = 4294967295

SQL> select 4294967295/1024/1024 from dual;

4294967295/1024/1024

4096

SQL> select 810241024*1024 from dual;

810241024*1024

8589934592

SQL> exit
[oracle@rac1 ~]$ exit
[root@rac1 ~]# /oracle/app/grid/11.2.0.3/bin/crsctl stop crs
[root@rac1 ~]# halt
关机后增加物理内存，开机后修改参数
[root@rac1 ~]# echo “kernel.shmmax = 8589934592” >> /etc/sysctl.conf
[root@rac1 ~]# sysctl -p
[root@rac1 ~]# df -ThP
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda5     ext4     87G   20G   63G  24% /
tmpfs        tmpfs    4.0G     0  4.0G   0% /dev/shm
/dev/sda1     ext4    504M   61M  418M  13% /boot
/dev/sda2     ext4    7.7G  146M  7.2G   2% /tmp
[root@rac1 ~]# grep tmpfs /etc/fstab
tmpfs                   /dev/shm                tmpfs   defaults        0 0
[root@rac1 ~]# vim /etc/fstab
[root@rac1 ~]# grep tmpfs /etc/fstab
tmpfs                   /dev/shm                tmpfs   defaults,size=8192m        0 0
[root@rac1 ~]# mount -o remount tmpfs
[root@rac1 ~]# df -ThP
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda5     ext4     87G   20G   63G  24% /
tmpfs        tmpfs    8.0G  116M  7.9G   2% /dev/shm
/dev/sda1     ext4    504M   61M  418M  13% /boot
/dev/sda2     ext4    7.7G  146M  7.2G   2% /tmp
调整
SQL> select 7*1024 from dual;

7*1024

7168

SQL> select 80.561024 from dual;

80.564.48*1024

4587.52

SQL> alter system set memory_max_target=7168M scope=spfile;

System altered.

SQL> alter system set memory_target=7168M scope=spfile;

System altered.

SQL> alter system set sga_max_size=4587M scope=spfile;

System altered.

SQL> alter system set sga_target=4587M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area    4793552896 bytes
Fixed Size                     2261328 bytes
Variable Size               1124077232 bytes
Database Buffers            3657433088 bytes
Redo Buffers                   9781248 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME                                 TYPE        VALUE

---

lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 4592M
sga_target                           big integer 4592M
SQL> show parameter pga

NAME                                 TYPE        VALUE

---

pga_aggregate_target                 big integer 0
SQL> show parameter mem

NAME                                 TYPE        VALUE

---

hi_shared_memory_address             integer     0
memory_max_target                    big integer 7G
memory_target                        big integer 7G
shared_memory_address                integer     0

SQL> exit

声明： 生产库操作，请明确每一个参数，每一条语句的含义。

作者：小颜Kevin
来源：CSDN
原文：https://blog.csdn.net/xiaoyankevin/article/details/54177668
版权声明：本文为博主原创文章，转载请附上博文链接！