Oracle Applications R12.1 Platform Migration, Tip #2

I've been battling with expdp and impdp trying to get my database moved to a different platform (more on that later).  During the course of events, I decided that I had to apply the 11.1.0.7.2 patches to my $OH to make sure I was up to date.  However, when I used opatch to apply, I got the following error:

ApplySession failed: Patch ID is null.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 73

Seems you have to be on the latest version of OPatch (patch 6880880) in order to apply it successfully.

Malwarebytes

This is the best set of instructions I've seen for how to use Malwarebytes.  I just used it to help me get rid of "Vista Internet Security".  Bastards.

Oracle Applications R12.1 Platform Migration

I've mostly been following note 741818.1 to migrate my R12.1 db from 10gR2 on Solaris to 11gR1 on Linux.  One gotcha, though, is that tablespace quotas must be re-assigned between step 4.3 and 4.4.  I generated a little script off the source db using dba_ts_quotas.

Halleluiah

Learned from Niall Litchfield that there is a HTML version of Metalink called http://supporthtml.oracle.com/.

Metalink Out?

Yes, Metalink is down.  It's been down for over an hour.  I was told "No ETA, but we're hoping by noon."

Installing Oracle Applications R12.1, Tip #5

While applying ademusr.sql, I ran into a couple errors similar to:

ORA-01720: grant option does not exist for 'SYS.DBA_USERS'

I researched the problem on metalink and found Doc 431067.1 which kind of suggested I could just grant permission to the object and move on. That's exactly what I did and restarted the worker with adctrl.

Installing Oracle Applications R12.1, Tip #4

Make sure you have your OLAP workspaces defined before you attempt the 12.1.1 upgrade portion.  Otherwise, you will get an error similar the below when running MSDODPCODE.sql:

ORA-37002: Oracle OLAP Failed to intiialize.  Please contact Oracle OLAP technical Support
ORA-33262: Analytic Workspace EXPRESS does not exist.

To fix:

sqlplus "/ as sysdba"
@?/olap/admin/olap.sql SYSAUX TEMP

Then you can restart your worker using adctrl.

Installing Oracle Applications R12.1, Tip #3

My Oracle Applications environment is a split configuration.  I have the db tier on one node and everything else on another node.  It came time for me to stage my db portion on my db node, so I fired up adautostg.pl and attempted to stage just the db software...

Starting AutoStaging at Fri Jan 15 10:11:48 2010
Using adautostg.pl version 120.12


This script allows you to stage Oracle Applications which contains:

    - Database Technology Stack (11g)
    - Database (Vision or Fresh)
    - Applications Technology Stack (10.1.2, 10.1.3)
    - Applications APPL_TOP & COMMON_TOP 


Enter a directory path where the media is to be staged (For example: To create  
the stage area at /u01/StageR12 enter /u01 ) :/mnt/install/OracleApps

The stage area will be created at /mnt/install/OracleApps/StageR12
 
Do you use AutoMount to mount/umount the media y/n [y]?

Enter the Mount Point location [/mnt/cdrom]: /media/cdrom


Select Option 1 for a complete Oracle Application Environment Install.

    1  - Complete Oracle Apps Env (11g, Database, 10.1.2, 10.1.3, APPL & COMM)

Or choose the individual components you want staged

    2  - Database Technology Stack Only (11g)
    3  - Database Only (Vision or Fresh)
    4  - Applications Technology Stack Only (10.1.2, 10.1.3)
    5  - APPL_TOP & COMMON_TOP Only

Enter number codes separated by spaces [1]: 2

Invalid component code: 2

You must choose at least one valid component.


Select Option 1 for a complete Oracle Application Environment Install.

    1  - Complete Oracle Apps Env (11g, Database, 10.1.2, 10.1.3, APPL & COMM)

Or choose the individual components you want staged

    2  - Database Technology Stack Only (11g)
    3  - Database Only (Vision or Fresh)
    4  - Applications Technology Stack Only (10.1.2, 10.1.3)
    5  - APPL_TOP & COMMON_TOP Only

Enter number codes separated by spaces [1]: 2

Invalid component code: 2
You must choose at least one valid component.


Select Option 1 for a complete Oracle Application Environment Install.

    1  - Complete Oracle Apps Env (11g, Database, 10.1.2, 10.1.3, APPL & COMM)

