Tuesday, January 02, 2007

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.

13 comments:

Anonymous said...

Please visit www.oralnx.com
I'm also ebs dba.
Now I am ready for db upgrading from 9208 to 10202 on hpux platform.

Bas Klaassen said...

Nice blog !
Good luck with your upgrade !

Anonymous said...

Hi there
I found your document on google website site that your manage to upgrade your database from 9.2.0.6.0 to 10g but when i go through read me for patch no 4547817 one of the system requirement, required by this patch is oracle database 10g(10.1.0.2 and right now my database version is 9.2.0.6. So just wanted to know how do you manage to upgrade your database though the mentioned steps?
Thanks
Regard Charles
cmchome@yahoo.com
n:b I want to use patch only to upgrade my e-bs database.

Bas Klaassen said...

Charles,

I have sent you an email

Anonymous said...

I'm Anonymous,do you have IM like googletalk or msn
my msn: bandari_cn#hotmail.com
my gt: bandari.huang#gmail.com
please chang '#' to '@'.

Bas Klaassen said...

Anonymous,

I have added you in my google talk

Vikram Das said...

Hi Bas,

When we did the 10g upgrade, we were simultaneously looking at our alert log also. In two of our iterations the compatible init paramater was set to 10.2.0 and no errors were reported when we did startup upgrade. However in our third iteration, following errors appeared in alert log:

TASK_smon_nnnn.trc: (Actual file name changed to generic variables)
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

To get more details about which table was missing we put in this line in the init.ora file:

event="942 trace name ERRORSTACK level 3"
An additional trace file was generated which gave us this detail:

ORA-00942: table or view does not exist
Current SQL statement for this session:
delete from javaobj$ where obj#=:1

The error can be ignored as javaobj$ object is a 10g object and technically speaking we were still in 9i.

I have described this in my blog: http://vikramdas.livejournal.com/3101.html

We are still investigating why the error did not appear in the alert log during the first two iterations.

Any thoughts ?

Vishal said...

Hi
I just want to upgrade my db from 9208 to 10g. As we are going to upgrade our 11.5.9 application to 11.5.10.2 first then we are going to upgrade our database.So can you help me?
My email id is patilvishals@rediffmail.com
Thanks and regards
Vishal

Prince Mishra said...
This comment has been removed by the author.
Prince Mishra said...

how to know in which session i deleted my trace file?
pls help me as soon as posible
my email id is- prince4382@gmail.com
thanks

Anonymous said...

Thanks for the nice post!

Unknown said...

Hi Blas,

I have a oracle applications version 11.5.10 CU1 and db 9.2.0.6 .. i want to upgrade my DB to 10g can you sent me a docs regarding what you are doing to your DB. It is posible that i upgrade my appliction 11.5.10 CU1 to R12 and my DB to 9.2.0.6 - 10g.. what are my requirements thanks

roel

my email address: roel.pascual@gmail.com
rapascual2005@gmail.com
roel.pascual@yahoo.com

Anonymous said...

Hi, I've upgrade an ebs db from 9206 to 10204 whith dbua, when I create the db xml I reeived an error and I can't create it, tomorrow I'll be at work, could you contact me via email please? laura.peppoloni@poste.it
I'll give you the exact error.
thanks
Laura