oracle 11g 修改 sga,oracle 11g 调整SGA

1. 查看oracle 使用的参数文件

SQL> show parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfilemmdsdb.ora

2.参看系统内存

SQL> ho cat /proc/meminfo

MemTotal:     16307484 kB

MemFree:        392528 kB

Buffers:        164264 kB

Cached:       14992764 kB

SwapCached:          0 kB

Active:        2756496 kB

Inactive:     12695660 kB

SwapTotal:    16771852 kB

SwapFree:     16771768 kB

Dirty:              28 kB

Writeback:           0 kB

AnonPages:      295140 kB

Mapped:         200196 kB

Slab:           378052 kB

SReclaimable:   361152 kB

SUnreclaim:      16900 kB

PageTables:      23564 kB

NFS_Unstable:        0 kB

Bounce:              0 kB

WritebackTmp:        0 kB

CommitLimit:  24925592 kB

Committed_AS:  1575240 kB

VmallocTotal: 34359738367 kB

VmallocUsed:    312888 kB

VmallocChunk: 34359424119 kB

HugePages_Total:     0

HugePages_Free:      0

HugePages_Rsvd:      0

HugePages_Surp:      0

Hugepagesize:     2048 kB

DirectMap4k:     10240 kB

DirectMap2M:  16766976 kB

#oracle 可以使用物理内存的50%~70%

3.调整SGA

SQL> alter system set sga_max_size=5120m scope=spfile;

System altered.

SQL> alter system set sga_target=5120m scope=spfile;

System altered.

4.重启实例

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 5344731136 bytes

Fixed Size                  2213136 bytes

Variable Size             603982576 bytes

Database Buffers         4697620480 bytes

Redo Buffers               40914944 bytes

Database mounted.

Database opened.

SQL>

5.验证是否成功

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 5G

sga_target                           big integer 5G

SQL>

6.spfile 与 pfile相互转换

pfile --> spfile

create pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmmdsdb.ora' from  spfile

spfile -->pfile

create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilemmdsdb.ora' from pfile