Or choose the individual components you want staged

    2  - Database Technology Stack Only (11g)
    3  - Database Only (Vision or Fresh)
    4  - Applications Technology Stack Only (10.1.2, 10.1.3)
    5  - APPL_TOP & COMMON_TOP Only

Enter number codes separated by spaces [1]: 2 

Invalid component code: 2

You must choose at least one valid component.


Select Option 1 for a complete Oracle Application Environment Install.

    1  - Complete Oracle Apps Env (11g, Database, 10.1.2, 10.1.3, APPL & COMM)
Or choose the individual components you want staged

    2  - Database Technology Stack Only (11g)
    3  - Database Only (Vision or Fresh)
    4  - Applications Technology Stack Only (10.1.2, 10.1.3)
    5  - APPL_TOP & COMMON_TOP Only

Enter number codes separated by spaces [1]: 1

Checking diskspace ...Ok.

Staging startCD ...Done.

Staging Rdbms ...

OK, then, I guess I have to stage all 20 DVDs to get the db software...

Corrupt Backup Piece

So, I was doing a project the other day to restore my production 10.2.0.3 database to my development server.  Since I use Recover Manager to backup, my normal course of action is to use the DUPLICATE feature of rman to restore my backup to my QA server and apply the archived redo logs.

It had been a long time since I last restored this particular database.  I had an old init.ora file laying around, so I figured I might as well use it.  I started the instance in NOMOUNT mode and fired off my duplication.

About half way through, I received the following error:

ORA-19870: error reading backup piece /nfs/backup/PROD/20100112.3fl38k2l_1_1
ORA-19599: block number 144709 is corrupt in backup piece /nfs/backup/PROD/20100112.3fl38k2l_1_1

Hmm, that's different.

I just figured my backup pieces were corrupt somehow, so I took another backup on the source db.  I started restoring it on the QA server, and BAM!  Same thing.

This was a job for Oracle Support.  I created an SR and while they didn't have an immediate solution for me, they did bring up that there were a couple unverified bugs with compressed backups that might cause this error.  Since I was using compressed backup pieces, I figured this was worth a shot to try.

I then took a backup on my production db without compressing the backup pieces.  When I went to duplicate on my QA server, the restore part succeeded!  The only thing that failed was creating the controlfile which gave me an error about a 9.2 controlfile was not compatible with a 10.2 database.  Seems as though the old init.ora file had a compatible=9.2.0 parameter in it which was preventing my controlfile from being created.  I change the compatible setting to 10.2.0.3 and recreated my controlfile and recovered my db with no problem.

My Oracle Support Analyst suggested I retry the duplicate using a compressed backup set.  Lo and Behold, I was able to duplicate with the compressed pieces.  A theory was tested that the compatible parameter set to 9.2 caused Oracle to think my backup pieces were corrupt.  I changed the parameter back to 9.2 and sure enough, same error during the duplicate.

Just something to be aware of (And no, the lesson is not be stupid and forget to check  your init.ora file).

Interesting Optimizer Result, Part II

My post on a tacky query I did to solve a problem garnered lots of comments.

I can't disagree with Gary Myers who said in the comments that this wasn't a straightforward solution. It's not that I didn't have reservations about implementing a GROUP BY that did nothing.  In fact, when I had to explain it to two different developers I kind of knew I went down the wrong path.

Some of the alternate workarounds and their results were:
1. Gary and Anonymous suggested that I duplicate the business logic of some_funky_package_call in the query itself. Good thought, but because some_funky_package_call is used in multiple places, I wouldn't want to have to go back and fix multiple queries when the underlying logic of some_funky_package_call changed.  In circumstances where some_funky_package_call was only used for this query, that method would work.

2. Craig Martin had two suggestions for me. The first one I tried was using the +MATERIALIZE hint.  This seemed like the easiest to implement, but resulted in an error I've never seen:

ERROR at line 6:
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "MYUSER.SOME_FUNKY_PACKAGE_CALL", line 24
ORA-06512: at line 1

Oh well, the easiest solution isn't necessarily the best anyway.

3. Then I tried Craig's second solution; use ROWNUM to force Oracle to evaluate the inline view first. This actually worked very well.  Both the trace and the elapsed time were at least twice as good as my GROUP BY query.

So a big THANK YOU to Gary, Craig, and Anonymous for helping me out.  I now have a more straightforward solution that works faster than my original "fix".

