Brent's Oracle DBA Blog

Beware of OEM 11g

2010-07-16T16:05:00.010-05:00

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

2010-03-16T15:38:00.009-06:00

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

2009-02-06T00:13:00.009-06:00

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

2008-05-27T15:02:00.010-05:00

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:

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"
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.
Fortunately, "bwolter" found a work-around for this sorry situation and published how to it in this Oracle OEM Forum post.
Follow his 3 easy steps, then continue with my Step 5 below.
Create a new Report and choose whatever you want for Category, etc.
Set the "Targets" value to the "Use the specified target" radio-button and select your Repository SID
Set the "Privileges" value to "Run report using target privileges of the report owner (SYSTEM)"
Click the "Elements" sub-tab, then Add a new element using the new "target_user_table_from_sql" type.
Click the "Set Parameters" icon and enter your SQL. Note that your value of KEY_VALUE will be different than mine!

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

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

2008-02-15T10:32:00.004-06:00

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

2008-02-11T15:57:00.000-06:00

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:

On UNIX01:

Create $HOME/.ssh, if it does not already exist
$ cd $HOME/.ssh
Generate your RSA key (NOTE: Your path may vary!)

/usr/bin/ssh-keygen -t rsa
When prompted for a passphrase, just press (ENTER) (leave it blank)

Generate your DSA key

/usr/bin/ssh-keygen -t dsa
When prompted for a passphrase, just press (ENTER) (leave it blank)

Store the 2 keys into the authorized_keys file

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

On UNIX02:

Repeat steps 1-5 above

On UNIX01:

Copy the 2 lines from authorized_keys on UNIX02
Add them to the authorized_keys file

On UNIX02:

Copy the 2 lines from authorized_keys on UNIX01
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

2007-10-04T10:50:00.000-05:00

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

2007-09-24T13:25:00.000-05:00

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');

What Do All 10g Flashback Features Rely on and what are their Limitations?

2007-09-10T13:18:00.000-05:00

There's a great MetaLink article on Flashback - check it out!

What Do All 10g Flashback Features Rely on and what are their Limitations?
Doc ID: Note:435998.1

What Do All Flashback Features Rely on ?
Differences Between New Oracle 10g Flashback Technologies
Limitations of Flashback Database
Limitations and Restrictions on Flashback Drop
Limitations and Restrictions on Flashback Tables

Here are two gems that were news to me:

To be able to flashback a table, you must enable row movement for the table
Because DML operations are used to bring the table back to its former state, the ROWIDs in the table change. As a result, Flashback Table is not a viable option for applications that depend on the table’s ROWIDs to remain constant

Note that altering a table to enable row movement will cause underlying PL/SQL to become invalid!!!

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

2007-09-05T13:48:00.000-05:00

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:

Set your Oracle environment variables as normal (I use "oraenv")

$ . oraenv

Start rman

$ rman

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

RMAN> connect target
RMAN> connect catalog rman@rmandb

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

Recover the database

RMAN> startup force nomount;

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

RMAN> alter database mount;

database mounted

Get the missing archive logs back from tape/disk/TSM/whatever

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")
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;
}

Open the database

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

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

2007-08-27T12:11:00.001-05:00

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

2007-08-27T11:12:00.000-05:00

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

2007-08-24T09:51:00.002-05:00

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:

Navigate to the database instance "Home" page
Click the "User Defined Metrics" link at the bottom of the page
Click the "Create" button to create a new UDM
Complete all of the form fields

SQL query you would like to run (don't use a semi-colon at the end!)
Credentials
Thresholds
Schedule
Note that you can customize the subject line of the notification emails
Click the "Test" button to make sure the results are what you want
Click "Ok" to save this UDM

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...
Click "Preferences" in the top right corner of any Grid Control page
Click "Rules" in the left nav bar
Create a new Notification Rule
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

2007-08-24T09:49:00.000-05:00

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

2007-08-09T14:48:00.000-05:00

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

2007-08-07T10:41:00.000-05:00

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

An insanely great trick to recover a cold backup database that starts needing recovery

2007-07-12T15:34:00.001-05:00

Why would a 10g database restored from cold backup need recovery? That's messed up.

So for the second time now, we took a small (~50GB) database the other day, shut it down using "shutdown immediate", started it back up, switched logs, and then shut it down again.

We then cloned it (took a cold backup of it, and brought it up with a different name), and low-and-behold it complains that it needs recovery. WTF?

Here's the upside though: There's a way to easily recover from this and bring the database up.

Check out MetaLink Note: 175015.1 - it's somewhat cryptic, but essentially all you need to do is take the most recent REDO log and copy it to the archivelog dest location and name it to whatever archivelog name Oracle is suggesting in the recover database command, then just hit (Return) to accept the filename and begin the recovery process. Next thing you know, boom, everything is up and running.

Now to the question of why this is happening...

Oracle MetaLink search plugin now available for Firefox

2007-07-12T14:47:00.000-05:00

Sweet! Check it out.

Thanks to the good folks at Oracle AppsLab!

Issues with RMAN on AIX using TDP for Oracle

2007-06-20T15:30:00.000-05:00

So our group has thankfully decided to go with RMAN for database backups, and you know i'm first in line to take this task on.

Here's my setup at a high level:

10gR2 database (rmandb - snappy name huh? :)
IBM's TDP 5.3 for Oracle
AIX 5.2

Steps:

My internal IT department setup my TSM domain, etc.
Sysadmin installed the TDP fileset and then generated the password file for me
I used the TSM Quick Start Guide to get all of my config files and directories setup
I used OEM GC R3 to set the RMAN preferences, and to setup the Tape parameters: ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)

