注：本文来源：小颜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