oracle10g 11g 调整sga和pga大小
-------------------------------------------------------------------
---- 2024年8月22日14:18:48
---- bayaimn
-------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Oracle 10.2.0.4.0
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1、查看主机系统版本、内存情况
[root@localhost ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.2 (Maipo)
[root@localhost ~]#
[oracle@localhost ~]$ free -g
total used free shared buff/cache available
Mem: 125 13 83 2 29 109
Swap: 0 0 0
[oracle@localhost ~]$ free -m
total used free shared buff/cache available
Mem: 128773 13376 85051 2216 30345 112596
Swap: 0 0 0
[oracle@localhost ~]$
2、查看数据库：环境变量、版本
[oracle@localhost ~]$ env | grep ORA
ORACLE_SID=jsjn
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@localhost ~]$ sqlplus -V
SQL*Plus: Release 10.2.0.4.0 - Production
3、查看SGA/ PGA
可以看到当前 SGA：1.5G
PGA：16G
su - oracle
sqlplus / as sysdba
set linesize 400
set pagesize 350
show parameter mem;
show parameter pga;
show parameter sga;
show parameter work;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
shared_memory_address integer 0
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 16086M
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1536M
sga_target big integer 1536M
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
workarea_size_policy string AUTO
SQL>
3、查询 spfilejsjn.ora、initjsjn.ora 的位置和完整性：
如果缺少文件，需要创建备份文件
[root@localhost dbs]# pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[root@localhost dbs]#
[root@localhost dbs]# ll
total 7252
-rwxrwxr-x. 1 oracle oinstall 988 Jun 25 09:20 alert_jsjn.log
drwxr-xr-x 2 root root 79 Jun 25 09:39 bak
-rwxrwxr-x. 1 oracle oinstall 1544 Jun 27 10:29 hc_jsjn.dat
-rw-r--r-- 1 oracle oinstall 1187 Jun 25 10:24 initjsjn.ora
-rw-r--r-- 1 oracle oinstall 1396 Jun 25 09:42 initjsjn.ora.bak
-rwxrwxr-x. 1 oracle oinstall 24 Mar 23 2021 lkJSJN
-rwxrwxr-x. 1 oracle oinstall 24 Mar 23 2021 lkJSJNSTD
-rw-r-----. 1 oracle oinstall 1536 Jun 20 17:01 orapwjsjn
-rwxrwxr-x. 1 oracle oinstall 7389184 Jul 5 11:30 snapcf_jsjn.f
-rw-r----- 1 oracle oinstall 3584 Jul 10 17:37 spfilejsjn.ora
-rw-r----- 1 oracle oinstall 3584 Jun 25 10:21 spfilejsjn.ora.bak
[root@localhost dbs]#
4、修改SGA/PGA
su - oracle
sqlplus / as sysdba
set linesize 400
set pagesize 350
alter system set sga_max_size=30G scope=spfile;
alter system set sga_target=30G scope=spfile;
alter system set pga_aggregate_target=3G scope=spfile;
5、然后重新启动数据库，最后查看一下是否生效
shutdown immediate
startup
show parameter sga_max_size;
show parameter sga_target;
show parameter pga_aggregate_target;
6、查询验证修改后结果：
SQL> show parameter sga_max_size;
show parameter sga_target;
show parameter pga_aggregate_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 30G
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 30G
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 3G
SQL>
SQL>
SQL>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
二、OA 办公测试 库：
Oracle 11.2.0.1.0
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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;
---------------------------------------------------------------------
[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;
shutdown immediate
startup
标签：11g,set,target,pga,oracle,SQL,sga,integer From： https://www.cnblogs.com/bayaim/p/18388074