R12 Upgrade from 12.1.3 to 12.2.0 on Solaris 10 Sun Sparc machine

[kahmed92]

Hi Erman,
I have reviewed many posts on your site and found it useful. I am also working on EBS upgrade. My environment is
Application version: 12.1.3
Database version: 11.2.0.4
OS: Solaris 10 64bit
Server: Sun Sparc
While upgrading my EBS to 12.2.0, I am facing too many worker failures. Initially I had skipped but were failed after some time.
I have googled and found objects need to recompile to run scripts successfully. But I am unable to recompiled successfully.
Could you tell me step by step procedure to upgrade my environment without facing any issue?

Regards,
Kaleem

[ErmanArslansOracleBlog]

Hi,

You need to follow the document (Oracle Support) step by step and you need to read and understand what you are doing.
12.1 to 12.2 upgrade is a long process and both dbas and functionals should work on it.
I mean, DBAs do some steps, then functionals come into play, then dba do more steps etc.

You can read my book (Practical Oracle E-Business Suite).
There is a seperate subtitle adressing your issue in my book.
"Upgrading from 12.1.3 to 12.2.0"

Amazon Link : https://www.amazon.co.uk/Practical-Oracle-E-Business-Suite-Implementation/dp/1484214234

[ErmanArslansOracleBlog]

General steps can be given as follows;
------------------------------------
Gather schema statistics (required)    
Install JRE on the database tier (conditional)
Reset init.ora parameters (required)
Run Rapid Install (required) (install techstack in upgrade mode)
Perform a system backup
Apply AD 12.2 upgrade driver (required)  
Apply all Consolidated Upgrade Patches (CUPs) (required)
Run the American English upgrade patch driver (required)
Configure Release 12.2 E-Business Suite instance (required).
Update the RDBMS ORACLE_HOME file system with AutoConfig and Clone files (conditional)
Run AutoConfig on the Apps Tier.
Run Rapid Install to configure Release 12.2 E-Business Suite instance
You must run Rapid Install a second time to complete the configuration using Run File System context file.
Upgrade considerations for Add-on Localization products (conditional)
Enabling Online Patching
Apply the latest AD and TXK patchsets
Apply the Latest Oracle E-Business Suite Release Update Pack for Release 12.2 (12.2.5)
Apply latest product patches (required)
Installing NLS upgrade patch driver and NLS Online Help ve post steps.

[ErmanArslansOracleBlog]

As for the documentation, I suggest you to read the following;

 Upgrade Advisor: E-Business Suite (EBS) Upgrade from 12.0/12.1 to 12.2.x (Doc ID 1593640.1)
Useful 12.2 Documents (Doc ID 1585889.1)
Oracle E-Business Suite Release Notes, Release 12.2 Note 1320300.1
Oracle E-Business Suite Release 12.2: Technical Planning, Getting Started, and Go-Live Checklist Note 1585857.1
Database Preparation Guidelines for an E-Business Suite Release 12.2 Upgrade (Doc ID 1349240.1)
Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)
http://docs.oracle.com/cd/E26401_01/doc.122/e48839.pdf
Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Oracle Solaris on SPARC (64-bit) (Doc ID 1330702.1)

[kahmed92]

Hi Erman,

Thanks for your quick and nice reply. I am exactly following same steps but still have no luck. I am facing worker failure issue again i.e.
ATTENTION: All workers either have failed or are waiting:

FAILED: file benzzcncprg00000.ldt on worker 1.
FAILED: file adparepa.ldt on worker 2.
FAILED: file asgprg.ldt on worker 3.
FAILED: file adzdpatch.ldt on worker 4.
FAILED: file arg12acp.ldt on worker 5.
FAILED: file arhzcp.ldt on worker 6.
FAILED: file aprtpcp.ldt on worker 7.
FAILED: file amspprog.ldt on worker 8.
FAILED: file benzzcncprg00003.ldt on worker 9.
FAILED: file aroir12acp.ldt on worker 10.
FAILED: file arobrr_cp.ldt on worker 11.
FAILED: file arocm12acp.ldt on worker 12.
FAILED: file astcpprg.ldt on worker 13.
FAILED: file arhz12acp.ldt on worker 14.
FAILED: file apoie12acp.ldt on worker 15.
FAILED: file ahlprg.ldt on worker 16.
Could you help me to resolve this issue?

Regards,
Kaleem.

[ErmanArslansOracleBlog]

This might be related with a compilation error.
The cause maybe an invalid apps package.

But in order to diagnose, I need to see the following:

