11g修改内存大小

最新推荐文章于 2024-07-15 19:07:15 发布

原创最新推荐文章于 2024-07-15 19:07:15 发布 · 2.2k 阅读

1 ·

CC 4.0 BY-SA版权

体系专栏收录该内容

13 篇文章

订阅专栏

本文档介绍了如何在Oracle 11g中查看和修改SGA_max_size, sga_target, PGA_AGGREGATE_TARGET以及MEMORY_TARGET参数。通过SQL查询展示参数值，并说明了如何通过创建pfile、调整Linux系统参数以及重启数据库来修改内存设置。当系统参数tmpfs小于MEMORY_TARGET时，会遇到'ORA-00845: MEMORY_TARGET not supported on this system'错误，需要通过调整/dev/shm的大小来解决。" 123284193,12483013,Python中的列表与元组详解,"['Python', '数据结构', '算法', '编程基础']

1.确认当前参数文件

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string /u01/oracle/product/11.2.0.4/d

bs/spfileappserv.ora

SQL>

sga_max_size为实例允许使用的sga上限,一个静态参数,是不能动态修改的

SQL> show parameter sga_max_size

NAME TYPE VALUE

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

sga_max_size big integer 8384M

SQL>

sga_target为10g推出的sga自动管理的参数,动态参数,可以动态修改.

SQL> show parameter sga_target

NAME TYPE VALUE

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

sga_target big integer 8384M

SQL>

PGA_AGGREGATE_TARGET-此参数用来指定所有session总计可以使用最大PGA内存。这个参数可以被动态的更改,取值范围从10M -- （4096G-1 ）bytes。

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE

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

pga_aggregate_target big integer 2258291200

SQL>

Oracle 11g则对这两部分进行综合，引入memory_target，可以自动调整所有的内存，这就是新引入的自动内存管理特性。

SQL> show parameter MEMORY_TARGET

NAME TYPE VALUE

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

memory_target big integer 0

SQL>

11g 中新增MEMORY_MAX_TARGET 参数

功能：memory_max_target是设定Oracle能占OS多大的内存空间

SQL> show parameter memory_max_target

NAME TYPE VALUE

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

memory_max_target big integer 0

SQL>

2.由当前spfile创建pfile（作备份用）

[root@appserv172 dbs]# ls -lrt

total 48

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r----- 1 oracle oinstall 24 Jan 16 2014 lkAPPSERV

-rw-r----- 1 oracle oinstall 1536 Jan 16 2014 orapwappserv

-rw-r--r-- 1 oracle oinstall 974 Feb 2 2015 initappserv.ora

-rw-rw---- 1 oracle oinstall 1544 Oct 30 19:30 hc_appserv.dat

-rw-r----- 1 oracle oinstall 3584 Nov 2 16:36 spfileappserv.ora

[root@appserv172 dbs]#

SQL> create pfile from spfile;

File created.

SQL>

[root@appserv172 dbs]# ls -lrt

total 48

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r----- 1 oracle oinstall 24 Jan 16 2014 lkAPPSERV

-rw-r----- 1 oracle oinstall 1536 Jan 16 2014 orapwappserv

-rw-rw---- 1 oracle oinstall 1544 Oct 30 19:30 hc_appserv.dat

-rw-r----- 1 oracle oinstall 3584 Nov 2 16:36 spfileappserv.ora

-rw-r--r-- 1 oracle oinstall 1006 Nov 3 09:55 initappserv.ora

[root@appserv172 dbs]#

[root@appserv172 dbs]# cat initappserv.ora

appserv.__db_cache_size=7449083904

appserv.__java_pool_size=33554432

appserv.__large_pool_size=234881024

appserv.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment

appserv.__pga_aggregate_target=2281701376

appserv.__sga_target=8791261184

appserv.__shared_io_pool_size=0

appserv.__shared_pool_size=1006632960

appserv.__streams_pool_size=0

*._serial_direct_read='NEVER'

*.audit_file_dest='/u01/oracle/admin/appserv/adump'

*.audit_trail='none'

*.compatible='11.2.0.4.0'

*.control_files='/u01/oracle/oradata/appserv/control01.ctl','/u01/oracle/fast_recovery_area/appserv/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='appserv'

*.db_recovery_file_dest='/u01/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=appservXDB)'

*.open_cursors=300

*.pga_aggregate_target=2258291200

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=555

*.sga_target=8774873600

*.undo_tablespace='UNDOTBS1'

[root@appserv172 dbs]#

3.检查linux系统参数限制

看下 /dev/shm 的大小

[root@appserv172 ~]#df -h | grep shm

tmpfs 8.0G 0 8.0G 0% /dev/shm

[root@appserv172 ~]#

/dev/shm 小了，

会报“ORA-00845: MEMORY_TARGET not supported on this system”

一会启动不起来了

[root@appserv172 ~]# cat /etc/fstab

LABEL=/ / ext3 defaults 1 1

LABEL=/u01 /u01 ext3 defaults 1 2

LABEL=/home /home ext3 defaults 1 2

LABEL=/tmp /tmp ext3 defaults 1 2

tmpfs /dev/shmtmpfsdefaults,size=8G 0 0

devpts /dev/pts devptsgid=5,mode=620 0 0

sysfs /sys sysfs defaults 0 0

proc /proc proc defaults 0 0

LABEL=SWAP-sda2 swap swap defaults 0 0

[root@appserv172 ~]#

12+4

PGA+sga总和

SQL> startup

ORA-00845: MEMORY_TARGET not supported on this system

这个是由于系统tmpfs小于MEMORY_TARGET导致的，需要调整tmpfs的大小

1.>mount -t tmpfsshmfs -o size=3g /dev/shm

2.>vi /etc/fstab

tmpfs /dev/shmtmpfsdefaults,size=16G 0 0

修改size的大小

重新挂载

mount -o remount /dev/shm

mount -o remount /dev/shm这个貌似不好使，用上面的就可以了

验证下

df -h

4.修改spfile

SQL> alter system set sga_max_size=14g scope=spfile;

SQL> alter system set sga_target=14g scope=spfile;

SQL> alter system set pga_aggregate_target=4g scope=spfile;这个不做了

SQL> alter system set sga_max_size=14g scope=spfile;

System altered.

SQL> alter system set sga_target=14g scope=spfile;

System altered.

5.重启

SQL> shutdown immediate

SQL> startup

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.4965E+10 bytes

Fixed Size 2267792 bytes

Variable Size 1476396400 bytes

Database Buffers 1.3455E+10 bytes

Redo Buffers 31272960 bytes

Database mounted.

Database opened.

SQL>

6.检查修改后参数

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string /u01/oracle/product/11.2.0.4/d

bs/spfileappserv.ora

SQL>

SQL> show parameter sga_max_size

NAME TYPE VALUE

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

sga_max_size big integer 14G

SQL>

SQL> show parameter sga_target

NAME TYPE VALUE

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

sga_target big integer 14G

SQL>