Wednesday, March 29, 2017

How to increase SGA_MAX_SIZE, SGA_TARGET and PGA_AGGREGATE_TARGET in Oracle 11g

SQL> show parameter sga

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga     boolean FALSE
pre_page_sga     boolean FALSE
sga_max_size     big integer 2G
sga_target     big integer 2G

SQL> ALTER system SET sga_max_size=4G scope=spfile;

System altered.

SQL> ALTER system SET sga_target=4G scope=spfile;

System altered.

---------------------------------------------------------------------------------------------------------------
SQL> show parameter pga

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 3G

SQL> alter system set PGA_AGGREGATE_TARGET=4G scope=spfile;

System altered.

***Bounce the database for apply the changes done.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size    2260088 bytes
Variable Size  738198408 bytes
Database Buffers 3523215360 bytes
Redo Buffers   12107776 bytes
Database mounted.

SQL> show parameter sga

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga     boolean FALSE
pre_page_sga     boolean FALSE
sga_max_size     big integer 4G
sga_target     big integer 4G


SQL> show parameter pga

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 4G






No comments:

Post a Comment

  RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time https://shivanandarao-oracle.com/2012/12/05/rman-20207-until-time-or-re...