Beware of OEM 11g

Hi Fellow DBA's,

I just wanted to take a moment to let you know that now might not be a good time to upgrade or install the new EM Grid Control 11g.

I've been fighting with it for about a month now and have nothing but bad things to say about it (and i'm the single biggest OEM proponent in my department!).

Here are the main issues I'm seeing with a fresh 11.2 database and a fresh OEM install:

• It's a major pain to install - go look at MetaLink, er, I mean, Oracle Support note [ID 1067438.1] "Master Note for Grid Control 11.1.0.1.0 Installation and Upgrade" for an idea of what you're in for.
• The JDK is hosed - Agent deploys of any kind fail with the dreaded "java.lang.NoClassDefFoundError: Could not initialize class oracle.sysman.oii.oiip.osd.unix.OiipuUnixOps" error (and just for the record: no, upgrading the OMS_HOME jdk to even the newest 1.6_21 does not fix the issue).
• There are almost no support notes for OEM 11g - you'll never solve any issues without opening an SR.
• It's unpatchable - there is a bug that prevents you from adding the Software Library path, which of course OPatch patches need once you get to the point of deploying PAR files.
• Job reporting is broken - the "Jobs" tab always shows no history no matter how you search.
• Policy Violations are broken - you know those great "All Targets Policy Violations" icons on the home page that you can click on? Yeah, try clicking on one and you'll get a nice "No Search Results found matching the criteria specified." message.

And this is just what I found - what about you guys, what issues have you seen?

PS: I hope to update this list with fixes as they come back from Oracle.

UPDATE: It turns out that some of these issues are due to a blunder on my part! I installed the 32-bit version of WLS 10.3.2, instead of the 64-bit one that you need your own Java install for.

This eliminated the horrible issue with the "OiipuUnixOps" error on Agent deploys and when trying to create a Software Library path.

If you did not install WLS using this syntax on a 64-bit system (eg: running the .bin file), then you did it wrong:

/usr/lib/jdk16/jdk1.6.0_18/bin/java -d64 -jar wls1032_generic.jar

Unfortunately, all the other gripes I have with OEM persist... :)

HowTo: Resolve Oracle 11g X-Windows installer issues on UNIX

Have you run the new Oracle 11g installer on *NIX and received a nasty message?

It happened to me this week! So, let's say you download the oracle11g installer for AIX, start x-windows (I prefer CygWin), export your display, test with xclock, then go into the "database" folder and do: ./runInstaller

These are the errors I received:

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 190 MB. Actual 11907 MB Passed

Checking swap space: must be greater than 150 MB. Actual 19968 MB Passed

Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

Preparing to launch Oracle Universal Installer from /oracle/tmp/OraInstall2010-03-11_12-25-37PM. Please wait ...

[oracle@myserver 12:26:03] (brent01) /oracle/INSTALL/database

