5 Replies Latest reply on Sep 14, 2018 3:24 PM by 2759957

    OEM Tablespace Space used (%) metrics constantly fluctuates 25%

    2759957

      Hi,

       

      I am using OEM Cloud Control 12c Rel 4 and alarm Tablespace Space used (%) metrics keeps fluctuating 25% (between 67 and 92%). Please see below for tablespace and other information:

       

      Tablespace size:      780   GB

      Tablespace free:       11.9 GB

      Tablespace used:      768 GB

      Tablespace max size: 1146.8 TB

       

       

      SELECT name

           , state

           , round( total_mb / 1024, 2 ) AS gb_total_size

           , round( USABLE_FILE_MB / 1024, 2 ) AS gb_usable_free

           , round(( USABLE_FILE_MB / total_mb ) * 100, 4 ) as prcnt_free

        FROM V$ASM_DISKGROUP

      ORDER BY 1;

       

      NAME         STATE        GB_TOTAL_SIZE GB_USABLE_FREE PRCNT_FREE

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

      NMERCND_DATA CONNECTED           1735.99           631.9 36.3998

      NMERCND_FRA CONNECTED            150               51.56     34.3767

      NMERCND_LOG CONNECTED             10 3.59     35.8734

       

       

       

      SELECT tablespace_name

           ,  ROUND( used_space /( 1024 * 1024 ), 2 ) AS used_gb

           ,  ROUND( tablespace_size /( 1024 * 1024 ), 2 ) AS tbspce_gb

           ,  ROUND( used_percent, 2 ) AS used_percnt

        FROM dba_tablespace_usage_metrics

      WHERE ROUND( used_percent, 2 ) > 90

      ORDER BY 1;

       

       

      TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

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

      NM_SETTLEMENT_DATA                      48.01            52.75         91.01

       

       

       

      So, is just a bug and I need to upgrade top 13 or am I missing something

       

      Thanks for your help

        • 1. Re: OEM Tablespace Space used (%) metrics constantly fluctuates 25%
          2759957

          One more piece of information from OEM Tablespace Space Used (%) page:

           

          Tablespace Name               Average Value   Low Value   High Value Last Known Value

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

          NM_SETTLEMENT_DATA                  79.76           67.1           91.01                     67.18

          • 2. Re: OEM Tablespace Space used (%) metrics constantly fluctuates 25%
            Ian Baugaard

            Hi,

             

            Are you sure that there aren't perhaps temporary segments being created/dropped repeatedly in said tablespace?

             

            Can you provide some indication from the repository to illustrate what's happening? Please adjust the values as applicable

             

            SELECT 
                 mh.target_name
                 , mh.key_value as tablespace_name
                 , 'Tablespace ' || mh.key_value ||' is '|| ROUND( TO_NUMBER( mh.average ), 2 ) ||'% utilized.' as message
                 , mh.rollup_timestamp 
            FROM sysman.mgmt$metric_hourly mh
            WHERE mh.target_guid = hextoraw('6D4530F1D0E7C3F855A2A67B8794958D')
            AND mh.metric_name = 'problemTbsp'
            AND mh.metric_column = 'pctUsed'
            AND mh.key_value = 'SYSTEM'
            ORDER BY mh.rollup_timestamp ;

             

            Finally, what version database being monitored is raising this issue?

             

            Ian

            • 3. Re: OEM Tablespace Space used (%) metrics constantly fluctuates 25%
              2759957

              Hi,

               

               

              Thanks for responding, since this is not the temp tablespace, there should not be any temporary segments.

               

               

              Hopefully this clarifies what is going on:

               

               

              SELECT bb.target_name

                   , aa.metric_name

                   , aa.column_label

                   , aa.key_value

                   , aa.alert_state

                   , to_char( aa.collection_timestamp, 'MM/DD/YYYY HH24:MI:SS' )

                   , round( aa.alert_duration * 60, 0 ) as alert_duration_min

                   , aa.message

                FROM MGMT$ALERT_HISTORY aa

                     INNER JOIN MGMT_TARGETS bb

                        ON aa.target_guid = bb.target_guid

              WHERE aa.collection_timestamp > sysdate -1

                 AND lower( aa.target_name ) like '%nmercnd%'

                 AND aa.key_value = 'NM_SETTLEMENT_DATA'

                ORDER BY aa.collection_timestamp DESC;

               

               

              Row#      TARGET_NAME          METRIC_NAME                 COLUMN_LABEL               KEY_VALUE     ALERT_STATE  COLLECTION_TIMESTAMP ALERT_DURATION_MIN                                                                                                  MESSAGE

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

              1       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 14:44:40                        CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              2       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 14:29:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              3       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 14:14:40                 15     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              4       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 14:04:40                 10     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              5       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 13:59:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              6       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 13:54:40                  5     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              7       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 13:44:40                 10     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              8       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 13:34:40                 10     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              9       nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 13:29:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              10      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 13:24:40                  5     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              11      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 13:09:40                 15     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              12      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 12:49:40                 20     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              13      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 12:39:40                 10     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              14      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 12:19:40                 20     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              15      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 12:04:40                 15     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              16      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 11:49:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              17      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 11:29:40                 20     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              18      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 11:19:40                 10     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              19      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 11:14:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              20      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 11:09:40                  5     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              21      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 10:59:40                 10     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              22      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 10:49:40                 10     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              23      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 10:44:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              24      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 10:29:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              25      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 10:24:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              26      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 10:19:40                  5     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              27      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 10:14:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              28      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 09:59:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              29      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 09:44:40                 15     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              30      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 09:29:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              31      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 09:14:40                 15     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              32      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 08:59:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              33      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 08:44:40                 15     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              34      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 08:29:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.21399459665849030406812607025500995048 percent] full

              35      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 08:09:40                 20     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.32921499214266192948893140202241049467 percent] full

              36      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 08:04:40                  5     Tablespace [NM_SETTLEMENT_DATA] is [91.20658958902207617901605961031147313371 percent] full

              37      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 07:59:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.318283043522820442743919103580213173 percent] full

              38      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 07:54:40                  5     Tablespace [NM_SETTLEMENT_DATA] is [91.19918458138566205396399315036793631693 percent] full

              39      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 07:34:40                 20     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.318283043522820442743919103580213173 percent] full

              40      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 07:19:40                 15     Tablespace [NM_SETTLEMENT_DATA] is [91.19918458138566205396399315036793631693 percent] full

              41      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 06:59:40                 20     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.30666784811423886307734353648537851872 percent] full

              42      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 06:49:40                 10     Tablespace [NM_SETTLEMENT_DATA] is [91.18344894015828203822835192298792058129 percent] full

              43      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 06:34:40                 15     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.30051862701557802678327411861164252528 percent] full

              44      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA       Warning     09/13/2018 06:29:40                  5     Tablespace [NM_SETTLEMENT_DATA] is [91.16030829129448789744064423566436802888 percent] full

              45      nmercnd.world          problemTbsp     Tablespace Space Used (%)     NM_SETTLEMENT_DATA         Clear     09/13/2018 06:24:40                  5     CLEARED - Tablespace [NM_SETTLEMENT_DATA] is [67.28412070408581579666575567094834654277 percent] full

               

               

               

               

              A couple of points of interest:

              1.) changing between 67% and 91% equals about 150 gb

              2.) It is very consistant in flipping between 67% and 91%, almost like something is stuck in a buffeer

              3.) To resolve this I added disk-space to the mount and extended the tablespace as well

                  - This just lowered the lkower level of the alarm from 75% to 67%, but it still flipped up to 91%

              • 4. Re: OEM Tablespace Space used (%) metrics constantly fluctuates 25%
                Ian Baugaard

                Hi,

                 

                Given the version of OEM you're on, it could very well be a bug.

                 

                Have a look at the following MOS notes and perhaps get confirmation from a SR from support

                 

                • EM 12c: False Tablespace Space Used (%) Metric Alert is Received from Enterprise Manager 12c Cloud Control Every Day after the Database Blackout Ends (Doc ID 2020997.1)
                • Collect Relevant Information for 'Tablespace Space Used (%)' Metric Problems in Enterprise Manager Cloud Control 12.1.0.4 (or higher) with db plugin 12.1.0.6 (or higher) (Doc ID 1932878.1)
                • Troubleshooting a "Database Tablespace Space Used (%)" Alert issue in 12c Cloud Control (Doc ID 1532334.1)

                 

                Hope this helps

                 

                Ian

                • 5. Re: OEM Tablespace Space used (%) metrics constantly fluctuates 25%
                  2759957

                  I checked those and they don't fit, so, I'll open an SR, thanks for your help.