RE: A tale of data dictionary corruption in Oracle10gR2

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 6 Oct 2006 09:24:13 -0400
Message-ID: <77A4D80DB2ADD74EB5D7F1D31626F0C0038A77A1@usa0300ms03.na.xerox.net>

Hi Anand,
The UTL_RECOMP.RECOMP_PARALLEL only re-compiles invalid objects and not those that are in valid state. The DBUA utility re-compiles all objects during the upgrade process. So, unless I am using the manual method to upgrade the database, I will not have control over the recompilation process. I can try some hack-method to invalidate all the objects first and then use the above procedure to recompile them in parallel but I would like to first look into some clean ways of doing it.  

Amir

---

	From: Anand Rao [mailto:panandrao_at_gmail.com] 
	Sent: Friday, October 06, 2006 9:17 AM
	To: Hameed, Amir
	Cc: oracle-l_at_freelists.org
	Subject: 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

Received on Fri Oct 06 2006 - 08:24:13 CDT