Step by Step How to increase SGA size in Oracle

February 14, 2021

Step by Step How to increase SGA size in Oracle

I was facing a memory issue in my environment after validating the AWR and ADDM reports and came to the conclusion that SGA is undersized.

Below are both options to increase SGA_MAX_SIZE and SGA_TARGET size using pfile & spfile. in our environment, it’s running using pfile.

Using PFILE

1:- Check file location of spfile/pfile.

First, we have to validate database is up & running using pfile or spfile. if the below query shows the location of spfile that means the database is up and running using spfile, but in my case database is running from pfile.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

2:- Check the value of SGA.

Below is the value of SGA before the increase.

SQL> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 20G
sga_target                           big integer 20G
unified_audit_sga_queue_size         integer     1048576

3:-Take a backup of pfile before modification.

Before modifying any parameter into the pfile, the best practice is to take backup existing pfile.

oracle@DBsGURU [BPP1][] [0:bash:~]
$ cd /u01/app/oracle/product/19cDB/dbhome/dbs/

oracle@DBsGURU [test][] [0:bash:/u01/app/oracle/product/19cDB/dbhome/dbs]
$ ls -lrt
total 73740
-rw-r--r-- 1 oracle oracle     3079 May 14  2015 init.ora
-rw-r----- 1 oracle oracle    53760 Nov  7 15:07 orapwtest
-rw-r----- 1 oracle oracle     1866 Dec  9 12:20 inittest.ora

oracle@DBsGURU [BPP1][] [0:bash:/c01/app/oracle/product/19cDB/dbhome/dbs]
$ cp inittest.ora inittest.ora_bak012521

oracle@banproddb1 [BPP1][] [0:bash:/c01/app/oracle/product/19cDB/dbhome/dbs]
$ ls -lrt
total 73740
-rw-r--r-- 1 oracle oracle     3079 May 14  2015 init.ora
-rw-r----- 1 oracle oracle    53760 Nov  7 15:07 orapwtest
-rw-r----- 1 oracle oracle     1866 Dec  9 12:20 inittest.ora
-rw-r----- 1 oracle oracle     1866 Jan 25 05:09 inittest.ora_bak012521

4:- Modify in Pfile for SGA

Below we have modified SGA_MAX_SIZE, SGA_TARGET, and PGA_AGGREGATE_LIMIT, applicable to both either increase or decrease size of SGA.

db_recovery_file_dest_size=805306368000
log_checkpoints_to_alert=TRUE
core_dump_dest=/u01/app/oracle/admin/test/cdump
db_create_file_dest="/u01/app/oracle/oradata"
compatible=19.3.0.0
dml_locks=10000
query_rewrite_enabled=TRUE
log_buffer=67108864
undo_management=AUTO
undo_tablespace=UNDOTBS1
db_create_online_log_dest_2=/u01/app/oracle/oradata
db_create_online_log_dest_1=/u01/app/oracle/oradata
sessions=2530
log_archive_max_processes=4
shared_pool_size=4026531840
java_jit_enabled=FALSE
sga_max_size=27917287424
db_name="test"
undo_retention=900
db_unique_name="test"
session_cached_cursors=1024
archive_lag_target=0
workarea_size_policy=auto
sga_target=27917287424
diagnostic_dest=/u01/app/oracle
audit_file_dest="/u01/app/oracle/admin/test/adump"
standby_file_management=AUTO
optimizer_mode=FIRST_ROWS_10
open_cursors=4096
control_file_record_keep_time=7
processes=1670
job_queue_processes=10
pga_aggregate_target=2147483648
db_block_size=8192
control_files='/u01/app/oracle/oradata/test/test_control_01.ctl','/u01/app/oracle/oradata/test/test_control_02.ctl'
remote_login_passwordfile=EXCLUSIVE
pga_aggregate_limit=5368709120

5:- Bounce the database.

After changing the SGA_MAX_SIZE and SGA_TARGET parameter in pfile, We have to stop the database and start the database using a modified pfile to reflect new values of SGA_MAX_SIZE and SGA_TARGET. It will reflect new values once the database is restarted.

SQL> shutdown immediate

SQL> startup pfile = '/u01/app/oracle/product/19cDB/dbhome/dbs/inittest.ora'

OR

SQL> startup

NOTE: 5.1 if pfile is on the default location then no need to pass the complete pfile path in the startup command.

6:-Validate new values of SGA.

once the database is restarted we have to validate the change.

SQL> show parameter sga_;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 26G
sga_target                           big integer 26G
unified_audit_sga_queue_size         integer     1048576

7:- Start the database using spfile upon made changes
This step is optional if you modified values using pfile and later want to start the database using spfile.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile from pfile='/home/oracle/initlabdb01.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size                  2928968 bytes
Variable Size             306187960 bytes
Database Buffers          440401920 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0/db_1/dbs/spfilelabdb01.ora

NOTE: 7.1 Above demo output from LAB environment.
7.2 if pfile is on the default location then no need to pass the complete pfile path in create spfile command.

Using SPFILE

1:- Check file location of spfile/pfile.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0/db_1/dbs/spfilelabdb01.ora

2:- Check the value of SGA.

SQL> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 720M
sga_target                           big integer 720M
unified_audit_sga_queue_size         integer     1048576

3:-Take a backup of spfile before modification.

Before modifying any parameter into the spfile, the best practice is to take backup existing spfile in text format.

SQL> create pfile='/home/oracle/initlabdb01.ora' from spfile;

File created.
SQL> !ls -lrt /home/oracle/initlabdb01.ora
-rw-r--r-- 1 oracle oinstall 845 Feb 14 15:41 /home/oracle/initlabdb01.ora

4:- Modify SGA values and reboot the database.

Below we have modified SGA_MAX_SIZE & SGA_TARGET which reflect in spfile after reboot the database, applicable to increase size of SGA.

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             390073072 bytes
Database Buffers          440401920 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL>  alter system set sga_target=800M scope=both;

System altered.

Follow the below steps if you want to decrease the size of SGA, here we are decreasing the size of SGA from 800M to 750M.

SQL>  alter system set sga_target=750M scope=both;

System altered.

SQL> alter system set sga_max_size=750M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  786432000 bytes
Fixed Size                  2929936 bytes
Variable Size             390073072 bytes
Database Buffers          440401920 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

SQL> show parameter sga_
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 750M
sga_target                           big integer 750M

5:-Validate new values of SGA.

SQL> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 800M
sga_target                           big integer 800M
unified_audit_sga_queue_size         integer     1048576

6:- Create pfile.
This step is optional if you want to create a new pfile after the database reboot and validation of the new value of SGA.

SQL> create pfile from spfile;   ==> PFILE created to default location

File created

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

DATABASE, ORACLE, SCRIPTS, TUNING