In case you are attending Oracle Open World 2008, the biggest Oracle conference in the world, and interested in either (or both) MySQL or Oracle Enterprise Manager Extensibility — I posted a proposal for a new presentation:

Extending Oracle Enterprise Manager by Example — Creating MySQL Management Plug-In

I’ve started looking into Oracle extensibility several years ago and since then I’ve seen lots of improvements in Extensibility Guide and many new plug-ins have seen the light of the day. However, creating a new plug-in is still considered to be something special and not available to mere mortals.
In this presentation we will see how easy it is to create a new plug-in. What are the steps and the method to follow. As an example we will work with MySQL Management Plug-in that I have recently released to public.
This session includes a live demo.

If you are interested, you may as well vote for it. If there are enough interested people, Oracle might select it for the conference.

If neither this nor my previous submission make it, well, I’ll go there anyway to have some fun and meet good old and, hopefully, new friends.

It’s been a while since the MySQL Management Plug-in 0.42 was released. Since then, I quietly updated it to version 1.0. The changes were very few; the biggest news was that the plug-in was certified by Oracle and added to OTN Oracle 10g Grid Control Extensions Exchange (see at the bottom).

I think the next version is due, as a few people have come back to me with some issues. The biggest was compatibility with Windows. Since I used the command line MySQL client, *nix and Windows shell incompatibilities were a major headache to solve, and I still couldn’t make it work reliably. I wanted to use DBI and DBD:MySQL, but it required installing and compiling Perl packages, which makes the deployment process very inconvenient.

Finally, I found a solution — Net::MySQL is a native Perl implementation of the MySQL client. I had to fix some bugs and add a few improvements to it, and I hope to get the author to re-introduce them back to the new CPAN distribution. Net::MySQL is dependent on IO::Socket, which is a core module that comes with the standard Perl distributed with the Oracle Management Agent.

Version 1.1 turned out to be a major rewrite for the Perl collection scripts and the net result is that compatibility across platforms is greatly improved. I have successfully tested the new version on Linux and Windows Agent hosts.

So what’s new in version 1.1 compared to 0.42?

• certified by Oracle; see OTN Extensions Exchange
• no MySQL client is required on Agent hosts. The Perl Net::MySQL package is distributed with the plug-in
• fully compatible with Windows
• MySQL client path property removed
• added support for local connection using Unix sockets
• added connection error message when target is down — can be seen in Availability History
• commands statistics skips collection for never-executed commands so less data is collected; thus, I could safely increased default collection frequency; command names are formated better
• changed default collection frequency for network, joins and sort statistics
• % command executions are collected right now — the “Questions” statistic didn’t match the total of all Com_ statistics.
• metric “Processes by Action” now excludes the plug-in’s own connection which was always adding one to “Query”
• a few minor typos fixed

Downloads, requirements, and installation instructions — as well as the datasheet — are available at the MySQL Plug-in for Oracle Grid Control home page.

Hello everyone,

Reading PlanetMySQL today, I discovered that Alex Gorbachev’s announcement that he has released the first public beta of his Oracle Grid Control plugin for MySQL was not aggregated! This is probably because Alex is primarily working on our Oracle space and so his feed isn’t on planet.

This plugin has been under development since 2006 and this is a major achievement.

Knowing that my feed is aggregated, and not willing to let this news and this amazing work go unnoticed by the MySQL community during the conference (I am at MySQLConf listening to Amazon.com’s CTO speak right now!)

In any event, if you missed them inline up there, here’s a link to Alex’s announcement with some impressive screenshots, and here’s a link to the product’s home page.

And check out the very positive comments from the first testers already on the announcement post.

Congratulations and thanks, Alex!

It has finally happened! The first public release of the MySQL plug-in for Oracle Enterprise Manager 10g Grid Control is out.

It’s been a while since I first started to work on this, first as part of the Grid Control Extensibility article that I wrote for IOUG SELECT Magazine in 2006 (thanks to John Kanagaraj for encouraging me to write it), and then later as part of a demo for my presentations. I was already working at Pythian and was considering further developing my example and releasing it.

In the last few months, more and more people showed interest in the potential plug-in, and even few guys from Oracle contacted me independently of each other with their own reasons to have a MySQL plug-in available. This interest accelerated the fermentation of thoughts in my brain, and I got down to work. Pythian generously sponsored my development time.

I’ve called this first release beta but as I didn’t do much testing, it should probably be called alpha. Having said that, it has been tested with MySQL 5.1 and 5.0, and it should also work with MySQL 4.1. I develop and use it with Oracle Management Agents running on the Linux platform, but I changed all the collection scripts to use the Perl that comes with the Oracle agent so it should run on Windows as well. It works quite stably for me, and I have verified it in several real-life environments.

