Oracle Database Application Monitor

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:

Kind regards

Ralf

Just voted.

Also, if I come across any sort of temporary work around I'll post it here.

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.

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!

This is awesome!

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

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

Hellow, Danut.

Please, how could you make it alert you if some tablespace is above the threshold?

I'm asking, because the "Oracle User Experience Monitor" component only allows the return of one numerical value.

On you query, if more than one TableSpace is above the limit, it will also be shown.

How did you use this query on Orion, please?

Thanks a lot for sharing it.

Hi,

I created a Powershell script which can be used for a component. I creates a table with the table spaces and highlights them "yellow" or "red" if they are having more then 80% or 90% full. If you need other trigger values you need to edit the script.

Output:

What you need also:

• The credentials in SAM
• Privileges in Oracle DB:
  • SYS privileges: CREATE SESSION, SET CONTAINER
  • Table privileges: SYS.DBA_DATA_FILES, SYS.DBA_FREE_SPACE
• A Custom Property called "ORA_DB_SERVICENAME"  for the service name or you change $datasource in the script. I use a service name because I need it for other components and monitors for an Oracle DB instance.
• You should change the path to the Oracle .Net library (could be find here:  Oracle .NET Software Downloads )

In the script you find in the select stattements "( + )" you should remove the spaces. Without the code will be changed to

Add-Type -Path "D:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

Function SecureStringToString($value)

{

    [System.IntPtr] $bstr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($value);

    try

    {

        [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($bstr);

    }

    finally

    {

        [System.Runtime.InteropServices.Marshal]::FreeBSTR($bstr);

    }

};

$credentials=Get-Credential -credential ${CREDENTIAL}

#Write-Host $credentials

[string] $username = $credentials.Username

[string] $password = SecureStringToString $credentials.Password

if ( "${Application.Custom.ORA_DB_SERVICENAME}x" -eq "x" ) {

   write-host "Message: Error! No ""ORA_DB_SERVICENAME"" set!"

   write-host "Statistic: 0"

   exit 1

}

$datasource = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${Node.Caption})(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=${Application.Custom.ORA_DB_SERVICENAME})))"

$query = "SELECT SUM(fs.bytes) / (1024 * 1024) ""Free"",

       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 not like 'UNDO%'

GROUP BY df.tablespace_name,df.bytes order by df.tablespace_name"

$query_free_percent = "SELECT Nvl(100 - (Round(SUM(fs.bytes) * 100 / df.bytes)),1) ""Used(%)"", 

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

                               Round((df.bytes - SUM(fs.bytes))/ (1024 * 1024),2) ""Used (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 not like 'UNDO%'

                        GROUP BY df.tablespace_name,df.bytes Order by df.tablespace_name"

Write-Host "<STYLE TYPE=""text/css"">

<!--

.main .main body, .main h1, .main h2, .main p

{

font-family:'verdana', 'sans-serif';

}

    /*For own grid rendering*/

    .activeAlerts-rowInfo {

        color: #333333;

    }

    .activeAlerts-rowWarning {

        color: #FCA01D;

    }

    .activeAlerts-rowError {

        color: #da3838;

    }

.perf, .perf TD, .perf TH

{

text-align:left;

vertical-align:top;

padding-right:1cm;

font-family:'verdana', 'sans-serif';

font-size:14px;

}

.ora_db, .ora_db TABLE, .ora_db TR, .ora_db TD, .ora_db TH

{

border:0px;

padding-right:25px;

padding-left:25px;

border-collapse:collapse;

font-family:'verdana', 'sans-serif';

font-size:14px;

}

-->

</STYLE>"

$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource

try {

    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)

    $connection.open()

    #$connection | Get-Member

    #$connection | Select-Object -Property *

} catch {

        try {

            Start-Sleep -s 30

            $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)

            $connection.open()

            } catch {

                $my_error = $_.Exception.Message

                Write-Host "Message: Can't open connection: "  $my_error

                Write-Host "Statistic: 0"

                exit 1

            }

}

