10.2. Monitoring Oracle Databases.

What should you configure in osagent.conf.xml to monitor your Oracle Databases ?

10.2.1. Checks for monitoring Oracle Databases

  • Checking running background processes and the listener with PROC

  • Checking open sockets on port xxx (listener) with SOCKETS

  • Checking the alert-log with LOGS

  • Checking your auto-extend tablespaces with ORAAX

  • Checking your non-auto-extend tablespaces with ORATS

  • Can we ping the tns listener ? Check with ORATNSPING

  • Are there enough "free" sessions (compared with max_processes)? Check with ORASESSIONS

  • Checking free space in data- and archive-log directories with DISK

  • Check for invalid objects (like views, packages,...) with ORAINVOBJ

10.2.2. Example configuration for monitoring Oracle Databases

			
<!-- ... -->
  <DISK>
    <FS>
      <!-- The oracle datafiles -->
      <FSNAME>^/usr/local/oracle/sid/daten$</FSNAME>
      <ERRORLEVEL>ERROR</ERRORLEVEL>
      <DESCRIPTION>This is the file system with oracle data files. 
        Maybe you want to re-size this file system or add a new one.</DESCRIPTION>
      <VALUE>98</VALUE>
      <FORMULA>PERCENT</FORMULA>
    </FS>
				
    <FS>
      <!-- The oracle archivelogs -->
      <FSNAME>^/usr/local/oracle/sid/archivelogs$</FSNAME>
      <ERRORLEVEL>ERROR</ERRORLEVEL>
      <DESCRIPTION>This is the file system with oracle archivelogs.
        If oracle cannot write new archivelogs to this file system, the 
        database will stop. To prevent this, resize the file system, archive some
        archivlogs to the backup tape or move they to some other location.
      </DESCRIPTION>
      <VALUE>70</VALUE>
      <FORMULA>PERCENT</FORMULA>
    </FS>
				
    <!-- More filesystems to check -->
  </DISK>

  <PROC>
    <PROCESS>
      <!-- Oracle Background processes (the names differ on some oracle versions
        -->
      <PROCNAME>ora_pmon_sid|ora_dbw0_sid|ora_lgwr_vas|ora_smon_sid</PROCNAME>
      <ERRORLEVEL>ERROR</ERRORLEVEL>
      <DESCRIPTION>Some/All oracle background processes are not running! 
        Please check this. You should take a look to alert_sid.log and to the
        trace files.</DESCRIPTION>
      <NUMBER>4</NUMBER><!-- 4 Background processes -->
    </PROCESS>

    <PROCESS>
      <!-- The oracle listener -->
      <PROCNAME>tnslsnr</PROCNAME>
      <ERRORLEVEL>ERROR</ERRORLEVEL>
      <DESCRIPTION>The listener is not running. Check the listener log and
       restart it.</DESCRIPTION>
    </PROCESS>

  </PROC>

  <LOGS>				
    <LOGFILE>
      <!-- Check the oracle alert logfile. -->
      <LOGFILENAME>/usr/local/oracle/sid/trace/back/alert_sid.log</LOGFILENAME>
      <LOGFILTER>
        <REGEX>\w\w\w \w\w\w .. \d\d:\d\d:\d\d \d\d\d\d</REGEX>
        <REGEX>alter tablespace .* (end|begin) backup</REGEX>
        <REGEX>alter database backup controlfile to</REGEX>
        <REGEX>Thread \d+ advanced to log sequence \d+</REGEX>
        <REGEX>Current log# \d+ seq# \d+ mem# \d+: </REGEX>
        <REGEX>ARC.: Beginning to archive log</REGEX>
        <REGEX>ARC.: Completed archiving log</REGEX>
        <REGEX>quence</REGEX>
        <REGEX>Creating archive destination LOG_ARCHIVE_DEST_</REGEX>
        <REGEX>alter tablespace .* coalesce</REGEX>
        <REGEX>Completed: alter tablespace .* coalesce</REGEX>
        <REGEX>\.\.\.</REGEX>
        <REGEX>alter tablespace .* coalesce</REGEX>
        <REGEX>expanded controlfile section</REGEX>
        <REGEX>requested to grow by.*record\(s\); added.*block\(s\) of records</REGEX>
        <REGEX>Created Undo Segment</REGEX>
        <REGEX>SMON offlining US=\d+</REGEX>
        <REGEX>Completed: </REGEX>
        <REGEX>alter tablespace .* add datafile</REGEX>
        <REGEX>Closing archive log file</REGEX>
      </LOGFILTER>
      <LOGFILTER>
        <REGEX>ORA-000060: Deadlock detected. More info in file</REGEX>
        <!-- That is AFTER all default priorities! -->
        <ERRORLEVEL>WARNING</ERRORLEVEL>
      </LOGFILTER>
      <LOGFILTER>
        <REGEX>.*</REGEX>
        <!-- That is AFTER all default priorities! -->
        <PRIORITY>1000</PRIORITY>
        <ERRORLEVEL>ERROR</ERRORLEVEL>
      </LOGFILTER>
    </LOGFILE>
  </LOGS>

  <ORATS>				
    <!-- Monitoring of non-autoextend tablespaces -->
    <TABLESPACE>
      <ORACLE_SID>sid</ORACLE_SID>
      <TABLESPACENAME>.*</TABLESPACENAME>
      <ERRORLEVEL>WARNING</ERRORLEVEL>
      <FORMULA>PERCENT</FORMULA>
      <VALUE>90</VALUE>
      <DESCRIPTION>This tablespace is getting full. Add a new datafile.
      </DESCRIPTION>
    </TABLESPACE>
    <TABLESPACE>
      <ORACLE_SID>sid</ORACLE_SID>
      <TABLESPACENAME>UNDO</TABLESPACENAME>
      <ERRORLEVEL>WARNING</ERRORLEVEL>
      <FORMULA>PERCENT</FORMULA>
      <VALUE>96</VALUE>
      <DESCRIPTION>This tablespace is getting full. Add a new datafile.
      </DESCRIPTION>
    </TABLESPACE>
    <TABLESPACE>
      <ORACLE_SID>sid</ORACLE_SID>
      <TABLESPACENAME>.*</TABLESPACENAME>
      <ERRORLEVEL>ERROR</ERRORLEVEL>
      <FORMULA>PERCENT</FORMULA>
      <VALUE>96</VALUE>
      <DESCRIPTION>This tablespace is getting full. Add a new datafile.
      </DESCRIPTION>
    </TABLESPACE>
  </ORATS>

  <ORAAX>
    <!-- Monitoring of autoextend tablespaces -->
    <TABLESPACE>
      <ORACLE_SID>SID2</ORACLE_SID>
      <TABLESPACENAME>.*</TABLESPACENAME>
      <ERRORLEVEL>WARNING</ERRORLEVEL>
      <FORMULA>PERCENT</FORMULA>
      <VALUE>70</VALUE>
      <DESCRIPTION>This tablespace is getting full. Add a new auto-extend 
        datafile with

        alter tablespace TS_XX add datafile
          '/usr/local/oracle/SID2/data1/TS_XX_28.dbf' 
          size 5M autoextend on next 100M maxsize 2000M;
				
      </DESCRIPTION>
  
    </TABLESPACE>
  </ORAAX>

  <!-- monitoring if the listener works correctly -->
  <ORATNSPING>
    <TNSNAME2PING>
      <TNSNAME>osmartdb1</TNSNAME>
      <ERRORLEVEL>ERROR</ERRORLEVEL>
    </TNSNAME2PING>
    <TNSNAME2PING>
      <TNSNAME>osmartdb2</TNSNAME>
      <ERRORLEVEL>WARNING</ERRORLEVEL>
    </TNSNAME2PING>
  </ORATNSPING>

  <!-- are there enough "free" sessions -->
  <ORASESSIONS>
    <DATABASE>
      <ORACLE_HOME>/usr/local/oracle/product/9.2.0</ORACLE_HOME>
      <ORACLE_SID>osmart</ORACLE_SID>
      <THRESHOLD>
        <PERCENT>70</PERCENT>
        <ERRORLEVEL>WARNING</ERRORLEVEL>
        <DESCRIPTION>if permits, increase max_sessions parameter</DESCRIPTION>
      </THRERSHOLD>
      <THRESHOLD>
        <PERCENT>90</PERCENT>
        <ERRORLEVEL>ERROR</PERCENT>
      </THRESHOLD>
    </DATABASE>
  </ORASESSIONS>
		
  <ORAINVOBJ>
    <DATABASE>
      <ORACLE_HOME>/usr/local/oracle/product/9.2.0</ORACLE_HOME>
      <ORACLE_SID>osmart</ORACLE_SID>
      <LOGFILTER>
        <REGEX>.*</REGEX>
        <ERRORLEVEL>WARNING</ERRORLEVEL>
      </LOGFILTER>
    </DATABASE>
  </ORAINVOBJ>

  <!-- ... -->