This discussion is archived
7 Replies Latest reply: Aug 30, 2013 5:05 AM by Srini Chavali-Oracle RSS

more invalid objects after upgraded to 11.2.0.2

Sivamani Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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

Legend

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