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: 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:

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!

HowTo: Clearing Alerts in OEM 10g Grid Control

I was browsing the Oracle Forums earlier today and this post with a bit of SQL to clear OEM alerts from mnazim, who always has good advice to offer:

To clear the alert:

1. Make a SELECT * from MGMT_CURRENT_SEVERITY and show for the TARGET_GUID, METRIC_GUID and KEY_VALUE.

2. Connect to db user SYSMAN and execute:
exec EM_SEVERITY.delete_current_severity(TARGET_GUID, METRIC_GUID, KEY_VALUE);

for example:
exec EM_SEVERITY.delete_current_severity('0DEB8E6980695B7548CF98871084AD10', 'F95BA0D95585002889E1ABF92B2DA7C3', 'SYS');

HOWTO: Disaster Recovery: Recovering the RMAN catalog database using RMAN

What's the first step in a DR scenario? Recovering the RMAN catalog database of course!

Can you use RMAN to recover RMAN? Yes, you can!

For my money, the best feature of RMAN is that even though the catalog database itself will not startup without recovery, it can still be used. Which is great, because otherwise we have one of those dreaded Star Trek space-time paradoxes (what do you do to recover your recovery system?!), and those make my head hurt. Even better, it's really pretty easy if you know what to do.

My scenario is:
I walk into work one morning and the production box that my RMAN catalog is running on got hosed-up somehow over night (i'm not naming any names... *cough* sysadmin *cough*) and now all of the databases on the box are down and in need of recovery as well as the RMAN database.

Note that in this particular case, the RMANDB database needed archive logs that were not on the system in order to be recovered. This is what necessitates the use of Step 5 below to retrieve the file, otherwise, this step could be skipped.

Here's how we fix the RMAN catalog database:

1. Set your Oracle environment variables as normal (I use "oraenv")
1. $ . oraenv
   
2. Start rman
1. $ rman
   
3. Connect to the target DB and then the catalog (I use the "rman" user as my catalog owner and i've named my RMAN catalog database "rmandb")
   
1. RMAN> connect target
2. RMAN> connect catalog rman@rmandb
1. RMAN-00571: ===========================================================
   RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   RMAN-00571: ===========================================================
   RMAN-04004: error from recovery catalog database: ORA-01034: ORACLE not available
   ORA-27101: shared memory realm does not exist
   IBM AIX RISC System/6000 Error: 2: No such file or directory
   
4. Recover the database
1. RMAN> startup force nomount;
1. Oracle instance started
   Total System Global Area 2164260864 bytes
   Fixed Size 2074256 bytes
   Variable Size 704645488 bytes
   Database Buffers 1442840576 bytes
   Redo Buffers 14700544 bytes
   
2. RMAN> alter database mount;
1. database mounted
   
5. Get the missing archive logs back from tape/disk/TSM/whatever
   
1. NOTE: When I first tried the "startup" command the database it told me it need archive log 241 and up, so that is where the value below came from (you can always use "restore archivelog all")
2. RMAN> run {
   allocate channel oem_restore type sbt parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
   allocate channel oem_restore_disk type disk;
   restore archivelog from logseq=241 thread=1;
   }
   
6. Open the database
1. RMAN> alter database open resetlogs;
   

Having gone through this exercise just a couple of months ago, I can tell you that I was absolutely thrilled when this worked!!

HowTo: Setup User Defined Metrics in OEM Grid Control

User Defined Metrics are a fantastic feature of OEM Grid Control. Do you use them? Well, you should! They are very easy to setup and are just as reliable as any other OEM alerts.

As an example, let's say you want to be notified whenever a certain schema has any invalid objects in it. This is how to setup such an alert:

1. Navigate to the database instance "Home" page
2. Click the "User Defined Metrics" link at the bottom of the page
   
3. Click the "Create" button to create a new UDM
   
4. Complete all of the form fields
   
   
   
1. SQL query you would like to run (don't use a semi-colon at the end!)
2. Credentials
3. Thresholds
   
4. Schedule
5. Note that you can customize the subject line of the notification emails
6. Click the "Test" button to make sure the results are what you want
7. Click "Ok" to save this UDM
   
5. Now, if you merely stop here, no notifications will ever be sent! Why? Because there is no Notification Rule defined, so let' s do that next...
   
6. Click "Preferences" in the top right corner of any Grid Control page
   
7. Click "Rules" in the left nav bar
8. Create a new Notification Rule
   
   
9. Voila! Now email notifications will be sent whenever your threshold is exceeded and the this metric automatically integrates with all of the Alert display boxes throughout OEM.

Cool huh?

HowTo: Reorg physical disks under ASM

Let's say I have one or more disks/mounts in an ASM disk group and I want to get all of my data off of this disk and then remove it from the disk group. How is this done?

Answer:

When I first built my lone ASM instance (+ASM), I had SysAdmin create 2 300GB RAW mounts and made them into a single disk group to be used by a single instance.

This setup lead to very poor ASM throughput of about 2MB/sec (and this is off of an 200MB/sec EMC SAN!). This is on a 64-bit AIX 5L box with each mount only having only a single LUN (which they did not bother to disclose to me until AFTER i reported slowness!).

So what i decided to do was to start again.

I went back to the SA's and ordered 20, 25GB mounts with a unique LUN for each mountpoint. After the space became available I added it to my existing disk group (DGROUP1) and let ASM rebalance itself over night.

Now, here's the genius part: All you have to do to get your data off of a disk and remove is to just go into the OEM Grid Control ASM Disk Group page, select the disk and click the "Remove" button.

ASM AUTOMATICALLY rebalances the data off of this one disk onto the remaining disks and then drops the disk!. How great is that?

Once you've selected a disk and clicked "Remove", you will see that the disk now has a status of "Dropping" (see screen-cap below).



You can also monitor the progress of the drop operation by using the "Pending Operations" link under the General section heading on the Disk Group page (see screen cap below).

HowTo: Force re-evaluation of Policy Violations in OEM Grid Control

Don't you hate when you fix a Security/Policy Violation in OEM and it won't re-evaluate it for another 24 hours?

Well there's 2 things you can try:

1. Apply your Monitoring Template to the object (this works especially well for Hosts), this will force a re-evaluation and clear outstanding alerts
2. Use an undocumented parameter for the EM Agent (10.2.0.1+) "emctl clearstate agent". This will cause the Agent to re-evaluate the instances on it's host - note: this can take a couple of hours to run and update in the background!