This content has been marked as final. Show 6 replies
Hi,1 person found this helpful
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)
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.
Hoep this helps.
[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
Hello Buddy,1 person found this helpful
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;
1. Now, I need to increase the SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in this RAC environment.You have to shutdown database... when you change sga_max_size initialization parameter.
Can somebody tell me the steps. Do I need to shutdown the database?
But you have to check about kernel.shmmax (all nodes) before
# sysctl -a | grep shmmax
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;
SQL> alter system set sga_max_size=new_size sid='DB1' scope=spfile;
By the way, If you need new size on SGA, you have to change SGA_TARGET initialized parameter as well.
About PGA_AGGREGATE_TARGET, you can change... no shutdown
SQL> alter system set PGA_AGGREGATE_TARGET=new_size sid='*';
SQL> alter system set PGA_AGGREGATE_TARGET=new_size sid='DB1';
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
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
- partition tables
- high Interconnect (NIC)
- DIsk RAID...
- Variable SQL (if use the same sql statement on OLTP)
Edited by: Surachart Opun (HunterX) on Aug 14, 2009 10:57 AM
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.
Hello,1 person found this helpful
If you do this restart instance by instance... There no are downtime on your application.
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.