EBS database upgrade 10GR2..

Here are the steps I have taken to upgrade my EBS database from 9.2.0.6 to 10.2.0.2

1. patch 5478710 (TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O)
[oracle@ebs2 bin]$ ./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS*** STDOUT = /appl/prodcomn/rgf/prod_ebs2/TXK/txkValidateRollup_Tue_Dec_19_23_36_11_2006_stdout.log
Reportfile /appl/prodcomn/temp/txkValidateRollup.html generated successfully.

enable maintenance mode using adadmin
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu

Enter your choice [3] : 1
sqlplus -s &un_apps/***** @/appl/prodappl/ad/11.5.0/patch/115/sql/adsetmmd.sql ENABLESpawned Process 30742
Successfully enabled Maintenance Mode.

After applying the patch make a new appsutil.zip file...
[oracle@ebs1 5478710]$ $ADPERLPRG $AD_TOP/bin/admkappsutil.plStarting the generation of appsutil.zipLog file located at /appl/prodappl/admin/log/MakeAppsUtil_12200852.logoutput located at /appl/prodappl/admin/out/appsutil.zipMakeAppsUtil completed successfully.
Copy appsutil.zip to your new 10g $ORACLE_RDBMS_HOME, once you have created this new $ORACLE_HOME. In the new ORACLE_HOME unzip -o..

run autoconfig on db-tier
[oracle@ebs2 prod_ebs2]$ ./adautocfg.shEnter the APPS user password:AutoConfig is configuring the Database environment...
AutoConfig will consider the custom templates if present. Using ORACLE_HOME location : /ebs/proddb/9.2.0 Classpath : /ebs/proddb/9.2.0/jre/1.4.2/lib/rt.jar:/ebs/proddb/9.2.0/jdbc/lib/ojdbc14.jar
:/ebs/proddb/9.2.0/appsutil/java/xmlparserv2.zip:/ebs/proddb/9.2.0/appsutil/java:/ebs/proddb/9.2.0/jlib/netcfg.jar
Using Context file : /ebs/proddb/9.2.0/appsutil/prod_ebs2.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Updating rdbms version in Context file to db920Updating rdbms type in Context file to 32 bitsConfiguring templates from ORACLE_HOME ...
AutoConfig completed successfully.
The log file for this session is located at: /ebs/proddb/9.2.0/appsutil/log/prod_ebs2/12200014/adconfig.log

Because I work with 11i.AD.I.2, I had to manually regenerate my jar files using adadmin.

2. patch 4653225, 11.5.10 INTEROP PATCH FOR 10GR2
3. 10201_database_linux32.zip
using runInstaller to install the 10GR2 software in it's own ORACLE_HOME /ebs/proddb/10.2.0
4. 10201_companion_linux32.zip
Install 10G products in the 10g ORACLE_HOME (second option in the install menu...)
5. p4547817_10202_LINUX.zip

6. before the database upgrade I first ran the tool utlu102i.sql in the old 9i database. This script wil generate a upgrade report, and will show what changes have to be made before you can upgrade.
SQL> @utlu102i.sql

Oracle Database 10.2 Upgrade Information Utility 12-20-2006 02:33:32
**********************************************************************
Database:
**********************************************************************
--> name: PROD-
-> version: 9.2.0.6.0
--> compatible: 9.2.0
--> blocksize: 8192.
**********************************************************************
Logfiles: [make adjustments in the current environment]**********************************************************************
--> The existing log files are adequate. No changes are required.
....
7. Gather statistics
8. created the SYSAUX tablespace..

CREATE TABLESPACE SYSAUX DATAFILE '/ebs/proddata/sysaux01.dbf' SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL
AUTOALLOCATEBLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

9. copy the initprod.ora to the new ORACLE_HOME and adjust the parameters for 10gR2

10. set the following variables to the new 10g home... - ORACLE_HOME - PATH - ORA_NLS10 - LD_LIBRARY_PATH
11. Startup database in upgrade mode....
SQL> startup upgrade pfile=/ebs/proddb/10.2.0/dbs/initprod.ora
ORA-32006: SQL_TRACE initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1264892 bytes
Variable Size 411042564 bytes
Database Buffers 650117120 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.
SQL> shutdown abort;
ORACLE instance shut down.
There are still wrong parameters in de init.ora, shuwdown and correct the parameterfile...
SQL> startup upgrade pfile=/ebs/proddb/10.2.0/dbs/initprod.ora
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1264892 bytes
Variable Size 411042564 bytes
Database Buffers 650117120 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.
SQL>SPOOL upgrade.log
SQL>@catupgrd.sql

During this sql the following error occurs..
ERROR at line 1:ORA-06553: PLS-213: package STANDARD not accessible
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> SELECT * FROM DBA_OBJECTS WHERE OWNER = 'SYS' 2 AND OBJECT_NAME = 'STANDARD';
Seems status is invalid..The standard package is needed to compile...
SQL> ALTER PACKAGE STANDARD COMPILE;
Still errors occured.

Then commend out the following plsql part in the init.ora
#plsql_optimize_level = 2 #MP
#plsql_code_type = native #MP
#plsql_native_library_dir = /prod11i/plsql_nativelib
#plsql_native_library_subdir_count = 149

restarted the catupgrd.sql and now the error did not occur...

Now the upgrade runs into a
ORA-0600 ORA-00600: internal error code,
arguments: [kqludp2], [0x49A44E2C], [1], [], [], [], [], []

Don't forget to set the following parameter to 0...
aq_tm_processes = 0

finally after a few days with ORA-0600 errors and startingover again..
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2006-12-27 17:33:11
1 row selected.
.Oracle Database 10.2 Upgrade Status Utility 12-27-2006 17:33:12.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.2.0 00:41:17
JServer JAVA Virtual Machine VALID 10.2.0.2.0 00:00:00
Oracle XDK VALID 10.2.0.2.0 00:00:00
Oracle Database Java Packages VALID 10.2.0.2.0 00:00:00
Oracle Text VALID 10.2.0.2.0 00:00:00
Oracle XML Database VALID 10.2.0.2.0 00:00:00
Oracle Real Application Clusters INVALID 10.2.0.2.0 00:00:02
Oracle Data Mining VALID 10.2.0.2.0 00:00:00
OLAP Analytic Workspace VALID 10.2.0.2.0 00:00:00
OLAP Catalog VALID 10.2.0.2.0 00:00:00
Oracle OLAP API VALID 10.2.0.2.0 00:00:00
Oracle interMedia VALID 10.2.0.2.0 00:00:00
Spatial VALID 10.2.0.2.0 00:05:28.
Total Upgrade Time: 01:11:16
PL/SQL procedure successfully completed.

12. Shutdown the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Do not use shutdown abort !!!!!
13. Compile remaining stored PL/SQL and JAVA code
SQL> startup restrict
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1264892 bytes
Variable Size 411042564 bytes
Database Buffers 650117120 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.
SQL>@utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2006-12-27 17:54:11

one hour later, still

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

COUNT(*)
----------
111314

invalid objects to go....
And already...

SQL> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
COUNT(*)
----------
45517

objects compiled...

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2006-12-28 09:30:58
1 row selected.

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

COUNT(*)
----------
230

SQL> select count(*) from dba_objects
2 where status like 'INVALID';
COUNT(*)
----------
238

Still invalid objects...maybe compiling via adadmin will work.

14. run $APPL_TOP/admin/adgrants.sql
[oracle@ebs2 admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 28 13:18:50 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> @adgrants.sql applsys

15. create spfile from pfile
SQL> create spfile from pfile='/ebs/proddb/10.2.0/dbs/initprod.ora';
File created.
16. grant create procedure to ctxsys
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1264892 bytes
Variable Size 411042564 bytes
Database Buffers 650117120 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.
SQL> conn apps/apps
Connected.
SQL> @adctxprv.sql manager CTXSYS
Connecting to SYSTEM
Connected.
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options

17. Next step should be 'run autoconfig'...but where is the context file ??
First create the context file...
[oracle@ebs2 bin]$ perl adbldxml.pl tier=db appsuser=apps appspass=apps
Starting context file generation for db tier..
Using JVM from /ebs/proddb/10.2.0/jre/1.4.2/bin/java to execute java programs..
The log file for this adbldxml session is located at:/ebs/proddb/10.2.0/appsutil/log/adbldxml_12281400.log
Enter the value for Display Variable: >ebs2:0.0
The context file has been created at:/ebs/proddb/10.2.0/appsutil/prod_ebs2.xml

Now run autoconfig
[oracle@ebs2 bin]$ ./adconfig.sh
Enter the full path to the Context file: /ebs/proddb/10.2.0/appsutil/prod_ebs2.xml
Enter the APPS user password:
AutoConfig is configuring the Database environment...
AutoConfig will consider the custom templates if present.
Using ORACLE_HOME location : /ebs/proddb/10.2.0 Classpath : /ebs/proddb/10.2.0/jre/1.4.2/lib/rt.jar:/ebs/proddb/10.2.0/jdbc/lib/ojdbc14.jar:/ebs/proddb/10.2.0/appsutil/java/xmlparserv2.zip
:/ebs/proddb/10.2.0/appsutil/java:/ebs/proddb/10.2.0/jlib/netcfg.jar:/ebs/proddb/10.2.0/jlib/ldapjclnt10.jar
Using Context file : /ebs/proddb/10.2.0/appsutil/prod_ebs2.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Updating rdbms version in Context file to db102Updating rdbms type in Context file to 32 bitsConfiguring templates from ORACLE_HOME ...
AutoConfig completed successfully.The log file for this session is located at: /ebs/proddb/10.2.0/appsutil/log/prod_ebs2/12281412/adconfig.log

18. Gather sys statistics
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1264892 bytes
Variable Size 415236868 bytes
Database Buffers 645922816 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.
SQL> @/appl/prodappl/admin/adstats.sql
Connected.
-----------------------------------------------------
adstats.sql started at 2006-12-28 14:18:20
---
Checking for the DB version and collecting statistics ...
PL/SQL procedure successfully completed.

---------------------------------------------------
adstats.sql ended at 2006-12-28 15:10:41
---
Commit complete.

19. Re-create grants and synonyms using adadmin
Maintain Applications Database Entities
---------------------------------------------------
1. Validate APPS schema
2. Re-create grants and synonyms for APPS schema

Following error occurs...
declare*ERROR at line 1:ORA-04063: package body "SYSTEM.AD_DDL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.AD_DDL"ORA-06512: at line 19
Seems a known problem according to Metalink.
Note 387745.1 brings the sollution...
Run utlrp.sql again....
[oracle@ebs2 admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 28 15:39:13 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1264892 bytes
Variable Size 415236868 bytes
Database Buffers 645922816 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.
SQL> @utlirp.sql
SQL>shutdown
SQL>startup
SQL>@utlrp.sql

Problem solved...
Again run adadmin

20. Startup services.