5 Replies Latest reply: Jan 30, 2013 11:16 AM by 826277 RSS

    Many db objects error after Upgrate EBS 12.1.1 to EBS 12.1.3

    953361
      Hi,

      recently we have upgraded our EBS from 12.1.1 to 12.1.3 and there are almost 777 invalid objects after recompiling using adadmin.

      I tried to recompile INVALID objects using utlrp.sql but I am getting below error.

      SQL> @utlrp.sql
      SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual
      *
      ERROR at line 1:
      ORA-00904: "DBMS_REGISTRY_SYS"."TIME_STAMP": invalid identifier


      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>#
      DECLARE
      *
      ERROR at line 1:
      ORA-04067: not executed, package body "APPS.UTL_RECOMP" does not exist
      ORA-06508: PL/SQL: could not find program unit being called: "APPS.UTL_RECOMP"
      ORA-06512: at line 4


      SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual
      *
      ERROR at line 1:
      ORA-00904: "DBMS_REGISTRY_SYS"."TIME_STAMP": invalid identifier



      PL/SQL procedure successfully completed.

      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>#
      select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist


      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>#
      select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist


      DECLARE
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist
      ORA-06512: at line 31


      BEGIN dbms_registry_sys.validate_components; END;

      *
      ERROR at line 1:
      ORA-06550: line 1, column 7:
      PLS-00201: identifier 'DBMS_REGISTRY_SYS.VALIDATE_COMPONENTS' must be declared
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored


      Please advise for further.
      Thanks in advance,
      Nish