6 Replies Latest reply on Mar 18, 2016 9:29 AM by Narsi M-Oracle

    Issues with custom MVIEW after online enablement patch 13543062

    1283669

      Hi Team,

      I have got below failures post applying online enablement patch 13543062. How to take it forward ? We have already fixed all the violations as pointed out by script ADZDPMAN.sql and hence we proceed to apply this patch.  These custom objects which got failed were part of the report generated by script ADZDPAUT.sql, so ideally they should have been taken care by enablement patch.

      Even now, only object OE_ORDER_HOLDS_INFO_V is reported by ADZDPAUT.sql script which should be autofixed.

       

       

      Phase Count Status

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

      COPY_TYPE 53 S - Successfully Executed

      COMPILE_TYPE 1 S - Successfully Executed

      COPY_EVOLVED_TYPE 1 S - Successfully Executed

      FIX_COLUMN 2 S - Successfully Executed

      FIX_TYPE 26 S - Successfully Executed

      FIX_PUBLIC_SYNONYM 4178 S - Successfully Executed

      RECREATE_AQ_OBJECT 15 S - Successfully Executed

      DROP_OBJECT 5 S - Successfully Executed

      ENABLE_EDITIONING 195 S - Successfully Executed

      UPGRADE_TABLE 18182 S - Successfully Executed

                                              2 E - Error

      UPGRADE_SEED 2605 S - Successfully Executed

      COLLECT_STATS 2605 S - Successfully Executed

      UPGRADE_MVIEW 115 S - Successfully Executed

      19 E - Error



      SQL>select phase,SQL_LOB,EXECUTED ,ERROR from apps.ad_zd_ddl_handler where status='ERROR';

      PHASE SQL_LOB E ERROR

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

      UPGRADE_MVIEW begin APPS.ad_zd_mview.upgrade('APPS','IPD_TRACK_INVOICE_FULL_V1_MV'); end; F ORA-38818: illegal reference to editioned object AR.RA_CUSTOMER_TRX_ALL#

      UPGRADE_MVIEW begin APPS.ad_zd_mview.upgrade('APPS','XXPHC_PRODUCTFAMILY_MODELS_MV'); end; F ORA-38818: illegal reference to editioned object INV.MTL_SYSTEM_ITEMS_B#

       

      UPGRADE_MVIEW begin APPS.ad_zd_mview.upgrade('IPIDE','IPD_TRACK_INVOICE_TSSA_MV'); end; F ORA-00942: table or view does not exist

      UPGRADE_MVIEW begin APPS.ad_zd_mview.upgrade('PHCCUSTOM','OE_ORDER_HOLDS_INFO_V'); end; F ORA-00942: table or view does not exist

      UPGRADE_TABLE begin ad_zd_table.upgrade('HR', 'PER_ALL_PEOPLE_F'); end; F ORA-04020: deadlock detected while  trying to lock object  APPS.PAY_TRIGGER_EVENTS

       

      UPGRADE_MVIEW CREATE INDEX "APPS"."IPD_TRACK_INV_FULL_V1_MV_N1" ON "APPS"."IPD_TRACK_INVOICE_F F ORA-00942: table or view does not exist

      UPGRADE_MVIEW CREATE INDEX "APPS"."IPD_TRACK_INV_FULL_V1_MV_N2" ON "APPS"."IPD_TRACK_INVOICE_F F ORA-00942: table or view does not exist

      UPGRADE_MVIEW CREATE INDEX "APPS"."IPD_TRACK_INV_FULL_V1_MV_N3" ON "APPS"."IPD_TRACK_INVOICE_F F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT ALTER ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT DEBUG ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT DELETE ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT FLASHBACK ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT INDEX ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT INSERT ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT ON COMMIT REFRESH ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT QUERY REWRITE ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT REFERENCES ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT SELECT ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW GRANT UPDATE ON "APPS"."IPD_TRACK_INVOICE_FULL_V1_MV" TO "IPIDE" F ORA-00942: table or view does not exist

      UPGRADE_MVIEW begin APPS.ad_zd_mview.upgrade('IPIDE','IPD_TRACK_INVOICE_TSS_MV'); end; F ORA-00942: table or view does not exist

       

      UPGRADE_TABLE begin ad_zd_table.upgrade('PHCCUSTOM', 'XXPHC_LOAD_QUALIFIER_LINES'); end; F ORA-00936: missing expression



      >> I tried re-creating the MVIEW but getting same error, Have already gone through the notes but unable to understand it.

      ORA-38818: Illegal Reference To Editioned Object When Creating Materialized View (Doc ID 1556379.1)


      15:24:02 SQL> select owner,object_name,object_type,created,status from dba_objects where object_name='XXPHC_PRODUCTFAMILY_MODELS_MV#';

       

      OWNER

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

      OBJECT_NAME

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

      OBJECT_TYPE         CREATED   STATUS

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

      APPS

      XXPHC_PRODUCTFAMILY_MODELS_MV#

      VIEW                15-MAR-16 VALID

       

      15:25:14 SQL> exec ad_zd_mview.upgrade('APPS', 'XXPHC_PRODUCTFAMILY_MODELS_MV');

      BEGIN ad_zd_mview.upgrade('APPS', 'XXPHC_PRODUCTFAMILY_MODELS_MV'); END;

       

      *

      ERROR at line 1:

      ORA-38818: illegal reference to editioned object INV.MTL_SYSTEM_ITEMS_B#

      ORA-06512: at "APPS.AD_ZD_MVIEW", line 272

      ORA-06512: at "APPS.AD_ZD_MVIEW", line 725

      ORA-06512: at "APPS.AD_ZD_MVIEW", line 1127

      ORA-06512: at "APPS.AD_ZD_MVIEW", line 1863

      ORA-06512: at line 1

       

       




      Regards,

      Prasad

        • 1. Re: Issues with custom MVIEW after online enablement patch 13543062
          Narsi M-Oracle

          Hello,

           

          Could you please check if the object "INV.MTL_SYSTEM_ITEMS_B" exists.

           

           

          Regards,

          Narsi

          • 2. Re: Issues with custom MVIEW after online enablement patch 13543062
            1283669

            Hi Narsi,

            Thanks for the reply. Yes it does present with editions enabled as well.

             

            SQL>  select OWNER,object_name,object_type,EDITION_NAME,status from dba_objects where object_name='MTL_SYSTEM_ITEMS_B';

             

             

            OWNER                          OBJECT_NAME                              OBJECT_TYPE         EDITION_NAME                   STATUS

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

            INV                            MTL_SYSTEM_ITEMS_B                       TABLE                                              VALID

            APPS                           MTL_SYSTEM_ITEMS_B                       SYNONYM             ORA$BASE                       VALID

            PHCCUSTOM                      MTL_SYSTEM_ITEMS_B                       SYNONYM             ORA$BASE                       VALID

            IPRIDE                         MTL_SYSTEM_ITEMS_B                       SYNONYM             ORA$BASE                       VALID

            APPS_AA                        MTL_SYSTEM_ITEMS_B                       SYNONYM             ORA$BASE                       VALID

            INV_AA                         MTL_SYSTEM_ITEMS_B                       SYNONYM             ORA$BASE                       VALID

             

             

            6 rows selected.

             

             

            SQL>  select OWNER,object_name,OBJECT_TYPE,EDITION_NAME,status from dba_objects where object_name='MTL_SYSTEM_ITEMS_B#';

             

             

            OWNER                          OBJECT_NAME                              OBJECT_TYPE         EDITION_NAME                   STATUS

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

            INV                            MTL_SYSTEM_ITEMS_B#                      VIEW                ORA$BASE                       VALID

             

             

            Regards,

            Prasad

            • 3. Re: Issues with custom MVIEW after online enablement patch 13543062
              Narsi M-Oracle

              Hello Prasad,

               

              Could you please review below MOS Notes:

              Create And Register CUSTOM Schema On EBS 12.2 (Doc ID 1929668.1)

               

               

              Hope this helps!!!

               

               

              Regards,

              Narsi

              • 4. Re: Issues with custom MVIEW after online enablement patch 13543062
                1283669

                Hi Narsi,

                We have already registered all the custom schemas with Oracle using below script as guided by $AD_TOP/sql/ADZDPMAN.sql and this scripts makes an entry in system.fnd_oracle_userid table for each custom schema.

                 

                sqlplus apps @$AD_TOP/patch/115/sql/ADZDREG.sql <system password>  <apps username> <CUSTOM SCHEMA NAME>

                 

                Moreover the custom MVIEW in this particular case is owned by APPS and not any other custom schema. Any thoughts from that side ?

                 

                Regards,

                Prasad

                • 5. Re: Issues with custom MVIEW after online enablement patch 13543062
                  1283669

                  Hi Narsi,

                  One more thing, what is the impact if we have many custom objects accessing seeded tables directly AND NOT via apps table synonym. What will happen if we continue to use them in the same way and use adop ? Will these objects get invalidated once their base tables get patched ? Will we have to compile them after each adop cycle ?

                   

                  Please shade some light on this.

                   

                  SECTION-26  [minimal] => "Query/DML statements must access tables via the APPS table synonym."        


                  Regards,

                  Prasad

                  • 6. Re: Issues with custom MVIEW after online enablement patch 13543062
                    Narsi M-Oracle

                    Hello Prasad,

                     

                    In 12.2, it is mandatory that all code must access the data model via APPS Synonym which points to the editioning view (Logical Model/Logical View).

                    You will not be able to use them in same way as in versions prior to 12.2.

                    Typically during patching cycle, these editioning views project the correct logical view of the physical tables to the Run & Patch editions.

                    It is important that you address all the violations, else it would result in invalid custom code after 12.2 upgrade.

                     

                     

                    Hope this helps!!!