This discussion is archived
5 Replies Latest reply: Sep 16, 2013 7:34 AM by User393329 RSS

Different SGA on each node is possible?

User393329 Newbie
Currently Being Moderated

I have a 4 node RAC, and i have several databases in node 1-2 and others in node 3-4.

Databases in 3-4 node have higher SGAs than databases in node 1-2 and i want to create a new instance from DATABASEA3  in node 1-2, but with half SGA memory than 3-4 node RAC.

 

F.E

Databasea3/4 --> 24 Gb SGA

NEW Databasea1/2--> 12 Gb SGA

 

Is this possible? Can I use the option

 

databasea1.memory_target =12G

databasea3.memory_target =24G

 

I didn't find infor about that. Thanks in advance

  • 1. Re: Different SGA on each node is possible?
    User393329 Newbie
    Currently Being Moderated

    I answer myself,

     

    Yes i can have several instances on RAC with distinct SGA.

     

    alter system set memory_max_target=1G scope=spfile sid='DATABASEA1';System altered.
    SQL> show parameters memory;
    NAME                     TYPE     VALUE
    ------------------------------------ ----------- ------------------------------------------------------------
    hi_shared_memory_address           integer     0
    memory_max_target                      big integer 2G
    memory_target                              big integer 1G
    shared_memory_address               integer     0

     

     

    SQL> alter system set memory_target=2G  scope=spfile sid='DATABASEA3';

    SQL> show parameters memory

    NAME                     TYPE     VALUE

    ------------------------------------ ----------- ------------------------------------------------------------

    hi_shared_memory_address         integer     0

    memory_max_target                    big integer 2G

    memory_target                            big integer 2G

    shared_memory_address             integer     0

     

    I have to test before ask.

  • 2. Re: Different SGA on each node is possible?
    User393329 Newbie
    Currently Being Moderated

    Ok, here is my new doubt:

     

    Can i create a new instance with less SGA directly??

     

    Can i create DATABASEA1 with 12G SGA  and avoid change memory_target parameters after new instance creation?

  • 3. Re: Different SGA on each node is possible?
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    Yes, You can , Only you have to specify that value in init file. if you are going to use DBCA then you can change the Values according to your usage  and your server  memory.

     

    Hope this Help

  • 4. Re: Different SGA on each node is possible?
    Levi-Pereira Guru
    Currently Being Moderated

    Hi,

    Yes, is possible.


    You should be aware of the issue which this can cause.


    If there is a large difference between the nodes of SGA, than the "bigger" SGA will attract more load, obviously, and in the case of failure the "smaller" node(s) will become overpowered.

    The general recommendation is that the nodes should be sized in such a way that the aggregated peak load of the large node(s) can be absorbed by the smaller node(s), i.e. smaller node should have sufficient capacity to run the essential services alone.


    It should also be noted especially if there is a large difference between the sizes of the nodes, the small nodes can slow down the larger node. This could be critical one if the smaller node is very busy and must serve data to the large node.

  • 5. Re: Different SGA on each node is possible?
    User393329 Newbie
    Currently Being Moderated

    I know that both instances must have the same SGA. At the beginning, DATABASEA in nodes 1/2 will be stopped, until both nodes of RAC3/4 goes down.

     

    Thanks for your support!

     

    I'm triying to add a new instance in PREPRO environment, DBCA doesn't show me the options to modify the memory of new instance.

     

    I can't upload the screenshot, but i'm in the final step where i can modify the paths of database.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points