Tag: monitoring

Monitoring Exadata database machine with Oracle Enterprise Manager 11g

~ rajeevramdas@aol.com ~ Leave a comment

Oracle Enterprise manager Grid control, is hands down the best monitoring and management tool, for the oracle exadata database machine. It comes with plugins to monitor all the hardware components of the database machine, and sensible, preset thresholds for proactive monitoring.

Update (Nov 2011) : Enterprise manager 12c is now available, and Certified to be used with exadata. The master MOS note 1110675.1 covers the installation and configuration details.

Some key points
  • You should use 11gR1 enterprise manager grid control for monitoring.
  • You should use 11gR1 enterprise manager agents, to monitor the targets on the database machine.
  • If you use enterprise wide monitoring tools like tivoli, openview or netcool, use snmp traps from oracle enterprise manager, to notify these monitoring tools (ie dont try to directly use snmp to monitor the exadata components. You could do this but it will be too time consuming).
  • You could potentially use 10.2.0.5 Oem, with 11g agents to monitor the dbmachine, but this is not recommended as a stable/long term solution.
  • The following components (And more) can be monitored using Enterprise Manager
    • Databases hosts
    • Exadata Cells
    • Cisco switch
    • KVM (Keyboard, Video, Mouse)
    • ILOM Monitoring
    • Infiniband switch
    • Power distribution unit (PDU)
You have 3 possible options to configure enterprise manager
  • If you have an existing 11gR1 enterprise manager grid control envrionment, you can patch it with the recommended patches and use that for monitoring the dbmachine targets.
  • You can setup and configure a brand new 11gR1 enterprise manager grid control environment (On a separate server) and configure it to monitor the dbmachine targets.
    • Download the required software
      • Weblogic server 10.3.2 (MOS Note 1106105.1, 1063112.1)
      • Jdk 64 bit (Mos Note 1063587.1)
      • 11gR1 Oms from download.oracle.com
    • Install Java and Web Logic Server (Wls)
      • MOS Note 1063762.1
    • Patch Web Logic Server
      • MOS Note 1072763.1
    • Install 11gR1 Enterprise manager Oracle Management Server (OMS)
      • Install/Create a 11gR2 database to serve as the Enterprise Manager Repository
      • Database pre-reqs for 11.1.0.1 repository (Mos Note 1064441.1)
      • Install/Configure Oms (Mos Notes 1130958.1, 1059516.1)
    • Patch OMS with the required patches to enable database machine monitoring
      • Mos Note 1323298.1
  • You can use an easy install option to setup and configure an enterprise manager environment and configure the plugins.
    • The easy install is delivered as a  patch 11852882 (EMGC setup automation kit)
    • The configuration worksheet has to be filled out properly (Before the installation) and the em.param file has to be generated.
    • Follow the instructions in the readme to do a quick install of a fully configured 11gR1 Enterprise manager installation.
    • This method helps you install/patch  and configure the full 11gR1 oms in just an few steps and is a huge time saver.
Download the required plugins to monitor the following components
Download the plugins from the enterprise manager extensions exchange
http://www.oracle.com/technetwork/database/exadata/index.html#plug-in (Exadata cell plugin)
http://www.oracle.com/technetwork/oem/grid-control/exadata-plug-in-bundle-188771.html (All the rest of the plugins)
Install and Configure the Agent and the Plugins
       Additional tutorials with screenshots on configuring the plugins can be found below
Sending SNMP traps to 3rd party monitoring tools.
  • Get the Mib (Management Information Base) file from your enterprise manager management server and send it to the 3rd party tool administrator (eg: openview or netcool). Follow MOS note 389585.1, to get this MIB file.
  • Then configure your notification methods and rules to send the required snmp traps to the 3rd party tool.

Grid control 10.2.0.5 New features – Part III

~ rajeevramdas@aol.com ~ Leave a comment

Real-time Sql monitoring is a really exciting new feature that was introduced in oracle database 11gR1. This feature allows you to monitor the performance of sql statements as they are executing. Sql monitoring is automatically started if a sql statement is executing in parallel, or if the sql statement has spend more than 5 seconds of CPU or I/O time during a single execution. Details for this feature can be found in the 11g Performance Tuning guide.

You can run the report from sqlplus (And spool the html output to an o/s file) as follows

sqlplus / as sysdba

set long 1000000000
set pages 0

spool sqlmon.html

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>’255xfn4xrq51a’,session_id=>170,session_serial=>7,
type=>’HTML’) as report
from dual;

spool off
exit;

A sample output can be seen here.

Grid control 10.2.0.5,exposes this functionality from the management console (Until now you could access this from 11g database control or using the API DBMS_SQLTUNE.REPORT_SQL_MONITOR). You can access this by navigating to Targets -> Databases -> Choose your 11g database -> Performance. You can then click on “SQL monitoring” to monitor your sql statements that are currently executing.

This screen gets refreshed every 10 seconds, and it shows the SQL Id, The total execution time up till now, Degree of parallelism in use, The database time, Total I/O count, Start Time (If the sql has completed, the End Time) and the actual sql statement.

You can click on Status to further drill down into the sql statement.

The overview section shows the Duration, Database Time, IO count and buffer gets done by the sql (And the screen refreshes every 10 seconds, showing you the progress.)

The details section has 3 different views . Plan statistics that shows you the execution plan , and time spend, in different steps in the execution plan.

The parallel view, gives you further drill down into each parallel server and shows the Db time, Wait Activity%, IO count and Buffer gets by each parallel server.

The activity view , gives you the color coded activity breakdown within the session CPU, and other wait events.

You can also get to the real-time sql monitoring screen by identifying a sqlid and drilling down into the sql id. Then there is a tab named “sql monitoring” , which gives you this same information.

Really neat, dont you think ?