注：本文来源：小颜Kevin 《oracle数据库内存调整之增加内存》
模拟操作系统内存从2G增加为8G后，调整数据库内存参数，示例中参数不作为实际生产环境参考，因为因需所取，调整参数需要根据数据库相应调整，避免小牛拉大车，大牛拉小车的现象。
查看原始配置数据
1 [oracle@rac1 ~]$ sqlplus / as sysdba
2
3 SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 7 18:42:30 2017
4
5 Copyright (c) 1982, 2013, Oracle. All rights reserved.
6
7 Connected to:
8 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
9 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
10 Data Mining and Real Application Testing options
11
12 SQL> show parameter sga;
13
14 NAME TYPE VALUE
15 ------------------------------------ ----------- ------------------------------
16 lock_sga boolean FALSE
17 pre_page_sga boolean FALSE
18 sga_max_size big integer 700M
19 sga_target big integer 0
20 SQL> show parameter pga
21
22 NAME TYPE VALUE
23 ------------------------------------ ----------- ------------------------------
24 pga_aggregate_target big integer 0
25 SQL> show parameter mem
26
27 NAME TYPE VALUE
28 ------------------------------------ ----------- ------------------------------
29 hi_shared_memory_address integer 0
30 memory_max_target big integer 1G
31 memory_target big integer 1G
32 shared_memory_address integer 0
33 SQL> ho cat /etc/sysctl.conf | grep shmmax
34 # oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4398046511104 on x86_64
35 # oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4294967295 on i386
36 kernel.shmmax = 4398046511104
37 kernel.shmmax = 4294967295
38
39 SQL> select 4294967295/1024/1024 from dual;
40
41 4294967295/1024/1024
42 --------------------
43 4096
44
45 SQL> select 8*1024*1024*1024 from dual;
46
47 8*1024*1024*1024
48 ----------------
49 8589934592
50
51 SQL> exit
52 [oracle@rac1 ~]$ exit
53 [root@rac1 ~]# /oracle/app/grid/11.2.0.3/bin/crsctl stop crs
54 [root@rac1 ~]# halt
55 关机后增加物理内存，开机后修改参数
56 [root@rac1 ~]# echo "kernel.shmmax = 8589934592" >> /etc/sysctl.conf
57 [root@rac1 ~]# sysctl -p
58 [root@rac1 ~]# df -ThP
59 Filesystem Type Size Used Avail Use% Mounted on
60 /dev/sda5 ext4 87G 20G 63G 24% /
61 tmpfs tmpfs 4.0G 0 4.0G 0% /dev/shm
62 /dev/sda1 ext4 504M 61M 418M 13% /boot
63 /dev/sda2 ext4 7.7G 146M 7.2G 2% /tmp
64 [root@rac1 ~]# grep tmpfs /etc/fstab
65 tmpfs /dev/shm tmpfs defaults 0 0
66 [root@rac1 ~]# vim /etc/fstab
67 [root@rac1 ~]# grep tmpfs /etc/fstab
68 tmpfs /dev/shm tmpfs defaults,size=8192m 0 0
69 [root@rac1 ~]# mount -o remount tmpfs
70 [root@rac1 ~]# df -ThP
71 Filesystem Type Size Used Avail Use% Mounted on
72 /dev/sda5 ext4 87G 20G 63G 24% /
73 tmpfs tmpfs 8.0G 116M 7.9G 2% /dev/shm
74 /dev/sda1 ext4 504M 61M 418M 13% /boot
75 /dev/sda2 ext4 7.7G 146M 7.2G 2% /tmp
76 调整
77 SQL> select 7*1024 from dual;
78
79 7*1024
80 ----------
81 7168
82
83 SQL> select 8*0.56*1024 from dual;
84
85 8*0.56*4.48*1024
86 ----------------
87 4587.52
88
89 SQL> alter system set memory_max_target=7168M scope=spfile;
90
91 System altered.
92
93 SQL> alter system set memory_target=7168M scope=spfile;
94
95 System altered.
96
97 SQL> alter system set sga_max_size=4587M scope=spfile;
98
99 System altered.
100
101 SQL> alter system set sga_target=4587M scope=spfile;
102
103 System altered.
104
105 SQL> shutdown immediate
106 Database closed.
107 Database dismounted.
108 ORACLE instance shut down.
109 SQL> startup
110 ORACLE instance started.
111
112 Total System Global Area 4793552896 bytes
113 Fixed Size 2261328 bytes
114 Variable Size 1124077232 bytes
115 Database Buffers 3657433088 bytes
116 Redo Buffers 9781248 bytes
117 Database mounted.
118 Database opened.
119 SQL> show parameter sga
120
121 NAME TYPE VALUE
122 ------------------------------------ ----------- ------------------------------
123 lock_sga boolean FALSE
124 pre_page_sga boolean FALSE
125 sga_max_size big integer 4592M
126 sga_target big integer 4592M
127 SQL> show parameter pga
128
129 NAME TYPE VALUE
130 ------------------------------------ ----------- ------------------------------
131 pga_aggregate_target big integer 0
132 SQL> show parameter mem
133
134 NAME TYPE VALUE
135 ------------------------------------ ----------- ------------------------------
136 hi_shared_memory_address integer 0
137 memory_max_target big integer 7G
138 memory_target big integer 7G
139 shared_memory_address integer 0
140
141 SQL> exit