Try these steps.
Before setting AMM SGA_MAX_SIZE value was set to 7G.
Setting AMM
_CONN / AS SYSDBA_
_ALTER SYSTEM SET MEMORY_MAX_TARGET=17G SCOPE=SPFILE;_
_ALTER SYSTEM SET MEMORY_TARGET=17G SCOPE=SPFILE;_
_ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;_
_ALTER SYSTEM SET SGA_MAX_SIZE=0 SCOPE=SPFILE;_
_ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;_
— Restart instance.
_SHUTDOWN IMMEDIATE;_
_STARTUP;_
show parameter sga;
SGA_MAX_SIZE=7008M — which is not what was set. It should be set to 0 but since it was previously set to 7G it is not changing.
Solution:
Reduce the MEMORY_TARGET value from 17G to 5G which is a low value and is lower than the SGA_MAX_SIZE.
_ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;_
_ALTER SYSTEM SET SGA_MAX_SIZE=0 SCOPE=SPFILE;_
— Restart instance.
_SHUTDOWN IMMEDIATE;_
_STARTUP;_
show parameter sga; (value set in pfile is 0 as we had set it to 0)
SGA_MAX_SIZE=3G — which is right as it is calculating 60% of the MEMORY_TARGET value which is 5G and the remaining 40% is allocated to pga.
Now increase the MEMORY_TARGET to 17G which you wanted to set it to initially.
— Restart instance.
_SHUTDOWN IMMEDIATE;_
_STARTUP;_
show parameter sga; (value set in pfile is 0 as we had set it to 0)
<u>SGA_MAX_SIZE=10440M</u> — which is right as it is calculating 60% of the MEMORY_TARGET value which is 17G and the remaining 40% is allocated to pga.
Previously the value was not changing from 7008M to 10440M which is the expected value it has to be set too according to the AMM automatic calculations.