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.