Blog for DBA Consultants: June 2010

Hi Folks, Last month I was busy in upgrade project. Upgrade EBS 11i (11.5.10.2) to release 12 (12.1.1) with 64bit oracle 11g (11.1.0.7) database. The following were the steps that were performed. The steps are just a rough run but if you want to see the exact phases and tasks then please refer to my earlier post 'Tasks and Phases to Upgrade Oracle Applications 11i to r12 (12.1.1)'. For any queries please email me at masamimalik@yahoo.com or call me on my cell 0097433164959.

Also refer to the oracle material from the following link that gives the details on how to upgrade.

http://download.oracle.com/docs/cd/B53825_05/current/acrobat/121upgrade.pdf

1) clone the apps tier and the db tier from PROD to TEST (perform this on TEST DB)

2) Apply the ATG RUP 6
Apply these patches on 11.5.10.2 using 9i database
4252319 - 06:48 PM - 06:50 (total 2 min)
5903765 - 06:52 PM - 08:30 PM
starts with 315 jobs
there will be 14000 invali objects
while running autoconfig , there were file locks .. so press ctrl +c and then reboot the server + then restart the adpatch session
5903765_AR
4583125
7705743
before upgrade and running the utlu111i.sql copy the timezone files from the patch or from the oacore directory of any existing 9.2.0 oracle home under f:
3)Upgrade the database to 11gR1 on the same TEST DB server
4)convert to multiorg
5) Install the filesystem pointing to the same driver
6) Apply these patches
6767273
7461070
7) The upgrade American english driver patch starts with 119322 jobs

workers will fail at

Time is: Sat May 15 2010 02:36:41

1) There are now 119259 jobs remaining (current phase=A0):
4 running, 60 ready to run and 119195 waiting.

FAILED: file funmochk.sql on worker 2 for product fun username FUN

Reason:

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\fun\12.0.0\patch\115\sql\funmochk.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.DECLARE
*
ERROR at line 1:
ORA-20000: Multi-Org architecture is not enabled. Please convert the target
instance to enable Multi-Org before upgrading to Release 12
ORA-06512: at line 19

Solution - enable multiorg

2) Time is: Mon May 17 2010 12:08:34

There are now 38169 jobs remaining (current phase=A144):
0 running, 0 ready to run and 38169 waiting.

ATTENTION: All workers either have failed or are waiting:

FAILED: file MSDODPCODE.sql on worker 1.

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\msd\12.0.0\patch\115\sql\MSDODPCODE.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.begin
*
ERROR at line 1:
ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE
using the specified access mode.
ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 984
ORA-06512: at "SYS.DBMS_AW", line 937
ORA-33262: Analytic workspace ODPCODE does not exist.
ORA-06512: at "SYS.DBMS_AW", line 908
ORA-06512: at "APPS.MSD_AW_LOADER", line 10
ORA-06512: at line 3
FAILED: file pqbgtasd.sql on worker 4.

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\pqh\12.0.0\patch\115\sql\pqbgtasd.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.
PL/SQL procedure successfully completed.

Commit complete.

API User Hook Pre-Processor One Module Report
=============================================
Created on 2010/05/17 12:08:31 (YYYY/MM/DD HH:MM:SS)

CREATE_BUDGET(Business Process API) successful.

Module Name: UPDATE_BUDGET
Module Type: Business Process API

Hook Package: PQH_BUDGETS_BK2
-----------------------------

Hook: Before Process
--------------------
(XX_RETURN_FROM_LEAVE_PKG.LEAVE_DEL) There is a parameter to the call procedure
which is not available at this hook. Check your call procedure parameters. Code
to carry out this hook call has not been created.

DELETE_BUDGET(Business Process API) successful.
BEGIN hr_api_user_hooks_utility.clear_hook_report; END;

Reason : hookup
solution delete it .

3) Time is: Tue May 18 2010 01:20:39

ATTENTION: All workers either have failed or are waiting:

FAILED: file czhist.sql on worker 1.
Start time for file is: Tue May 18 2010 01:11:38

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\cz\12.0.0\patch\115\sql\czhist.sql &un_cz

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.
PL/SQL procedure successfully completed.

BEGIN FND_STATS.GATHER_COLUMN_STATS('CZ','CZ_DEVL_PROJECTS','DELETED_FLAG'); END;

*
ERROR at line 1:
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old. Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "APPS.FND_STATS", line 664
ORA-06512: at "APPS.FND_STATS", line 2497
ORA-06512: at line 1

4)

