Building an Oracle Management Pack for OpsMgr Cross-Platform Agents, Part 3

Continuing on with the development of a management pack for monitoring Oracle database servers on a UNIX/Linux platform (part 1, part 2), I will describe in this post some of the characteristics and methodology of the class structure and object discoveries. 

Class Structure

To start the class structure, I defined a few arbitrary abstract classes to function as the base classes for Oracle objects:  OracleSCX.OracleSubsystem, OracleSCX.OracleApplication, and OracleSCX.OracleComponent.  Subsequently, I created a set of classes to represent the Oracle objects and components that would be discovered.  This class hierarchy is best illustrated with this image:

As for the object relationships, the discovered objects are organized as follows:

• Oracle SCX Server
  • Oracle SCX Listeners      
    • Oracle SCX Listener
  • Oracle SCX Instance
    • Oracle SCX TableSpaces
      • Oracle SCX TableSpace
        • Oracle SCX Data File
    • Oracle SCX Processes
      • Oracle SCX Process
    • Oracle SCX Databases
      • Oracle SCX Database
    • Oracle SCX Alert Logs

A diagram view of a discovered Oracle system illustrates these relationships:

Secure References

To facilitate credential configuration for priviliged actions, two Secure References (RunAs Profiles) were created:  “Oracle Action Account Profile” and “Oracle SQL Query Account Profile.”   The Oracle Action Account profile is used in the custom Invoke Probe Action described in a previous post on the topic.   The reason for configuring a special profile for this probe action is to support the use of an account for actions such as invoking lsnrctl or reading tnsnames.ora that may differ from the standard agent RunAs profile account.   The SQL Query account credentials are passed to sqlplus by the SQL Query probe action. 

Object Discoveries

Regarding the discoveries, a few basic discovery methodologies are used.   The first discovery methodology that I will discuss combines a discovery scheduler, ExecuteShellCommand invoker probe action, and either a PowerShell Discovery probe action or Class Snapshot Data Mapper.  The second method that I will describe combines a discovery scheduler, SQL Query probe action, and a PowerShell Discovery probe action.  Additional (and more traditional) discoveries are also used combing  WSMan Enumerator probe actions with Class Snapshot Data Mapper modules.

Shell Command Discovery Example:  Discovering Oracle installations

The file oratab (located at /var/opt/oracle/oratab or /etc/oratab) itemizes a list of configured Oracle Instances.   So to discover Oracle installations, I wanted to create a data source that would execute a shell command string to enumerate the contents of this file, such as:  cat -s /var/opt/oracle/oratab |grep -v \# |grep :;cat -s /etc/oratab |grep -v \# |grep :;  This command string will return an output like:

cat: /var/opt/oracle/oratab: No such file or directory
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y

The StdOut from this command execution can then be filtered for a substring like:  “oracle/product” to indicate the presence of an installed Oracle Database server.    To facilitate a discovery using this methodology, I created two data sources, the first of which is just a generic shell command execution discovery data source (so that it can be reused), and the second of which follows the shell command execution discovery data source and adds a discovery mapper module.   The generic shell command discovery data source starts with a System.Discovery.Scheduler data source module.  

This module is then followed by the OracleSCX.ProbeAction.ShellCommand probe action:

and then a System.ExpressionFilter module to filter out errors:

The second data source starts with the generic shell command discovery data source as the initial data source module:

and then a System.Discovery.FilteredClassSnapshotDataMapper to map the discovery data:

SQL Query Discovery Example:  Discovering TableSpaces

A list of Oracle TableSpaces for an Instance and their size in MB can be queried with the SQL:   SELECT TABLESPACE_NAME, ROUND(SUM (BYTES/1048576)) FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;.  To make the results of this query a little easier to process in a script, I added some CONCAT operations to the query:  SELECT CONCAT(CONCAT(CONCAT(‘value:’,TABLESPACE_NAME),’:’),ROUND(SUM (BYTES/1048576))) AS COL001 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;.  This produces a result like:

value:UNDOTBS1:125
value:SYSAUX:700
value:USERS:5
value:SYSTEM:720

To implement discoveries based on SQL queries like this, I also used a multiple data source approach, with the first data source being a generic SQL query discovery data source, and additional data sources following this with custom filters and mapper or script probe modules. 

The generic  SQL discovery data source starts with a System.Discovery.Scheduler, followed by the OracleSCX.ProbeAction.SQLQuery module. 

This is followed by a System.ExpressionFilter module to filter out errors. 

The specific TableSpace discovery data source starts with the generic SQL discovery data source (note that the OracleSID and OracleHome parameters are supplied by discovered properties of the Oracle Instance object).

In the case of the SQL Query probe action, the actual SQL query is made through a shell command execution of sqlplus.  Because of this, an error in the exeuction of a SQL query will not affect the StdErr of the shell command execution, as sqlplus may have been called successfully.  Therefore, additional filtering must be done to filter out SQL command errors exposed in the contents of StdOut.

Because the SQLQuery returns a single string output (StdOut), a script probe action is required to parse the string into the separate discovery data instances.  As the Cross Platform agents are only supported on R2 anyway, there’s no reason not to use the preferable PowerShell modules instead of the standard WSH scripting modules.  In this example, the PowerShell Property Bag Probe module is configured like:

The actual script is:

param ([string]$sInstList,[string] $SourceID,[string]$ManagedEntityID,[string]$PrincipalName, [string]$TSContainerName,[string]$OracleInstance)

$api = New-Object -comObject ‘Mom.ScriptAPI’
$discoveryData = $api.CreateDiscoveryData(0, $SourceID, $ManagedEntityID)

if ($sInstList -ne $null){
    $sInstList = $sInstList.Replace([Environment]::newline,” “)
  [array]$arList = $sInstList.Split(” “)
  $arList | ForEach-Object{
    $sLine = $_
    if ($sLine.Contains(“value:”) -eq $true){
      $arTemp = $_.Split(“:”)
      if([int]$arTemp.Length -ge [int]1){
        $sName = $arTemp[1]
        $nSize = $arTemp[2]
    $oInst = $discoveryData.CreateClassInstance(“$MPElement[Name=’OracleSCX.TableSpace’]$”)
    $oInst.AddProperty(“$MPElement[Name=’MicrosoftUnixLibrary!Microsoft.Unix.Computer’]/PrincipalName$”, $PrincipalName)
    $oInst.AddProperty(“$MPElement[Name=’OracleSCX.TableSpaces’]/Name$”, $TSContainerName)
    $oInst.AddProperty(“$MPElement[Name=’OracleSCX.TableSpace’]/Name$”, $sName)      $oInst.AddProperty(“$MPElement[Name=’OracleSCX.TableSpace’]/SizeMB$”, $nSize)
    $oInst.AddProperty(“$MPElement[Name=’OracleSCX.OracleInstance’]/Name$”, $OracleInstance)
    $oInst.AddProperty(“$MPElement[Name=’System!System.Entity’]/DisplayName$”,$sName)
        $discoveryData.AddInstance($oInst)
      }else{
        $sInst = “”
        $sHome = “”
      }
    }
  }
  $discoveryData
}
Remove-variable api
Remove-variable discoveryData
Remove-variable arTemp
Remove-Variable arList
Remove-Variable oInst

 

These examples should illustrate two of the discovery methodologies used in this Oracle SCX management pack.