upgrade R12.1.3 to R12.2

[satish]

Dear Erman,

We are upgrading R12.1.3 to R12.2

DB 19.17
Application R12.1.3

Step 14: Apply the consolidated upgrade patch and run the 12.2.0 Upgrade (required) from https://docs.oracle.com/cd/E26401_01/doc.122/e73540/T660854T660862.htm#8333937

We dont have preinstall directory under $APPL_TOP/admin/$TWO_TASK.Hence we created preinstall directory and proceed furthur.

Merge the patch drivers in $APPL_TOP/admin/$TWO_TASK/preinstall directory with the 12.2.0 upgrade driver $AU_TOP/patch/115/driver/u10124646.drv

Done:

Change directory to <AU_TOP>/patch/115/driver

$ cd $AU_TOP/patch/115/driver
$ admrgpch -d . -preinstall -master u10124646.drv
Note: The default merged driver by name u_merged.drv is then created in the destination directory that is specified.


We are applying the patch with command $ adpatch options=nocopyportion,nogenerateportion

But failed.

 Assigned: file afgstusr.sql on worker  1 for product fnd username APPLSYS.
   FAILED: file afgstusr.sql on worker  1 for product fnd username APPLSYS.
 Deferred: file afgstusr.sql on worker  1 for product fnd username APPLSYS.               (Deferment number 1 for this job)
 Assigned: file afgstusr.sql on worker  1 for product fnd username APPLSYS.
   FAILED: file afgstusr.sql on worker  1 for product fnd username APPLSYS.
 Deferred: file afgstusr.sql on worker  1 for product fnd username APPLSYS.               (Deferment number 2 for this job)
 Assigned: file afgstusr.sql on worker  1 for product fnd username APPLSYS.
   FAILED: file afgstusr.sql on worker  1 for product fnd username APPLSYS.

ATTENTION: All workers either have failed or are waiting:

           FAILED: file afgstusr.sql on worker  1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.


Logs:
====

        Control
Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120 pl  afgstusr.sql                FAILED
     2  Run       AutoPatch R120 pl                              Wait
     3  Run       AutoPatch R120 pl                              Wait
     4  Run       AutoPatch R120 pl                              Wait
     5  Run       AutoPatch R120 pl                              Wait
     6  Run       AutoPatch R120 pl                              Wait
     7  Run       AutoPatch R120 pl                              Wait
     8  Run       AutoPatch R120 pl                              Wait



