Monday, February 27, 2012

Compile 10g Forms/Reports Takes a Very Long Time against 11g database


Hi All

Here the new issue that I found during upgrade to R12 with 11g Database.
As you know the biggest part of patches are generating Forms and Reports in the end of patch installation. This procedure was very slow and takes a lot of time.

After some search in Metalink and Google I found the Doc ID 880660.1 - Compilation Against a 11g Database Hangs or Takes a Very Long Time (Thanks to Harry Tieb).

Action Plan 

1. Apply the database Patch 8560951 on top of your Database. 
   The Patch 8560951 brings modifications in a sensible area and it is needed to use _FIX_CONTROL to enable the fix. 

*** This patch is already included in higher database versions (e.g. 10.2.0.5, 11.2.0.2). For these it's not necessary to install the patch.  But, _FIX_CONTROL='8560951:ON' still needs to be set as the fix is disabled by default. 

2. ALTER SYSTEM SET "_FIX_CONTROL"='8560951:ON'; - This will enable the fix in memory. 
OR add this parameter to init.ora file and restart the database.

3. Compile the Forms/Reports again.
    If needed to restore things as they were, you can similarly turn the fix off with:
    ALTER SYSTEM SET "_FIX_CONTROL"='8560951:OFF';
OR 
Use the following workaround: 

1. Connect to the DB with SQL*Plus as the user who compiles the Forms application 
2. Use the following command to create a synonym all_objects with:
    create synonym all_objects for sys.dba_objects; 
    If it's not working, grant SELECT privelege on sys.dba_objects to the user who will be  compiling the form.
3. Compile the Forms/Reports again.
    If you want at the end, you can drop this synonym with:  drop synonym all_objects;

Good Luck ...

3 comments:

  1. Excellent tip. This was causing a real problem and this solved it. I would point out however that you must be aware of making the change and then restarting the DB with an init.ora file. Make the change in the init.ora file if you are not using an spfile.

    ReplyDelete
  2. Hi Arty,

    I dont have words how to thank you .... I lost 15hrs behind the issue in middle a R12 upgrade and I dont know why this hit was not popping up in metalink or anywhere in any blog.

    Ultimately you saved me thanks a million.

    Appreciate it

    Thanks
    Sridhar

    ReplyDelete
    Replies
    1. Hi Sridhar..
      im realy happy that it helped you..
      Good luck in your upgrde.

      Delete