4 Replies Latest reply: Apr 5, 2012 10:16 AM by Hussein Sawwan-Oracle RSS

    EBS 12.1.3 Upgrade issue

    Syed Qadri
      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
          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
            Syed Qadri
            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
              Syed Qadri
              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
                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