Interesting Optimizer Result

So, I was working on tuning a query for a developer the other day and found something interesting.  The query was of the form:

SELECT
   a.id,
   b.description,
   some_funky_package_call(a.id, b.description)
FROM
   tableA a,
   tableB b
WHERE a.id = b.id
AND some_funky_package_call(a.id, b.description) <> 0

After I played around with it a bit, I found that the slowest part was using the function call in the WHERE clause.  If the query included the package call in the WHERE clause, the query finished in over an hour.  If the package call was not in the WHERE clause, the query finished in 5 minutes (but did not return the correct results).

No problem, we'll just use an inline view and filter later.  So I came up with:

SELECT id, description,  bal
FROM (
   SELECT
      a.id,
      b.description,
      some_funky_package_call(a.id, b.description) bal
   FROM
      tableA a,
      tableB b
   WHERE a.id = b.id
)
WHERE bal <> 0

Confident in my fondness for inline views, I ran the query fully expecting to get the results back in a few minutes.  Except the query went on, and on, and on for a full 15 minutes before I killed it.  I traced the session and found out the execution profile was not different than my original query.

Eventually, I came up with a solution using HAVING.  My data is such that tableA.id is guaranteed to be unique, so I knew a.id, b.description would also be unique.  I changed my query to:

SELECT id, description, sum(bal)
FROM (
   SELECT
      a.id,
      b.description,
      some_funky_package_call(a.id, b.description) bal
   FROM
      tableA a,
      tableB b
   WHERE a.id = b.id
)
GROUP BY id, description
HAVING sum(bal) <> 0

Since I know that only one row will be returned per a.id, I also knew that SUM() would be summing one row.  The GROUP BY essentially was doing nothing, but the HAVING clause filtered after Oracle already figured out the result set.

Edit: See "Interesting Optimizer Result, Part II" for the resolution.

User Feedback

Tell me, how does this really help me?

Installing Oracle Applications R12.1, Tip #2

Repeat this to yourself three times, "My Oracle Support is my friend!"

Before embarking on any install project, you should always check the current support notes.  For example, I found out from Note 789258.1 that you have to upgrade your version of rapidwiz before you install (x86_64 version).

You can find out what your current rapidwiz version by running ./RapidWizVersion from the startCD/Disk1/rapidwiz directory.  I found out my rapidwiz version was 12.1.1.9 and I needed to apply patch 8626041 to get me to 12.1.1.10 (the most current version as of today).

There's also a couple more patches listed in there, so make sure you read and follow the notes.

Installing Oracle Applications R12.1, Tip #1

Staging your DVDs on disk is a great idea.  Oracle gives you a script (adautostg.pl) to help you create the right structure so that rapidwiz goes smoothly.

However, you should verify that adautostg.pl actually copies all the files on the DVDs correctly.  For example, I had two disks that didn't get completely copied and I received an "AC-00023: Error: Exception: java.io.FeilNotFoundException: filename" when I installed for the first time.  After I compared my media with the staged directories, I found out that some files on my DVD were corrupt and didn't copy.

To fix this issue, I had to download the affected disks from http://edelivery.oracle.com

Help MySQL Stay Free

There's a campaign started by Monty Widenius to save MySQL from the evil clutches of Oracle. You can read about it here.

A Case Against "New and Improved"

My email provider has recently switched their webmail interface from a straight HTML version to an Adobe Flash based version. I don't really use this version a lot since I use Thunderbird as an email client, but it has some nice new features that I guess are helpful if you use the web interface a lot.

I was recently on vacation bouncing between four different hotels without a laptop. I came to appreciate the straight HTML interface more as I moved from place to place. All of the hotels I visited had "business centers" or public computers that you could access (some free, some not). I ran into two major problems with these computers; you can't install an updated version of Flash on a computer where you are only a guest, and Flash operates really slow over satellite connections.

I felt a bit of deja vu as I remembered the transition from Client/Server based programs to three-tier architectures in the earlier part of the decade. Here we are again with a thick version specific client. The more things change, the more they stay the same.

New to the "Top 5" list

A new entry to my all-time Top 5 list of bad advice I got from Oracle Support:

Oracle Support: You should distribute your log groups to multiple disks to avoid contention.
Me: But doesn't Oracle write to only one group at a time?
Oracle Support: No, we're multi-threaded.
Me: So you're telling me that Oracle writes to more than one redo log group at a time?
Oracle Support: Yes.
Me: Please close this TAR, I'll open another one.

