当前位置：首页 > article >正文

0x-1 记一次SGA PGA设置失败，重新开库

article 2025/6/7 12:50:24

0、生产侧定时平台上传数据库11g hang，修改无法startup

厂商统一发放的虚拟机作为前置机导入平台后，直接开机使用。主机在虚拟化平台中，实例卡死后，按照虚拟机系统64G，原SGA=2g,不知哪个大聪明给默认设置的。保守计划修改为16G

小伙伴一顿操作，修改的内存远大于可用内存，实例无法startup

1、修改SGA、PGA

现场和生产也许会因为各式各样的要求，例如：业务要求、其他厂商提出等（需要修改最大的内存使用）去修改Oracle数据库的初始化参数，但如果修改错误，重启之后就会导致数据库无法启动。

比如参数sga_max_size=32G、sga_target=24G，某位手抖修改成sga_max_size=16G、sga_target=24G，数据库启动的时候提示sga_target大于sga_max_size，无法启动。

于是需要修改初始化参数，由于数据库没有办法启动到任何阶段，无法使用alter system修改parameter。祭出手动大法，修改数据库文本参数文件。

2、spfile文件位置

默认Oracle是使用spfile启动，这个文件二级制文件，看见的是一堆乱码（极端情况也有修改后保存成pfile的，非极端不必要挑战）对于DBA看是不能直接修改的。大家能修改的是init.ora这个文件，属于文本类型，需要先行进行参数文件的转换。操作如下：

是不是大部分人都SID都是orcl，这个全省发放的前置机sid就是orcl

查找spfile文件位置，切换到该目录下

默认位置：

Linux，oracle用户记得查看.bash_profile定义的$ORACLE_HOME

$ORACLE_HOME/dbs/spfileorcl.ora

Windows:

%ORACLE_HOME%\database\spfileorcl.ora

cd $ORACLE_HOME/dbs

3、操作步骤

登录sqlplus

sqlplus / as sysdba

1、create pfile from spfile;

生成的initorcl.ora，可修改的文本类型的初始化参数文件（路径位于：$ORACLE_HOME/dbs/）

2、更正initorcl.ora里面错误的参数（vim修改参数为设置之前的那部分）

alter system set sga_max_size=16G scope=spfile;

alter system set sga_target=16G scope=spfile;

sga_max_size=16G

sga_target=16G

alter system set pga_aggregate_target=8196M scope=spfile;

/u01/app/oracle/product/11.2.0/db_01/dbs

startup pfile='/u01/app/oracle/product/11.2.0/db_01/dbs/initorcl.ora';

3、重新登录

提示connect to idle instance

数据库执行启动：startup pfile='/u01/app/oracle/product/11.2.0/db_01/dbs/initorcl.ora';

能够正常启动后确认参数没有错误，按照业务使用再调整。

4、最后再执行create spfile from pfile

执行任何修改前，务必备份任何要修改的文件，cp一次，比什么都好。

4、SGA和PGA最佳实践（OCM也是必考）：

总内存分配，按照64g测算
物理内存的80% 分配给Oracle（约51.2GB），剩余20%保留给操作系统及其他进程。
公式：Oracle可用内存 = 64GB × 80% = 51.2GB，换成MB后很多老师按照习惯再减-1，
这个部分的典故。“+1MB”的由来与适用性
在早期版本（如Oracle 8i/9i），SGA计算公式包含额外1MB的固定开销：
SGA = (db_block_buffers × block_size) + (shared_pool_size + ...) + 1MB这是为SGA内部管理预留的空间。
10g及以上：
自动内存管理（AMM/ASMM）已动态处理此开销，无需手动减1MB。
不过若手动配置组件（如shared_pool_size），仍需确保总和 ≤ SGA_MAX_SIZE，但无需额外扣除。
直接使用目标值,验证设置

--设置SGA_TARGET或SGA_MAX_SIZE时，直接指定目标值（单位可为GB/MB）：
--设置SGA_MAX_SIZE=36G（64G物理内存的OLTP场景）
ALTER SYSTEM SET SGA_MAX_SIZE=36G SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=36G SCOPE=BOTH;

