oracle 10g调整sga和pga大小

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