10 Replies Latest reply: Nov 12, 2010 7:30 AM by fjfranken RSS

    Tablespace Alerts Not Generated

    MattR
      Hi,

      In our GC setup we have several databases reporting via the "tablespace space used % alerts" metric.
      We have recently noticed that some databases won't trigger alerts for certain tablespaces.

      And no... this is not because autoextend = yes.

      I have created a test tablespace and filled it to both alert and critical levels and it will trigger an alert.
      However, for some pre-existing tablespaces I cannot get an alert to be generated even though they are well over the thresholds.

      Any ideas?

      Cheers,
      Matt
        • 1. Re: Tablespace Alerts Not Generated
          Rajesh Lathwal
          MattR wrote:
          Hi,

          In our GC setup we have several databases reporting via the "tablespace space used % alerts" metric.
          We have recently noticed that some databases won't trigger alerts for certain tablespaces.

          And no... this is not because autoextend = yes.

          I have created a test tablespace and filled it to both alert and critical levels and it will trigger an alert.
          However, for some pre-existing tablespaces I cannot get an alert to be generated even though they are well over the thresholds.
          Hi Matt,

          Are you getting any metric collection error for those databases ?

          Also check out alert on GRID console > alerts check if you have a entry over there ..

          Also tell me what is OMS/Agent and target database version.

          Regards
          Rajesh
          • 2. Re: Tablespace Alerts Not Generated
            dba05
            Hi,

            We faced an issue in 10g DBs were these alerts were no triggred.
            TS with size>320G alerts were nt generated.

            BUG#5245039 - Tablespace space used % alert not triggered when used space is > 320gb

            Please check whtr ur hitting same bug.
            • 3. Re: Tablespace Alerts Not Generated
              Rob Zoeteweij
              Check MOS Note Bug 5245039 - Tablespace space used % alert not triggered when used space is > 320gb [ID 5245039.8]

              If this refelcts your situation, the best thing to do would be to installed the suggested Patch 10.2.0.4 or 11.1.06

              You might want to create a User defined metric to monitor you tablespaces in the meantime.

              Regards
              Rob
              http://oegc.wordpress.com
              • 4. Re: Tablespace Alerts Not Generated
                MattR
                Hi,

                Thanks for the replies.
                I think you are correct in that we are hitting bug 5245039.
                It may also be bug 8898153 - from point 4 in following note [ID 403264.1]

                No test system for this db (prd only) :S so will have to do a test restore and try patching it there.

                Cheers,
                Matt
                • 5. Re: Tablespace Alerts Not Generated
                  Rob Zoeteweij
                  Think about creating an UDM in the meantime, so you will not get supprised with any tablespace space related issues.
                  regards
                  Rob
                  • 6. Re: Tablespace Alerts Not Generated
                    MattR
                    Hi Rob,

                    Yeah, I tried creating a UDM to no avail. Its because the database bug is actually preventing an alert being raised in the db (and thus reported in grid).
                    See below.

                    Cheers,
                    Matt

                    SQL>
                    SQL> -- QUERY 1: The following query shows the outstanding alerts that the RDBMS is aware of:
                    SELECT REASON, METRIC_VALUE, MESSAGE_TYPE, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS'), HOST_ID
                    FROM SYS.DBA_OUTSTANDING_ALERTS;SQL> 2

                    no rows selected

                    SQL> -- QUERY 2: The following query shows the current thresholds settings for the RDBMS tablespace full metric:
                    SELECT METRICS_NAME, WARNING_OPERATOR WARN_OP, WARNING_VALUE WARN_VAL, CRITICAL_OPERATOR CRIT_OP
                    , CRITICAL_VALUE CRIT_VAL, OBJECT_TYPE OBJ_TYPE, OBJECT_NAME OBJ_NAME, STATUS
                    FROM SYS.DBA_THRESHOLDS
                    WHERE metrics_name LIKE '%Tablespace%';SQL> 2 3 4

                    METRICS_NAME WARN_OP
                    ---------------------------------------------------------------- ------------
                    WARN_VAL
                    --------------------------------------------------------------------------------
                    CRIT_OP
                    ------------
                    CRIT_VAL
                    --------------------------------------------------------------------------------
                    OBJ_TYPE
                    ----------------------------------------------------------------
                    OBJ_NAME
                    --------------------------------------------------------------------------------
                    STATUS
                    -------
                    Tablespace Bytes Space Usage DO NOT CHECK
                    0
                    DO_NOT_CHECK
                    0
                    TABLESPACE

                    VALID

                    Tablespace Space Usage GE
                    85
                    GE
                    97
                    TABLESPACE
                    MY_TABLESPACE
                    VALID

                    Tablespace Space Usage GE
                    98
                    GE
                    99
                    TABLESPACE
                    UNDOTBS1
                    VALID


                    SQL>
                    SQL> -- QUERY 3: The following query shows the current values for the Tablespace Used(%) metric from the perspective of the RDBMS:
                    SELECT TABLESPACE_NAME TBSP_NAME, USED_SPACE, TABLESPACE_SIZE TBSP_SIZE, USED_PERCENT
                    FROM SYS.DBA_TABLESPACE_USAGE_METRICS;
                    SQL> 2
                    TBSP_NAME USED_SPACE TBSP_SIZE USED_PERCENT
                    ------------------------------ ---------- ---------- ------------
                    MY_TABLESPACE 95319512 97648640 97.6147871
                    UNDOTBS1 441096 512000 86.1515625
                    • 7. Re: Tablespace Alerts Not Generated
                      Rob Zoeteweij
                      Something like:
                      SELECT d.tablespace_name,
                      round(((a.bytes - NVL(f.bytes,0))*100/a.maxbytes),2) used_pct
                      FROM sys.dba_tablespaces d,
                      (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes
                      from sys.dba_data_files group by tablespace_name) a,
                      (select tablespace_name, sum(bytes) bytes
                      from sys.dba_free_space group by tablespace_name) f
                      WHERE d.tablespace_name = a.tablespace_name(+)
                      AND d.tablespace_name = f.tablespace_name(+)
                      AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
                      AND (d.tablespace_name NOT LIKE 'UNDO%' AND d.tablespace_name NOT IN ('USERS','TOOLS'))

                      Comparison Operator     >
                      Warning     80
                      Critical     90

                      UDM_TSPctUsed_Others: Tablespace %Key%, PctUsed = %value%
                      • 8. Re: Tablespace Alerts Not Generated
                        bgalbraith
                        MattR -

                        Question for you... In those tablespaces that you are not getting the alert on... a) do they have more than 1 datafile? b) is autoextend off? c) if you were to login to your Grid Control Console, click on the DB instance --> server --> tablespaces and select the tablespace in question - is the used MB for each datafile displayed as a negative number (or an incorrect number)?

                        I am running into an issue with UsedMB display when you select a tablespace and then look at each individual datafile. I am wondering if you are having the same issue.

                        We are running EMGC 11g - this issue is affecting our tablespace alert generation also.

                        Thanks,
                        Brenna
                        • 9. Re: Tablespace Alerts Not Generated
                          MattR
                          Hi Brenna,

                          a) do they have more than 1 datafile?

                          Yes. But is an issue tablespaces with one datafile and for tablespaces with more than one datafile.

                          b) is autoextend off?

                          Yes. But again, still an issue regardless.

                          c) if you were to login to your Grid Control Console, click on the DB instance --> server --> tablespaces and select the tablespace in question - is the used MB for each datafile displayed as a negative number (or an incorrect number)?

                          No - displayed correctly. Verified by logging into database and querying directly


                          We are running EM on 10g
                          • 10. Re: Tablespace Alerts Not Generated
                            fjfranken
                            bgalbraith wrote:
                            MattR -

                            Question for you... is the used MB for each datafile displayed as a negative number (or an incorrect number)?
                            We do have the same issue with - as far as I can see now - 1 tablespace in 1 database using GridControl11g
                            I already installed PSU 11.1.0.1.1 for GridControl (p10065631) but that did not solve this "problem"

                            Do you have had any luck in this already ?


                            Thanks
                            FJFranken