Once all of this was done and "$ tdpoconf SHOWENVironment" looked good, i went for my first test drive.

I logged into RMAN, connected to target, and then ran a connection test:

run {
allocate channel oem_sbt_backup type 'sbt_tape' format '%U' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
release channel oem_sbt_backup;
}

And it worked fine. Woot!

Next, I used the "Test Backup" button on the Backup Setting OEM page. It's a simple test consisting of:

run {
allocate channel oem_sbt_backup type 'sbt_tape' format '%U' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup as BACKUPSET current controlfile tag '05252007020711';
restore controlfile validate from tag '05252007020711';
release channel oem_sbt_backup;
}

And... it fails with this lovely error output:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 05/22/2007 16:28:09
ORA-19506: failed to create sequential file, name="0aiiceoi_1_2", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
ANS0231E (RC2025) The management class name was not found.

Nice huh?? What the heck is "ANS0231E (RC2025) The management class name was not found."?

If you think that's depressing, try entering "ANS0231E" into Google. There is less than 30 results worldwide! I hate when that happens...

After working with IS and IBM for 3 weeks (3 weeks!!!), we finally worked our way up to a SEV-1 analyst on a conference call and he figures it out.

Wait for it...

My tdpo.opt file had no stanza for "TDPO_MGMT_CLASS_2" (or _3, or _4) because I was only allocating one channel (these values are only used when more than one channel is opened to TSM, otherwise, it used the channel appropriately named "DEFAULT").

Here's the gotcha: I setup RMAN to do 'duplex' copies, e.g.: create 2 copies of all datafiles and 2 copies of all control files.

The problem is RMAN opens 2 channels when duplex is set!!!

Bottom Line: (I had IBM add this to their KB)
If you receive the dreaded "ANS0231E (RC2025) The management class name was not found." error, the first question you need to ask yourself is, "Am i using duplex copies", if so, i'll bet you don't have a value for "TDPO_MGMT_CLASS_2" in your tdpo.opt file.

This was a hard lesson learned... :-)

Upgrading EM Grid Control from 10.2.0.1 to 10.2.0.3

2007-06-14T09:50:00.000-05:00

Okay, so I finally bit the bullet and upgraded EM Grid Control from the initial install version of 10.2.0.1 to 10.2.0.3 and upgraded the EM repository (cleverly named EMREP) from 10.1.0.5 to 10.2.0.1 .

Here's a few observations about the upgrade:

The EMREP page in GC still showed the old 10.1 home instead of the new 10.2 home.

This stems from the fact that I had 2 different ORACLE_HOME installs: /orabase/product/ora10g and /orabase/OracleHomes/db10g.
Upgrading meant moving from one home to another and GC did not pick up on this change
Solution: Remove EMREP from monitoring and re-add it (BTW, refreshing the host configuration did not work!)

It took much longer than I had planned.

As part of the 10.2.0.3 patch, a repository upgrade was necessary - this step alone took about 45 minutes - yikes!

There are about 8 new security policies that come with GC 10.2.0.3 ("Password Complexity Verification Function Usage", etc.)

Once you've upgraded and logged in to GC, you'll see a whole bunch of new security policy warnings.
Sadly, these have to be fixed by going to your Monitoring Templates (you do use templates right??) and using the "Add Policies To Template" button to add the offending policies and then set them appropriately.

One last thing:

They finally fixed that god-forsaken bug that kept the Database->Administration->Tablespaces page from rendering on 9i databases in under an hour! Jeez, that was annoying as hell! :-)

SQL of the Day: How to view progress of DML operations

2007-05-31T13:47:00.000-05:00

So, you're running an INSERT/UPDATE/DELETE statement and it's just sitting there processing. Then your boss comes by and says, "When will this be done?". Well now you can tell them.

