Friday, December 10, 2010

Something Interesting about the 11g Client

If you're just beginning to implement Oracle 11g, you need to be aware of the Automatic Diagnostic Repository (ADR).  It has nothing to do with AWR, ADDM, ASM, ASSM, or any of the other acronyms Oracle has come up with over the last two releases.

In terms of the database, ADR could be a great thing.  It's integration with Enterprise Manager is probably one of the best parts about the feature.  The database manages the retention policy of how long you want to keep these files around based on the scheduler, all things we know how to deal with.

However, you need to be aware that in a client-only configuration (such as a shared client over NFS) there is nothing to manage the logfiles generated by ADR.  The logs get place in /var/tmp and could potentially be across every machine in your environment.  That could be a nightmare if you're running with several hundred clients or multiple domains.  Left unchecked, it could fill up your root disk if that's where you have /var/tmp. I'll leave it to your imagination how I found out about this new feature.

Instead, you might want to turn ADR off by setting DIAG_ADR_ENABLED=off in your sqlnet.ora file.

Wednesday, December 08, 2010

Removing OLAP from 10g

There is a metalink note that describes how to remove the OLAP option if it was installed in your database incorrectly.  See ML Note 739032.1 for details.  Also know that the described procedure doesn't fully remove your OLAP object and that you have to drop some manually according to Note 1060023.1.

Friday, December 03, 2010

Working with Oracle 11g

As I continue to work on Oracle 11g, I am constantly reminded of a quote from Bertrand Russell:
In all affairs it's a healthy thing now and then to hang a question mark on the things you have long taken for advantage.

Wednesday, December 01, 2010

Most Ridiculous Password

I encountered a website yesterday that required a complex password.  It's password rules were:

  • at least 9 characters
  • must include a capitol letter
  • must include three digits
  • must not repeat a letter, but can repeat a number
  • must include one "special" character above a number [!@#$%^&*()]
  • can not include "special" characters that is not above a number
  • can not start with a capitol letter
  • can not start with a number

I am glad they have a "password reset" link.

Tuesday, November 23, 2010

Some Great New Features

I've been off exploring Oracle 11g for a little bit trying to figure out a strategy to upgrade my environment. Oracle 11g has a ton of new features, most of which are now "options" (in other words they cost more money).

When Oracle came out with compression at the segment level in 9i, I thought it was a great feature, at least in theory. But as I got to use compression, I found out that DDL didn't exactly work the way you expected. Then I found out rows had to be added in a certain non-standard way and decided that while the feature was great in theory, in practice it was only about 80% of what I really wanted. Fast forward to 11g and we find out that now segment compression works the way you think it should; except now it costs you extra.

Another feature, Data Guard, is quite frankly awesome. Sure, you have to license both hosts in a data guard setup, but that's the penalty you have to pay to have data in two locations. One of the things that always bothered management was that the standby host is essentially idle just waiting for a disaster that may never come. Sure, you can open the standby in read-only mode, but you have to stop keeping it up-to-date in order to do that. Flash forward to 11g and now you have the ability to have the standby database applying logs but you can also run queries against it!  Wow, this will make my management really happy...until they find out they have to pay to use an incremental enhancement to a basic EE feature.

I should not really be surprised.  Oracle came up with a free statistics collection tool called "StatsPack" way back in 8i.  Using the statistics you could do all sorts of historical trending and figure out why your database was slow after the fact.  Flash forward to 10g and we have this new product called Automatic Workload Repository (AWR) ... but now you have to pay for it.  It's really just StatsPack version 2, but now they need to garner extra revenue for it.

Meanwhile, my support bill is due this month so I can pay for "Software Updates and Support" for another year.

Thursday, March 18, 2010

Job Market

The job market must be getting a little better. In the last weeks I've had three recruiter calls and they actually had jobs to fill.

Thursday, March 11, 2010

Upgrading to R12.1.2, Tip #1

While applying patch 7303033 on top of a 12.1.0 R12 installation, I got the following error in one of my workers:

FNDLOAD APPS/***** 0 Y UPLOAD @FND:patch/115/import/afscursp.lct @JTF:patch/115/import/US/jtfdiagresp.ldt -

Connecting to APPS......Connected successfully.

Calling FNDLOAD function.

Returned from FNDLOAD function.

Log file: /r12u/apps/apps_st/appl/admin/UPG/log/US_jtfdiagresp_ldt.log
Error calling FNDLOAD function.

Time when worker failed: Thu Mar 11 2010 13:45:18

The log from my worker showed:

Current system time is Thu Mar 11 13:45:16 2010


Uploading from the data file /r12u/apps/apps_st/appl/jtf/12.0.0/patch/115/import/US/jtfdiagresp.ldt
Altering database NLS_LANGUAGE environment to AMERICAN
Dumping from LCT/LDT files (/r12u/apps/apps_st/appl/fnd/12.0.0/patch/115/import/afscursp.lct(120.4.12010000.2), /r12u/apps/apps_st/appl/jtf/12.0.0/patch/115/import/US/jtfdiagresp.ldt) to staging tables
Dumping LCT file /r12u/apps/apps_st/appl/fnd/12.0.0/patch/115/import/afscursp.lct(120.4.12010000.2) into FND_SEED_STAGE_CONFIG
Dumping LDT file /r12u/apps/apps_st/appl/jtf/12.0.0/patch/115/import/US/jtfdiagresp.ldt into FND_SEED_STAGE_ENTITY
Dumped the batch (FND_APPLICATION JTF , FND_RESPONSIBILITY JTF DIAG_TOOL_RESP ) into FND_SEED_STAGE_ENTITY
Uploading from staging tables
  Error loading seed data for FND_RESPONSIBILITY:  APPLICATION_SHORT_NAME = JTF, RESP_KEY = DIAG_TOOL_RESP,  ORA-20001: APP-FND-01572: Invalid foreign key data was found for this record.
Value DIAG_TOOL_MENU for column MENU_NAME does not exist in table FND_MENUS_VL.

Concurrent request completed
Current system time is Thu Mar 11 13:45:18 2010

I searched Metalink and didn't find an answer, so I created a TAR.  Oracle Support came back and said this was covered in unpublished note 1066653.1 which suggests you can safely skip this script.

If you are unfamiliar with skipping a step from a failed worker, you can go into adctrl and choose 8 (although there are only 7 menu choices).  That will ask you which worker you want to skip and restart.

Friday, February 26, 2010

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.

Wednesday, February 24, 2010

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.

Tuesday, February 23, 2010

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.

Monday, February 22, 2010

Halleluiah

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

Friday, January 29, 2010

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

Thursday, January 28, 2010

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.

Wednesday, January 27, 2010

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.

Tuesday, January 19, 2010

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

Monday, January 18, 2010

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

Thursday, January 14, 2010

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

Wednesday, January 13, 2010

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.

Wednesday, January 06, 2010

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.

Tuesday, January 05, 2010

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

Saturday, January 02, 2010

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.