Kevin Kempf's Blog

April 23, 2009

Oracle 11i on 11g – Success!


Upgraded our production RDBMS from 10.2.0.3/64 to 11.1.0.7/64 last weekend, and I thought I’d toss out a few first impressions.

The upgrade itself went about as well as any RDBMS upgrade ever goes.  For the first time I had to account for our disaster recovery (Dataguard) environment in New Jersey.  Of course, I had a few issues that I hadn’t seen in testing.

First, dataguard wouldn’t talk to the remote node, no matter what I tried it failed to authenticate and gave some vague message to that effect.  I fixed this by recreating my password files with orapwd ignorecase (my sys password was mixed case).  By the time I got this figured out, I was only about 1,000 logs behind at the phsyical standby… 3 hours to catch up.

Then I had one custom report which just dogged the system by pegging a CPU at 100% for 5-15 minutes depending upon parameters.  This is a result of the 11g optimizer changes; the interim solution was to use alter session set optimizer_features_enable=’10.2.0.4′; and suddenly, the query runs in seconds again.  Feels like _optimizer_extend_jppd_view_types (a new 11g optimizer feature) is the culprit, but I’m working with support to resolve/confirm this.  Someone told me Oracle boasted they would make their optimizer to be infallible.  Must be a future release.

Here’s the really important take-away from this upgrade:  I’ve just explained every significant issue… meaning there was 1 technical and 1 functional glitch in a major RDBMS upgrade.  This is an amazing success, when historically compared to a 9i to 10g upgrade or even a 10.2.0.X to 10.2.0.X+1 upgrade.  I have to admit that Oracle 11g is a solid release (no, they didn’t pay me to say this), has caused no functional 11i issues, and, on the aggregate, is running everything as good as or better than 10.2 did.  My EM SQL Response time (with a reference collection taken back when I was on 10g) proves this.  I suppose this is a result of some combination of thorough Oracle 11i 11g certification, and our own in-house regression test abilities.   I’d recommend the 11g database to go with the Oracle ERP without reservation.

*edit* Support wasn’t able to “fix” the optimizer bug I encountered on the report, and I closed the ticket. In the end, they were perfectly willing to work with me on the issue, but the information gathering requirements would have taken a small army of DBAs a week to collect.

*edit* I have a record of my 11i/11g upgrade path. If there’s interest in seeing this, leave a comment

Advertisements