1)worker logs
2)the number of the patch that you are trying to apply
3)the FNDLOAD logs (you will se the full path of the FNDLOAD logs in your ad workers' log file.
For example: in your adworker's log file, you will see something like this:

FNDLOAD APPS/***** 0 Y UPLOAD @FND:patch/115/import/erman.lct
Log file: "/u01/ebserman/apps_st/fs1/EBSapps/appl/admin/ebserman/log/US_erman_ldt.log"
Error calling FNDLOAD function

[kahmed92]

Patches4Ver4.z01
Patches4Ver4.z02
Patches4Ver4.z03
Patches4Ver4.zip
Hi Erman,
All logs including Invalid Object List is attached for your review that help you to offer more accurate answer.

Regards,
Kaleem.

[ErmanArslansOracleBlog]

Your problem is:

PLS-00306: wrong number or types of arguments in call to 'LOAD_ROW'

[ErmanArslansOracleBlog]

You have lots of invalids, but probably the following package is important FND_CONCURRENT_PROGRAMS_PKG because it has a LOAD_ROW function.
First, recompile the invalids and see if they can be compiled or not.

and let's focus on 1 of the ldt files and see which package they are using ..

Let's take adparepa.ldt

Send me the lct file for it.
This one ->
/d01/ERPTEST/apps122/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/afcpprog.lct


In the mean time , you can try what is suggested in community->

1)
That is;

cd $FND_TOP/patch/115/sql
sqlplus <as apps>
@AFCPMCPS.pls
sqlplus <as apps>
@AFCPMCPB.pls


2)
REL 12.2 - Patch fails with PLS-00306: wrong number or types of arguments in call to 'LOAD_ROW' (Doc ID 1933052.1)

[kahmed92]

afcpprog.lct
Hi Erman,

I've already run mentioned script that resolve "afgstusr.sql Is Failing During R12.2" error. Should I run it again or not?

Regards,
Kaleem.

[ErmanArslansOracleBlog]

In lct, FND_CONCURRENT_PROGRAMS_PKG is called using the following arguments;

:CONCURRENT_PROGRAM_NAME,
          :APPLICATION_SHORT_NAME,
          :OWNER,
          :USER_CONCURRENT_PROGRAM_NAME,
          :EXEC_EXECUTABLE_NAME,
          :EXEC_APPLICATION_SHORT_NAME,
          :EXECUTION_METHOD_CODE,
          :ARGUMENT_METHOD_CODE,
          :QUEUE_CONTROL_FLAG,
          :QUEUE_METHOD_CODE,
          :REQUEST_SET_FLAG,
          :ENABLED_FLAG,
          :PRINT_FLAG,
          :RUN_ALONE_FLAG,
          :SRS_FLAG,
          :DESCRIPTION,
          :CLASS_APPLICATION,
          :CONCURRENT_CLASS_NAME,
          :EXECUTION_OPTIONS,
          :SAVE_OUTPUT_FLAG,
          :REQUIRED_STYLE,
          :OUTPUT_PRINT_STYLE,
          :PRINTER_NAME,
          :MINIMUM_WIDTH,
          :MINIMUM_LENGTH,
          :REQUEST_PRIORITY,
          :ATTRIBUTE_CATEGORY,
          :ATTRIBUTE1,
          :ATTRIBUTE2,
          :ATTRIBUTE3,
          :ATTRIBUTE4,
          :ATTRIBUTE5,
          :ATTRIBUTE6,
          :ATTRIBUTE7,
          :ATTRIBUTE8,
          :ATTRIBUTE9,
          :ATTRIBUTE10,
          :ATTRIBUTE11,
          :ATTRIBUTE12,
          :ATTRIBUTE13,
          :ATTRIBUTE14,
          :ATTRIBUTE15,
          :OUTPUT_FILE_TYPE,
          :RESTART,
          :NLS_COMPLIANT,
          :CD_PARAMETER,
          :INCREMENT_PROC,
          :ME_EX_ID,
          :ME_AP_ID,
          :ENABLE_TIME_STATISTICS,
          :SECURITY_GROUP_NAME,
          :RESOURCE_CONSUMER_GROUP,
          :ROLLBACK_SEGMENT,
          :OPTIMIZER_MODE,
          :SRS_FLEX,
          :CUSTOM_MODE,
          :LAST_UPDATE_DATE,
          :REFRESH_PORTLET,
          :ACTIVITY_SUMMARIZER,
          :PROGRAM_TYPE,
          :ALLOW_MULTIPLE_PENDING_REQUEST,  
          :TEMPLATE_APPL_SHORT_NAME,
          :TEMPLATE_CODE,
          :MULTI_ORG_CATEGORY
         
          );


However, in the FND_CONCURRENT_PROGRAMS_PKG, I think some of these arguments may be missing.
That is, I think there is a mismatch between FND_CONCURRENT_PROGRAMS_PKG.LOAD_ROW (AFCPMCPB.pls) and afcpprog.lct .

Your lct version is afcpprog.lct 120.7

So what about your FND_CONCURRENT_PROGRAMS_PKG ?
Check it. Check your AFCPMCPB.pls version as well.

**Send me the output of the following;

SELECT t1.name , t1.text
  FROM dba_source t1
WHERE t1.NAME IN
  (
  'FND_CONCURRENT_PROGRAMS_PKG'
  )
  AND t1.line = 2
order by t1.name;

[kahmed92]

/* $Header: AFCPMCPS.pls 120.2.12010000.2 2014/01/17 21:41:21 ckclark ship $ */
/* $Header: AFCPMCPB.pls 120.4 2009/07/22 15:24:29 tkamiya ship $ */

[ErmanArslansOracleBlog]

In Lct file, you have LOAD_ROW in two places.
fnd_executables_pkg.LOAD_ROW;
fnd_concurrent_programs_pkg.LOAD_ROW

Gathered version info:

AFCPMCPS.pls 120.2.12010000.2 2014/01/17 21:41:21 ckclark ship
AFCPMCPB.pls 120.4 2009/07/22 15:24:29 tkamiya ship
afcpprog.lct 120.7 2012/01/13 20:54:45 tkamiya ship

There is a mismatch between your lct version and FND_CONCURRENT_PROGRAMS_PKG version , which lead us to the note: REL 12.2 - Patch fails with PLS-00306: wrong number or types of arguments in call to 'LOAD_ROW' (Doc ID 1933052.1)
However, this note is for EBS 12.2 and it modified lots of PLS versions. That's why I can't recommend it for now.

Do the following -->

1)
cd $FND_TOP/patch/115/sql
sqlplus <as apps>
@AFCPMCPS.pls
sqlplus <as apps>
@AFCPMCPB.pls
select status , object_name from dba_objects where object_name = 'FND_CONCURRENT_PROGRAMS_PKG';
--update me with the output