Run this SQL to show the progress of DML operations:

SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
       rows_processed "Total Rows Processed",
       ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
       TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
       TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
  FROM SYS.v_$sqlarea
WHERE sql_text LIKE 'INSERT INTO some_table%'
   AND open_versions > 0
   AND rows_processed > 0;

D'oh! of the Day: Creating a tablespace with 16k blocksize in a database with 8k blocksize default

2007-05-24T14:28:00.000-05:00

So, this should have been a no-brainer.

I'm trying to improve the performance of a DSS database (10gR2 with ASM), so I figure, "Hey, i'll create a new tablespace with a 16k blocksize and reorg 2 big tables into it to see if that helps."

SQL> CREATE SMALLFILE TABLESPACE "DATA_16K"
DATAFILE'+DGROUP1(DATAFILE)' SIZE 1M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K;
ORA-27035: logical block size is invalid

Huh??

Here's the solution (the D'oh! moment): I never created a buffer pool for 16k blocks!

SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID|NAME |BLOCK_SIZE|CURRENT_SIZE| BUFFERS
----------|--------------------|----------|------------|----------
3|DEFAULT | 8192| 2560| 317439

SQL> alter system set db_16K_cache_size=2m scope=both;
System altered.

SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID|NAME |BLOCK_SIZE|CURRENT_SIZE| BUFFERS
----------|--------------------|----------|------------|----------
3|DEFAULT | 8192| 2528| 313471
7|DEFAULT | 16384| 32| 2014

SQL> CREATE SMALLFILE TABLESPACE "DATA_16K"
DATAFILE'+DGROUP1(DATAFILE)' SIZE 1M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K;

Tablespace created.

Yes, i'm easily amused some days.

Having issues using DBCA with ASM on 10gR2?

2007-05-14T09:34:00.000-05:00

So, i'm feeling particularly pleased last week when I finally get ASM up and running last week on a new reporting server. It's an IBM AIX box, with a 600GB disk group (DGROUP1), using the usual "+ASM" instance name. Also note that I used my 10gR2 (10.2.0.1 EE) home as my ASM home, since i'm only using ASM for a single database on this box.

Once i verify that ASM is working and that it's hooked into EM Grid Control, I cd to my Oracle 10gR2 home, setup my environment by using oraenv, and then run dbca.

Everything is going great until I get to the storage page, where I select "ASM" and click Next. Within a couple of seconds, I see this gem of an error message:

"DBCA could not startup the ASM instance configured on this node. To proceed with the database creation using ASM, ASM instance needs to be up and running. Do you want to recreate ASM instance on this node?"

Hmm. Not what I wanted to see, that's for sure. :-(

So it's off to Metalink to see what Oracle has to say. In no short order, I find about 6 separate issues (some with their own accompanying bug numbers) all reporting the exact same issue, and all with existing ASM
instances.

The only note that had a real work-around involved changing some OAS (Oracle Advanced Security) settings as ODBC did not work well with them. That's all well-and-good, but i'm not using OAS. My sqlnet.ora file has one line!

Next, I did what all good DBA's do when faced with this type of situation: go to Google. Unfortunately, I was still unable to find a solution. The OTN forums were not much help either. Oddly enough, some of my searches even resulted in googlewhacks.

The one interesting result I found on Metalink said something like, "I managed to use dbca to setup a database with ASM before, but now that I try it a second time, it does not work!" Interesting. To me, that meant there HAD TO BE a solution here. There's just no way that dbca is unable to create a database with ASM. Common!!

Here's what i'm doing: I have 2 terminal windows open - one was running dbca, the other was my working terminal. The working one being the terminal I used to change listener settings, tnsping, etc.

After 2 hours of a hapless tango between clicking Next> and seeing the same error, then changing settings ... , I finally throw in the towel at 4:30 and close the term window running dbca.

Just for fun, I run dbca from the OTHER term window where i've been continually futzing with settings. I get to Storage, select ASM and click Next... NOW IT WORKS!!! WTF?!

That's why it works for some people sometimes and then stops the next time. We've got some env variable or something set wrong!

SOLUTION: If you get this error, check your settings, open another window, re-run oraenv, keep trying!

As soon as dbca completed, I catpured my 'env' output - here's the items of interest:

ORACLE_SID=+ASM
ORACLE_HOME=/orabase/product/ora10g
LIBPATH=/orabase/product/ora10g/lib:/orabase/product/ora10g/lib32:/usr/lib:/lib
JAVA_HOME=/usr/java5/bin

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

2007-05-11T12:52:00.000-05:00

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:

Apply your Monitoring Template to the object (this works especially well for Hosts), this will force a re-evaluation and clear outstanding alerts
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!