$ Exception in thread "main" java.lang.UnsatisfiedLinkError: /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a ( 0509-022 Cannot load module /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a.

0509-150 Dependent module /usr/lpp/X11/lib/libXt.a(shr_64.o) could not be loaded.

0509-152 Member shr_64.o is not found in archive

0509-022 Cannot load module /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a.

0509-150 Dependent module /oracle/tmp/OraInstall2010-03-11_12-25-37PM/jdk/jre/bin//motif21/libmawt.a could not be loaded.)

at java.lang.ClassLoader.loadLibraryWithPath(ClassLoader.java:986)

Well, that's a fine mess!

Before you comb MetaLink and create soft links to other libraries, etc. do yourself a favor and try this:

$ env | grep -i libpath

If your LIBPATH environment variable contains the system's X11 lib, then that's your problem. Mine showed:

LIBPATH=/lib:/usr/lpp/X11/lib:/oracle/oracle8i/product/8.1.7/lib

Removing "/usr/lpp/X11/lib" piece from my LIBPATH resolved the error.

$ export LIBPATH="/lib"

$ ./runInstaller

Success!

HOWTO: Connect to Oracle 11g on Windows Vista

After installing 11g on Windows Vista, everything works fine. The listener is up, I can connect with SQL*Plus and misc case tools, etc.

However, no other Windows box on the same network segment (and in the same Workgroup) can access the instance on Vista.

Even a simple ping from a nearby XP box fails with "request timed out."

Google searches for things like "unable to ping vista from xp", etc. yielded the usual "check these" suspects:

• Workgroup name is the same
• Simple file sharing is on
• Network discovery is on
• Network is Private

All check out on both boxes for me.  I even have both Windows Firewalls disabled, and neither computer has McAfee or Norton, so that's not it.

Finally after 2 days or trying, I figure it's time to do a Crazy Ivan. This is Microsoft we're dealing with... so, I do something crazy; I turn on the Windows Firewall on Vista.  I then clicked over to the second tab on the Firewall dialog and looked thru the checklists.

Do you know what I noticed?  The "File and Printer Sharing" checkbox isn't checked. (Thanks Microsoft for letting me enable file sharing in the Network and Sharing Center and not telling me it was off in the firewall!)

So, [click], I enable that sucker.  Then, since I was already here, I created a new rule (the "Add port..." button) for port 1522:

Now it's back to the XP box again... and VOILA! the ping starts working.  I can even ping the Vista box by it's Windows name (that failed before).  More importantly, I can connect to the ORCL database on vista from XP now!

So, I ~200,000 results in Google for "unable to ping Vista from XP" and it's as simple as a checkbox or two.  This is a newer Vista install, so it appears that this checkbox is off by default, couple that with the inability of Vista to warn you when you enable file sharing, and I can see why almost a quarter million people are going "WTF?!"

HowTo: Create a universal UDM report page in OEM

HowTo: Create a universal UDM report page in OEM

PROBLEM:

• You have a bunch of User Defined Metrics (UDM) setup in OEM, and you want to know the staus of each of them.
• OEM does not provide a single page to show the status of UDMs - users must navigate to each instance and look at the UDM page.
  

SOLUTION:

• Create a standard OEM Report to display all of your UDMs in one place!

STEPS:

1. First, if you have not already discovered this the hard way, you cannot create a Report against a non-EMREP/non-Repository tables. Why, i'll never know. But, whatever. The thing is, if you try to create a report using something easy like showing how many records are in an Target Database table, it will return the error "ORA-00942: table or view does not exist"
2. In this case, we are actually hitting an OEM Repository table, but it's not one of the objects we are allowed to query via the "Table From SQL" report element type.
   
3. Fortunately, "bwolter" found a work-around for this sorry situation and published how to it in this Oracle OEM Forum post.
4. Follow his 3 easy steps, then continue with my Step 5 below.
5. Create a new Report and choose whatever you want for Category, etc.
6. Set the "Targets" value to the "Use the specified target" radio-button and select your Repository SID
7. Set the "Privileges" value to "Run report using target privileges of the report owner (SYSTEM)"
8. Click the "Elements" sub-tab, then Add a new element using the new "target_user_table_from_sql" type.
9. Click the "Set Parameters" icon and enter your SQL. Note that your value of KEY_VALUE will be different than mine!

10. SELECT DISTINCT UPPER(target_name) "Target Name",
           key_value || '__' "Metric Name",
           TO_CHAR(collection_timestamp, 'MM-DD-YYYY HH24:MI') "Last Collected_____",
           string_value "Metric Value"
      FROM sysman.em$current_metrics
     WHERE metric_name = 'SQLUDM'
       AND UPPER(key_value) LIKE 'ENSURE%'
       AND string_value IS NOT NULL
     ORDER BY 1, 2

11. My KEY_VALUE (the "Name" you used for the UDM) is "Ensure PMON is running as MST", so obviously you will set your value to whatever the Name of your UDM is.
12. Rinse-and-repeat Steps 8-10 for each UDM type or gorup you want to show on the Report.

NOTES:

• I use underscore in my column alias names to stretch the columns out for easy reading.
• During this project I found a UDM which had encountered errors and stopped running! OEM showed no indication outside of a small icon on the Instance's UDM page (nor did it send a notification). This report will save you from that potential embarrasement!!
  

RESULTS:

• Here is what my final UDM Report looks like:

Good article on MERGE statement enhancements in 10g

I don't get to use MERGE too often, but when I need it, it is usually something for something I need to Google the syntax for.

Here's a great post on using MERGE in Oracle 10g with plenty of syntax examples for each.

BTW, thanks Oracle for finally adding support for UPDATE-only or INSERT-only operations!

HowTo: Setup SSH equivalency

Here's a simple approach to setting up SSH (Secure SHell) equivalency across 2 UNIX boxes. This is also known as "using SSH and SCP without passwords".

There are a variety of reasons to setup SSH equivalency across UNIX boxes. I assume you already have a desire to do so or you would not be reading this, so let's skip the sales patch and get to the howto part.

While there are scripts that do this for you (especially if you're doing this for OEM setup), this is really easy to do on your own, so forget those kludgey scripts!

NOTE: I'm using "UNIX01" and "UNIX02" below to represent 2 different UNIX boxes along with user "oracle" - you can use whatever user you want, just ensure it's the same on both boxes.

STEPS:

1. On UNIX01:
   
1. Create $HOME/.ssh, if it does not already exist
2. $ cd $HOME/.ssh
   
3. Generate your RSA key (NOTE: Your path may vary!)
   
1. /usr/bin/ssh-keygen -t rsa
2. When prompted for a passphrase, just press (ENTER) (leave it blank)
   
4. Generate your DSA key
   
1. /usr/bin/ssh-keygen -t dsa
2. When prompted for a passphrase, just press (ENTER) (leave it blank)
5. Store the 2 keys into the authorized_keys file
1. cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
2. cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
2. On UNIX02:
1. Repeat steps 1-5 above
3. On UNIX01:
1. Copy the 2 lines from authorized_keys on UNIX02
2. Add them to the authorized_keys file
4. On UNIX02:
1. Copy the 2 lines from authorized_keys on UNIX01
2. Add them to the authorized_keys file

That's it!

Test your setup as follows:

• [oracle@UNIX01] $ ssh -l oracle unix02 date

It should just show you the date without prompting for a password.

Now you can use scp and other ssh commands with ease!

HowTo: Remove parameters from the spfile

Here's a question that comes up often:

• How do you remove an SPFILE parameter (not change the value of, but actually purge it outright)?

Answer:

• Use "ALTER SYSTEM RESET ..." (For database versions 9i and up)

Syntax:

• ALTER SYSTEM RESET PARAMETER SID='SID|*'
• ALTER SYSTEM RESET "_TRACE_FILES_PUBLIC" SCOPE=SPFILE SID='*';

NOTE: The "SID='SID|*'" argument is REQUIRED!