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 initBPP1.ora initBPP1.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
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.
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.
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
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.
237 total views, 1 views today
Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and its 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!
