WARNING: Granules of pga_aggregate_target 400 cannot be more than memory_target (404) - sga_target

环境:

linux 6.8 + oracle 11.2.0.4 rac

 

alert 日志有以下告警:

WARNING: Granules of pga_aggregate_target 400 cannot be more than memory_target (404) - sga_target (

 

相关信息:

SQL> show parameter mem

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address integer 0

memory_max_target big integer 101G

memory_target big integer 101G

shared_memory_address integer 0

SQL> show parameter sga

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 101G

sga_target big integer 0

SQL> show parameter pga

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target big integer 0

 

 

WARNING: GRANULES OF PGA_AGGREGATE_TARGET CANNOT BE MORE THAN MEMORY_TARGET (Doc ID 970274.1)

 

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Information in this document applies to any platform.

*** Checked for relevance on 24-Jul-2017 ***

Symptoms

 

The following message is reported in the alert log:

 

WARNING: Granules of pga_aggregate_target %d cannot be more than memory_target (%d) - sga_target (%d) or min_sga(%d)

Changes

 

The MEMORY_TARGET and SGA_MAX_SIZE or SGA_TARGET parameters are set.

Cause

 

This warning message is raised because the MEMORY_TARGET/MEMORY_MAX_TARGET and SGA_MAX_SIZE/SGA_TARGET parameters are set or default to minimum values, and that the difference between the parameter values is less than the required PGA_AGGREGATE_TARGET value. In Oracle 11g the MEMORY_TARGET parameter defines the total amount of memory that Oracle can use, which incorporates both SGA and PGA areas. Therefore if these are set the same as the SGA_MAX_SIZE/SGA_TARGET parameters or the difference between them is too small, then this leaves no memory available for the PGA, and hence this warning message is raised.

Solution

 

Ensure that the MEMORY_TARGET/MEMORY_MAX_TARGET parameter settings are set to at least the sum of SGA_MAX_SIZE/SGA_TARGET plus the PGA_AGGREGATE_TARGET parameter.

解决方法：确保MEMORY_TARGET/MEMORY_MAX_TARGET参数设置为sga_max_size/sga_target 加上pga_aggregate_target的总数。

 

这里可以改为 :

alter system set sga_max_size=80G scope=spfile sid='*';

alter system set pga_aggregate_target=20G scope=spfile sid='*';

 

注意:改完需要重启实例才能生效。

 

或者重新配置为ASMM模式。