6 Replies Latest reply: Aug 16, 2009 6:46 PM by 665881 RSS

    How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC

    665881
      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?
        • 1. Re: How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC
          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
          • 2. Re: How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC
            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
            • 3. Re: How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC
              Surachart Opun
              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
              • 4. Re: How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC
                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.
                • 5. Re: How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC
                  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
                  • 6. Re: How to increase SGA_MAX_SIZE and PGA_AGGREGATE_TARGET in 10g RAC
                    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.