Re: A tale of data dictionary corruption in Oracle10gR2

Hameed,

i remember somewhere that you can compile all the objects in parallel. maybe it is UTL_RECOMP.RECOMP_PARALLEL( ... ) ;

this considerably reduces the time taken. use as many parallel threads as you can. set job_queue_processes appropriately.

well, pardon my ignorance but i guess an Apps upgrade automatically invalidates all the packages, etc. so, did you first try to recompile them in the normal mode? i.e, do not complie in native mode. just do the normal utlirp.sql...can't remember the parallel equivalent.

once you recompile successfully in normal mode, then you can take a backup of db (if feasible) and then try the native mode recompilation in parallel.

i do remember a client saying native mode did provide some improvement, doesn't hurt to try it out...on a test system. i guess you a test system would really help you out here.

hope that helps,

thanks
anand

On 06/10/06, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
>
> I just wanted to share some information with this list on a corruption
> scenario that I have recently experienced in my lab environment. I
> recently upgraded an Oracle applications 11.5.9 database from version
> 9.2.0.6(64-bit) to version 10.2.0.2 (64-bit) on Solaris9. The upgrade
> went fine. While testing the new plsql setting where the plsql code can
> be natively compiled, I ran into some issues that eventually corrupted
> the data dictionary. This is what happened:
>
> 1. The plsql_code_type parameter was set to "native". The goal was to
> natively generate the plsql code of the APPS schema. A very brief
> description of the APPS schema (for those who do not know what the APPS
> schema is) is that this schema owns all the standard PLSQL code in the
> 11i applications and the count of the combined
> packages/procedures/triggers/functions is in thousands. In my
> environment, the count was 34,647
>
> 2. Because in order to natively generate the PLSQL code, the code needs
> to be re-compiled. The option that I used was to execute procedure
> dbms_utility.compile_schema for the APPS schema as shown below:
> dbms_utility.compile_schema('APPS',true)
>
> The caveat of running this procedure is that it compiles everything
> (unconditionally) including the views.
>
> 3. This procedure first invalidated all the plsql code as well as views.
> It then started to compile them but the compilation process was very
> slow and after approximately 16 hours, I checked and the invalid-count
> showed that it was almost half way done. I decided to terminated the
> process so I issued the "startup force" command. The instance got
> terminated but then would not start in NORMAL mode and kept returning
> ORA-0600 error as shown below:
> ORA-00600: internal error code, arguments:
> [kksfbc-reparse-infinite-loop], [0xFFFFFFFF7A7F62F0], [], [], [], [],
> [], []
> I was able to start the instance only in the RESTRICT mode
>
> 4. At this point there were a lot of invalid packages owned by SYS;
> which was not the case when I had started the compilation of APPS code
>
> 5. I tried to re-compile the INVALID packages owned by SYS manually via
> SQLPLUS but I started to get the same ORA-0600 error
>
> 6. I opened a TAR. The analyst suggested running the catproc.sql script
> which I did but it did not help and the script errored out with message:
> ERROR at line 1:
> ORA-04045: errors during recompilation/revalidation of
> SYS.DBMS_SCHEDULER
> ORA-00942: table or view does not exist
> ORA-00942: table or view does not exist
> ORA-00942: table or view does not exist
> ORA-00942: table or view does not exist
> I tried to also manually re-compile this package but got the
> same error
>
> 7. Oracle has concluded that because I forcefully disrupted the
> compilation process, some of the timestamps in the dictionary dependency
> tree have gone out of synch and that the dictionary is now corrupted.
> They are suggesting that I re-run the upgrade, which I can but they have
> not been able to provide me the answer on how to natively compile the
> PLSQL code in a relatively quick time because we have mission critical
> systems and if the only way to compile PLSQL is via the compile_schema
> procedure then based upon the test timing, it is not feasible
>
> Does anyone has any suggestions/comments on this?
>
> Thanks
> Amir
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
All I need to make a comedy is a park, a policeman and a pretty girl -
Charlie Chaplin

--
http://www.freelists.org/webpage/oracle-l