ORA-00093: pga_aggregate_limit must be between

As the workload increases with more client connections, accordingly it’s normal to think about increasing the value of the parameter PROCESSES which specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.

When doing so, you need to check what values are set for two PGA parameters: PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT.

PGA_AGGREGATE_TARGET could be set to 0 or a non-zero value. When set to 0, WORKAREA_SIZE_POLICY parameter is set to MANUAL, SQL working areas are sized using the *_AREA_SIZE parameters. When set to a non-zero value, it either acts as the minimum value for the size of the instance PGA if Automatic Memory Management is enabled — MEMORY_TARGET is set to a positive value or Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the working areas to private memory.

On the other hand, another PGA related parameter PGA_AGGREGATE_LIMIT puts a hard limit for aggregate PGA memory.

If your system has PGA_AGGREGATE_LIMIT set to a specific value, it must be at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).

From 19c’s guide:

If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).
For a PDB, the default value is the same as the CDB’s default value.

If PGA_AGGREGATE_LIMIT is set to a non-zero value specifically, and you want to increase the value of PROCESSES, make sure PGA_AGGREGATE_LIMIT is also adjusted to be 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).

Or when you restart Oracle, you will see the error “ORA-00093: pga_aggregate_limit must be between” immediately. Oracle doesn’t even write anything into the alert log.

To get out of this error, just open the spfile, copy the plain text part out and modify PGA_AGGREGATE_LIMIT based on the newly changed value of PROCESSES, then save the file and use it to start Oracle with pfile. After verify everything is good, re-create spfile from the pfile.

References: