7 Replies Latest reply: Aug 30, 2013 7:05 AM by Srini Chavali-Oracle RSS

    more invalid objects after upgraded to 11.2.0.2

    Sivamani

      Hi all,

       

      I upgraded my 12.1.3 EBS oracle database from 11.2.0.1 to 11.2.0.2.It was successful.

       

      After the upgrade i ran utlrp,sql . It shows

       

       

       

      TIMESTAMP

      --------------------------------------------------------------------------------

      COMP_TIMESTAMP UTLRP_END  2013-08-30 01:43:52

       

       

      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

      -------------------

                       57

       

       

      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

      ---------------------------

                                8

       

       

      And when query to see the invalid objects in apps schema it showing 3200 invalid objects.

       

      select object_name from dba_objects where status like 'INVALID' and owner like 'APPS'

       

      3200

       

       

      Please can any one help me how to resolve this issue.

       

      Thanks,

        • 1. Re: more invalid objects after upgraded to 11.2.0.2
          TSharma-Oracle

          Run utlrp.sql 1 more time and paste the output of the following

           

          select,object_name,object_type,status from dba_objects where status = 'INVALID' and owner = 'APPS';

           

          Are you sure you don't need to run any procedure from the application side after the upgrade?

          • 2. Re: more invalid objects after upgraded to 11.2.0.2
            Srini Chavali-Oracle

            Pl post a list of the invalid objects, along with the errors you get when you try to compile them manually.

             

            Were all of the steps in MOS Doc 1058763.1 followed ?

             

            HTH
            Srini

            • 3. Re: more invalid objects after upgraded to 11.2.0.2
              Sivamani

              i ran one more time.Still there are lot of objects available. Sample output

               

              OBJECT_NAME

              --------------------------------------------------------------------------------

              HR_LOCATIONS_ALL

              PER_ADDRESSES

              AP_TAX_ENGINE_PKG

              AP_WEB_EXPENSE_WF

              AP_WEB_CUST_DFLEX_PKG

              BEN_DETERMINE_ACTIVITY_BASE_RT

              BEN_DETERMINE_ELIGIBILITY2

              BEN_DETERMINE_ELIGIBILITY

              BEN_MAINTAIN_DESIGNEE_ELIG

              BEN_DETERMINE_RATES

              BEN_BATCH_UTILS

               

               

              OBJECT_NAME

              --------------------------------------------------------------------------------

              BEN_EVALUATE_RATE_PROFILES

              BEN_DETERMINE_VARIABLE_RATES

              BEN_EXT_CHLG

              BEN_EXT_PERSON

              BEN_EXT_EVALUATE_INCLUSION

              BEN_LOCATION_OBJECT

              BEN_PERSON_OBJECT

              GHR_AGENCY_UPDATE

              GHR_SF52_DO_UPDATE

              GHR_SF52_PRE_UPDATE

              GHR_BREAKDOWN_RPT

               

               

              OBJECT_NAME

              --------------------------------------------------------------------------------

              GHR_API

              GHR_HISTORY_CONV_RG

              GHR_MASS_ACTIONS_PKG

              GHR_HISTORY_FETCH

              GHR_MTO_INT

              GHR_SF52_POS_UPDATE

              GHR_PA_REQUESTS_PKG

              GHR_POSN_RULES

              GHR_PSN_POS_GRP1_PK

              GHR_PSN_PSN_GRP2_PKG

              GHR_PSN_PSN_VAL_PKG2

               

               

              OBJECT_NAME

              --------------------------------------------------------------------------------

              GHR_AGENCY_CHECK

              GHR_VALIDATE_CHECK

              GHR_GHRWS52L

              GHR_UPD_HR_VALIDATION

              GHR_FETCH_POSITION_HISTORY

              GHR_MASS_AWARDS_PKG

              GHR_DS_JAN99_PKG

              GR_PROCESS_ORDERS

              HXT_BATCH_PROCESS

              HXT_BATCH_VAL

              HXT_RETRO_VAL

               

               

              OBJECT_NAME

              --------------------------------------------------------------------------------

              INV_PICK_RELEASE_PUB

              JL_ZZ_AP_AWT_DEFAULT_PKG

              PAY_MWS_MAGTAPE_REPORTING

              PAY_US_MAGW2_REPORTING

              HR_JP_ID_PKG

              PER_ADD_SHD

              PER_ADD_BUS

              PER_ADD_FLEX

              PER_ADD_INS

              PER_ADD_UPD

              PER_ADD_DEL

               

               

              OBJECT_NAME

              --------------------------------------------------------------------------------

              PER_ASG_BUS2

              PER_KAD_SHD

              PER_KAD_BUS

              PER_KAD_INS

              PER_KAD_UPD

              PER_KAD_DEL

              HR_LOC_SHD

              HR_LOC_BUS

              HR_LOC_INS

              HR_LOC_UPD

              HR_LOCATION_API

               

               

              OBJECT_NAME

              --------------------------------------------------------------------------------

              HR_LOC_DEL

              PER_US_VETC_TAP

              PER_US_VETS_TAP

              PO_CORE_S

              PO_LOCATIONS_S

              • 4. Re: more invalid objects after upgraded to 11.2.0.2
                TSharma-Oracle

                We need object_type also. Run this and post the result

                 

                select,object_name,object_type,status from dba_objects where status = 'INVALID' and owner = 'APPS';

                • 5. Re: more invalid objects after upgraded to 11.2.0.2
                  TSharma-Oracle

                  Also, Read these. These will help

                   

                  Invalid objects for the Modules EDW and FII [ID 412032.1]

                  EDW related Invalid Objects [ID 554036.1]

                  Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]

                   

                  https://forums.oracle.com/thread/2400233

                  • 6. Re: more invalid objects after upgraded to 11.2.0.2
                    Sivamani

                    Hi Srini / Sharma,

                     

                    1.Yes i followed the document 1058763.1.

                     

                    For section Apply additional 11.2.0.3 RDBMS patches i applied the same patches which i downloaded for 11.2.0.2

                     

                    2.I am getting the below error when i try to manually compile.

                     

                    SQL> SHOW ERRORS PACKAGE BODY PQP_ADDRESS_SYNCHRONIZATION;

                    Errors for PACKAGE BODY PQP_ADDRESS_SYNCHRONIZATION:

                     

                    LINE/COL ERROR

                    -------- -----------------------------------------------------------------

                    92/25    PL/SQL: Item ignored

                    92/25    PLS-00905: object APPS.PER_ADDRESSES is invalid

                    93/25    PL/SQL: Item ignored

                    93/25    PLS-00905: object APPS.PER_ADDRESSES is invalid

                    94/25    PL/SQL: Item ignored

                    94/25    PLS-00905: object APPS.PER_ADDRESSES is invalid

                    146/3    PL/SQL: Statement ignored

                    146/3    PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    147/3    PL/SQL: Statement ignored

                     

                     

                    LINE/COL ERROR

                    -------- -----------------------------------------------------------------

                    147/3    PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    148/3    PL/SQL: Statement ignored

                    148/3    PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    149/3    PL/SQL: Statement ignored

                    149/3    PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                     

                     

                    LINE/COL ERROR

                    -------- -----------------------------------------------------------------

                    150/3    PL/SQL: Statement ignored

                    150/3    PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    151/3    PL/SQL: Statement ignored

                    151/3    PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    152/3    PL/SQL: Statement ignored

                    152/3    PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    LINE/COL ERROR

                     

                    SQL> SHOW ERRORS PACKAGE BODY  PQP_NL_PENSION_EXTRACTS ;

                    Errors for PACKAGE BODY PQP_NL_PENSION_EXTRACTS:

                     

                     

                    LINE/COL ERROR

                    -------- -----------------------------------------------------------------

                    5919/4   PL/SQL: Statement ignored

                    5920/21  PLS-00905: object APPS.BEN_EXT_PERSON is invalid

                    6180/11  PL/SQL: Statement ignored

                    6181/31  PLS-00905: object APPS.BEN_EXT_PERSON is invalid

                    6187/11  PL/SQL: Statement ignored

                    6189/32  PLS-00905: object APPS.BEN_EXT_PERSON is invalid

                    6192/9   PL/SQL: Statement ignored

                    6193/25  PLS-00905: object APPS.BEN_EXT_PERSON is invalid

                    7003/1   PL/SQL: SQL Statement ignored

                    7004/8   PL/SQL: ORA-04063: view "APPS.PER_ADDRESSES_V" has errors

                    7019/14  PL/SQL: Item ignored

                     

                     

                    LINE/COL ERROR

                    -------- -----------------------------------------------------------------

                    7019/14  PLS-00905: object APPS.PER_ADDRESSES_V is invalid

                    7026/4   PL/SQL: SQL Statement ignored

                    7026/30  PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    7029/4   PL/SQL: Statement ignored

                    7029/34  PLS-00320: the declaration of the type of this expression is

                             incomplete or malformed

                     

                     

                    7053/1   PL/SQL: SQL Statement ignored

                    7054/8   PL/SQL: ORA-04063: view "APPS.PER_ADDRESSES_V" has errors

                     

                     

                    LINE/COL ERROR

                    -------- -----------------------------------------------------------------

                    7069/16  PL/SQL: Item ignored

                    7069/16  PLS-00905: object APPS.PER_ADDRESSES_V is invalid

                    • 7. Re: more invalid objects after upgraded to 11.2.0.2
                      Srini Chavali-Oracle

                      If you followed all of the steps correctly as per the the MOS Doc, then you should not have this number of invalids. Pl review all of your steps and confirm. Did you upgrade the database using DBUA ?

                       

                      Srini