9 Replies Latest reply: May 21, 2012 5:25 PM by jgarry RSS

    Reducing memory_target below 6.5G gives ORA-00838

    stuartu
      We have a server (Linux 5.7, 11.2.0.3 64-bit, 16Gb RAM) which unfortunately has more than Oracle running on it (SAS and R).

      The requirements changed recently where both groups of users (Oracle / non-Oracle) came to an agreement about server usage. Oracle users want Thu-Sun and non-Oracle Mon-Wed.

      I set the memory_max_size recently to 10G, and was trying to reduce it to 2Gb (for our SAS / R users), but when I do this, I get the error:
      PREDICT: SYS AS SYSDBA> alter system set memory_target=2G scope=both;
      alter system set memory_target=2G scope=both
      *
      ERROR at line 1:
      ORA-02097: parameter cannot be modified because specified value is invalid
      ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6576M
      The database is using an spfile, and when I look at the SGA setting, I see the below:
      PREDICT: SYS AS SYSDBA> show parameter memory
      
      NAME                         TYPE      VALUE
      ------------------------------------ ----------- ------------------------------
      hi_shared_memory_address          integer      0
      memory_max_target               big integer 10G
      memory_target                    big integer 10G
      shared_memory_address               integer      0
      PREDICT: SYS AS SYSDBA> 
      PREDICT: SYS AS SYSDBA> 
      PREDICT: SYS AS SYSDBA> show sga            
      
      Total System Global Area 8551575552 bytes
      Fixed Size              2245480 bytes
      Variable Size           6241127576 bytes
      Database Buffers      2298478592 bytes
      Redo Buffers              9723904 bytes
      PREDICT: SYS AS SYSDBA> 
      PREDICT: SYS AS SYSDBA> select pool, sum(bytes) from v$sgastat group by pool;
      
      POOL          SUM(BYTES)
      ------------ ----------
                2310447976
      java pool      16777216
      streams pool   16777216
      shared pool  1442840576
      large pool     16777216
      
      5 rows selected.
      
      PREDICT: SYS AS SYSDBA> select pool, name, sum(bytes) from v$sgastat where name = 'free memory' group by pool, name;
      
      POOL          NAME               SUM(BYTES)
      ------------ -------------------------- ----------
      streams pool free memory            15447672
      shared pool  free memory           176283512
      java pool    free memory            16777216
      large pool   free memory             5271552
      
      4 rows selected.
      Yeah, sure, I have space to bounce the database, but I was curious why Oracle thinks it can't resize below 6.5G. We have plenty of other databases which survive on less RAM.

      Is there some caveat to AMM I'm not aware of (or perhaps my brain isn't functioning after a trip to the dentist this morning)...
        • 1. Re: Reducing memory_target below 6.5G gives ORA-00838
          Srini Chavali-Oracle
          Pl post current values of SGA_TARGET and PGA_AGGREGATE_TARGET

          OERR: ORA 838 Specified value of MEMORY_TARGET is too small, needs to be at least %sM [ID 786820.1]

          HTH
          Srini
          • 2. Re: Reducing memory_target below 6.5G gives ORA-00838
            stuartu
            Yes, I know what the error means. The question is more 'why won't it reduce smaller'?
            PREDICT: SYS AS SYSDBA> show parameter sga
            
            NAME                         TYPE      VALUE
            ------------------------------------ ----------- ------------------------------
            lock_sga                    boolean      FALSE
            pre_page_sga                    boolean      FALSE
            sga_max_size                    big integer 8G
            sga_target                    big integer 0
            PREDICT: SYS AS SYSDBA> show parameter pga
            
            NAME                         TYPE      VALUE
            ------------------------------------ ----------- ------------------------------
            pga_aggregate_target               big integer 0
            PREDICT: SYS AS SYSDBA> 
            So I'll try flushing buffers...
            PREDICT: SYS AS SYSDBA> alter system flush buffer_cache;
            
            System altered.
            
            PREDICT: SYS AS SYSDBA> alter system set memory_target=2G scope=both;
            alter system set memory_target=2G scope=both
            *
            ERROR at line 1:
            ORA-02097: parameter cannot be modified because specified value is invalid
            ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6576M
            
            PREDICT: SYS AS SYSDBA> alter system flush shared_pool;
            
            System altered.
            
            PREDICT: SYS AS SYSDBA> alter system set memory_target=2G scope=both;
            alter system set memory_target=2G scope=both
            *
            ERROR at line 1:
            ORA-02097: parameter cannot be modified because specified value is invalid
            ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6576M
            
            PREDICT: SYS AS SYSDBA> 
            I was hoping setting scope=spfile and bouncing would not be the only option here....
            • 3. Re: Reducing memory_target below 6.5G gives ORA-00838
              rp0428
              What is your allocation for /dev/shm?
              • 4. Re: Reducing memory_target below 6.5G gives ORA-00838
                stuartu
                it is 10Gb.
                it_dba@predict:/home/it_dba> cat /etc/fstab | grep shmfs
                shmfs                  /dev/shm              tmpfs   size=10G         0
                it_dba@predict:/home/it_dba> df -h 
                Filesystem            Size  Used Avail Use% Mounted on
                ...
                ...
                shmfs                  10G  3.7G  6.4G  37% /dev/shm
                it_dba@predict:/home/it_dba> 
                • 5. Re: Reducing memory_target below 6.5G gives ORA-00838
                  sb92075
                  PGA_AGGREGATE_TARGET

                  Property     Description
                  Parameter type     Big integer
                  Syntax     PGA_AGGREGATE_TARGET = integer [K | M | G]
                  Default value     10 MB or 20% of the size of the SGA, whichever is greater

                  http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams189.htm#REFRN10165
                  • 6. Re: Reducing memory_target below 6.5G gives ORA-00838
                    stuartu
                    sb92075 wrote:
                    PGA_AGGREGATE_TARGET

                    Property     Description
                    Parameter type     Big integer
                    Syntax     PGA_AGGREGATE_TARGET = integer [K | M | G]
                    Default value     10 MB or 20% of the size of the SGA, whichever is greater

                    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams189.htm#REFRN10165
                    Not sure what you mean. We are using AMM, so PGA_AGGREGATE_TARGET should be 0.

                    http://www.oracle-base.com/articles/11g/automatic-memory-management-11gr1.php
                    • 7. Re: Reducing memory_target below 6.5G gives ORA-00838
                      sb92075
                      You should only believe half of what appears on oracle-base.com
                      but you can never be sure of which half that is!
                      • 8. Re: Reducing memory_target below 6.5G gives ORA-00838
                        stuartu
                        sb92075 wrote:
                        You should only believe half of what appears on oracle-base.com
                        but you can never be sure of which half that is!
                        I researched this more, and the most comprehensive doc on this is on MOS:

                        Automatic Memory Management (AMM) on 11g [ID 443746.1]

                        Reading point 4, it refers to a 60/40 rule fixed ratio for SGA/PGA if both sga_target and pga_aggregate_target=0, which goes some way to explain the error around the ~6.5Gb minimum memory requirement out of the 10G memory_max_target I had set. 'fixed' seems to be an understatement...

                        Setting the sga_target and pga_aggregate_target so they are considered minimum values looks to be much more flexible than setting them to zero.
                        PREDICT: SYS AS SYSDBA> alter system set pga_aggregate_target='1G' scope=spfile;
                        
                        System altered.
                        
                        PREDICT: SYS AS SYSDBA> alter system set sga_target='1G' scope=spfile;
                        
                        System altered.
                        
                        PREDICT: SYS AS SYSDBA> show sga 
                        
                        Total System Global Area 8551575552 bytes
                        Fixed Size              2245480 bytes
                        Variable Size           6157241496 bytes
                        Database Buffers      2382364672 bytes
                        Redo Buffers              9723904 bytes
                        PREDICT: SYS AS SYSDBA> shutdown immediate;
                        Database closed.
                        Database dismounted.
                        ORACLE instance shut down.
                        PREDICT: SYS AS SYSDBA> startup
                        ORACLE instance started.
                        
                        Total System Global Area 8551575552 bytes
                        Fixed Size              2245480 bytes
                        Variable Size           6157241496 bytes
                        Database Buffers      2382364672 bytes
                        Redo Buffers              9723904 bytes
                        Database mounted.
                        Database opened.
                        PREDICT: SYS AS SYSDBA> alter system set memory_target='2G' scope=both;
                        
                        System altered.
                        
                        PREDICT: SYS AS SYSDBA> alter system set memory_target='1G' scope=both;
                        alter system set memory_target='1G' scope=both
                        *
                        ERROR at line 1:
                        ORA-02097: parameter cannot be modified because specified value is invalid
                        ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 2048M
                        • 9. Re: Reducing memory_target below 6.5G gives ORA-00838
                          jgarry
                          How about the half that says http://kevinclosson.wordpress.com/2007/08/24/oracle11g-automatic-memory-management-part-ii-automatically-stupid/