oracle10g 11g 调整sga和pga大小
-------------------------------------------------------------------
---- 2024年8月22日14:18:48
---- bayaimn
-------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
二、OA 办公测试 库：
Oracle 11.2.0.1.0
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1、Linux 系统版本查询
cat /proc/version
cat /etc/issue
lsb_release -a
cat /etc/redhat-release
free -g
free -m
df -h /dev/shm
cat /etc/fstab | grep shm
lscpu
uname -a
env | grep ORA
sqlplus -V
sqlplus / as sysdba
set linesize 400
set pagesize 350
【查看SGA/ PGA】--------------------------------
show parameter mem;
show parameter pga;
show parameter sga;
show parameter work;
SELECT * FROM V$SGAINFO;
select * from v$sga;
show parameters area_size
SELECT * FROM v$pgastat;
# 查看 pga
show parameters area_size
SELECT * FROM v$pgastat;
# 查看pga建议
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;
---------------------------------------------------------------------
[oracle@host-192-168-111-69 ~]$ cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[oracle@host-192-168-111-69 ~]$ free -g
total used free shared buff/cache available
Mem: 15 4 0 4 10 6
Swap: 5 0 5
[oracle@host-192-168-111-69 ~]$ free -m
total used free shared buff/cache available
Mem: 16040 4709 435 4313 10896 6642
Swap: 6015 0 6015
[oracle@host-192-168-111-69 ~]$
[oracle@host-192-168-111-69 ~]$
[oracle@host-192-168-111-69 ~]$ env | grep ORA
ORACLE_SID=jsjn
ORACLE_HOME_LISTNER=/oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_BASE=/oracle/app
ORACLE_TERM=vt100
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1
[oracle@host-192-168-111-69 ~]$ sqlplus -V
SQL*Plus: Release 11.2.0.1.0 Production
3、查询 spfilejsjn.ora、initjsjn.ora 的位置和完整性：
如果缺少文件，需要创建备份文件
[root@localhost dbs]# pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
SQL> show parameter pfile;
NAME TYPE VALUE
--------- -------------------------
spfile string /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilejsjn.ora
cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initjsjn.ora
create pfile from spfile;
[oracle@host-192-168-111-69 dbs]$ ll
total 40
-rw-rw----. 1 oracle dba 1544 Aug 20 14:49 hc_DBUA0.dat
-rw-rw----. 1 oracle dba 1544 Sep 6 2021 hc_jngl.dat
-rw-rw---- 1 oracle dba 1544 Aug 20 14:42 hc_jsjn.dat
-rw-r--r-- 1 oracle dba 937 Aug 28 11:12 initjsjn.ora
show parameter work;SQL> SQL> SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 6464M
memory_target big integer 6464M
shared_memory_address integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 6464M
sga_target big integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
listener_networks string
workarea_size_policy string AUTO
SQL>
startup pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initjsjn.ora';
【方法1】
alter system set memory_max_target=11G scope=spfile;
alter system set memory_target=11G scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;
alter system set sga_target=0 scope=spfile;
SQL> SQL>
show parameter mem;
show parameter pga;
show parameter sga;
show parameter work;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 11G
memory_target big integer 11G
shared_memory_address integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 11G
sga_target big integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
listener_networks string
workarea_size_policy string AUTO
SQL>
SQL>
【方法2】
alter system set memory_max_target=6.3G scope=spfile;
alter system set memory_target=6.3G scope=spfile;
alter system set sga_max_size=4.3G scope=spfile;
alter system set sga_target=4.3G scope=spfile;
alter system set pga_aggregate_target=2G scope=spfile;
shutdown immediate
startup
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 8G
memory_target big integer 8G
shared_memory_address integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 2G
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 6G
sga_target big integer 6G
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
listener_networks string
workarea_size_policy string AUTO
SQL>
SQL>
alter system set memory_max_target=13G scope=spfile;
alter system set memory_target=13G scope=spfile;
alter system set sga_max_size=10G scope=spfile;
alter system set sga_target=10G scope=spfile;
alter system set pga_aggregate_target=3G scope=spfile;
show parameter cursor;
alter system set open_cursors = 2000 scope = spfile;
show parameter session;
select count(1) from V$SESSION;
show parameter process;
select count(1) from V$PROCESS;
alter system set processes=1500 scope=spfile SID='baydb1';
alter system set sessions=2000 scope=spfile SID='baydb1';
shutdown immediate
startup
-------------------------------------------------------------------------------
windows:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as sys@WINDB36 AS SYSDBA
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1648M
sga_target big integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> show parameter mem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1648M
memory_target big integer 1648M
shared_memory_address integer 0
SQL> show parameter work
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
listener_networks string
workarea_size_policy string AUTO
SQL>
==================================================
linux
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
aq_tm_processes integer 1
cell_offload_processing boolean TRUE
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 600
processor_group_name string
SQL> show parameter SGA ;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3008M
sga_target big integer 3008M
SQL> show parameter PGA ;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
pga_aggregate_target big integer 999M
SQL> show parameter mem;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter work;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
fileio_network_adapters string
listener_networks string
workarea_size_policy string AUTO
SQL>
SQL>
SQL>
----------------------------------------------------------------------------------------------
【报错一】：
ORA-00845: MEMORY_TARGET not supported on this system
【处理办法】：
[root@rac2 ~]# cat /etc/fstab | grep shm
tmpfs /dev/shm tmpfs defaults,size=15G 0 0
[root@rac2 ~]#
[root@rac2 ~]# df -h /dev/shm
文件系统 容量 已用 可用 已用% 挂载点
tmpfs 9.0G 0 9.0G 0% /dev/shm
[root@rac2 ~]#
修改完后，需要重新挂载/dev/shm生效：
[root@rac2 ~]# mount -o remount /dev/shm
[root@rac2 ~]# mount -o remount /dev/shm
[或者，方法2]
# 普通快速修改
# mount -o size=15G -o remount /dev/shm
[root@rac2 ~]#
[root@rac2 ~]# df -h /dev/shm
文件系统 容量 已用 可用 已用% 挂载点
tmpfs 15G 0 15G 0% /dev/shm
[root@rac2 dbs]# free -h
total used free shared buff/cache available
Mem: 9.8G 1.4G 2.2G 1.9G 6.2G 6.1G
Swap: 9G 0B 9G
[root@rac2 dbs]# df -lh | grep /dev/shm
tmpfs 15G 0 15G 0% /dev/shm
---------------------------------------------
Oracle 11g中，ORACLE把SGA与PGA统一管理，总和为memory_target参数的设定，也就是MAX(SGA+PGA)<= memory_target(当然可以在创建新实例的时候确定是否使用这种方案，在10g中是没有的)。
如果错误设定sga_max_size>=memory_target(相等也不行，PGA至少需要大概十几M内存)，则无法进行startup nomount，但是修改SGA命令又需要至少在startup nomount下面执行，所以必须使用另一种方式修改该参数。
【方法一】：
cd /u01/app/oracle/product/11.2.0/db_1/dbs
ls -lt
【会话1】 bayaim：
这个memory 的大小不能超过 /dev/shm 大小
否则报错： ORA-00845: MEMORY_TARGET not supported on this system
create spfile from pfile;
alter system set memory_max_target=11G scope=spfile;
alter system set memory_target=11G scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;
alter system set sga_target=0 scope=spfile;
shutdown immediate
startup
startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb2.ora';
【方法二】：
alter system set memory_max_target=8704M scope=spfile;
alter system set memory_target=8704M scope=spfile;
alter system set sga_max_size=6G scope=spfile;
alter system set sga_target=6G scope=spfile;
alter system set pga_aggregate_target=2G scope=spfile;
shutdown immediate
startup
解决方法如下：
SQL> show parameter stream
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>
将streams_pool_size的值设置为非0，
SQL> alter system set streams_pool_size=10M scope=memory;
System altered.
------------------------------------------------------------------------------------------
su - oracle
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
sqlplus / as sysdba
show parameter cursor;
alter system set open_cursors = 2000 scope = spfile;
show parameter session;
select count(1) from V$SESSION;
show parameter process;
select count(1) from V$PROCESS;
alter system set processes=1500 scope=spfile SID='baydb1';
alter system set sessions=2000 scope=spfile SID='baydb1';
shutdown immediate
startup
标签：11g,target,pga,memory,system,SQL,sga,integer From： https://www.cnblogs.com/bayaim/p/18388079