000031254 - How to change the Oracle SGA/PGA on an RSA Via L&G appliance which has version 6.9.1 newly installed

When RSA Via L&G  is installed on an appliance, the values that are configured during the installation for the Oracle Instance for the Oracle SGA and PGA settings are based on how much physical memory is available on the appliance.   Typically on systems which have upwards of 32gb of Memory, 1/2 of the memory is allocated for Oracle, and that is evenly divided between the SGA and PGA.  This means that the largest default value that is set during installation is 8gb for the SGA and 8gb for the PGA.  
Systems that have more than 32gb of memory, even those R720's that have 256gb of memory will still have an installation value no more than 8gb.  
(Note that in the newer Version 7.0.0 release, the Oracle installation calculates larger SGA and PGA settings, also based on how much physical memory is available - and typically does not need to be manually adjusted).
Users that wish to manually configure a larger value for the Oracle SGA and PGA settings have to set these after successfully installing the application.
Note that the instructions found in the knowledge base article KB-1158 - Correct sizing for Oracle SGA / PGA have been correct for all versions prior to 6.9.1.  
These instructions are also correct for an appliance systems where the Oracle setting for sga_target = sga_max_size.   
These instructions not correct when sga_target and sga_max_size are not equal and  sga_target = 512M.     The instructions from this article should be followed instead.
If the value for  sga_max_size is increased before the sga_target value is increased, an ORA-00812 will occur on database instance restart.

 

The correct sequence of steps depends on the current settings.  Determine the existing Oracle SGA and PGA values and then follow the guidelines noted below.
If at any point errors are noted, do not proceed.  Contact RSA Support for additional guidance.

1) Before changing any Oracle Instance settings, the application server should be stopped.  On an appliance, this can be done by the Linux oracle user entering one of the following commands:  

acm stop

OR  

service aveksa_server stop

2) Before making any changes to the existing system, the current Oracle Instance startup settings should be saved to an Oracle 'pfile' that can be used to startup Oracle in the event of any unexpected typing errors.  This can be done by accessing the database as sysdba, either from sqlplus or a client tool such as sqldeveloper and issuing a 'create pfile' command.  The examples shown below are all from sqlplus executed from the Linux OS 'oracle' user account.

$ sqlplus "/as sysdba"
sql> create pfile = '/tmp/current_working_pfile.ora' from spfile;

3) As either the Linux oracle user, or using a client tool such as sqldeveloper, login as the sysdba and check the current settings.  

$ sqlplus "/as sysdba"
sql> sho parameter sga_
sql> show parameter pga

If sga_target is same value as sga_max_size, then you can adjust sga and pga memory values as needed, as shown in step 4 below.



If sga_target is 512M, then it needs to be set to the EXACT same size as the current sga_max_size and the Oracle Database Instance restarted  before proceeding to step 4 below.



Note that this means exactly as shown  which means  2000M  is NOT equivalent to 2G.



For example, if these are the values seen:

sql>  sho parameter sga_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 2000M
sga_target                           big integer 512M

Then execute this command:

sql> alter system set sga_target = 2000M  comment='internally adjusted' scope=spfile;
sql> exit

Or, for example, if these are the values seen:

SQL> sho parameter sga_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 8G
sga_target                           big integer 512M

Execute this command:

sql> alter system set sga_target = 8g  comment='internally adjusted' scope=spfile;
sql> exit

And then restart the Oracle database

$ acm stopdb
$ acm startdb

4) At this point, the SGA and PGA values can now be 'safely' increased without risk of incurring an ORA-00821 on Oracle database Instance startup.  Login as sysdba and alter the SGA and PGA settings as advised by RSA Support or Engineering.   These settings typically should not exceed 1/2 of the total memory available on the physical appliance.   The general 'rule of thumb'  for SGA and PGA values for systems that have more than 64gb of memory is PGA=1/2 of SGA
For example, to set SGA=48g and PGA = 24g, the following commands should be run by a  sysdba user:

$ sqlplus "/as sysdba"
sql> alter system set pga_aggregate_target = 24g comment='internally adjusted'  scope=both; 
sql>alter system set sga_max_size = 48g  comment='internally adjusted' scope=spfile;
sql> alter system set sga_target = 48g  comment='internally adjusted'  scope=spfile;

5)  Stop and start the database server.  This can be done by entering:

$ acm stopdb
$ acm startdb

6) Once the database has started sucessfully, log in as sysdba user and confirm settings:

$ sqlplus "/as sysdba"
sql> sho parameter sga_
sql> show parameter pga

7) Once the database has started successfully and new settings are confirmed, start the application server.

$ acm start

Note: Once the Oracle memory settings have been validated and the instance successfully restarted, that is a good time to make another pfile copy of the new Oracle instance settings.  
Again, login to sqlplus as sysdba, and execute this command:

$ sqlplus "/as sysdba"
sql> create pfile = '/tmp/current_working_pfile.ora' from spfile;
sql> exit