You set the following parameters in the parameter file and restarted the database:
MEMORY_MAX_TARGET=0
MEMORY_TARGET=500M
PGA_AGGREGATE_TARGET=90M
SGA_TARGET=270M
Which two statements are true about these parameters after the database instance is restarted?
(Choose two.)
A.
The MEMORY_MAX_TARGET parameter is automatically set to 500 MB.
B.
The PGA_AGGREGATE_TARGET and SGA_TARGET parameters are automatically set to zero.
C.
The value of the MEMORY_MAX_TARGET parameter remains zero until it is changed manually.
D.
The lower bounds of PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to 90 MB
and 270 MB, respectively.
MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter.
PGA_AGGREGATE_TARGET is a database initial parameter, controls memory allocated by Oracle for the Process global area (PGA)
SGA_TARGET – used for automatic SGA memory sizing.
MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is “0”.
MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart.
C is incorrect.
MEMORY_MAX_TARGET: This parameter defines the MEMORY_TARGET maximum value can be achieved without having to restart the instance. And if none is set MEMORY_MAX_TARGET value, the default value is equal to the MEMORY_TARGET.
SGA_TARGET and PGA_AGGREGATE_TARGET represents the minimum set their respective memory area, to make Oracle complete control memory management.
These two parameters should be set to 0. But it does not set because MEMORY_TARGET, these two parameters will automatically change back to 0.
The answer is actually C and D if the instance is restarted. If the instance is not restarted then current answer of A and D is correct.
If you omit MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.
When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.
SQL> alter system set MEMORY_MAX_TARGET=0 scope = spfile;
System altered.
SQL> alter system set MEMORY_TARGET=500M scope = spfile;
System altered.
SQL> alter system set PGA_AGGREGATE_TARGET=90M scope = spfile;
System altered.
SQL> alter system set SGA_TARGET=270M scope = spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET