You set the following parameters in the parameter file and restart the database instance:
NEMORY_MAX_TARGET=0
MEMORY__TARGET=500M
PGA_AGGREGATE_TARGET=90M
SGA_TARGET=270M
Which two statements are true?
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 for the database instance.
D.
The lower limits of the pga_aggregate_target and sga_target parameters are set to 90 MB
and 270 MB respectively.
E.
The instance does not start up because Automatic Memory Management (AMM) is
enabled but pga_aggregate_target and sga_target parameters are set to nonzero values.
Explanation:
Reference:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm#ADMIN11200
(See the second note on the page)
C & E should be the answers.
Tried to startup after setting the above parameters with the following error.
SQL> startup nomount
ORA-01078: failure in processing system parameters
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL> !oerr ora 837
00837, 00000, “Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET”
// *Cause: The specified value of MEMORY_TARGET was greater than MEMORY_MAX_TARGET.
// *Action: Set MEMORY_TARGET less than or equal to MEMORY_MAX_TARGET.
C and E
C and D should be correct answers.
Also, E is halfway correct – “The instance does not start up”
but justification part – “because Automatic Memory Management (AMM) is
enabled but pga_aggregate_target and sga_target parameters are set to nonzero values.” is incorrect.
if you set parameters as below, instance starts up and minimum values of sga_target and pga_aggregate_target are set to 270M and 90M respectively.
NEMORY_MAX_TARGET=500M
MEMORY__TARGET=500M
PGA_AGGREGATE_TARGET=90M
SGA_TARGET=270M
If you omit memory_max_target in pfile, your instance will start and correct answers will be A,D. But here correct answers is absent because instance doesn’t start with ORA-00837
can anyone confirm the final correct answer?
C and E?
The correct answer is A , D
https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN11201
From the above 12c link –
Note:
In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, then the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
6.Shut down and restart the database.
See Chapter 3, “Starting Up and Shutting Down” for instructions.
7.If you started your Oracle Database instance with a server parameter file, enter the following commands:
ALTER SYSTEM SET MEMORY_TARGET = nM;
ALTER SYSTEM SET SGA_TARGET = 0;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
where n is the value that you determined in step 3.
Note:
With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA. By setting both of these to zero as shown, there are no minimums, and the SGA and instance PGA can grow as needed as long as their sum is less than or equal to the MEMORY_TARGET setting. The sizing of SQL work areas remains automatic.
You can omit the statements that set the SGA_TARGET and PGA_AGGREGATE_TARGET parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.
In addition, you can use the PGA_AGGREGATE_LIMIT initialization parameter to set an instance-wide hard limit for PGA memory. You can set PGA_AGGREGATE_LIMIT whether or not you use automatic memory management. See “Using Automatic PGA Memory Management”.
A is not correct.
B is not correct too.
A and D are correct.
SQL> alter system set memory_target=500m;
alter system set memory_target=500m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least
1440M
We can not set memory_target = 500m. The question is wrong.
write “scope=spfile ” end of the sentence and restart instance.
or set above parameters inside the pfile restart the instance from pfile .
ok.
A,D correct
MEMORY_MAX_TARGET=0 ==> as this value is mentioned – the database cannot be started, if this parameter is not there in pfile – MEMORY_MAX_TARGET takes the value of MEMORY_TARGET – so A is wrong answer.
C and D are the only logical answers
A – wrong – because memory_max_target is explicitly set to 0 here. It takes the value of memory_target only when the mem_max_tgt is not set
B – wrong – wrong wrong wrong – they are set here explicitly
E – wrong – this is partially correct i.e. database will not start – but the reason is totally wrong. correct reason is that memory_max_target = 0
A and D:
SQL> show parameter memory
NAME TYPE VALUE
memory_max_target big integer 0
memory_target big integer 0
optimizer_inmemory_aware boolean TRUE
SQL> show parameter sga
NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 2G
sga_target big integer 2G
SQL> show parameter pga
NAME TYPE VALUE
———————————— ———– ——————————
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 512M
SQL> alter system set memory_target=3g scope=spfile;
System altered.
SQL> show parameter memory
SQL> startup force
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size 3050800 bytes
Variable Size 1677722320 bytes
Database Buffers 1526726656 bytes
Redo Buffers 13725696 bytes
Database mounted.
Database opened.
SQL> show parameter memor
NAME TYPE VALUE
———————————— ———– ——————————
memory_max_target big integer 3G
memory_target big integer 3G
If memory_max_target is not included in this question then.
CD acceptable.
If you get such an immaturely framed question in the exam go for
D&E.
Because that sounds more incorrect for such flaw infested question
A (Correct): Default value for MEMORY_MAX_TARGET is 0. Hence if it is not set when MEMORY_TARGET is set, Oracle automatically sets MEMORY_MAX_TARGET equal to MEMORY_TARGET. In case one has set lower (non-zero) value for MEMORY_MAX_TARGET than MEMORY_TARGET, instance will not start and it will ask to equal or make it’s value greater than MEMORY_TARGET.
B (Incorrect): These values considered as minimum lowest values for parameters.
C (Incorrect): -As mentioned in above answer for A-
D (Correct): -As mentioned in above answer for B-
E (Incorrect)
Assuming values are set in pfile, (A) is wrong. If misprint is intentional (NEMORY_MAX_TARGET instead of MEMORY_MAX_TARGET) then instance doesn’t start with “unknown parameter name ‘NEMORY_MAX_TARGET'” . If MEMORY_MAX_TARGET is set to 0, startup fails with “Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET”. (A) would work if MEMORY_MAX_TARGET omitted , but its not the case (or the question is wrong).
(D) is correct and (B) is wrong, everyone seems to agree on that.
I think (C) is better choice than (E) and I’d finally go with (C,D).
A and D.
The answer is here:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm#ADMIN11200
Note:
In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
Note:
The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.
So, I think A and D.
Actually in exam parameter “NEMORY_MAX_TARGET=0” is absent, so answer is straightforward
A & D
https://oracle-base.com/articles/11g/automatic-memory-management-11gr1
A And D
A : if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.
D : With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA
https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN11200
(Read Note: section of webpage)
AD