--重启数据库后，通过视图确认实际分配：
-- 检查动态组件分配              
SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;
SYS@orcl> SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME      GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- ------------------- ------------
shared pool                                                        2046820352 1979711488 2046820352                   0          1 GROW          DEFERRED  2025-05-29 01:05:38     33554432
large pool                                                          167772160  167772160 1207959552                   0          1 SHRINK        DEFERRED  2025-05-27 15:55:31     33554432
java pool                                                           134217728  134217728  134217728                   0          0 STATIC                                          33554432
streams pool                                                                0          0          0                   0          0 STATIC                                          33554432
DEFAULT buffer cache                                               1.4328E+10 1.3355E+10 1.4395E+10                   0          2 SHRINK        DEFERRED  2025-05-29 01:05:38     33554432
KEEP buffer cache                                                           0          0          0                   0          0 STATIC                                          33554432
RECYCLE buffer cache                                                        0          0          0                   0          0 STATIC                                          33554432
DEFAULT 2K buffer cache                                                     0          0          0                   0          0 STATIC                                          33554432
DEFAULT 4K buffer cache                                                     0          0          0                   0          0 STATIC                                          33554432
DEFAULT 8K buffer cache                                                     0          0          0                   0          0 STATIC                                          33554432
DEFAULT 16K buffer cache                                                    0          0          0                   0          0 STATIC                                          33554432
DEFAULT 32K buffer cache                                                    0          0          0                   0          0 STATIC                                          33554432
Shared IO Pool                                                              0          0          0                   0          0 STATIC                                          33554432
ASM Buffer Cache                                                            0          0          0                   0          0 STATIC                                          3355443214 rows selected.

5、动态性能视图监控：

SGA使用：SELECT * FROM V$SGASTAT;
PGA使用：SELECT * FROM V$PGASTAT WHERE name='maximum PGA allocated';
建议调整：

SELECT * FROM V$SGA_TARGET_ADVICE;

SELECT * FROM V$PGA_TARGET_ADVICE;

SYS@orcl> SELECT * FROM V$PGASTAT WHERE name='maximum PGA allocated';NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA allocated                                             475544576 bytesSYS@orcl> SELECT * FROM V$SGA_TARGET_ADVICE;SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------4000             .25        17598              1.0743             44960316000            .375        16902              1.0318             32536368000              .5        16587              1.0126             298894210000            .625        16435              1.0033             275753712000             .75        16392              1.0007             269659814000            .875        16383              1.0001             268612916000               1        16381                   1             268451818000           1.125        16381                   1             268451820000            1.25        16381                   1             268451822000           1.375        16381                   1             268451824000             1.5        16381                   1             268451826000           1.625        16381                   1             268451828000            1.75        16352               .9982             263458630000           1.875        16348                .998             263458632000               2        16348                .998             263458615 rows selected.SYS@orcl> SELECT * FROM V$PGA_TARGET_ADVICE;PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED  ESTD_TIME ESTD_EXTRA_BYTES_RW
----------------------- ----------------- --- --------------- ---------- -------------------
ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------------- --------------------2097152000              .125 ON       3.6002E+10     742998                   0100                    04194304000               .25 ON       3.6002E+10     742998                   0100                    08388608000                .5 ON       3.6002E+10     742998                   0100                    01.2583E+10               .75 ON       3.6002E+10     742998                   0100                    01.6777E+10                 1 ON       3.6002E+10     742998                   0100                    02.0133E+10               1.2 ON       3.6002E+10     742998                   0100                    02.3488E+10               1.4 ON       3.6002E+10     742998                   0100                    02.6844E+10               1.6 ON       3.6002E+10     742998                   0100                    03.0199E+10               1.8 ON       3.6002E+10     742998                   0100                    03.3554E+10                 2 ON       3.6002E+10     742998                   0100                    05.0332E+10                 3 ON       3.6002E+10     742998                   0100                    06.7109E+10                 4 ON       3.6002E+10     742998                   0100                    01.0066E+11                 6 ON       3.6002E+10     742998                   0100                    01.3422E+11                 8 ON       3.6002E+10     742998                   0100                    014 rows selected.SYS@orcl>