2

i am running oracle 11.2.0.4 SE RAC on Red Hat linux 6, the database is using ASM for datafile management

i have been experimenting with turning on AMM and set the following parameters

alter system set pga_aggregate_target='0' scope=spfile sid='*';
alter system set sga_target='0' scope=spfile sid='*';
alter system set memory_max_target='8G' scope=spfile sid='*';
alter system set memory_target='8G' scope=spfile sid='*';

but now i wanted to turn off AMM on the instances and database, but when i make the required changes

alter system set pga_aggregate_target=2G scope=spfile sid='*';
alter system set sga_target=6G scope=spfile sid='*';
alter system set memory_max_target=0 scope=spfile sid='*';
alter system set memory_target=0 scope=spfile sid='*';

i receive this error when starting the instance

SQL> startup pfile='?/dbs/initdev12.ora';
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 6442450944 cannot be set to more than MEMORY_MAX_TARGET 0.
ORA-01078: failure in processing system parameters

if i set the MEMORY_MAX_TARGET back to 8G i can start the database but my parameters now look like this

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 6G
sga_target                           big integer 6G
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 8208M
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 2G

any ideas what i'm doing wrong?

cheers dave

  • You stored our changes into spifile, but you use initdev12.ora to start the database. What is in this file? – ibre5041 Nov 12 '15 at 11:08
  • the initdev12.ora file points to the spfile on the ASM disk, SPFILE='+DATA/dev1/spfiledev1.ora' i am using this so i can debug the problem as the usual srvctl was not giving very useful messages. – davegreen100 Nov 12 '15 at 11:24
2

In case one does not want to use AMM MEMORY_MAX_TARGET setting has to be not set at all:

alter system reset memory_target;
alter system reset memory_max_target;
  • perfect, this did the job! – davegreen100 Nov 12 '15 at 14:46
0

Please note that you are trying to start the DB from (using the) pfile, where you changed values in spfile using commands:

  1. You can edit pfile and set following parameters to 0

    • memory_target
    • memory max target
  2. Then set following values

    • sga_max_size
    • sga_target
    • pga_aggregate_target
  3. Shutdown all instance and bring up one by one using pfile

  4. Create a new spfile: create spfile from pfile;

  5. Start all the instances

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.