This discussion is archived
4 Replies Latest reply: Apr 5, 2012 8:16 AM by Hussein Sawwan-Oracle RSS

EBS 12.1.3 Upgrade issue

812503 Newbie
Currently Being Moderated
Hi,

I installed 12.1.1 with the Vision Database on my machines which has a 64 bit Linux 5 update 5 operating system and everything went fine. After that I decided to upgrade to 12.1.3. I installed the following patches in order they are given below :

1) R12.AD.B.DELTA.3 Patch 9239089
2) Patch 9239090
3) Patch 9239095
4) 9817770:R12.ATG_PF.B POST-R12.ATG_PF.B.DELTA.3CONSOLIDATED PATCH
5) 9966055:R12.FND.B [TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED

The only issues I had was during the compilation of one form library I got an error in Patch 923090 and I said to continue. The patches completed successfully (I think) since I can log into applications. My issue is I have a lot of invalid objects. I tried to manually compile them but failed then followed one of the other threads to run utlrp.sql and utlirp.sql scripts as per (R12.1.1 - Invalid Objects After Patching [ID 1093163.1])

I have followed that document and tried to run but my invalid count stays the same. Here is the snapshot when I query the database :

SQL> SELECT COUNT(*), OWNER FROM DBA_OBJECTS WHERE STATUS='INVALID'
2 GROUP BY OWNER;

COUNT(*) OWNER
---------- ------------------------------
1 RE
2 CA
4 PUBLIC
1 HERMAN
228 APPS
2 FLOWS_010500

*********************************************************************
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2012-04-05 07:23:05

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2012-04-05 07:24:29

DECLARE
*
ERROR at line 1:
ORA-00904: "FALSE": invalid identifier
ORA-06512: at line 13


DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
4

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
4


PL/SQL procedure successfully completed.

Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.


However for some reason the job utlrp.sql only shows 4 invalids as shown when I run it. But the number changes once the script is finished and I am not sure why. If I query SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6) now after utlrp.sql has finished running I get the below

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

COUNT(*)
----------
234

But if I query the same query while utlrp.sql is running I get a number as low as 10 I have seen but once the script finishes it goes back up. I don't know what to do. I have run autoconfig on both the db and apps tier. Is there anything anyone can suggest me ? I am totally lost on this.

Thanks..
  • 1. Re: EBS 12.1.3 Upgrade issue
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Did you try to compile invalid objects via adadmin utulity? What

    select count(*) from dba_objects where status='INVALID' before and after run compile invalid objects via adadmin utulity

    Regard
    Helios
  • 2. Re: EBS 12.1.3 Upgrade issue
    812503 Newbie
    Currently Being Moderated
    Hi Helois,

    Yes I did try the adadmin utility it's the same result. I think I know now maybe the issue is related to this :

    Many  db  objects error after Upgrate EBS 12.1.1 to EBS 12.1.2(7303033)

    I now realize I didn't set the property for disable_fast_validate=TRUE.

    My database version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production so I think I need to set that and then run utlrp.sql again ? I am going to try it and post the results here.

    Thanks
  • 3. Re: EBS 12.1.3 Upgrade issue
    812503 Newbie
    Currently Being Moderated
    Hi I did set the disablefast_validate=TRUE and re-ran only utlrp.sql to re-compile now I have a lot more packages compiled but I still have some invalids. I just want to know can I ignore these and move on or are they important packages / objects in APPS which may impact functionality ?

    below is the results of the invalid objects.
    Thanks

    OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
    PUBLIC,WWV_FLOW_LIST_OF_VALUES_DATA,,1006661,,SYNONYM,09-FEB-07,09-FEB-07,2007-02-09:00:33:03,INVALID,N,N,N,1,ORA$BASE
    PUBLIC,WWV_FLOW_LISTS_OF_VALUES$,,1006663,,SYNONYM,09-FEB-07,09-FEB-07,2007-02-09:00:33:03,INVALID,N,N,N,1,ORA$BASE
    PUBLIC,WWV_FLOW_GENERIC,,1006687,,SYNONYM,09-FEB-07,09-FEB-07,2007-02-09:00:33:03,INVALID,N,N,N,1,ORA$BASE
    PUBLIC,WWV_FLOW_FIELD_TEMPLATES,,1006699,,SYNONYM,09-FEB-07,09-FEB-07,2007-02-09:00:33:03,INVALID,N,N,N,1,ORA$BASE
    RE,RE_PROFILER,,1207268,0,PACKAGE BODY,17-NOV-04,05-APR-12,2012-04-05:10:52:51,INVALID,N,N,N,2,
    HERMAN,RDT_1,,1713973,1713973,TABLE,18-JAN-06,18-JAN-06,2006-01-18:13:18:02,INVALID,N,N,N,1,
    APPS,XLA_00707_AAD_C_000026_PKG,,2362389,0,PACKAGE BODY,03-AUG-07,05-APR-12,2012-04-05:10:52:52,INVALID,N,N,N,2,
    APPS,XLA_20065_AAD_C_000030_PKG,,2370236,0,PACKAGE BODY,16-AUG-07,05-APR-12,2012-04-05:10:53:05,INVALID,N,N,N,2,
    APPS,FSAH_DUPLICATE_PKG,,2385307,0,PACKAGE BODY,23-AUG-07,05-APR-12,2012-04-05:10:53:08,INVALID,N,N,N,2,
    APPS,XLA_00707_AAD_C_000044_PKG,,2661674,0,PACKAGE BODY,14-JAN-08,05-APR-12,2012-04-05:10:53:08,INVALID,N,N,N,2,
    APPS,MSD_DEM_OBI_DEMANTRA_MV,,3255317,,MATERIALIZED VIEW,09-JUL-08,09-JUL-08,2008-07-09:09:14:28,INVALID,N,N,N,19,
    CA,F,,3260665,3260665,TABLE,24-SEP-08,24-SEP-08,2008-09-24:16:00:14,INVALID,N,N,N,1,
    CA,G,,3260683,3260683,TABLE,24-SEP-08,24-SEP-08,2008-09-24:16:00:15,INVALID,N,N,N,1,
  • 4. Re: EBS 12.1.3 Upgrade issue
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Search MOS for those invalid objects and you should get couple of hits. For example (XLA Invalid Body Packages after Patching [ID 967352.1]).

    You may also compile those invalid objects manually to get more details about the error -- Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 [ID 1325394.1]

    Thanks,
    Hussein

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points