ad & txk 9 upgrade

[Adhrushta]

Hi erman,

we are facing an error while upgrading txk 9.
we have 11.2.0.3 database and 12.2.0 erp


adop phase=apply patches=25178222,25250923,24481010,25615812 merge=yes restart=yes flags=autoskip

ATTENTION: All workers either have failed or are waiting:

           FAILED: file adsysapp2.sql on worker  1.

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

[ErmanArslansOracleBlog]

send me the worker's log file.

[Adhrushta]

************* Start of AD Worker session *************
AD Worker version: 12.2.0
AD Worker started at: Mon Jul 17 2017 15:33:28

APPL_TOP is set to /u01/apps/fs2/EBSapps/appl

Worker id = 1
Started by: adpatch

NLS_LANG value from the environment is    : AMERICAN_AMERICA.WE8ISO8859P9
NLS_LANG value for this AD utility run is : AMERICAN_AMERICA.WE8ISO8859P9

Connecting to SYSTEM......Connected successfully.

Already asked for the ORACLE username and password
of Application Object Library.
(The given username/password was: APPLSYS/*****)

Connecting to APPS......Connected successfully.


The status of various features in this run of AD Worker is:

                                           <-Feature version in->
Feature                          Active?   APPLTOP    Data model    Flags
------------------------------   -------   --------   -----------   -----------
CHECKFILE                        Yes       1          1             Y N N Y N Y
PREREQ                           Yes       6          6             Y N N Y N Y
CONCURRENT_SESSIONS              No        2          2             Y Y N Y Y N
PATCH_TIMING                     Yes       2          2             Y N N Y N Y
PATCH_HIST_IN_DB                 Yes       6          6             Y N N Y N Y
SCHEMA_SWAP                      Yes       1          1             Y N N Y Y Y
JAVA_WORKER                      Yes       1          1             Y N N Y N Y
CODELEVEL                        Yes       1          1             Y N N Y N Y



Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...

Currently, the following languages are installed:

Code   Language                                Status
----   --------------------------------------- ---------
US     American English                        Base        
TR     Turkish                                 Install      

Reading language information from applTR.txt ...

Your base language will be AMERICAN.

Your other languages to install are: TURKISH

Ready to run jobs.
Setting parallel context: PARALLEL AUTOPATCH AT R120


Time when worker started job: Mon Jul 17 2017 15:33:29


Start time for file is: Mon Jul 17 2017 15:33:29

sqlplus -s APPS/***** @/u01/apps/fs2/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/apps/fs2/EBSapps/appl/admin/adsysapp2.sql &systempwd'
Connected.

PL/SQL procedure successfully completed.

Connected.
USER is "SYSTEM"

--- adsysapp2 started at 17-07-2017 15:33:29 ---

Dropping packages no longer used in Release 11.5 and 12.0

drop package bb_dist
*
ERROR at line 1:
ORA-04043: object BB_DIST does not exist


drop package ak_mls
*
ERROR at line 1:
ORA-04043: object AK_MLS does not exist


drop package ak_org
*
ERROR at line 1:
ORA-04043: object AK_ORG does not exist


drop package ak_org_private
*
ERROR at line 1:
ORA-04043: object AK_ORG_PRIVATE does not exist


drop package ak_org_data
*
ERROR at line 1:
ORA-04043: object AK_ORG_DATA does not exist


drop package ad_mcurr
*
ERROR at line 1:
ORA-04043: object AD_MCURR does not exist



Creating package specifications...


--- Creating AD_APPS_PRIVATE package header (AD common PL/SQL routines)

Creating package ad_apps_private ...

Package created.

--- Creating AD_DDL package header (Runtime DDL Support)

Package created.

--- Creating AD_INST package header (AutoUpgrade Server-Side Support)

Package created.

--- Creating AD_MLS package header (Multi-Lingual Support)

Creating package ad_mls ...

Package created.

--- Creating AD_COMPILE package header

Package created.

--- Creating AD_INVOKER package header

Creating package specification for ad_invoker ...

Package created.

--- Creating AD_CLONE_UTILS package header

Package created.


Creating package bodies...


--- Creating AD_APPS_PRIVATE package body

Creating package body ad_apps_private ...

Package body created.

--- Creating AD_DDL package body

Package body created.

--- Creating AD_INST package body

Package body created.

--- Creating AD_MLS package body

Creating package body ad_mls ...

Package body created.

--- Creating AD_COMPILE package body

Package body created.

--- Creating AD_INVOKER package body

Creating package body for ad_invoker ...

Package body created.


It is OK if the above package body was created with compilation
errors accessing AD_INVOKER_TASKS, FND_ORACLE_USERID,
AD_PARALLEL_COMPILE_ERRORS, or AD_TIMESTAMPS

The errors should be resolved by the time AutoUpgrade completes

--- Creating AD_CLONE_UTILS package body

Package body created.


--- create public synonyms for ad_inst and ad_ddl
        from   xmltable(('"AD_DDL","AD_INST"')) t) list
                                                 *
ERROR at line 6:
ORA-06550: line 6, column 50:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored




Time when worker failed: Mon Jul 17 2017 15:33:29


Time when worker started job: Mon Jul 17 2017 15:33:29


Start time for file is: Mon Jul 17 2017 15:33:29

sqlplus -s APPS/***** @/u01/apps/fs2/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/apps/fs2/EBSapps/appl/admin/adsysapp2.sql &systempwd'
Connected.

PL/SQL procedure successfully completed.

Connected.
USER is "SYSTEM"

--- adsysapp2 started at 17-07-2017 15:33:30 ---

Dropping packages no longer used in Release 11.5 and 12.0

drop package bb_dist
*
ERROR at line 1:
ORA-04043: object BB_DIST does not exist


drop package ak_mls
*
ERROR at line 1:
ORA-04043: object AK_MLS does not exist


drop package ak_org
*
ERROR at line 1:
ORA-04043: object AK_ORG does not exist


drop package ak_org_private
*
ERROR at line 1:
ORA-04043: object AK_ORG_PRIVATE does not exist


drop package ak_org_data
*
ERROR at line 1:
ORA-04043: object AK_ORG_DATA does not exist


drop package ad_mcurr
*
ERROR at line 1:
ORA-04043: object AD_MCURR does not exist



Creating package specifications...


--- Creating AD_APPS_PRIVATE package header (AD common PL/SQL routines)

Creating package ad_apps_private ...

Package created.

--- Creating AD_DDL package header (Runtime DDL Support)

Package created.

--- Creating AD_INST package header (AutoUpgrade Server-Side Support)

Package created.

--- Creating AD_MLS package header (Multi-Lingual Support)

Creating package ad_mls ...

Package created.

--- Creating AD_COMPILE package header

Package created.

--- Creating AD_INVOKER package header

Creating package specification for ad_invoker ...

Package created.

--- Creating AD_CLONE_UTILS package header

Package created.


Creating package bodies...


--- Creating AD_APPS_PRIVATE package body

Creating package body ad_apps_private ...

Package body created.

--- Creating AD_DDL package body

Package body created.

--- Creating AD_INST package body

Package body created.

--- Creating AD_MLS package body

Creating package body ad_mls ...

Package body created.

--- Creating AD_COMPILE package body

Package body created.

--- Creating AD_INVOKER package body

Creating package body for ad_invoker ...

Package body created.


It is OK if the above package body was created with compilation
errors accessing AD_INVOKER_TASKS, FND_ORACLE_USERID,
AD_PARALLEL_COMPILE_ERRORS, or AD_TIMESTAMPS

The errors should be resolved by the time AutoUpgrade completes

--- Creating AD_CLONE_UTILS package body

Package body created.


--- create public synonyms for ad_inst and ad_ddl
        from   xmltable(('"AD_DDL","AD_INST"')) t) list
                                                 *
ERROR at line 6:
ORA-06550: line 6, column 50:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored




Time when worker failed: Mon Jul 17 2017 15:33:30


Time when worker started job: Mon Jul 17 2017 15:33:30


Start time for file is: Mon Jul 17 2017 15:33:30

sqlplus -s APPS/***** @/u01/apps/fs2/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/apps/fs2/EBSapps/appl/admin/adsysapp2.sql &systempwd'
Connected.

PL/SQL procedure successfully completed.

Connected.
USER is "SYSTEM"

--- adsysapp2 started at 17-07-2017 15:33:30 ---

Dropping packages no longer used in Release 11.5 and 12.0

drop package bb_dist
*
ERROR at line 1:
ORA-04043: object BB_DIST does not exist


drop package ak_mls
*
ERROR at line 1:
ORA-04043: object AK_MLS does not exist


drop package ak_org
*
ERROR at line 1:
ORA-04043: object AK_ORG does not exist


drop package ak_org_private
*
ERROR at line 1:
ORA-04043: object AK_ORG_PRIVATE does not exist


drop package ak_org_data
*
ERROR at line 1:
ORA-04043: object AK_ORG_DATA does not exist


drop package ad_mcurr
*
ERROR at line 1:
ORA-04043: object AD_MCURR does not exist



Creating package specifications...


--- Creating AD_APPS_PRIVATE package header (AD common PL/SQL routines)

Creating package ad_apps_private ...

Package created.

--- Creating AD_DDL package header (Runtime DDL Support)

Package created.

--- Creating AD_INST package header (AutoUpgrade Server-Side Support)

Package created.

--- Creating AD_MLS package header (Multi-Lingual Support)

Creating package ad_mls ...

Package created.

--- Creating AD_COMPILE package header

Package created.

--- Creating AD_INVOKER package header

Creating package specification for ad_invoker ...

Package created.

--- Creating AD_CLONE_UTILS package header

Package created.


Creating package bodies...


--- Creating AD_APPS_PRIVATE package body

Creating package body ad_apps_private ...

Package body created.

--- Creating AD_DDL package body

Package body created.

--- Creating AD_INST package body

Package body created.

--- Creating AD_MLS package body

Creating package body ad_mls ...

Package body created.

--- Creating AD_COMPILE package body

Package body created.

--- Creating AD_INVOKER package body

Creating package body for ad_invoker ...

Package body created.


It is OK if the above package body was created with compilation
errors accessing AD_INVOKER_TASKS, FND_ORACLE_USERID,
AD_PARALLEL_COMPILE_ERRORS, or AD_TIMESTAMPS

The errors should be resolved by the time AutoUpgrade completes

--- Creating AD_CLONE_UTILS package body

Package body created.


--- create public synonyms for ad_inst and ad_ddl
        from   xmltable(('"AD_DDL","AD_INST"')) t) list
                                                 *
ERROR at line 6:
ORA-06550: line 6, column 50:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored




Time when worker failed: Mon Jul 17 2017 15:33:30


Manager says to quit.


Time when worker quit: Mon Jul 17 2017 15:33:39

AD Worker is complete.

Errors and warnings are listed in the log file
/u01/apps/fs_ne/EBSapps/log/adop/57/apply_20170717_152906/DEV_erpdev/ADOP_MRG_20170717_1500294597/log/adwork001.log

and in other log files in the same directory.

[Adhrushta]

I logged with my other account,this is sabrina

[ErmanArslansOracleBlog]

This is weird.

"create public synonyms"

SNONYMS??? with S??

The command and in-turn the patch is corrupt.

Send me this failing sql.. I mean the adsysapp2.sql from the patch directory.

I think it is written wrongly.
Let's see if we can modify it..

[Adhrushta]

REM *=======================================================================+
REM |  Copyright (c) 2005, 2016  Oracle and/or its affiliates.              |
REM |  All rights reserved.                                                 |
REM |  Version 12.0.0                                                       |
REM +=======================================================================+
REM | $Header: adsysapp2.sql 120.4.12020000.9 2016/12/20 16:01:46 jwsmith ship $
REM |
REM dbdrv: sql admin top ~FILE none none none sqlplus_driver &phase=first \
REM dbdrv: checkfile:nocheck &systempwd
REM |
REM | FILENAME
REM |   adsysapp2.sql
REM |
REM | DESCRIPTION
REM |   SQL script to create Oracle E-Business Suite packages and other objects
REM |   in the SYSTEM schema.
REM |
REM |   Must be run prior to installing/upgrading Oracle E-Business Suite  
REM |
REM | ARGUMENTS
REM |   You must pass the SYSTEM password as an argument to this script.
REM |
REM | NOTES
REM |   Before you run this script, make sure that:
REM |   - The database is running
REM |   - You are pointing to the right database
REM |     (Check ORACLE_HOME, ORACLE_SID/TWO_TASK settings)
REM |   - No one else is using the database
REM |
REM +=========================================================================+

set verify off

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR  EXIT FAILURE ROLLBACK;


spool adsysapp2.lst

connect SYSTEM/&&1
show user

set head off
set feed off
select '--- adsysapp2 started at '||
        to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')||' ---' from sys.dual;
set head on
set feed on

prompt
prompt Dropping packages no longer used in Release 11.5 and 12.0
prompt

whenever sqlerror continue

drop package bb_dist;
drop package ak_mls;
drop package ak_org;
drop package ak_org_private;
drop package ak_org_data;
drop package ad_mcurr;

whenever sqlerror exit failure rollback

prompt
prompt Creating package specifications...
prompt

REM
REM Create AD_APPS_PRIVATE, AD_DDL, AD_INST, AD_MCURR,
REM AD_MLS package specifications
REM

prompt
prompt --- Creating AD_APPS_PRIVATE package header (AD common PL/SQL routines)

@adaprs.pls

prompt --- Creating AD_DDL package header (Runtime DDL Support)

@adddls.pls

prompt --- Creating AD_INST package header (AutoUpgrade Server-Side Support)

@adinsts.pls

prompt --- Creating AD_MLS package header (Multi-Lingual Support)

@admlss.pls

prompt --- Creating AD_COMPILE package header

@adcmps.pls

prompt --- Creating AD_INVOKER package header

@adinvsps.pls

prompt --- Creating AD_CLONE_UTILS package header

@adclutls.pls

prompt
prompt Creating package bodies...
prompt

REM
REM Create AD_APPS_PRIVATE, AD_DDL, AD_INST, AD_MCURR,
REM AD_MLS package bodies
REM

prompt
prompt --- Creating AD_APPS_PRIVATE package body

@adaprb.pls

prompt --- Creating AD_DDL package body

@adddlb.pls

prompt --- Creating AD_INST package body

@adinstb.pls

prompt --- Creating AD_MLS package body

@admlsb.pls

prompt --- Creating AD_COMPILE package body

@adcmpb.pls

prompt --- Creating AD_INVOKER package body

@adinvspb.pls

prompt --- Creating AD_CLONE_UTILS package body

@adclutlb.pls


REM We need this synonym because we want to run odf from apps and apps
REM must have the ability to create grants by calling this package.

prompt
prompt --- create public synonyms for ad_inst and ad_ddl

declare
cursor ddls is
select 'create or replace public synonym '||list.synname||
       ' for system.'||list.synname as text
from   (select trim(t.column_value) synname
        from   xmltable(('"AD_DDL","AD_INST"')) t) list
where  not exists (select null from dba_synonyms where
                   synonym_name = list.synname and owner='PUBLIC'
                   and table_name=synonym_name and table_owner='SYSTEM');
begin
  for ddl in ddls
  loop
    execute immediate ddl.text;
  end loop;
end;
/

prompt

set head off
set feed off
select '--- adsysapp2 completed at '||
        to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')||' ---' from sys.dual;
set head on
set feed on

prompt

spool off

commit;
exit;

[ErmanArslansOracleBlog]

What do you get, when run this sql in your database with apps?

select 'create or replace public synonym '||list.synname||
       ' for system.'||list.synname as text
from   (select trim(t.column_value) synname
        from   xmltable(('"AD_DDL","AD_INST"')) t) list
where  not exists (select null from dba_synonyms where
                   synonym_name = list.synname and owner='PUBLIC'
                   and table_name=synonym_name and table_owner='SYSTEM');

---*check out the ' and " characters when copying and pasting

[ErmanArslansOracleBlog]

The syntax of this sql is problematic.

[ErmanArslansOracleBlog]

fixing this sql is a developer work.. It has XML related things.
Anyways, I suggest you to take the scripts of these synonym from another environment.

you can use dba_synonyms for this

select * from dba_synonyms where table_name like '%APPS_DDL%'

Check the name of the synonym from the output of the query above.. (check the public one)

After that, take the scripts of the synonym and create it on this database where the patch is failing.

Lastly, comment out the problematic sql in the patch file (adsysapp2.sql) and reapply the patch..

Update me.

[ErmanArslansOracleBlog]

Sorry, I think there may be some other problems as well.

I mean, I tried it in another database and the query didn't encounter any errors.

So , there may be a problem with your AD_DDL and AD_INST packages or something with your database version..

[ErmanArslansOracleBlog]

The statements that should be executed are ->

create or replace public synonym AD_DDL for system.AD_DDL
create or replace public synonym AD_INST for system.AD_INST

However, I didnt' understand why the xmltable operation is not working on your db.
Maybe because your db is 11.2.0.3

anyways, check ad_ddl and ad_inst , ensure they are valid. Then run the above creation scripts with apps user and then you are good to go.

update me.

[Adhrushta]

When i check AD_DDL and AD_INST,they are valid.

I run tese command;

create or replace public synonym AD_DDL for system.AD_DDL
create or replace public synonym AD_INST for system.AD_INST

but patch still getting error,after that I also comment syntax but still same error;

#prompt
#prompt --- create public synonyms for ad_inst and ad_ddl
#
#declare
#cursor ddls is
#select 'create or replace public synonym '||list.synname||
#       ' for system.'||list.synname as text
#from   (select trim(t.column_value) synname
#        from   xmltable(('"AD_DDL","AD_INST"')) t) list
#where  not exists (select null from dba_synonyms where
#                   synonym_name = list.synname and owner='PUBLIC'
#                   and table_name=synonym_name and table_owner='SYSTEM');
#begin
#  for ddl in ddls
#  loop
#    execute immediate ddl.text;
#  end loop;
#end;
#/
#
#prompt

[ErmanArslansOracleBlog]

This is SQL!!!

Why are you using "#" for comments??

[Adhrushta]

sorry this is my bad, I deleted those lines and started apply phase again,now waiting ,I ll update asap when its finish

[Adhrushta]

I didnt get error but,its waiting here since yesterday ;

 Calling /u01/apps/fs2/EBSapps/comn/util/jdk32/jre/bin/java ..

waiting part of the log;

There are now 107 jobs remaining (current phase=A1):
    0 running, 1 ready to run and 106 waiting.

 Assigned: file adctxpkg.sql on worker  1 for product ad  username APPLSYS.

Time is: Wed Jul 19 2017 14:52:12

Completed: file adctxpkg.sql on worker  1 for product ad  username APPLSYS.

Time is: Wed Jul 19 2017 14:52:15


Starting phase 2 (A2): first+10


There are now 106 jobs remaining (current phase=A2):
    0 running, 1 ready to run and 105 waiting.

 Assigned: file ADZDCTXB.pls on worker  1 for product ad  username APPLSYS.

Time is: Wed Jul 19 2017 14:52:15

Completed: file ADZDCTXB.pls on worker  1 for product ad  username APPLSYS.

Time is: Wed Jul 19 2017 14:52:15


Starting phase 3 (A3): first+40


There are now 105 jobs remaining (current phase=A3):
    0 running, 1 ready to run and 104 waiting.

 Assigned: file fndpxutb.pls on worker  1 for product fnd username APPLSYS.

Time is: Wed Jul 19 2017 14:52:15 -------waiting here since yesterday

[ErmanArslansOracleBlog]

Check the db session of that ad worker and see what it is waiting for..

[Adhrushta]

it seems processes are waiting sqlnet message from client

[Adhrushta]

Do I have to apply patches with merge? is it must?

Can i apply one by one without merge?

[ErmanArslansOracleBlog]

then they are not doing anything in db. Check İF they are working in the apps tier. İF they are not doing anything at all, cancel that patch application & retry.

26 Tem 2017 10:42 tarihinde "Abhayankari [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

it seems processes are waiting sqlnet message from client

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/ad-txk-9-upgrade-tp2945p3052.html

To start a new topic under EBS 12.2, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Adhrushta]

I already retried 1000 times, I created SR from Oracle Support and their answer;

Hello,

Unfortunately the error you are facing - "FAILED: file adsysapp2.sql on worker 1 for product admin username APPLSYS" it is due because of the database version - 11.2.0.3. You will have to upgrade to minim 11.2.0.4.

Thank you for your understanding.

Regards,