Monitoring Exadata database machine with Oracle Enterprise Manager 11g

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.

Creating a view only user in Enterprise Manager grid control

Sometimes you would want to give only database monitoring access to some grid control users. You dont want them to get all other administrative privileges, like shutdown database, create tables, alter tables, drop tables etc. You can create such administrators in enterprise manager grid control by following the steps below.

Whenever you want to monitor a database target, you need to be able to login as a user to that database. Sometimes you might be logging in as SYSTEM or some other user that has DBA privileges. So the first step we need to perform, is to create a user in the target database, that has only limited privileges.

sqlplus system@target

create user oem_view identified by xxx

default tablespace users temporary tablespace temp;

grant create session, oem_monitor to oem_view;

OEM_MONITOR is a role in the database, that has some specific privileges granted to it. If you do not want to grant all those privileges to this user, you can then query the data dictionary to see which privileges are granted to OEM_MONITOR and then decide which subset of that you want to grant to your user OEM_VIEW.

Once the user in the target database is created, you can use enterprise manager grid control to create the new grid control administrator.

Login to enterprise manager grid control as SYSMAN (Or any super administrator)

Setup -> Administrators -> Create

Remove the “Public” role that is listed in the right hand side table

Under Create Administrator: System Privileges select ‘VIEW ANY TARGET’.

Under Create Administrator: Targets, choose all the targets this new admin should be able to view

Click Apply.

Refernce : Metalink Note 377310.1

Login as this new administrator user you created and set oem_view as the username for the database target in preferred credentials.

Grid control 10.2.0.5 New features – Part III

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 ?

Grid control 10.2.0.5 new features – Part II

Two more new features from 10.2.0.5 enterprise manager, grid control.

Fine-grained, target privileges

When you create an administrator in Grid control, to whom you are granting operator privileges, you can now further control what activities they are able to perform.

You can choose from granting them

Blackout target

Manage target metrics

Configure target

Manage target alerts

Clone database from existing backup

Prior to grid control 10.2.0.5, your only two options, to clone a database using the provisioning pack, were to 1) do a hotclone from an existing database 2) use an existing backup from a previous clone.

Now in 10.2.0.5 grid control, to clone a database, you can  choose to use an existing database backup. You can choose to clone from a existing database, and in the Source Type screen, you can choose “An Existing database backup”.

Grid control 10.2.0.5 new features – Part I

There are numerous new features in grid control 10.2.0.5. In this post I will be exploring  2 of them.

Improvements in Monitoring Templates.

In 10.2.0.5 when you create and apply templates, you can control how the metrics on the target are being modified. You have two options

1) Template will completely replace all metric settings in the target

2) Template will only override metrics that are common to both the template and the target.

If you choose option 1, all the metrics on the target other than the one’s specified in the template will be removed, and the ones specified in the template will be updated, with the thresholds set in the template.

If you choose option 2, it will only modify the metrics that are specified in the template.

Improvements in Alert Handling

In 10.2.0.5,  when you are checking the details regarding an alert you get  1 new option. You get a button that lets you re-evaluate the metric. So if the condition that triggered the alert has cleared, then the alert can be cleared.

Also another improvement is that the “acknowledge” function is now present as a button (Previously it was a URL at the bottom of the page).

Upgrading Enterprise Manager to 10.2.0.5

Finally today, i upgraded my grid control installation to 10.2.0.5 (From 10.2.0.4). Instructions for installing 10.2.0.4 can be found in my previous blog post, Installing Grid Control 10.2.0.4

Amazing but true, the process was fairly straight forward. I mostly followed the instructions in the Readme and its close, more detailed cousin, Metalink Note 464674.1. So without further ado, below are the steps that i followed.

Download the grid control patch  10.2.0.5

Unzip gc_10205_part2of2.zip (Because the file  p3731593_10205_LINUX.zip you need is in that)

Take a good backup of  the oms, agent and oracle database.

All of the following actions were performed on the management server.

Analyze the SYSMAN schema

Login to the SYSMAN schema in the management repository

SQL> exec emd_maintenance.analyze_emd_schema(‘SYSMAN’)

Check for Invalid Objects

Login to the SYSMAN schema in the management repository

SQL> select object_name, object_type from all_objects where owner=’SYSMAN’ and status <> ‘VALID’;

Login to the SYS schema in the management repository

SQL> select object_name, object_type from all_objects where status<>’VALID’ and object_name like ‘DBMS%’;

Stop jobs running in Enterprise Manager

Login to the SYSMAN schema in the management repository

SQL> execute emd_maintenance.remove_em_dbms_jobs;

Setup Database Initialization Parameters

Login to the SYS schema in the management repository

alter system set sga_max_size=1024M scope=spfile
alter system set sga_target=1024M scope=spfile;
alter system set job_queue_processes=0 scope=spfile;
alter system set shared_pool_size=512M scope=spfile;

Shutdown and restart the database.

Ensure that the database listener is up and running.

I had left the iasconsole (emctl start iasconsole) and oracle management server running (opmnctl startall)

Make sure that the following files under the Oracle home of OMS have write permissions before staring the Upgrade process.
<ORACLE_HOME>/j2ee/OC4J_EM/applications/em/em/online_help/paf_cs/paf_cs_he lp.jar
<ORACLE_HOME>/j2ee/OC4J_EM/applications/em/em/online_help/paf/paf_help.jar

If you do not remember the password for your iasconsole (It is supposed to be the same as the password you specified for SYSMAN during the original install), this will be a good time to follow metalink note 396344.1 and change this password (Because the patch installer will prompt you for this password, and will not proceed unless you provide the correct password).

Apply patch 10.2.0.5 to the OMS

cd to your software staging directory