Virus'

I've been battling the "Vundo" virus on a machine I was asked to work on for a family member. Somewhere between the 13th and 14th time scanning with Trend Micro's Internet Security, my mind began to wander.

Exactly what type of person writes a virus intent on inflicting aggravation on countless people? Are virus programmers the same people that cut you off in traffic? Are they the type of people that beat puppies?

I bet there are plenty of virus writers who think it's funny when a bus blows up.

I finally came to the conclusion that these people are lower than dog shit. I hope they can carry on a conversation with Saddam Hussein when they meet him.

Pick Two

There's a saying in the computer business, "Good, Fast, Cheap. Pick Two." This saying represents the trade offs we have to make every day on the front lines of IT.

Recently, we have started playing around with MySQL Enterprise Monitor (MEM). MEM is MySQL's offering for an event driven monitoring application similar to Oracle's Enterprise Manager. There are four pieces to MEM; the monitoring agent that gathers that db statistics, the query analyzer that allows you to "trace" queries, the connection proxy that allows you to redirect connections, and the data repository.

Our first stab at configuring MEM was putting the monitoring agent, query analyzer, and query proxy on the host being monitored with the repository db on a different machine. This actually worked pretty well until we had some big queries run through the query proxy. CPU utilization spiked because both the query proxy and the database had to handle the same set of data.

So MySQL Support suggested we move the proxy, query analyzer, and monitoring agent to a separate host. This "middle-tier" would handle the extra CPU load we were generating and keep our db host performance consistent. This solution worked very well, except we weren't getting host related statistics like CPU utilization on the DB host.

No problem, right? We moved the monitoring agent to the db host and kept the proxy and query analyzer on the middle-tier. Now we had our host related statistics, except we couldn't get the query analyzer to work because it has to have a monitoring agent present in order to work properly.

The only solution MySQL Support can offer us at this point is to have two agents monitoring the same database; one on the middle-tier host and one on the db host. OK, we'll do what needs to be done, but I have two fundamental problems with that configuration. First, my database background tells me that duplication will come back and bite you in the ass someday. My other very practical issue is now we are storing twice as much data in the repository for 10% more information.

So while the concept of MEM is great, the actual implementation is a compromise.

Note: Some of the above configurations are not currently supported by MySQL. Use at your own risk.

SGA Latch Contention

We had been getting a pretty persistent "WARNING: inbound connection timed out (ORA-3136)" error thrown to the alert.log on one of our 10.2.0.4 dbs. This error wasn't received once or twice, but about 40 times within a 2 minute window.

I did the research on it and found some sources that suggested my SQLNET.INBOUND_CONNECT_TIMEOUT needed to be adjusted upwards from the default value of 60 to 120 or something higher. I was pretty sure that wasn't the case as it was already set at 120. For the sake of progress, I bumped the timeout to 300 (5 minutes) and monitored closely.

About two days later, we received the same warning message in the alert.log file, except this time it happened about 90 times in a two minute period. Aha, a change. So that told me that something was going wrong during the authentication phase since increasing the connection timeout made the problem worse.

During the last occurrence, I also noticed that the mman process was consuming 100% of the CPU. At the time I wasn't sure if it was a symptom or a byproduct of the problem. However, as soon as the problem went away, the ora_mman process went back down to nearly 0%.

I setup a script to automatically do three systemstate dumps a minute apart when the mman process went to 100%. Oracle Support was able to tell me that during this time period, a bunch of my SQL had been invalidated as was waiting on a latch to be loaded back into the shared_pool. They also indicated that this massive amount of reparsing could happen because somebody did a DDL on a popular object or somebody flushed the shared pool.

I was pretty sure nobody flushed the shared pool, but that got me to thinking what would happen if Oracle shrank my shared pool due to the automatic memory management? I checked v$sga_resize_ops and found that around the times of my warning message, the shared_pool was being re-sized down in size. I brought this up to the support analyst and he suggested I set the shared_pool to the maximum size Oracle had re-sized it to.

That was three days ago, and we haven't had a warning since.

The theory is that while the shared_pool was being re-sized in the SGA, Oracle grabbed a latch. The memory resize operation took a while and while Oracle held that latch, nobody could login. Interesting theory, we'll see if it holds.