11 Replies Latest reply on Feb 24, 2015 12:38 PM by 12cdb

    Out of Memory

    2888673

      Dear everyone,

       

      Once in a week our db breaks down with the following error:

       

      ORA-01034: ORACLE not available

      ORA-27102: out of memory

      Linux-x86_64 Error: 12: Cannot allocate memory

      Additional information: 2

      Additional information: 786443

      Additional information: 1660944384


      I am not a oracle crack, so any help is appreciated.


      Regards

      Tobi

       

      ulimit -a:

       

      core file size          (blocks, -c) 0

      data seg size           (kbytes, -d) unlimited

      scheduling priority             (-e) 0

      file size               (blocks, -f) unlimited

      pending signals                 (-i) 63696

      max locked memory       (kbytes, -l) 64

      max memory size         (kbytes, -m) unlimited

      open files                      (-n) 1024

      pipe size            (512 bytes, -p) 8

      POSIX message queues     (bytes, -q) 819200

      real-time priority              (-r) 0

      stack size              (kbytes, -s) 10240

      cpu time               (seconds, -t) unlimited

      max user processes              (-u) 16384

      virtual memory          (kbytes, -v) unlimited

      file locks                      (-x) unlimited


      /etc/sysctl.conf:

       

      # Kernel sysctl configuration file for Red Hat Linux

      #

      # For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and

      # sysctl.conf(5) for more details.

       

      # Controls IP packet forwarding

      net.ipv4.ip_forward = 0

       

      # Controls source route verification

      net.ipv4.conf.default.rp_filter = 1

       

      # Do not accept source routing

      net.ipv4.conf.default.accept_source_route = 0

       

      # Controls the System Request debugging functionality of the kernel

      kernel.sysrq = 0

       

      # Controls whether core dumps will append the PID to the core filename.

      # Useful for debugging multi-threaded applications.

      kernel.core_uses_pid = 1

       

      # Controls the use of TCP syncookies

      net.ipv4.tcp_syncookies = 1

       

      # Disable netfilter on bridges.

      net.bridge.bridge-nf-call-ip6tables = 0

      net.bridge.bridge-nf-call-iptables = 0

      net.bridge.bridge-nf-call-arptables = 0

       

      # Controls the default maxmimum size of a mesage queue

      kernel.msgmnb = 65536

       

      # Controls the maximum size of a message, in bytes

      kernel.msgmax = 65536

       

      # Controls the maximum shared segment size, in bytes

       

      # Controls the maximum number of shared memory segments, in pages

      kernel.shmall = 4294967296

       

      # oracle-rdbms-server-12cR1-preinstall setting for fs.file-max is 6815744

      fs.file-max = 6815744

       

      # oracle-rdbms-server-12cR1-preinstall setting for kernel.sem is '250 32000 100 128'

      kernel.sem = 250 32000 100 128

       

      # oracle-rdbms-server-12cR1-preinstall setting for kernel.shmmni is 4096

      kernel.shmmni = 4096

       

      # oracle-rdbms-server-12cR1-preinstall setting for kernel.shmall is 1073741824 on x86_64

       

      # oracle-rdbms-server-12cR1-preinstall setting for kernel.shmmax is 4398046511104 on x86_64

      kernel.shmmax = 4398046511104

       

      # oracle-rdbms-server-12cR1-preinstall setting for kernel.panic_on_oops is 1 per Orabug 19642132

      kernel.panic_on_oops = 1

       

      # oracle-rdbms-server-12cR1-preinstall setting for net.core.rmem_default is 262144

      net.core.rmem_default = 262144

       

      # oracle-rdbms-server-12cR1-preinstall setting for net.core.rmem_max is 4194304

      net.core.rmem_max = 4194304

       

      # oracle-rdbms-server-12cR1-preinstall setting for net.core.wmem_default is 262144

      net.core.wmem_default = 262144

       

      # oracle-rdbms-server-12cR1-preinstall setting for net.core.wmem_max is 1048576

      net.core.wmem_max = 1048576

       

      # oracle-rdbms-server-12cR1-preinstall setting for fs.aio-max-nr is 1048576

      fs.aio-max-nr = 1048576

       

      # oracle-rdbms-server-12cR1-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500

        • 1. Re: Out of Memory
          12cdb

          Hi,

          Can you add DB version and following DB parameter values;

           

          show parameter sga_

          show parameter pga_

          show parameter memory

          show parameter pool

           

          Also provide total memory allocation on the server as below:

           

          $free -g

           

          Also look into the database alert log or more info and copy paste the complete error message.

           

          Regards,

          Pradeep

          • 2. Re: Re: Out of Memory
            2888673

            I am using Oracle 12c (Release 12.1.0.1.0) on Oracle Linux 6

             

            Maybe I also have to say, that I am running two db instances.

            And the error occurs since I have modified memory_target and memory_max_target of SID one, because the database seemed to be slow, and I wanted to allocate more memory.

             

            show parameter sga_:

            SID1:

             

            NAME                                 TYPE        VALUE

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

            sga_max_size                         big integer  4G

            sga_target                           big integer  0

            unified_audit_sga_queue_size         integer      1048576

             

            SID2:

             

            NAME                                 TYPE        VALUE

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

            sga_max_size                         big integer 1200M

            sga_target                           big integer 1200M

            unified_audit_sga_queue_size         integer     1048576

             

            show parameter _pga:

            SID1:

             

            NAME                                 TYPE        VALUE

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

            pga_aggregate_limit                  big integer 2848M

            pga_aggregate_target                 big integer 0


            SID2:

             

            NAME                                 TYPE        VALUE

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

            pga_aggregate_limit                  big integer 2G

            pga_aggregate_target                 big integer 395M


            show parameter memory:

            SID1:

             

            NAME                                 TYPE         VALUE

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

            hi_shared_memory_address             integer     0

            memory_max_target                    big integer 4G

            memory_target                        big integer 4G

            shared_memory_address                integer     0


            SID2:

             

            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


            show parameter pool:

            SID1:

             

            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 26843545

            shared_pool_size                     big integer 0

            streams_pool_size                    big integer 0


            SID2:

             

            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 14260633

            shared_pool_size                     big integer 0

            streams_pool_size                    big integer 0


            free -g:

             

                         total       used       free     shared    buffers     cached

            Mem:             7          5          2          3          0          4

            -/+ buffers/cache:          0          7

            Swap:            3          0          3


            alert-log:

             

            Thu Feb 19 04:29:13 2015

            TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P3610 (1876) VALUES LESS THAN (TO_DATE(' 2015-02-20 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

            TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P3611 (1876) VALUES LESS THAN (TO_DATE(' 2015-02-20 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

            Thu Feb 19 05:28:57 2015

            Process m000 died, see its trace file

            Process m000 died, see its trace file

            Process m000 died, see its trace file

            Process m001 died, see its trace file

            Process m000 died, see its trace file

            Process m001 died, see its trace file

            Process m000 died, see its trace file

            • 3. Re: Out of Memory
              JohnWatson2

              How big is your tmpfs /dev/shm file system?

              • 4. Re: Out of Memory
                John Stegeman

                "database seemed to be slow" so you added more memory.

                 

                What facts did you have that indicated adding more memory would solve the issue? Sounds like the classic, "Ready, fire, aim" approach to problem resolution

                • 5. Re: Out of Memory
                  12cdb

                  total host memoery = 7G

                  SID 1 :

                  memory_max =4G   (Total SGA+PGA)

                  sga_max_size = 4G

                  PGA= 2.28G

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

                  TOTAL = 6.28G

                  How can you cross the 4GB max limit by allocating individual components more i.e. 6.28G.

                  As for as I know you can not do that so you have to reset sga and pag allocation to make sure the total allocation does not cross 4GB limit set by memory_max.

                  example: 

                  sga_max_size = 2.5G

                  PGA= 1.5G

                   

                  Changing sga_max_size will require database bounce.

                   

                   

                  HTH,

                  Pradeep

                  • 6. Re: Out of Memory
                    JohnWatson2

                    Expected behaviour. sga_max_size will default to memory_max_size.

                    • 7. Re: Out of Memory
                      12cdb

                      Thanks.

                      As for as I remeber memory_max is sum of  SGA &  PGA, and if it defaults sga_max_size = memory_max, what space is allocated to PGA when the instance starts ?

                      • 8. Re: Out of Memory
                        jgarry

                        First of all, throwing memory at a system because it is slow is the wrong thing to do.  Does it work when you set it back?  You need to find out what is slow to be an oracle crack, rather than on crack.

                        Second of all, running 2 instances means you have to account for both, Oracle doesn't do that for you.  It could well be what JohnWatson2 said.

                        Third, see Oracle Automatic Memory Management | Kevin Closson's Blog: Platforms, Databases and Storage

                        • 9. Re: Out of Memory
                          Osama_Mustafa

                          Ora-27102: Out Of Memory: Linux Error: 12: Cannot Allocate Memory with LOCK_SGA=TRUE (Doc ID 401077.1)

                          • 10. Re: Out of Memory
                            2888673

                            I have raised my tmpfs to 10 GB.

                             

                            Ok let see if I understand it right.

                            I actually could set sga_target and sga_max_target, pga_aggregate_target and memory_max_target to 0.

                             

                            If I only set memory_target, oracle will automatically use 60% for SGA and 40% PGA correct?

                             

                            But I still have to make sure that the sum of all memory_targets is not higher than my allocated tmpfs. Correct?

                             

                            Would that be a solution?

                            • 11. Re: Out of Memory
                              12cdb

                              >> If I only set memory_target, oracle will automatically use 60% for SGA and 40% PGA correct?

                              Not correct asllocation to SGA & PGA will be manged by Oracle automatically and as for as I know not in that ratio you have give.


                              You can set MEMORY_MAX_TARGET & MEMORY_TARGET to same value and make sure they are set below available memory on the server and tmpfs.

                               

                              HTH,

                              Pradeep