15 Comments

  1. Hi Kevin,

    Glad to here that E-Business Database 11g upgrade is smooth with no issues.

    We are on 11.5.10 CU2 E-Business suite version with 10.2.0.3 database version. OS is Solaris 10. We are now planning to to upgrade our database from 10.2.0.3 to 11.1.0.7.

    I am going through net to check if any issues are there after 11g upgrade on E-business suite. I am planning to upgrade one of our dba instance and I need some guidelines from you.

    Do you have steps to follow this upgrade task and any bugs after upgrade we need to fix?

    If possible can you mail it to me @ manishspmv@gmail.com. I will be very thankful to you.

    Regards,
    Manish Patil
    Oracle Apps DBA

    Comment by Manish Patil — July 10, 2009 @ 3:40 pm

  2. Manish,

    I’ll email you the documentation outline I used. The only 2 issues I had after the upgrade which I don’t mention in the blog entry above were 1) Advanced compression and Dataguard bug (working with Oracle for a fix still) and 2) FNDCPASS issue which required me to add the DB init parameter sec_case_sensitive_logon=false (I talked about this on July 8 in this blog). Good luck to you with your upgrade.

    Comment by kkempf — July 13, 2009 @ 8:41 am

  3. Hi Kevin

    Nice catch. We had another issue with our DW build using DB link. We database is creating some table using DB link from another database from view, it pulls some 500k rows.On 10g it was all fine and in satisfactory time. But after 11g Upgarde it slowed to down for ever. Seems setting optimizer_features_enable=’10.2.0.4′ will help. We plan to use this as hint in that particular issue.

    Thanks
    Kumar

    Comment by Kumar Priyadarshi — February 23, 2010 @ 2:49 am

  4. Hi Kevin,

    I am also interested to impliment Oracle 11g with 11i apps. Kindly send me also document for more detail installation steps. Thanks a lot in Advance. Nice blogging. Please keep it up.

    Comment by Jack Nicholson — April 15, 2010 @ 12:07 am

    • I’ll email you the information I have shortly.

      Comment by kkempf — April 15, 2010 @ 12:31 pm

  5. Hi kevin ,

    Would you kindly share the detailed installation steps , I am planning for one

    Regards,
    skumar

    Comment by skumar — December 1, 2010 @ 8:19 pm

    • I’ll see if I still have the documentation and let you know-

      Comment by kkempf — December 2, 2010 @ 12:37 pm

  6. Hi Kevin,

    Congratulations for the upgrade E-Business Database 11g

    I’m on 11.5.10 CU2 E-Business suite version with 9.2.0.6 database version. OS is RHEL 4. We are now planning to upgrade our database from 9.2.0.6 to 11.1.0.7. I need some guidelines from you.

    Do you have steps to follow this upgrade task and any bugs after upgrade we need to fix?

    If possible can you mail it to me @ dani4779@gmail.com. I will be very thankful to you.

    Regards,
    Dani
    Oracle Apps DBA

    Comment by agus yulian mardani — January 12, 2011 @ 9:42 am

    • I’ll send the document I used your way; in reality it’s getting pretty dated now and you will have to double check all the patches to make sure they aren’t superseded. The general framework, however, should still be valid.

      Comment by kkempf — January 12, 2011 @ 11:34 am

      • Hi Kevin,

        Thanks alot for the documentation. it’s awesome and keep blogging. cheers..

        Regards,
        Dani
        Oracle DBA APPS

        Comment by agus yulian mardani — January 12, 2011 @ 11:54 am

  7. Kevin,

    Would you have any BKM’s input, documented migration process for migrating from Oracle 10.2.0.3 on AIX to 11.2.0 Linux.

    thanks,

    Martin

    Comment by MartinG — July 14, 2011 @ 4:55 pm

    • Not sure what BKM’s means? Aside from the generic note I posted going from 10.2 to 11.1, I can’t say I have anything for you. I’ve never done a cross platform migration (and based upon what I’ve read, I don’t envy you), and I went from 10.2 to 11.1 when I did that upgrade.

      Comment by kkempf — July 15, 2011 @ 9:12 am

  8. Hi Kevin,

    I know this is too old post to comment on, But Iam having sam exact performance issue of our custom form after our DB upgrade from 10.2.0.4 to 11GR2. I know I can set “Initialization SQL Statement Custom” with value “Alter system optimizer_features_enable= 10.2.0.4” in profile option at user, responsibility, application and site level to improve performance of queries by using 10.2.0.4 optimizer, But I want to know how you get rid of this performance issue permenantly with out using 10.2.0.4 optimizer hint ?, Also can you tell how we can use same hint in code (exact syntax please). Also I did not find any parameter in my 11g DB with “_optimizer_extend_jppd_view_types”.

    Also, Can you please send me documentation of your upgrade project, so that I can see whether we did not mistakes in setting optimizer parameters…

    Thanks in advance for your help!!

    Thanks
    Naveen Kantamneni

    Comment by Naveen Kantamneni — January 13, 2012 @ 4:22 pm

    • You’re asking a lot of things, about which I think the value of which has changed over time. I should say first that I’m not even running 11GR2 yet, so I can’t speak to it in any precise terms. I would say that, in general, the proper course of action would be to contact Oracle support on all performance issues unless it’s a customization. It’s their job to keep your Ebusiness Suite properly tuned to their (certified) database. When/if you unilaterally apply an optimizer hint, you are no doubt over-riding many benefits of upgrading to 11GR2.

      First of all, how do you get rid of the performance issue permanently without using the 10.2.0.4 optimizer hint? In short: SQL tuning. Way too big of a topic to go into here.

      Can you tell me how we can use the same hint in code? Nope. I don’t know how you would do this, without a hint, derived from your performance tuning.

      I did not find any parameter in my 11g DB with “_optimizer_extend_jppd_view_types” OK. I don’t know what this means.

      I am more than reluctant to provide you with my original project documentation at this point. It’s not because I don’t want to share, it’s because it’s so old it’s worthless, and you’re going to a completely different version of the database than I discussed. Oracle has had 2 years to tweak binaries and provide security patches and there’s been 2 years of OS kernel changes.

      Comment by kkempf — January 17, 2012 @ 11:51 am

      • Thank you very much for your reply Kevin. I find out issue in DB. I saw that “_optimizer_cost_based_transformation” is set to “OFF” which is causing performance issues in our custom code. I set this to “LINEAR” to make optimizer transform query based on cost. Issue Resolved after that.

        –Naveen Kantamneni

        Comment by Naveen Kantamneni — January 17, 2012 @ 12:43 pm


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Blog at WordPress.com.