6 Replies Latest reply on Feb 7, 2018 10:12 AM by danut.nastasa

    Oracle Database Application Monitor

    jdwinns

      Hello everyone,

       

      Quick question in regards to the Oracle Database application monitor for SAM, in particular the "used space in Tablespace (%)" component monitor within the Oracle Database template.

       

      The default query for that component monitor is posted below. I am trying to have it check ALL the tablespaces, not just 'SYSTEM'. So I removed the AND fs.tablespace_name = 'SYSTEM' portion of the query and assumed that would force it to look at all the tablespaces. What it seems to be doing now is only looking at the first (alphabetically) tablespace that comes up in that query.

       

      Has anyone else dealt with this or know of way to modify the following query to have it cycle/check all the tablespaces?

       

      SELECT Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used",

             df.tablespace_name "Tablespace"

        FROM dba_free_space fs,

             (SELECT tablespace_name,SUM(bytes) bytes

                FROM dba_data_files

               GROUP BY tablespace_name) df

      WHERE fs.tablespace_name   = df.tablespace_name

             AND fs.tablespace_name = 'SYSTEM'

      GROUP BY df.tablespace_name,df.bytes

        • Re: Oracle Database Application Monitor
          ralfs

          Hello,

           

          I'm facing the same "issue". The first problem here is that the used "Oracle User Experince Monitor" only allows one set of Statisic/Message so you may add as many of the "Used Space in Tablespace (%)" componets as needed and change the tablespace name in the query.

           

          The second problem is that you may have more the ten table spaces in your database so that you cannot use a simple script monitor to check all tablespaces you have. At the moment I'm thinking of a monitor that checks all tablesapces and sends some kind of html-table back that shows all "red", "yellow" and "green" tablespaces and regarding the overall status something like "critical" or "warning". The disadvantage is that you can't collect statisitcs over the tablespaces. The second approach might be an alert that checks the amount of tablespaces actual on the database, compares them with the monitred one and add/remove automatically the monitors but I'm not sure if that can be done with SWQL as you can do it on the GUI.

           

          Also a good idea is to vote for Oracle AppInsight Monitor: AppInsight for Oracle

           

          Kind regards

          Ralf

          • Re: Oracle Database Application Monitor
            orioncrack

            Yeaop. This was a bear to configure for a large client. I had to make like 300 of these CMs.

             

             

             

            OpsLogix was much simpler, and discovered all TS intelligently. I wish Solar was like this.

            • Re: Oracle Database Application Monitor
              danut.nastasa

              Add new component Oracle User Experience Monitor  with the following sql query:

               

              select

              count(df.tablespace_name) "Nr of Tablespaces",

              listagg(df.tablespace_name || ' - Total space: ' || df.TotalSpace ||'(MB); Used space: ' || tu.TotalUsedSpace || '(MB); Free Space: ' || (df.TotalSpace - tu.TotalUsedSpace)|| '(MB)' || ' ~ ' || (round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)))|| '%;', chr(10)) WITHIN GROUP (ORDER BY df.tablespace_name) "Free Space(MB and %)"

              from

              (select tablespace_name, sum(bytes)/1048576 TotalSpace

              from dba_data_files

              group by tablespace_name) df,

              (select tablespace_name, sum(bytes)/1048576 TotalUsedSpace

              from dba_segments

              group by tablespace_name) tu

              where df.tablespace_name = tu.tablespace_name

              and

              df.totalspace >= 10 -- if total table space is greater that 10 mb

              and (round(df.totalspace - tu.totalusedspace) <=5 -- if free space is less than 5 mb

              or

              --and

              round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) < 20) --if free space is less than 20%

               

               

              This way you can output all / some tablespaces and the statistics can be used for alerting. Enjoy!

                • Re: Oracle Database Application Monitor
                  orioncrack

                  This is awesome!

                   

                  Just wondering how you would handle the alerting for each individual one?

                    • Re: Oracle Database Application Monitor
                      danut.nastasa

                      My purpose was to alert on all tablespaces with issues, not really in a individual way. That can be done with the default sql script from Solarwinds, and you just replace the System with your tablespace name.

                      SELECT SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

                             df.tablespace_name "Tablespace"

                        FROM dba_free_space fs,

                             (SELECT tablespace_name,SUM(bytes) bytes

                                FROM dba_data_files

                               GROUP BY tablespace_name) df

                      WHERE fs.tablespace_name   = df.tablespace_name

                             AND fs.tablespace_name = 'SYSTEM' ---replace here

                      GROUP BY df.tablespace_name,df.bytes