ora-02097 parameter cannot be modified because specified value is invalid

You might get ORA-02097 while changing PGA_AGGREGATE_LIMIT.

SQL> show parameter PGA_AGGREGATE_LIMIT

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
pga_aggregate_limit big integer 2G
SQL>
SQL> alter system set PGA_AGGREGATE_LIMIT=5G scope=both;
alter system set PGA_AGGREGATE_LIMIT=5G scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 6000M and 100000G

To set PGA_AGGREGATE_LIMIT we should take care of following allowable values :

• 2 GB (default value)
• 200% of the PGA_AGGREGATE_TARGET value
• 3 MB times the value of the PROCESSES parameter.
• Less then 1.2 X RAM – SGA

Here In my case I am changing value according my processes parameter.

SQL> show parameter process

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
processes integer 2000
processor_group_name string

Processes parameter contains 2000 process.

So PGA_AGGREGATE_LIMIT=2000*3MB

Thate is 6000 MB that is less than 6144 MB.

SQL> alter system set PGA_AGGREGATE_LIMIT=6G scope=both;

System altered.

SQL>
SQL>
SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
pga_aggregate_limit big integer 6G
pga_aggregate_target big integer 48M
SQL>

Stay tuned for More articles on Oracle RAC 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp