Step by Step How to increase SGA size in Oracle
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.
Related Articles
- How to Perform a Health Check of an Oracle Database
- Step by Step How to increase SGA size in Oracle
- How to run sql tuning advisor in Oracle
- How to Check Retention Period of AWR Snapshot in Oracle
- How to Find Last Analyzed date on Tables, Partition, and Subpartition in Oracle
4,677 Total Views, 17 Views Today
Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.
Thanks for the visits!
Share Learn Grow!
Thanks team for such a great article.
Thank you Krishna! Keep review articles and share feedback which encourage us.