if ($connection.State -eq 'Open') {

    $errorLevel = 0

    $command=$connection.CreateCommand()

    $command.CommandText=$query_free_percent

    Write-Host -NoNewline "Message: <B>Tablespaces</B>"

    Write-Host -NoNewline "<TABLE class=""ora_db"">"

    Write-Host -NoNewline "<tr class=""ora_db""><th class=""ora_db"">Tablespace Name</th><th>Used (%)</th><th>Free (MB)</th><th>Used (MB)</th></tr>"

    $reader=$command.ExecuteReader()

        while ($reader.Read()) {

            if ($reader["Used(%)"] -ge 92 -and $reader["Used(%)"]  -le 94) {

                if ($errorLevel -lt 2 ) {

                    $errorLevel = 1

                }

                Write-Host -NoNewline "<tr class=""ora_db activeAlerts-rowWarning"">"

            } elseif ($reader["Used(%)"] -ge 95) {

                $errorLevel = 2

                Write-Host -NoNewline "<tr class=""ora_db activeAlerts-rowError"">"

            } else {

                Write-Host -NoNewline "<tr class=""ora_db activeAlerts-rowInfo"">"

            }

            Write-Host -NoNewline "<td>" $reader["Tablespace"] "</td><td>" $reader["Used(%)"] "</td><td>" $reader["Free (MB)"] "</td><td>" $reader["Used (MB)"] "</td></tr>"

        }

    Write-Host "</TABLE>"

    $connection.close()

    if ($errorLevel -eq 0) {

        Write-Host "Statistic: 1"

        exit 0

    } elseif ($errorLevel -eq 1) {

        Write-Host "Statistic: 0"

        exit 2

    } elseif ($errorLevel -eq 2) {

        Write-Host "Statistic: 0"

        exit 3

    }

    exit 1  

}

Hi Ralfs.

Thank you so much for replying.

As far as I understood, this script really monitors how much a tablespace is really used.

However, for the statistic perspective, it only returns 0 or 1, to indicate "Up" and "Down", and, then, based on the Up/Down, you come to the Application/Component and check which one is showing the high utilization based on the message/table created.

If I understood well, it attends the need of alerting/monitoring, but it does not provide a history, as the statistic is always 0 or 1, so you can't have a tablespace growth history.

Is this correct?

Again, thanks a lot for responding and sharing your code.

Hi,

yes you are right. The reason for that is quite simple: you have only 10 parameter you can measure in a component for the eleventh you need a new component. That's results in to two or three solutions for the problem to create a monitor for the Tablespaces:

1. You create one script for all Tablespaces.
   • Advantage: One component to monitor all Tablespaces of an Oracle DB so you can create a monitoring template to easily add DB's and you do not have to maintain it later
   • Disadvantage: No (easy) history. Yes you have the data as HTML table in the DB and you can get them via WSQL/SQL but you need to do a lot of scripting to get the information out of it.
2. One component for each Tablespace
   • Advantage: You have all data in a key/value format and can do all thinks you like with data in an easy way (alerts, reports custom dashboards)
   • Disadvantage: if something changes (add/remove/change of a Tablespace) you need to change you component/monitoring template by hand which means the workflow for that change takes longer and is much more complicate. Normally is the result that after a while new Tablespaces won't be monitored.
3. One component for each Tablespace together with a Tablespace alert script

What does that mean: You can create an alert which checks the running Tablespace components against the Tablespaces configured in the DB. If there is a gap you can run a Powershell Script automatically from the alert which creates/deletes/changes a Tablespace component to be aligned with the actual DB configuration. Together with the SWISS Powershell module it is possible to do this.

• • Advantage: Best of both worlds: The advantage of solution one and two.
  • Disadvantage: You need to have a careful look on what is possible and what is not possible with SWISS. You have also the problem that your alerting is much more complicate. If a Tablespace component goes "red" you need to check if it is a real problem or just because it is deleted or changed and then create a ticket or send an email.

I take solution one because we do not need the Tablespace history. For us it is enough to have that history over the storage monitoring.

But at the end: As long as Solarwinds do not provide AppInsight for Oracle DB's or opens the API's and workflows they are using for the AppInsight monitoring to everyone, all we do on Oracle DB monitoring will not be perfect. So if do not have it done yet: Vote for AppiIsight for Oracle DB ( ) !

Have a nice weekend