I would probably take some more time before releasing it, but I really wanted to have the plug-in out before I leave for COLLABORATE 08 (should I also tell you the readiness level of my presentation?). I hope to get some feedback and first impressions from DBAs who try the plug-in. Bear in mind that this is the very first release — expect some rough edges. Please do report them here. We will probably set up a more structured set of pages, but for now leaving comments here is the way to go.

Here are the details. The first release version is 0.42 because that’s obviously the perfect number to start something good.

What’s implemented so far

(more…)

Disclaimer: In most countries, looking at user passwords is illegal. Never try what is written below on a system that somebody other than you can access.

Oracle Grid Control documentation warns against leaving the emkey in the Grid Control repository. It says here:

After the emkey has been copied, you must remove it from the Management Repository as it is not considered secure. If it is not removed, data such as database passwords, server passwords and other sensitive information can be easily decrypted.

You may wonder: how easily?

A Bit of Background

When you deal with management tools, you want to collect metrics and run various tasks on different targets. Unless you evolve in an very advanced security infrastructure such as a PKI or another “real” Single Sign-On solution, it’s likely that you’ll need a username/password to connect to a remote server, a remote database, or a remote application server. Because Grid Control enables you to automate a great number of tasks, it has to be able to connect without prompting the users for credentials. In order to do that, it has to know the real passwords because it will itself authenticate on the targets. Because there no magic in there, it has to store these informations in its repository!

As a consequence, if the password you type to connect to the Grid Control is stored in a HASH form only, the one you store in Grid Control to run a task, collect data, or simply avoid typing it when you drill down to a target, has to be reversible. But I guess it’s the same for all management solution.

Let’s Be More Specific

Oracle Management Service 10.2 uses several ways to protect these sensitive data, including Virtual Private Database and Password Encryption.

• To overcome the first one, you have to be able to connect to the database as the SYS user.
• To overcome the second one, you have to know the encrypted password form, the key, and the associated algorithm.

Obviously, the key used to cipher the password is the emkey. It is located in $OMS_HOME/sysman/config/emkey.ora by default, and it can be generated/configured with "emctl xxx emkey". So the next question is, “Where are stored the ciphered passwords?”.

(more…)

I’m a Linux fan, and when it comes to specific problems, I’m afraid not all operating systems are equally armed. Enabling a specific user to listen on a port below 1024 is one of these problems that was solved for years with various approaches:

• The Windows approach: we just don’t care!
• The Solaris 10 approach: we have an advanced privilege (net_privaddr)
• The Linux approach*: you’ll find a way to make it work anyway (man setuid)

So you may think, obviously you can access the GridControl 10.2 agent on Linux with HTTPS only, on port 443! And obviously you can — but:

1. The agent cannot listen on port 443 on Linux because it uses the OC4J HTTP listener and cannot run under port 1024. I didn’t try to install the agent as root and you may know something I don’t.
2. You cannot, with 10.2, set up an HTTP front-end, like Apache, for your agent. That’s because the agent sends its listening port to the Oracle Management Server (OMS) so that the OMS can interact with it.
3. Here is a section of EM 10.2.0.4 documentation, you may want to consider: “The final step in that configuration process is to restrict uploads from the Management Agents to secure channels only. Before completing that step, configure your firewalls to allow both HTTP and HTTPS traffic between the Management Agent and Management Repository and test to be sure that you can log in to Enterprise Manager . . . ”
4. There is another interesting section of the documentation. It explains that the OMS directly accesses the target database with the Oracle*Net Protocol.

Regarding the last point, you can open the firewall to Oracle*Net traffic or have a look at Connection Manager (CMAN) to create a kind of Oracle*Net DMZ access to your databases. I’m wondering if SSH tunnels are reliable enough to handle this? I wouldn’t use it.

I know I least one other way, but I need to keep some material for future posts.

Part 1: How to Make the OMS to Agent Traffic via port 443

Can you use only port 443 between the OMS and the agent on Linux? The answer is obviously yes, and here is at least one approach: port address translation. (more…)

I’ve got notification of new download on OTN. Here is the link to OEM download page with 10.2.0.4 is for Linux x86 and Windows x86. Other platforms should be available upon release on the same page. I think I also saw it on in my Google Reader mentioned but today I couldn’t find it.

We are rolling it out for one Oracle Agent installation with one of our clients today - let’s see how it works.