Time is: Sun May 16 2010 03:04:48

ATTENTION: All workers either have failed or are waiting:

FAILED: file csppl.odf on worker 1.
FAILED: file csprecm.odf on worker 2.
FAILED: file csdcsdv.odf on worker 3.
FAILED: file csmcsma.odf on worker 4.

Time is: Sun May 16 2010 09:24:04
There are now 81692 jobs remaining (current phase=A70):
3 running, 115 ready to run and 81574 waiting.

ATTENTION: All workers either have failed or are waiting:

FAILED: file csppl.odf on worker 1.
FAILED: file csprecm.odf on worker 2.
FAILED: file csdcsdv.odf on worker 3.
FAILED: file csmcsma.odf on worker 4.

ATTENTION: All workers either have failed or are waiting:

FAILED: file bivdelmenu.sql on worker 1.
FAILED: file BIVMEDE71.sql on worker 2.
FAILED: file amwmndel.sql on worker 3.
FAILED: file arhdelmn.sql on worker 4.

Reason :Start time for file is: Sun May 16 2010 15:52:28

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\amw\12.0.0\patch\115\sql\amwmndel.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.BEGIN
*
ERROR at line 1:
ORA-04063: package body "APPS.FND_FUNCTION_SECURITY" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APPS.FND_FUNCTION_SECURITY"
ORA-06512: at line 2

ATTENTION: All workers either have failed or are waiting:

FAILED: file bivdelmenu.sql on worker 1.
FAILED: file BIVMEDE71.sql on worker 2.
FAILED: file amwmndel.sql on worker 3.
FAILED: file arhdelmn.sql on worker 4.

Time is: Sun May 16 2010 22:52:18

ATTENTION: All workers either have failed or are waiting:

FAILED: file IGSPR4700925.sql on worker 1.

Reason :
sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\igs\12.0.0\patch\115\sql\IGSPR4700925.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system. WHERE CURRENT OF c_pr_inst_sta_ref;
*
ERROR at line 29:
ORA-06550: line 29, column 26:
PLS-00404: cursor 'C_PR_INST_STA_REF' must be declared with FOR UPDATE to use
with CURRENT OF

solution : invalid objects and disable and enable the events parameter in the init.ora file

Time is: Sat May 15 2010 21:34:01

FAILED: file jtf_diagnostic_report_s.xdf on worker 3 for product fnd username APPS.

Time is: Sun May 16 2010 00:52:53

FAILED: file PO_VENDORS_OBS_F1.xdf on worker 2 for product po username APPS.

Time is: Sun May 16 2010 00:52:54

FAILED: file PO_VENDOR_CONTACTS_OBS_N4.xdf on worker 3 for product po username APPS.

Time is: Sun May 16 2010 00:53:40

FAILED: file jtfiaitn.sql on worker 4 for product jtf username JTF.

Reason :
Start time for file is: Sun May 16 2010 00:53:31

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\jtf\12.0.0\patch\115\sql\jtfiaitn.sql &un_jtf &pw_jtf

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.Connected.
DECLARE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SQL", line 1053
ORA-06512: at line 35

Time is: Sun May 16 2010 02:03:29

FAILED: file eamasso.odf on worker 3 for product eam username EAM.
Time is: Sun May 16 2010 02:03:48

FAILED: file dppobjects.odf on worker 4 for product dpp username DPP.

Time is: Sun May 16 2010 02:05:22

FAILED: file egov083.odf on worker 3 for product ego username EGO.

Start time for statement below is: Sun May 16 2010 02:02:04

D Worker error:
The following ORACLE error:

ORA-00600: internal error code, arguments: [kksfbc-reparse-infinite-loop], [0x549E78EC], [], [], [], [], [], [], [], [], [], []

occurred while executing the SQL statement:

