Problem: ORA-10631 errors encountered during "SHRINK SPACE" operation

OEM was nice the other day and offered some Segment Adviser Recommendations to shrink a few tables that had become fragmented. However, when I ran the shrink job, it failed.

PROBLEM:

• "ALTER TABLE my_test_table SHRINK SPACE;" results in "ORA-10631: SHRINK clause should not be specified for this object"
  

SOLUTION:

• Tables that have function-based indexes cannot be compacted using the SHRINK clause
• Apparently, someone forgot to inform OEM of this fact (thanks the bogus recommendations! :-)
  
• See MetaLink Bug No.:5968539

Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

SELECT   dt.owner, dt.table_name,
         (CASE
            WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
            ELSE 'N'
         END) AS can_shrink
    FROM dba_tables dt,
         (SELECT   table_name, COUNT(*) cnt
              FROM dba_indexes di
             WHERE index_type LIKE 'FUNCTION-BASED%'
          GROUP BY table_name) ind
   WHERE dt.table_name = ind.table_name(+)
     AND dt.table_name NOT LIKE 'AQ$%'
     AND dt.table_name NOT LIKE 'BIN$%'
     AND dt.owner = 'BRENT'
ORDER BY 1, 2;

Problem: Unable to copy data files when 10g database is in hot backup mode

So, our customers are FINALLY making the transition to 10gR2 databases this summer and we're having all kinds of great "Oops!" moments. Here's a good example:

PROBLEM:

• A tablespace's physical files cannot be copied even when the tablespace is in hot backup mode.
  
• We created a pretty standard 10.2.0.3 database on an AIX 5L box, and when we put the tablespaces into hot backup mode, we were unable to copy the data files to a backup location as AIX kept reporting that they were locked.

Of course our first reaction was "HUH?!", and after much head scratching, sysadmin pestering, and SAR opening, we finally got the solution from Oracle.

SOLUTION:

• There is a bug with the FILESYSTEMIO_OPTIONS init parameter in 10g that prevents the "setall" value from working correctly.
• We were able to copy the data files once we made the following change: filesystemio_options=async
  
• See MetaLink Note:434385.1
  

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?

11g New Features for DBA's

Arup Nanda is already hard at work detailing the new 11g features for DBA's!

Check it out.

Diagnosing Oracle I/O problems in AIX

Check out this great post by Matt Fleming.

I was searching for how to create a Physical Volume (PV) to Logical Volume (LV) LUN mapping spreadsheet for my AIX system. I didn't find anything as good as this, so H/T to my buddy Mark for sending me this link.

Now I know how get this information, plus I saved the AIX I/O Stack diagram for future reference.

Very nice!

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).