Tuesday, May 1, 2012

How to change SGA in Oracle Rac

This Article for newbe Oracle DBA Since If you trying to change sga on rac with wrong way , spfile maybe be corrupted . 

Node One : ORCL1
Node Two : ORCL2

Note : You Need To Check Memory Parameter On Database , if its Have Available Size .
  • Connect To Node One :

alter system set sga_max_size=16g scope=spfile sid = 'ORCL1';
alter system set sga_target=12g scope=spfile sid = 'ORCL1';
alter system set sga_max_size=16g scope=spfile sid = 'ORCL2';
alter system set sga_target=12g scope=spfile sid = 'ORCL2';

Or In Another Way :
sql>alter system set sga_target=12G scope=spfile sid='*';
sql>alter system set sga_max_size=16G scope=spfile sid='*';
sql>alter system set sga_max_size=16G scope=spfile ;
sql>alter system set sga_target=12G scope=spfile;

 PGA :

sql>alter system set pga_aggregate_target=4G scope =spfile sid='*';
sql>alter system set pga_aggregate_target=4G scope=both;


  • shutdown database PROD (two instances should be shut down)
 $>srvctl stop database -d PROD
$>srvctl START database -d PROD


Check The New Size For Both Instance By :

Show parameter sga ;


Thank you
Osama mustafa 

6 comments:

  1. Hi
    How can i change memory in oracle AMM setup?

    Thanks

    ReplyDelete
  2. Your Welcome i will post it as topic Don't Worry

    ReplyDelete
  3. Hi man, let me have my opinion. If you have RAC, you should seize it by restarting one instance at a time (srvctl stop instance -i -d . That way, there's no outage. Please take a look at my blog :) http://danielrobertosoto.blogspot.com.ar/ Thanks!

    ReplyDelete
  4. Thanks for sharing these information. It’s a very nice topic. We are providing online training classes oracle rac online training

    ReplyDelete