CREATE OR REPLACE FORCE VIEW APPS.CSM_REQ_LINES_V ("ACCESS_ID",
"REQUIREMENT_LINE_ID", "REQUIREMENT_HEADER_ID", "INVENTORY_ITEM_ID",
"ITEM_NAME", "ITEM_DESCRIPTION", "UOM_CODE", "REQUIRED_QUANTITY",
"SHIP_COMPLETE_FLAG", "REVISION", "SOURCE_ORGANIZATION_ID",
"SOURCE_SUBINVENTORY", "ORDERED_QUANTITY", "ORDER_LINE_ID",
"RESERVATION_ID", "LOCAL_RESERVATION_ID", "ORDER_BY_DATE", "ARRIVAL_DATE",
"SHIPPING_METHOD_CODE", "LAST_UPDATE_DATE", "ORDER_NUMBER",
"SOURCED_FROM", "PO_REQUISITION_NUMBER", "ORDER_STATUS",
"ATTRIBUTE_CATEGORY", "ATTRIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3",
"ATTRIBUTE4", "ATTRIBUTE5", "ATTRIBUTE6", "ATTRIBUTE7", "ATTRIBUTE8",
"ATTRIBUTE9", "ATTRIBUTE10", "ATTRIBUTE11", "ATTRIBUTE12", "ATTRIBUTE13",
"ATTRIBUTE14", "ATTRIBUTE15", "LIKELIHOOD", "ITEM_SCRATCHPAD",
"SCHEDULE_ARRIVAL_DATE", "WAYBILL_NUMBER", "ACTUAL_SHIPMENT_DATE",
"SHIPPING_METHOD_MEANING", "SOURCED_FROM_DISP", "SOURCE_ID", "CREATED_BY",
"LAST_UPDATED_BY") AS SELECT acc.access_id , lines.requirement_line_id ,
lines.requirement_header_id , lines.inventory_item_id ,
csm_util_pkg.item_name(msi.concatenated_segments) AS item_name,
msi.description AS item_description, lines.uom_code ,
lines.required_quantity , lines.ship_complete_flag , lines.revision ,
lines.source_organization_id , lines.source_subinventory ,
lines.ordered_quantity , lines.order_line_id , lines.reservation_id ,
lines.local_reservation_id , lines.order_by_date ,
nvl(oe_lines.actual_arrival_date,oe_lines.schedule_arrival_date)
arrival_date, oe_lines.shipping_method_code, lines.last_update_date,
oe_headers.order_number, lines.sourced_from, null po_requisition_number,
CSP_PICK_UTILS.get_order_status (oe_lines.LINE_ID,
oe_lines.FLOW_STATUS_CODE) order_status, lines.attribute_category,
lines.attribute1, lines.attribute2, lines.attribute3, lines.attribute4,
lines.attribute5, lines.attribute6, lines.attribute7, lines.attribute8,
lines.attribute9, lines.attribute10, lines.attribute11, lines.attribute12,
lines.attribute13, lines.attribute14, lines.attribute15 ,
lines.LIKELIHOOD, lines.ITEM_SCRATCHPAD,oe_lines.schedule_arrival_date,
csp_pick_utils.get_attribute_value('WAYBILL') waybill_number,
oe_lines.actual_shipment_date, flv.meaning shipping_method_meaning,
fl.meaning SOURCED_FROM_DISP, rld.source_id,
CSM_UTIL_PKG.GET_NUMBER(lines.created_by) AS CREATED_BY,
CSM_UTIL_PKG.GET_NUMBER(lines.last_updated_by) AS LAST_UPDATED_BY FROM
csm_req_lines_acc acc, csp_requirement_lines lines, oe_order_lines_all
oe_lines, oe_order_headers_all oe_headers, mtl_system_items_b_kfv msi,
csp_req_line_details rld , fnd_lookup_values flv, fnd_lookups fl WHERE
acc.user_id = asg_base.get_user_id AND acc.requirement_line_id =
lines.requirement_line_id AND (lines.sourced_from = 'INVENTORY' OR
lines.sourced_from is null) AND lines.requirement_line_id =
rld.requirement_line_id(+) AND rld.source_id = oe_lines.line_id(+) AND
oe_lines.header_id = oe_headers.header_id(+) AND msi.inventory_item_id =
lines.inventory_item_id AND msi.organization_id =
nvl(lines.source_organization_id,
csm_profile_pkg.get_organization_id(acc.user_id)) and flv.LOOKUP_TYPE(+) =
'SHIP_METHOD' and flv.LANGUAGE(+) = asg_base.get_language and

Resetting events to create views with compilation ...

AD Worker error:
The following ORACLE error:

ORA-01012: not logged on

occurred while executing the SQL statement:

alter session set events '10520 trace name context off'

Solution : execute the view creation manually

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\igs\12.0.0\patch\115\sql\IGSAD4287260A.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.DECLARE
*
ERROR at line 1:
ORA-20100: Error due to ORA-06502: PL/SQL: numeric or value error: character to
number conversion error
ORA-06512: at line 146

After resolving the above issue did the database upgrade to 11g with database migration to 64bit.
============
custom 6i forms and reports files should be migrated to the new 10g forms reports format using the following script,