sqlplus -s APPS/***** @/u01/CRP/app/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/CRP/app/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql/afgstusr.sql &pw_apps'
Connected.

PL/SQL procedure successfully completed.

DECLARE
*
ERROR at line 1:
ORA-04063: package body "APPS.FND_USER_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APPS.FND_USER_PKG"
ORA-06512: at "APPS.FND_WEB_SEC", line 1410
ORA-06512: at "APPS.FND_WEB_SEC", line 1433
ORA-06512: at "APPS.FND_WEB_SEC", line 2381
ORA-06512: at line 200

We have these invalids:

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
FND_CONCURRENT_PROGRAMS_PKG    PACKAGE BODY
FND_CONC_CLONE                 PACKAGE BODY
FND_FLEX_DESCVAL               PACKAGE BODY
FND_FLEX_EXT                   PACKAGE BODY
FND_FLEX_KEY_API               PACKAGE BODY
FND_FLEX_SERVER                PACKAGE BODY
FND_FLEX_SERVER4               PACKAGE BODY
FND_FLEX_XML_PUBLISHER_APIS    PACKAGE BODY
FND_OID_SUBSCRIPTIONS          PACKAGE BODY
FND_USER_PKG                   PACKAGE BODY
FND_WEB_SEC                    PACKAGE BODY

11 rows selected.


SQL> alter package FND_USER_PKG compile body;

Warning: Package Body altered with compilation errors.

SQL> sho errors;
Errors for PACKAGE BODY FND_USER_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
80/11    PLS-00323: subprogram or cursor 'LOAD_ROW' is declared in a
         package specification and must be defined in the package body

420/10   PLS-00323: subprogram or cursor 'VALIDATESSOLOGIN' is declared in
         a package specification and must be defined in the package body

1236/11  PLS-00323: subprogram or cursor 'REMOVESECURITYATTRS' is declared
         in a package specification and must be defined in the package
         body

1293/12  PLS-00323: subprogram or cursor 'REMOVE_PII_USER' is declared in

LINE/COL ERROR
-------- -----------------------------------------------------------------
         a package specification and must be defined in the package body

1306/10  PLS-00323: subprogram or cursor 'GETEFPASSWORD' is declared in a
         package specification and must be defined in the package body

SQL> alter package FND_WEB_SEC compile body;

Package body altered.

SQL> alter package FND_USER_PKG compile body;

Warning: Package Body altered with compilation errors.

SQL> sho errors;
Errors for PACKAGE BODY FND_USER_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
80/11    PLS-00323: subprogram or cursor 'LOAD_ROW' is declared in a
         package specification and must be defined in the package body

420/10   PLS-00323: subprogram or cursor 'VALIDATESSOLOGIN' is declared in
         a package specification and must be defined in the package body

1236/11  PLS-00323: subprogram or cursor 'REMOVESECURITYATTRS' is declared
         in a package specification and must be defined in the package
         body

1293/12  PLS-00323: subprogram or cursor 'REMOVE_PII_USER' is declared in

LINE/COL ERROR
-------- -----------------------------------------------------------------
         a package specification and must be defined in the package body

1306/10  PLS-00323: subprogram or cursor 'GETEFPASSWORD' is declared in a
         package specification and must be defined in the package body

SQL>


Verified:


SQL> select fnd_web_sec.validate_login('GUEST','ORACLE') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
--------------------------------------------------------------------------------
Y

SQL> select fnd_vault.get('FND','GUEST_USER_PWD') from dual;


FND_VAULT.GET('FND','GUEST_USER_PWD')
--------------------------------------------------------------------------------
GUEST/ORACLE



We found this note id R12 E-Business Suite Applications DBA Patch Installation Failed At Script Afgstusr.sql With Error 'ORA-04063: package body APPS.FND_WEB_SEC has errors' And 'ORA-06508: PL/SQL: could not find program unit being called: APPS.FND_WEB_SEC' (Doc ID 2360954.1)

Patch applied

SQL> select bug_number from ad_bugs where bug_number='25475909';

BUG_NUMBER
------------------------------
25475909

SQL>

Do we need to downlaod the patch and get the adgrants.sql and run it? Please guide

We found one more note id,but unable to open As per Note ID Afgstusr.sql Is Failing During R12.2 Upgrade ( Doc ID 1982679.1 )



Thanks,
Satish

[satish]

Dear Erman,

Note id 1:
======

R12 E-Business Suite Applications DBA Patch Installation Failed At Script Afgstusr.sql With Error 'ORA-04063: package body APPS.FND_WEB_SEC has errors' And 'ORA-06508: PL/SQL: could not find program unit being called: APPS.FND_WEB_SEC' (Doc ID 2360954.1)


Patch is already applied.

SQL> select bug_number from ad_bugs where bug_number='25475909';

BUG_NUMBER
------------------------------
25475909

We tried running adgrants.sql but it didnt help. Faced same error.


Note id 2:
======

We found one more note id,but unable to open As per Note ID Afgstusr.sql Is Failing During R12.2 Upgrade ( Doc ID 1982679.1 )

Could you please guide us.

Thanks,
Satish

[ErmanArslansOracleBlog]

you can compile FND_WEB_SEC.
Your problem is in FND_USER_PKG and especially the following ones are the causes;

PLS-00323: subprogram or cursor 'LOAD_ROW' is declared in a
         package specification and must be defined in the package body

PLS-00323: subprogram or cursor 'VALIDATESSOLOGIN' is declared in
         a package specification and must be defined in the package body

PLS-00323: subprogram or cursor 'REMOVESECURITYATTRS' is declared
         in a package specification and must be defined in the package
         body

PLS-00323: subprogram or cursor 'REMOVE_PII_USER' is declared in
 subprogram or cursor 'GETEFPASSWORD' is declared in a
         package specification and must be defined in the package body

*There is mismatch there. So you need to correct your packages, recreate them with the correct versions.. In this case, your spec and body are not aligned.

See the following oracle community thread for this -->

https://community.oracle.com/mosc/discussion/3035316/5903765-11i-applications-technology-linux-x86-failed-on-failed-file-afgstusr-sql-on-worker-1

This will help you.

[satish]

Thanks for the update. It helped.
We missed to run preinstall=y for latest CUP patch. We are working on it