oracle 11g 调整sga和pga大小

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