OEM: Obscure Tools for Tough Problems


Two aspects of OEM (Oracle Enterprise Manager) that I’m getting more acquainted with…

Even though OEM automates tasks and provides a GUI interface to make tasks easier, you still need scripting. You can use the OEM gui to fire off a SQL statement against 50 different databases. But what if you need to change the value OEM stores for the password for the database user with which OEM monitors all those instances? What if you want to change the monitoring threshold on a tablespace in 20 databases?

You need OEM’s command line scripting interface to pull off tasks like those. It’s called, gasp, emcli. You can get it configured quite quickly on your OEM management server. It can also be run from remote laptops, but that seems like a security risk to me. Interestingly, the emcli in OEM version seems to have a wider variety of features than does emcli in OEM. An example is a bunch of switches in the change password command that make it more secure.

Enterprise Manager Command Line Interface manual provides all the detailed documentation needed.

Diagnosing Problems: EM Diag Kit
Keeping OEM happy involves endless trips to Metalink to identify issues with various Oracle versions and configurations. Lately I’ve been trying to solve why the OEM agent stops and restarts itself on some hosts. My efforts with the folks at Metalink led today to a utility called the EMDiag Kit. This little dynamo scans the OEM repository to help the support folks figure out what’s going on with various OEM components. If you think a little EM diagnosis is needed on your OEM system, hop on to Metalink and check out article EMDiagkit Download and Master Index, Doc ID 421053.1.

The Elusive Oracle ILM Assistant

Leave a comment

A few years back, I began seeing Oracle web sites trumpeting the arrival of Oracle Information Lifecycle Management or ILM. ILM was a buzz word in computer trade magazines. The concept seemed to boil down to: you have tons and tons of data, here are some ways to classify and manage it all appropriately, and get rid of older stuff or move it out of the way so your system isn’t bogged down any longer.

Back in the days of Oracle 9i, Oracle ILM appeared to use existing features to enable the Oracle database to put older data on cheaper hard disks, even if the old data and the current data resided in the same table. That was accomplished by using partitions within tables. The web sites also mentioned something about an ILM Assistant, but I never spent enough time to be able to track it down.

Now Oracle 11g database is current and at last I’ve come face to face with the ILM Assistant. It’s still pretty hard to find.

Read a quick overview of ILM here. And this download web page is where you can get hold of the install sql scripts. Here’s a user guide. And Chapter 5 of the VLDB Guide has information as well.

How do you do the install?
1. You need to have Oracle APEX working on your target instance.
2. If you want to run the ILM Assistant with the demonstration data, you need to install the Example schema SH. The example schemas come on a separate download from the Oracle Database installer.
3. Then obtain the ILM Assistant installation zip files mentioned above. You simply follow the steps to run sqlplus to execute sql scripts. Same for the demo data if you want that.
4. Once that is in place, you can access ILM Assistant from a web browser.

I don’t have enough time to figure out just how much value ILM assistant adds to the DBA/IT Cost/Man Hour/Quality equation, but the tool does list out partitions and features, and also provides a methodology for creating an information lifecycle. There are dedicated tools from other vendors that just work on ILM. It’s hard to tell whether Oracle ILM offerings amount to much or provide only a thin veneer on partitioning. I may have an opportunity to find out as we grapple with a large database at work.