当物理内存小于sga+pga时

最新推荐文章于 2021-05-16 19:33:56 发布

cnzh2014

最新推荐文章于 2021-05-16 19:33:56 发布

阅读量125

点赞数

文章标签：数据库

1，查看当前系统的物理内存
[oracle@node1 ~]$ free -m
   total used free shared buffers cached
Mem: 2026 1288 738 0 75 1051
-/+ buffers/cache: 161 1865
Swap: 2047 0 2047
2，打开数据库，备份pfile，并查看当前内存和swap的使用率
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 9 20:48:52 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ test>startup
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 633343568 bytes
Database Buffers 209715200 bytes
Redo Buffers 5132288 bytes
Database mounted.
Database opened.
SYS@ test>
SYS@ test>
[oracle@node1 ~]$ top
top - 20:50:14 up 6 min, 2 users, load average: 2.55, 0.75, 0.28
Tasks: 137 total, 2 running, 135 sleeping, 0 stopped, 0 zombie
Cpu(s): 4.3%us, 1.6%sy, 0.0%ni, 69.6%id, 23.3%wa, 0.2%hi, 0.9%si, 0.0%st
Mem: 2075468k total, 1351256k used, 724212k free, 76984k buffers
Swap: 2097144k total, 0k used, 2097144k free, 1073448k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3007 oracle 18 0 968m 50m 47m D 8.0 2.5 0:00.25 oracle
1 root 15 0 2072 632 544 S 0.0 0.0 0:00.25 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.03 events/0
6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
7 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
10 root 10 -5 0 0 0 S 0.0 0.0 0:00.01 kblockd/0
11 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
49 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
52 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd
54 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod
118 root 16 0 0 0 0 S 0.0 0.0 0:00.00 khungtaskd
119 root 25 0 0 0 0 S 0.0 0.0 0:00.00 pdflush
120 root 15 0 0 0 0 S 0.0 0.0 0:00.02 pdflush
121 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kswapd0
122 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0
271 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused
295 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 ata/0
296 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 ata_aux
299 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_0
300 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_1
301 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_2
306 root 13 -5 0 0 0 S 0.0 0.0 0:00.00 kstriped
315 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 ksnapd
326 root 10 -5 0 0 0 S 0.0 0.0 0:00.02 kjournald
352 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kauditd
385 root 21 -4 2480 944 392 S 0.0 0.0 0:00.09 udevd
885 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 iprt/0

3，修改memory_target pga sga的大小
SYS@ test>create pfile='/home/oracle/init.ora' from spfile;
File created.

SYS@ test>alter system set memory_max_target=3g scope=spfile;
System altered.

SYS@ test>alter system set sga_target=2g scope=spfile;
System altered.

SYS@ test>alter system set pga_aggregate_target=100m scope=spfile;
System altered.

4，从新打开数据库，查看pga和sga的内存变大了，同时swap空间的使用率上升了。

SYS@ test>shutdown abort;
ORACLE instance shut down.
SYS@ test>startup
ORA-00845: MEMORY_TARGET not supported on this system
[root@node1 ~]# mount -t tmpfs shmfs -o size=7g /dev/shm
[root@node1 ~]# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
shmfs 7.0G 0 7.0G 0% /dev/shm

SYS@ test>startup
ORACLE instance started.
Total System Global Area 2142715904 bytes
Fixed Size 1337688 bytes
Variable Size 369100456 bytes
Database Buffers 1761607680 bytes
Redo Buffers 10670080 bytes
Database mounted.
Database opened.
SYS@ test>show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2G
sga_target big integer 2G
SYS@ test>show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 100M

[root@node1 ~]# top
top - 21:08:59 up 24 min, 2 users, load average: 0.31, 0.46, 0.43
Tasks: 124 total, 3 running, 121 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.3%sy, 0.0%ni, 98.4%id, 0.0%wa, 0.0%hi, 1.3%si, 0.0%st
Mem: 2075468k total, 2014620k used, 60848k free, 2840k buffers
Swap: 2097144k total, 1549140k used, 548004k free, 759264k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 15 0 2072 632 544 S 0.0 0.0 0:00.27 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.15 events/0
6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
7 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
10 root 10 -5 0 0 0 S 0.0 0.0 0:00.01 kblockd/0
11 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
49 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
52 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd
54 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod
118 root 15 0 0 0 0 S 0.0 0.0 0:00.00 khungtaskd
119 root 15 0 0 0 0 S 0.0 0.0 0:00.01 pdflush
120 root 15 0 0 0 0 S 0.0 0.0 0:00.05 pdflush
121 root 19 -5 0 0 0 S 0.0 0.0 0:00.37 kswapd0
122 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0
271 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused
295 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 ata/0
296 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 ata_aux
299 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_0
300 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_1
301 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_2
306 root 13 -5 0 0 0 S 0.0 0.0 0:00.00 kstriped
315 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 ksnapd
326 root 10 -5 0 0 0 S 0.0 0.0 0:00.04 kjournald
352 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kauditd
385 root 21 -4 2480 944 392 S 0.0 0.0 0:00.09 udevd
885 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 iprt/0
1373 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kmpathd/0

SYS@ test>shutdown abort
ORACLE instance shut down.
5，恢复数据库
SYS@ test>startup pfile='/home/oracle/init.ora';
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 633343568 bytes
Database Buffers 209715200 bytes
Redo Buffers 5132288 bytes
Database mounted.
Database opened.
SYS@ test>create spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfile.ora' from pfile='/home/oracle/init.ora';
File created.
SYS@ test>shutdown abort
ORACLE instance shut down.
SYS@ test>startup
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 633343568 bytes
Database Buffers 209715200 bytes
Redo Buffers 5132288 bytes
Database mounted.
Database opened.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29108064/viewspace-1293458/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29108064/viewspace-1293458/