SQL> alter package FND_CONCURRENT_PROGRAMS_PKG compile body;
SQL> show err
--update me with the output

2)
select status , object_name from dba_objects where object_name = 'FND_EXECUTABLES_PKG';
SQL> alter package FND_EXECUTABLES_PKG compile body;
SQL> show err
--update me with the output

3)

SQL>alter system set events '6550 trace name errorstack level 3';
Then, recreate the error (retry the failed worker)
Check user_dump_dest for the new trace files
Upload the trace file
...disable the trace SQL>alter system set events '6550 trace name context off'

[kahmed92]

Hi Erman,

I'd done as you suggested and output was:
1. Body and specifications are valid
    Compiled successfully.
    No Error
2. Body and specifications are valid
    Compiled successfully.
    No Error
3. There wasn't any dump file generated in diagnostic/user_dump_dest.
Therefore, I was skipped workers and 3 times and after that 10124646 patch was completed but a lot of objects were invalid.
Now, I am applying online patch enablement patch and facing challenges. After more than 40 hours it was still showing 171 jobs were remaining. So, I seach MOS and found parameters that need to change where were: _kgl_large_heap_warning_threshold from 51200K to 83886080 and
max_dump_file_size to unlimited from 20480
after restarting db and applying patch it is showing 40 jobs are remaining after passing 3.5 hours. Now, I am waiting for its completion or error it generates.

Can you give your expert advice to reduce this time and error generation?

Regards,
Kaleem

[ErmanArslansOracleBlog]

If you can give me , the details about that 40 jobs are remaining (I mean adworker status, the files the files the adworkers are working on and the db session waits of these adworkers ), then I can give you my expert on this.
Without diagnostics data, only Oracle can know it :)

[kahmed92]

Hi Erman,
Please attached find patch log.

13543062_logs.zip

Regards,
Kaleem.

[ErmanArslansOracleBlog]

Aha. The "ADZDWRKR.sql"
Actually, we need to see the db session waits of these adworkers, but please have a look at the following thread:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/adop-cutover-td1817.html  --> The same issue here but in a different place (in cutover)..

Again, we need to see the db session wait and the current sql statements of these sessions.
Can you provide that? Using toad or sqldeveloper send me a formatted table that lists the current waits and sql statements of these worker sessions..

[ErmanArslansOracleBlog]

Bytheway, check this document :

Patch 1354362 - 12.2 ONLINE ENABLEMENT PATCH Hangs on Script ADZDWRKR.sql With No Details in The Log and Does Not Progress For Long Time (Doc ID 1946877.1)