cd /u01/oem
unzip gc_10205_part2of2.zip
unzip p3731593_10205_LINUX.zip
cd 3731593
./runInstaller

When it prompts you,  run root.sh from the oms home.

Apply patch 10.2.0.5 to the Agent

cd /u01/oem/3731593
./runInstaller

Completing the Upgrade

Login as the schema SYS to the management repository

alter system set sga_max_size=500M scope=spfile;
alter system set sga_target=500M scope=spfile;
alter system set job_queue_processes=10 scope=spfile;
alter system set shared_pool_size=0 scope=spfile;

Restart the jobs scheduled in Enterprise Manager

Login as the schema SYSMAN to the management repository

SQL> EXEC emd_maintenance.submit_em_dbms_jobs;

Stop and restart the agent, management server and management repository database.

Login to enterprise manager and enjoy using 10.2.0.5

ORA-00845 Memory_Target Not supported on this system

I was working on testing some 11g streams configurations today. I needed to startup 3 databases instances on the same server. I was using AMM (Automatic memory management). When i was trying to startup the 3rd database, i kept getting the error message “ORA-00845: MEMORY_TARGET not supported on this system”. I also had error messages in the alert log.

This is because, the space allocated for /dev/shm is not sufficient to allocate the SGA+PGA’s for all the 3 database instances (When using the initialization parameter memory_target). The space allocated needs to be >= the total SGA+PGA size of all the 3 instances together.

You can increase the space allocated using the command  “mount -t tmpfs shmfs -o size=2000m /dev/shm” ( I had 3 instances 600mb each SGA+PGA). You can persist this allocation across reboots by adding it to the /etc/fstab.

Installing Grid control 10.2.0.4 with oracle database 10.2.0.4 for the repository

Below is what i think is the quickest (And possibly the only supported way to do this directly (instead of installing 10.2.0.2, finishing the grid control install and upgrading to 10.2.0.4))set of steps to install grid control 10.2.0.4, while using oracle database version 10.2.0.4 for the grid control management repository on Linux x86.

Typically you would install grid control 10.2.0.1, which installs a 10.1.0.4 database as the management repository, then you would upgrade enterprise manager to 10.2.0.4 and then eventually upgrade the database to 10.2.0.4. Apart from the numerous steps in this method you also take more time because the entreprise manager database is fully configured when you install 10.2.0.1 and then it takes even more time updating all this configuration when you install the 10.2.0.4 patch. In the method outlined below all the configuration is done when you run the configuration script in the end, saving you precious time.

  • Install Oracle RDBMS 10.2.0.1
  • Upgrade Oracle RDBMS to 10.2.0.4
  • Create a listener
  • Create a Database
    • When you create/configure the database make sure that, you DO NOT choose the option to enable database control for the instance. (If you choose to enable database control, you will have to de-configure it).
  • Configure the Initialization parameters
  • Install Oracle Grid Control 10.2.0.1, software only.
    • Install Oracle Grid Control 10.2.0.1, software only, Subsection titled ” Enterprise Manager Grid Control Using an Existing Database“.
    • Here be extremely careful when you are configuring the em_using_existing_db.rsp file. When you are setting the value for the parameter s_reposPort=”1521″, remember to use the double quotes around the 1521 or else you will have issues with the installation.
  • Install Oracle Grid Control 10.2.0.4, software only.
    • In the section of the documentation you were following above, follow instructions in “Step 7 Apply the 10.2.0.4 patch set to OMS
  • Apply the 10.2.0.4 patch set to the Agent on the management server
    • Follow instructions in “Step 9, “Apply the 10.2.0.4 patch set to Agent
  • Run the configuration scripts to configure grid control to use the already created 10.2.0.4 database.
    • Follow instructions in “Step 10, 11 Configure Enterprise Manager Grid control by running the ConfigureGc.pl….

Instructions on how to upgrade this installation to Enterprise Manager 10.2.0.5 can be found in my followup post , Upgrading Enterprise Manager to 10.2.0.5.

Some Useful Metalink Notes for Grid control Installation and Upgrades

Documentation Reference for 10.2.0.5 Install and Upgrades

Installing Enterprise Manager 10.2.0.4 using an 11g Database for the repository

Steps to upgrade 10.2.0.2 or higher Repository to 11g

Install grid control 10.2.0.5 on enterprise linux 4

Install grid control 10.2.0.5 on enterprise linux 5

You can find samples of the rsp files below

em_using_existing_db.rsp

patchsetoms.rsp

Grid control agent secure

I recently had some trouble installing and configuring 10.2.0.4 grid control. I was installing grid control 10.2.0.4 using a pre-existing 10.2.0.4 database. I somehow messed up something in the automated response files and for the life of me i could not figure out the password to use for securing the agent (It was definitely not using the passwords i had thought, i used in the response file).

The security best practice is to leave the management server secured. The procedure below is just adhoc, so that you can unsecure and continue working and later reset your password.

So i had to do the following to unsecure the management server.

First figure out if the OMS is secured.

cd <OMS_ORACLE_HOME>/bin
./emctl status oms -secure

If the output of the command shows HTTPS, then you know that the oms is secured .

In order to fix the agent

First stop the agent

On the Oracle Management server

cd <OMS_ORACLE_HOME>/opmn/bin
./opmnctl stopall
cd
./emctl secure unlock
cd <OMS_ORACLE_HOME>/opmn/bin
./opmnctl startall

Once this is done you can unsecure the agent

cd <AGENT_HOME>/bin
./emctl stop agent
./emctl unsecure agent
./emctl start agent

For further information (and to understand how to secure the OMS) please refer the following metalink notes.

How To Secure / Unsecure The Grid Control Components (Agent / OMS) In 10g
Problem: Agent Upload Fails: OMS VERSION NOT CHECKED YET