ORA-02097, ORA-00837 (a small problem encountered when modifying memory_target)

ORA-02097, ORA-00837 (a small problem encountered when modifying memory_target)

ORA-02097, ORA-00837 (a small problem encountered when modifying memory_target)

Today I saw a little questions about ocp, I did a small experiment, encountered two errors of ORA-02097 and ORA-00837;

Question:

Q484. You set the following parameters in the parameter fileand restarted the database:

MEMORY_MAX_TARGET = 0

MEMORY_TARGET = 500 M

PGA_AGGREGATE_TARGET = 90 M

SGA_TARGET = 270 M

Which two statements are true regarding these parameters afterthe database instance is restarted? (Choose two .)

A. The MEMORY_MAX_TARGET parameter is automatically set to500 MB.

B. The value of the MEMORY_MAX_TARGET parameter remains zerotill it is changed manually.

C. The PGA_AGGREGATE_TARGET and SGA_TARGET parameters areautomatically set to zero.

D. The lower bounds of PGA_AGGREGATE_TARGET and SGA_TARGETparameters are set to 90 MB and 270 MB, respectively.

Answer: A, D

I thought B should be correct, not A; after the experiment, the answer is no problem;

Experiment Process

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C: \ Documents ents and Settings \ Administrator> sqlplus/as sysdba
SQL * Plus: Release 11.2.0.1.0 Production on Saturday August 3 09:54:47 2013
Copyright (c) 1982,201 0, Oracle. All rights reserved.

Connect:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter mem

NAME TYPE VALUE
-----------------------------------------------------------------------------
Hi_shared_memory_address integer 0
Memory_max_target big integer 0
Memory_target big integer 0
Shared_memory_address integer 0

SQL> show parameter pga


NAME TYPE VALUE
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 194 M

SQL> show parameter sga

NAME TYPE VALUE
-----------------------------------------------------------------------------
Lock_sga boolean FALSE
Pre_page_sga boolean FALSE
Sga_max_size big integer 800 M
Sga_target big integer 700 M

Where an error occurs:

SQL>
Alter system set memory_target = 900 M scope = both
*
Row 3 has an error:
ORA-02097: The parameter cannot be modified because the specified value is invalid
ORA-00837: the specified value of MEMORY_TARGET is greater than MEMORY_MAX_TARGET


Since the existing values cannot be adjusted, use the policy that takes effect after restart.

SQL>

The system has been changed.

It seems that this is acceptable. We recommend that you create a pfile before restarting to avoid manual adjustment when the file cannot be started.
SQL> create pfile = 'd: \ pfile_t.ora 'from spfile;

The file has been created.

SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> startup
The ORACLE routine has been started.

Total System Global Area 836976640 bytes
Fixed Size 1377812 bytes
Variable Size 683674092 bytes
Database Buffers 146800640 bytes
Redo Buffers 5124096 bytes
The database has been loaded.
The database has been opened.

It seems that the modification is successful. Next, let's take a look at the situation.

SQL> show parameter mem

NAME TYPE VALUE
-----------------------------------------------------------------------------
Hi_shared_memory_address integer 0
Memory_max_target big integer 900 M
Memory_target big integer 900 M
Shared_memory_address integer 0

Memory_max_target has been automatically adjusted to 900 M

SQL> show parameter pga
NAME TYPE VALUE
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 194 M

SQL> show parameter sga
NAME TYPE VALUE
-----------------------------------------------------------------------------
Lock_sga boolean FALSE
Pre_page_sga boolean FALSE
Sga_max_size big integer 800 M
Sga_target big integer 700 M

Pga_aggregate_target and sga_target remain unchanged