10 Replies Latest reply: May 21, 2012 1:10 PM by 931915 RSS

    urgent help with memory issue (ORA-04031)

    931915
      Hi Geeks,
      Came Across this below error in alert log in of our databases. Interestingly this error only pops up while the database backup runs. Below are the memory parameters.

      memory_max_target big integer 14G
      memory_target big integer 13G
      pga_aggregate_target big integer 0
      sga_target big integer 0
      shared_pool_reserved_size big integer 192M
      shared_pool_size big integer 0

      ORA-04031: unable to allocate 5792 bytes of shared memory
      ("shared pool","unknown object","sga heap(1,1)","ges resource ")
      Errors in file /apps/opt/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_lmd0_14123.trc

      I have tried specifying a value to shared_pool_size but with no luck..!! Pls help. I am not sure how to resolve this.
        • 1. Re: urgent help with memory issue (ORA-04031)
          sb92075
          928912 wrote:
          Hi Geeks,
          Came Across this below error in alert log in of our databases. Interestingly this error only pops up while the database backup runs. Below are the memory parameters.

          memory_max_target big integer 14G
          memory_target big integer 13G
          pga_aggregate_target big integer 0
          sga_target big integer 0
          shared_pool_reserved_size big integer 192M
          shared_pool_size big integer 0

          ORA-04031: unable to allocate 5792 bytes of shared memory
          ("shared pool","unknown object","sga heap(1,1)","ges resource ")
          Errors in file /apps/opt/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_lmd0_14123.trc

          I have tried specifying a value to shared_pool_size but with no luck..!! Pls help. I am not sure how to resolve this.
          is this from ASM instance?

          what is being backed up to where?

          post results from following SQL

          SELECT * FROM V$SESSION;
          • 2. Re: urgent help with memory issue (ORA-04031)
            Helios-GunesEROL
            Please follow below and see its helpful:

            ORA-040310 Unable To Allocate XXXX Bytes Of Memory [ID 1061139.6]
            Getting ORA-04031: unable to allocate 86448 bytes of shared memory [ID 307608.1]


            For can get more details about ORA-04031 check below

            Diagnosing and Resolving Error ORA-04031 [ID 146599.1]
            OERR: ORA 4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" [ID 19837.1]
            OERR: ORA 4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" [ID 19837.1]
            ORA-040310 Unable To Allocate XXXX Bytes Of Memory [ID 1061139.6]



            Regard
            Helios
            • 3. Re: urgent help with memory issue (ORA-04031)
              fjfranken
              Hi, ORA-4031 in this case can be caused by unrestricted growth of the PGA, thus forcefully reducing the SGA.
              We've had this in the past also.
              The solution is in older versions to increase the shared_pool_size as you mentioned but with ASMM, this is no longer an option. You can however retrict the growth of the PGA by setting SGA_TARGET.
              On our system this now looks like:
              SQL> show parameter _target
              
              NAME                                 TYPE                 VALUE
              ------------------------------------ -------------------- ------------------------------
              db_flashback_retention_target        integer              1440
              memory_max_target                    big integer          10G
              memory_target                        big integer          10G
              pga_aggregate_target                 big integer          0
              sga_target                           big integer          8G
              See how we in fact set the max growth of the PGA to 2Gb ( 10Gb - 8Gb)

              Try this and see if ot works for you also


              Success!!
              FJFranken
              • 4. Re: urgent help with memory issue (ORA-04031)
                931915
                hi fjfranken,
                thanks for the reply... but how can we make sure that the issue with pga in my case.

                SQL> show parameter target

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------

                memory_max_target big integer 14G
                memory_target big integer 13G
                pga_aggregate_target big integer 0
                sga_target big integer 0

                SQL> show parameter 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 192M
                shared_pool_size big integer 0
                streams_pool_size big integer 0

                pgamax_size big integer 1009240K
                pga_aggregate_target big integer 0

                sga_max_size big integer 14G
                sga_target big integer 0

                These are the memory settings right now in place and this happens on only one node RAC
                • 5. Re: urgent help with memory issue (ORA-04031)
                  sb92075
                  fjfranken wrote:
                  Hi, ORA-4031 in this case can be caused by unrestricted growth of the PGA, thus forcefully reducing the SGA.
                  We've had this in the past also.
                  The solution is in older versions to increase the shared_pool_size as you mentioned but with ASMM, this is no longer an option. You can however retrict the growth of the PGA by setting SGA_TARGET.
                  REALLY?

                  http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams140.htm#REFRN10285

                  "Total memory usage can grow beyond the value of MEMORY_TARGET. For example, memory is allocated to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available at the operating system level."

                  Above is really PGA growing without any size limitation.
                  • 6. Re: urgent help with memory issue (ORA-04031)
                    726263
                    Can you check these while the backup is running.
                    You should use your Large Pool for your RMAN buffers.

                    select * from v$sgastat where pool='large pool';

                    select pool,name,bytes from v$sgastat where upper(name) like '%KSFQ%';


                    Regards,
                    KDSDBA
                    • 7. Re: urgent help with memory issue (ORA-04031)
                      fjfranken
                      sb92075 wrote:
                      fjfranken wrote:
                      Hi, ORA-4031 in this case can be caused by unrestricted growth of the PGA, thus forcefully reducing the SGA.
                      We've had this in the past also.
                      The solution is in older versions to increase the shared_pool_size as you mentioned but with ASMM, this is no longer an option. You can however retrict the growth of the PGA by setting SGA_TARGET.
                      REALLY?

                      http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams140.htm#REFRN10285

                      "Total memory usage can grow beyond the value of MEMORY_TARGET. For example, memory is allocated to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available at the operating system level."

                      Above is really PGA growing without any size limitation.
                      Thanks,

                      I see now I should have said it somewhat different. By setting SGA_TARGET you can prevent the PGA from crushing your SGA pools.

                      FJFranken
                      • 8. Re: urgent help with memory issue (ORA-04031)
                        sb92075
                        In the Default value field, IMMEDIATE mode autotuning requests are necessary to avoid ORA-04031 errors.
                        • 9. Re: urgent help with memory issue (ORA-04031)
                          726263
                          Move the KSFQ buffers from the PGA to the Large Pool.
                          RMAN takes advantage of using the Large Pool instead of the PGA.

                          Regards,
                          KDSDBA
                          • 10. Re: urgent help with memory issue (ORA-04031)
                            931915
                            Hi ,

                            Move the KSFQ buffers from the PGA to the Large Pool.
                            RMAN takes advantage of using the Large Pool instead of the PGA.

                            Regards,
                            KDSDBA


                            How should i move those buffers?

                            Thanks