12 Replies Latest reply: Apr 9, 2014 7:33 AM by David Mateluna RSS

    ORA-04031 Oracle RAC 11.2.0.3 with ASM

    David Mateluna

      Hi:

       

      I need your help.

      When the automatic memory increase of BD more than 8 Gb, my BD RAC indicates failure.

       

      Environment:

      Oracle RAC 11.2.0.3 with 2 nodes.

      O.S.: SunOS rac1 5.11 11.1 sun4v sparc sun4v

      RAM: 32 Gb.

      Oracle Grid Infrastructure 11.2.0.3

      Oracle ASM 11.2.0.3

      Oracle Database 11.2.0.3 Enterprise Edition with RAC option.

       

      Symptom:

      I created my DB RAC with MEMORY_TARGET = 1G.

      I've been increasing the parameter to 8Gb without problems, however from the 9Gb, this error occurs:

      *.memory_max_target=9g      

      *.memory_target=4g

       

      SQL> startup nomount;

      ORACLE instance started.

      ORA-00604: error occurred at recursive SQL level 2

      ORA-04031: unable to allocate 32 bytes of shared memory ("shared

      pool","X$KSMSD","KGLS^c51f85ea","kglHeapInitialize:temp")

       

      Background:

       

      These are the kernel parameters for the oracle account:

      oracle@rac1:~$ prctl -n project.max-shm-memory -i project user.oracle

      project: 100: user.oracle

      NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

      project.max-shm-memory

              privileged     20.00GB      -   deny                                 -

              system          16.0EB    max   deny                                 -

       

      oracle@rac1:~$ prctl -n project.max-sem-ids -i project user.oracle

      project: 100: user.oracle

      NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

      project.max-sem-ids

              privileged      16.0M       -   deny                                 -

              system          16.8M     max   deny                                 -

       

      How I can fix this?

       

      Thanks

      David

        • 1. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
          Levi Pereira

          What is output of:

           

          SQL> show parameter pool


          or post here our PFILE.

          • 2. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
            Emad Al-Mousa

            Getting error ORA-4031: unable to allocate 2600 bytes of shared memory ("shared

            pool","unknown object","sga heap(1,0)","FileOpenBlock").

             

             

             

            High hard parsing due to FileOpenBlock eating up shared pool memory.

             

             

             

            Query X$KSMDD for FileOpenBlock object and there will be an increase in size of this object:

             

             

             

            select * from X$KSMDD where name='FileOpenBlock';

             

            Also, increase the shared pool size:

             

            SQL> alter system set shared_pool_size=?

            • 3. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
              AK - Oracle

              can you provide the results of  below to see the current internal memory settings, please run the following SQL statements:

               

              COL NAME FORMAT A32

              COL VALUE FORMAT A40

              SPOOL SGAPARAMS.TXT

              /* Database Identification */

              select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;

              select * from V$version where banner like 'Oracle Database%';

              /* Shared Pool parameters */

              select nam.ksppinm NAME, val.KSPPSTVL VALUE

              from x$ksppi nam, x$ksppsv val

              where nam.indx = val.indx and (nam.ksppinm like '%shared_pool%' or nam.ksppinm like '_4031%' or nam.ksppinm in ('_kghdsidx_count','_ksmg_granule_size','_memory_imm_mode_without_autosga'))

              order by 1;

              SPOOL OFF

               

              Ref Doc

              Troubleshooting and Diagnosing ORA-4031 Error [Video] [ID 396940.1]

              ORA-4031 Common Analysis/Diagnostic Scripts [Video] [ID 430473.1]

              • 4. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                BDK

                If it is development database then got with below command ..

                SQL> alter system flush shared_pool;

                 

                System altered.

                ===================================

                ##As per metalink##

                 

                ORA-04031 unable to allocate string bytes of shared memory ("string","string","string","string")

                 

                Cause: More shared memory is needed than was allocated in the shared pool.

                 

                Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.


                Please post your SGA value(sga_target) and kernel parameters(/etc/sysctl.conf)

                • 5. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                  JohnWatson

                  I have had similar problem with AMM on Solaris.

                  It does look as though you have configured adequate DISM segments, but remember that you must also configure swap space greater than the total DISM. Have you done that? Sure, the swap may never be used, but it must be there because of the way Solaris implements DISM.

                  • 6. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                    Jyoti Verma -Oracle

                    Have you checked: OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" (Doc ID 4031.1)

                    • 7. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                      David Mateluna

                      Hi Levi-Pereira

                       

                      Thank you for your attention.

                       

                      The BD does not start when the parameter memory_max_target = 9G, therefore should change the value to 8Gb to show the values of the required parameters:

                       

                      SQL> show parameters pool

                       

                      NAME                                 TYPE        VALUE

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

                      buffer_pool_keep                     string

                      buffer_pool_recycle                  string

                      global_context_pool_size             string

                      java_pool_size                       big integer 0

                      large_pool_size                      big integer 0

                      olap_page_pool_size                  big integer 0

                      shared_pool_reserved_size            big integer 11744051

                      shared_pool_size                     big integer 0

                      streams_pool_size                    big integer 0

                       

                       

                      SQL> show parameters memory

                       

                      NAME                                 TYPE        VALUE

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

                      hi_shared_memory_address             integer     0

                      memory_max_target                    big integer 8G

                      memory_target                        big integer 1G

                      shared_memory_address                integer     0

                      SQL>

                       

                       

                      SQL> show parameters sga

                       

                      NAME                                 TYPE        VALUE

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

                      lock_sga                             boolean     FALSE

                      pre_page_sga                         boolean     FALSE

                      sga_max_size                         big integer 8G

                      sga_target                           big integer 0

                      SQL>

                      • 8. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                        David Mateluna

                        Hi Emad Al-Mousa

                         

                        Thank you for your attention.

                        The BD does not start when the parameter memory_max_target = 9G, therefore should change the value to 8Gb to show query required:

                         

                        select * from X$KSMDD where name='FileOpenBlock';

                         

                        ADDR                   INDX INST_ID NAME            ELEMENTS_CHUNK   ITEMS_PT INITENTRIES NUMENTRIES CURENTRIES  NUMCHUNKS ELEMSIZE FLAGS

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

                        HEAP             SECONDARY

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

                        FFFFFFFF7BC5B3B0 4294967295       1 FileOpenBlock                8        256        6400       6400          0        800      264     0

                        0000000380002100 00

                         

                         

                        alter system set shared_pool_size=345m scope=memory;

                         

                        SQL> show parameters shared_pool_size

                        shared_pool_size                     big integer 0

                        • 9. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                          David Mateluna

                          Hi AK - Oracle:

                           

                          Thank you for your attention.

                          The BD does not start when the parameter memory_max_target = 9G, therefore should change the value to 8Gb to show query required:

                           

                          COL NAME FORMAT A32

                          COL VALUE FORMAT A40

                          SPOOL SGAPARAMS.TXT

                           

                          /* Database Identification */

                          select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;

                          select * from V$version where banner like 'Oracle Database%';

                           

                          NAME      PLATFORM_ID DATABASE_ROLE

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

                          TEST                2 PRIMARY

                           

                          BANNER

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

                          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                           

                           

                          /* Shared Pool parameters */

                          select nam.ksppinm NAME, val.KSPPSTVL VALUE

                          from x$ksppi nam, x$ksppsv val

                          where nam.indx = val.indx and (nam.ksppinm like '%shared_pool%' or nam.ksppinm like '_4031%' or nam.ksppinm in ('_kghdsidx_count','_ksmg_granule_size','_memory_imm_mode_without_autosga')) order by 1;

                          SPOOL OFF

                           

                          NAME                             VALUE

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

                          _4031_dump_bitvec                67194879

                          _4031_dump_interval              300

                          _4031_max_dumps                  100

                          _4031_sga_dump_interval          3600

                          _4031_sga_max_dumps              10

                          __shared_pool_size               587202560

                          _dm_max_shared_pool_pct          1

                          _enable_shared_pool_durations    TRUE

                          _io_shared_pool_size             4194304

                          _kghdsidx_count                  7

                          _ksmg_granule_size               16777216

                           

                           

                          NAME                             VALUE

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

                          _memory_imm_mode_without_autosga TRUE

                          _shared_pool_max_size            0

                          _shared_pool_minsize_on          FALSE

                          _shared_pool_reserved_min_alloc  4400

                          _shared_pool_reserved_pct        5

                          shared_pool_reserved_size        11744051

                          shared_pool_size                 0

                           

                          18 rows selected.

                           

                          SQL>

                          • 10. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                            JWF

                            Your swap file has to be at least as big as your physical ram or in your specific case at least 9 GB or larger.  Set your swap to 32 GB if you plan on using AMM however I wouldn't use AMM since Oracle doesn't support it on Solaris.  Only supported for the ASM instance and not the databases.

                            • 11. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                              teits

                              hello David,

                               

                              can you increase project.max-shm-memory to 32GB and retry increasing memory_max_target to 9GB.

                              • 12. Re: ORA-04031 Oracle RAC 11.2.0.3 with ASM
                                David Mateluna

                                Estimated JWF:


                                You're right, I suspended the use of AMM in Solaris 11 and it worked.

                                With 32 Gb RAM, could not allocate more SGA to 8Gb.
                                Suspending the use of MEMORY_TARGET (AMM), I could now allocate 10Gb to SGA.

                                These are the fixed parameters of the SGA:
                                pga_target = 3G
                                sga_target = 10G
                                db_cache_size = 7G
                                shared_pool_size = 1G

                                The BD started OK.
                                Thanks for the contributions to the solution.
                                Also contributed to solving this website: http://www.pythian.com/blog/why-is-oracle-ignoring-my-memory-parameters/

                                Thank you all.

                                David
                                (from Chile)