frmcmp userid=apps/password@prodsid module=XXPER_ENTITLEMENT.fmb batch=yes module_type=form

::compile_forms.bat
cls
Echo compiling Forms....
for %%f IN (*.fmb) do frmcmp userid=apps/password@testerp module=%%f batch=yes module_type=form compile_all=yes window_state=minimize
ECHO FINISHED COMPILING

REM WINDOWS COMPILE REPORT
::compile_report.bat
cls
Echo compiling Report .....
for %%f IN (*.rdf) do rwconverter userid=apps/password@testerp batch=yes source=%%f stype=rdffile DTYPE=rdffile compile_all=yes OVERWRITE=yes logfile=replog.txt
ECHO FINISHED COMPILING
=============
STEPS FOR DATABASE UPGRADE--ROUGH

oracle home installation for 11.1.0.7 -
Database binary installation -
started at 07:15 PM -07:32 PM

example installation -
started at 07:34 - 07:38 PM

Oracle Database 11.1 Post-Upgrade Status Tool 05-12-2010 05:32:04
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.7.0 01:04:22
JServer JAVA Virtual Machine
. VALID 11.1.0.7.0 00:15:13
Oracle Real Application Clusters
. INVALID 11.1.0.7.0 00:00:04
OLAP Analytic Workspace
. VALID 11.1.0.7.0 00:00:41
OLAP Catalog
. VALID 11.1.0.7.0 00:01:59
Oracle OLAP API
. VALID 11.1.0.7.0 00:00:44
Oracle XDK
. VALID 11.1.0.7.0 00:03:19
Oracle Text
. VALID 11.1.0.7.0 00:02:40
Oracle XML Database
. VALID 11.1.0.7.0 00:04:36
Oracle Database Java Packages
. VALID 11.1.0.7.0 00:00:46
Oracle Multimedia
. VALID 11.1.0.7.0 00:05:41
Spatial
. VALID 11.1.0.7.0 00:08:17
Gathering Statistics
. 00:21:59
Total Upgrade Time: 02:10:29

the job was started completed at COMP_TIMESTAMP UPGRD__BGN 2010-05-12 03:14:13 and completed at 05:32 AM

so the total time is - 2 1/2 hours

2 nd job script ran from 05:32 to 08:15

- 2 1/2 hours

so total 5 hours for this job .

====================
IMPORTANT FOR PROD ATG UPGRADE ---> ROUGH

6502082 / AR
5989593 / AR post step adgrants
4252319
3218526 / AR
3854951 / AR
3761838 / AR
4206794 / AR
./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps
5903765 /AR
4619025
4583125

Run Autoconfig

CREATE TABLESPACE APPS_TS_TOOLS DATAFILE
'datafile_name.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Copy the following font files from $FND_TOP/resource to the /lib/fonts directory (under OA_JRE_TOP and JRE_TOP) on all Web and concurrent nodes.
If $FND_TOP/resource does not contain the font files, apply patch 3639533: Albany (Display) Font 4.02 Release.
The font files are:
ALBANYWT.ttf - "single-byte" characters only
ALBANWTJ.ttf - Unicode font with Japanese Han Ideographs
ALBANWTK.ttf - Unicode font with Korean Han Ideographs
ALBANWTS.ttf - Unicode font with Simplified Chinese Han Ideographs
ALBANWTT.ttf - Unicode font with Traditional Chinese Han Ideographs

select bug_number,CREATION_DATE from ad_bugs where bug_number in ('6502082','5989593','4252319','3218526','3854951','3761838','4206794','5903765','4619025','4583125');

BUG_NUMBER CREATION_
------------------------------ ---------

4252319 10-MAY-10
4583125 11-MAY-10
5903765 10-MAY-10

select bug_number,CREATION_DATE from ad_bugs where bug_number in ('3639533');
===========********===========
Post upgrade tasks has many steps while one of them is as follows,

Human Resources
Important: Customers using Oracle iRecruitment and running database
11gR1 version 11.1.0.7 must add the following line to their database
initialization file:
event="31151 trace name context forever, level 0x100"
Setting this event provides a fix for the issue reported in bug 6952870.
===============================
If you face any issues in upgrade please don't hesitate to contact me at masamimalik@yahoo.com or call me on my cell 009743164959

Blog for DBA Consultants

Monday, June 21, 2010

Upgrade steps from 11i to r12 with db 11g - rough

Tasks and Phases to Upgrade Oracle Applications 11i to r12 (12.1.1)

Followers