Resizing SGA

 
Follow the steps below to correctly resize SGA.

Changing SGA_MAX_SIZE requres a database reboot but changing the other SGA parameters do not.

Step 1.) Create a copy of existing spfile or pfile (for backup/rollback purposes):
— for databases started up with spfile (please make sure ORACLE_SID and ORACLE_HOME variables are set
create pfile='$ORACLE_HOME/dbs/init${ORACLE_SID}.ora.backup' from spfile;
— for datbabase started with pfile
do a unix copy

2.) Execute changes on the Database
alter system set sga_max_size = 48G scope = spfile;
alter system set sga_target = 48G scope = spfile;
alter system set pga_aggregate_target = 24G scope = spfile;
alter system set db_cache_size = 18G scope = spfile;
alter system set shared_pool_size = 16G scope = spfile;
alter system set shared_pool_reserved_size = 2G scope = spfile;
alter system set large_pool_size = 2G scope = spfile;

3.) Shutdown and restart database instance.
shutdown immediate;
startup;

If your database has dataguard enabled (physical standby) you must apply the parameter changes to the DR/Standby databases, database parameters are not automatically propagated to the DR/Standby environment.

1.) Stop dataguard managed recovery mode
alter database recover managed standby database cancel;

2.) Create a copy of existing spfile or pfile (for backup/rollback purposes):
— for databases started up with spfile (please make sure ORACLE_SID and ORACLE_HOME variables are set
create pfile='$ORACLE_HOME/dbs/init${ORACLE_SID}.ora.backup' from spfile;
— for datbabase started with pfile
do a unix copy

3.) Execute changes on the CLMT01P instance
alter system set sga_max_size = 48G scope = spfile;
alter system set sga_target = 48G scope = spfile;
alter system set pga_aggregate_target = 24G scope = spfile;
alter system set db_cache_size = 18G scope = spfile;
alter system set shared_pool_size = 16G scope = spfile;
alter system set shared_pool_reserved_size = 2G scope = spfile;
alter system set large_pool_size = 2G scope = spfile;

4.) Shutdown and restart database instance
shutdown immediate;
startup nomount;
alter database mount standby database;

5.) Start dataguard managed recovery mode
alter database recover managed standby database disconnect from session;

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart