Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC

665881Aug 13 2009 — edited Aug 16 2009
All,

We have built new RAC environment with following configurations.

Linux - RHEL5
Oracle - 10.2.0.4.0
2 Node RAC environment.

Now the database is on live.

1. Now, I need to increase the SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in this RAC environment.
Can somebody tell me the steps. Do I need to shutdown the database?
Note: Still OEM is not setup for this one.

2. Performance Testers are complaining about "High Response Time". Any guidance on this one?
This post has been answered by Surachart Opun on Aug 13 2009
Jump to Answer

Comments

Satishbabu Gunukula
Hi,

If you want to chagne sga_max_size, the you must shutdown the DB. Here are the steps

1.alter system reset sga_max_target=<size> scope=spfile SID='*'; (from any one node)
2. shutdown the database
SQL> shutdown immediate (run on both nodes)
or
srvctl stop database -d <dbname>
3. Start the DB using below command
srvctl start database -d <dbname>

pga_aggregate_target is a dynamic parameter and you can run below command online.
SQL>alter system set pga_aggregate_target=<size> scope=both SID='*';

Pls refer below doc before sizing and you will not see any performance related issues.

http://www.dba-oracle.com/tips_pga_aggregate_target.htm
http://www.dba-oracle.com/bk_sga_max_size.htm

Hoep this helps.

Regards,
Satishbabu Gunukula
http://oracleracexpert.blogspot.com
[Click here to learn Voting Disk backup and recovery|http://oracleracexpert.blogspot.com/2009/08/voting-disk-backup-and-recovery.html]

Edited by: Satishbabu Gunukula on Aug 13, 2009 3:46 PM

Edited by: Satishbabu Gunukula on Aug 13, 2009 3:47 PM

Edited by: Satishbabu Gunukula on Aug 13, 2009 3:49 PM
1 person found this helpful
Mufalani
Hello Buddy,

1. Alter the kernel parameters if necessary (shmmax and others on /etc/sysctl.conf)

After change on sysctl file on all nodes

[NODE1]# sysctl -p
[NODE1]# sysctl -w

[NODE2]# sysctl -p
[NODE2]# sysctl -w

2. Alter the Oracle parameters

SQL>alter system set sga_max_size=NEW_VALUE sid='*' scope=spfile;

SQL>alter system set sga_target=NEW_VALUE sid='*' scope=spfile;

3. Restart all instances one by one through srvctl

[NODE2]# srvctl stop instance -d YOUR_DATABASE -i INSTANCE_NAME1 -o immediate

[NODE2]# srvctl start instance -d YOUR_DATABASE -i INSTANCE_NAME1

4. PGA_AGGREGATE_TARGET is on the fly

SQL>alter system set pga_aggregate_target=NEW_VALUE sid='*' scope=both;

Cheers,

Rodrigo Mufalani
http://mufalani.blogspot.com
1 person found this helpful
Surachart Opun
Answer
1. Now, I need to increase the SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in this RAC environment.
Can somebody tell me the steps. Do I need to shutdown the database?
You have to shutdown database... when you change sga_max_size initialization parameter.
But you have to check about kernel.shmmax (all nodes) before
# sysctl -a | grep shmmax
kernel.shmmax =xxxxx

if you need more .... and you have many physical memory ;)
you can change kernel.shmmax at /etc/sysctl.conf and then "sysctl -p" like Mufalani told you.


SQL> alter system set sga_max_size=new_size sid='*' scope=spfile;
or
SQL> alter system set sga_max_size=new_size sid='DB1' scope=spfile;

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams192.htm#REFRN10198

By the way, If you need new size on SGA, you have to change SGA_TARGET initialized parameter as well.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams193.htm#REFRN10256

About PGA_AGGREGATE_TARGET, you can change... no shutdown

SQL> alter system set PGA_AGGREGATE_TARGET=new_size sid='*';
or
SQL> alter system set PGA_AGGREGATE_TARGET=new_size sid='DB1';
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams157.htm#REFRN10165

When you change "sga_max_size" .... to spfile, you have to stop/start database, I think
You should stop/start one node before (make sure no error)... and stop/start another node else.

Example: 2 nodes (racdb1, racdb2)
check shmmax 2 nodes
# sysctl -a | grep shmmax
kernel.shmmax = 8G

SQL> create pfile='/tmp/pfile-backup' from spfile;
SQL> alter system set sga_max_size=8G sid='*' scope=spfile;

$ srvctl stop instance -d racdb -i racdb1 -o immediate
$ srvctl start instance -d racdb -i racdb1

if error... check alert log on racdb1 and solve(don't forget to change "SGA_TARGET" as well), if no error stop/start on racdb2

$ srvctl stop instance -d racdb -i racdb2 -o immediate
$ srvctl start instance -d racdb -i racdb2

Check:
select * from v$sgainfo;
select * from v$sga;

about PGA_AGGREGATE_TARGET you can ... (no stop/start DB)
SQL> alter system set PGA_AGGREGATE_TARGET=2G sid='*';
2. Performance Testers are complaining about "High Response Time". Any guidance on this one?
use ADDM + AWR or Statspack...
make snapshot on each of node by AWR and make report while testing.

By the way "High Response Time" still depend on
- Index
- partition tables
- high Interconnect (NIC)
- DIsk RAID...
- Variable SQL (if use the same sql statement on OLTP)
and ...

Good Luck

Edited by: Surachart Opun (HunterX) on Aug 14, 2009 10:57 AM
Marked as Answer by 665881 · Sep 27 2020
665881
Thanks to all for your valuable input.

I requested the application team for the permission to shutdown database for changing the SGA_MAX_SIZE. Before that as per your advice, i will check the kernel parameters.
Also, awaiting to add my target server to Grid, so that I can use OEM also for ADDM.

I will update you the results.
Mufalani
Hello,

If you do this restart instance by instance... There no are downtime on your application.


Best regards,

Rodrigo Mufalani
http://mufalani.blogspot.com
1 person found this helpful
665881
Thanks to all.

I have successfully updated SGA_MAX_SIZE and PGA_AGGREGATE_TARGET.
I got window to shutdown the database. Hence easily solved.

Reg. High response time, I took ADDM report through Toad and it recommends mostly on SQL Tuning.
I passed the ADDM report to Application team.
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 13 2009
Added on Aug 13 2009
#performance-availability, #rac, #real-application-clusters
6 comments
60,459 views