Upgrade to R12.2

[satish]

Dear Erman,

We are upgrading from R12.1.3 to R12.2.11

We have applied online enablement patch which completed in less than 1 hour. We need your support to confirm if it went fine and whether all ddls executed.

[applcrp@sssterpdev1 tmp]$ cat adzdshowddls.out
DOC>
DOC>  Count of Current DDL Jobs per Phase and Status
DOC>
DOC>  Phases:
DOC>    1.  DROP_UNUSED_OBJECT : Remove obsolete objects from XLA schema.
DOC>    2.  COPY_TYPE          : Create EBS Types in APPS_NE schema, stop Advanced Queues.
DOC>    3.  COMPILE_TYPE       : Recompile APPS_NE schema.
DOC>    4.  COPY_EVOLVED_TYPE  : Create evolved types in APPS_NE schema.
DOC>    5.  FIX_COLUMN         : Fix TYPE reference to APPS_NE.<type> at table and AQ level.
DOC>    6.  FIX_TYPE           : Additional check, Fix TYPE reference and then drop TYPE
DOC>                             from source schema.
DOC>    7.  FIX_PUBLIC_SYNONYM : Fix public synonyms.
DOC>    8.  RECREATE_AQ_OBJECT : Re-create internal AQ objects after TYPE Ref fix.
DOC>    9.  DROP_OBJECT        : Drop objects like CTXSYS.<package>, CTXSYS.<synonym> and others if any.
DOC>    10. ENABLE_EDITIONING  : Enable registered schemas for Online Patching.
DOC>    11. UPGRADE_TABLE      : Create Editioning View cover layer over EBS data model.
DOC>    12. UPGRADE_SEED       : Upgrade seed data tables to support edition based storage.
DOC>    13. COLLECT_STATS      : Gather Statistics on Seed Data Tables after upgrade.
DOC>    14. UPGRADE_MVIEW      : Convert Materialized Views with expand query.
DOC>    15. CUTOVER            : Perform maintenance operations on columns and indexes after adop APPLY phase
DOC>                             and before actually changing Patch-Edition as Default-Edition.
DOC>    16. ACTUALIZE_ALL      : Actualize all editioned objects into the current edition.
DOC>    17. CLEANUP            : Cleanup task after adop CUTOVER.
DOC>
DOC>
DOC>  Results spooled to: ADZDSHOWDDLS.out
DOC>#

Phase                  Count Status
-------------------- ------- ------------------------------
COPY_TYPE                 41 S - Successfully Executed

COMPILE_TYPE               1 S - Successfully Executed

COPY_EVOLVED_TYPE          1 S - Successfully Executed

FIX_COLUMN                 2 S - Successfully Executed

FIX_TYPE                  22 S - Successfully Executed

FIX_PUBLIC_SYNONYM      4486 S - Successfully Executed

RECREATE_AQ_OBJECT        11 S - Successfully Executed

DROP_OBJECT               10 S - Successfully Executed

ENABLE_EDITIONING        164 S - Successfully Executed

UPGRADE_TABLE          18032 S - Successfully Executed

UPGRADE_SEED            2608 S - Successfully Executed

COLLECT_STATS           2608 S - Successfully Executed

UPGRADE_MVIEW            119 S - Successfully Executed
                           1 E - Error

CLEANUP                    1 N - Not Executed


15 rows selected.

DOC>
DOC>  Currently running phase is:
DOC>
DOC>#

no rows selected




=====================================================

[applcrp@sssterpdev1 afterop]$ cat adzdexrpt.txt

Start Time: 08-01-2023 18:54:46

PL/SQL procedure successfully completed.

DOC>
DOC>  Online Patch Enablement - Execution Status Report of SQL jobs
DOC>
DOC>  Report Script: $AD_TOP/sql/ADZDEXRPT.sql
DOC>  Result Spooled Into: adzdexrpt.txt
DOC>
DOC>  Currently running Workers (database sessions) for SQL jobs execution.
DOC>
DOC>#

no rows selected

DOC>
DOC>  *************************************************************
DOC>  SECTION 1
DOC>  *************************************************************
DOC>  Count of Current DDL Jobs per Phase and Status
DOC>
DOC>  Phases:
DOC>    1.  DROP_UNUSED_OBJECT : Remove obsolete objects from XLA schema.
DOC>    2.  COPY_TYPE          : Create EBS Types in APPS_NE schema, stop Advanced Queues.
DOC>    3.  COMPILE_TYPE       : Recompile APPS_NE schema.
DOC>    4.  COPY_EVOLVED_TYPE  : Create evolved types in APPS_NE schema.
DOC>    5.  FIX_COLUMN         : Fix TYPE reference to APPS_NE.<type> at table and AQ level.
DOC>    6.  FIX_TYPE           : Additional check, Fix TYPE reference and then drop TYPE
DOC>                             from source schema.
DOC>    7.  FIX_PUBLIC_SYNONYM : Fix public synonyms.
DOC>    8.  RECREATE_AQ_OBJECT : Re-create internal AQ objects after TYPE Ref fix.
DOC>    9.  DROP_OBJECT        : Drop objects like CTXSYS.<package>, CTXSYS.<synonym> and others if any.
DOC>    10. ENABLE_EDITIONING  : Enable registered schemas for Online Patching.
DOC>    11. UPGRADE_TABLE      : Create Editioning View cover layer over EBS data model.
DOC>    12. UPGRADE_SEED       : Upgrade seed data tables to support edition based storage.
DOC>    13. COLLECT_STATS      : Gather Statistics on Seed Data Tables after upgrade.
DOC>    14. UPGRADE_MVIEW      : Convert Materialized Views with expand query.
DOC>    15. CUTOVER            : Perform maintenance operations on columns and indexes after adop APPLY phase
DOC>                             and before actually changing Patch-Edition as Default-Edition.
DOC>    16. ACTUALIZE_ALL      : Actualize all editioned objects into the current edition.
DOC>    17. CLEANUP            : Cleanup task after adop CUTOVER.
DOC>
DOC>#

Phase                              Count Status
------------------------------ --------- ------------------------------
COPY_TYPE                             41 S - Successfully Executed

COMPILE_TYPE                           1 S - Successfully Executed

COPY_EVOLVED_TYPE                      1 S - Successfully Executed

FIX_COLUMN                             2 S - Successfully Executed

FIX_TYPE                              22 S - Successfully Executed

FIX_PUBLIC_SYNONYM                  4486 S - Successfully Executed

RECREATE_AQ_OBJECT                    11 S - Successfully Executed

DROP_OBJECT                           10 S - Successfully Executed

ENABLE_EDITIONING                    164 S - Successfully Executed

UPGRADE_TABLE                      18032 S - Successfully Executed

UPGRADE_SEED                        2608 S - Successfully Executed

COLLECT_STATS                       2608 S - Successfully Executed

UPGRADE_MVIEW                        119 S - Successfully Executed
                                       1 E - Error

CLEANUP                                1 N - Not Executed


15 rows selected.

Elapsed: 00:00:00.02
DOC>
DOC>  *************************************************************
DOC>  SECTION 2
DOC>  *************************************************************
DOC>  Failed Jobs with UNEXPECTED error, For complete error stack and DDL,
DOC>  Please check AD_ZD_DDL_HANDLER table for corresponding DDL_ID.
DOC>
DOC>#

no rows selected

Elapsed: 00:00:00.01
DOC>
DOC>  *************************************************************
DOC>  SECTION 3
DOC>  *************************************************************
DOC>  Failed Jobs with ERRORS, For complete error stack and DDL,
DOC>  Please check AD_ZD_DDL_HANDLER table for corresponding DDL_ID
DOC>
DOC>#

Phase                      DDL ID Status       SQL                                                Error
-------------------- ------------ ------------ -------------------------------------------------- --------------------------------------------------
UPGRADE_MVIEW               10055 ERROR        begin                                              ORA-22818: subquery expressions not allowed here
                                               APPS.ad_zd_mview.upgrade('APPS','MRP_COMPANY_USERS
                                               _SN'); end;


1 row selected.

Elapsed: 00:00:00.01
DOC>
DOC>
DOC>  *************************************************************
DOC>  SECTION 4
DOC>  *************************************************************
DOC>  Running Jobs , Please check AD_ZD_DDL_HANDLER table for
DOC>  corresponding DDL_ID
DOC>
DOC>#

no rows selected

Elapsed: 00:00:00.01
DOC>
DOC>  *************************************************************
DOC>  SECTION 5
DOC>  *************************************************************
DOC>  Failed Jobs with Warnings, For complete error stack and DDL,
DOC>  Please check AD_ZD_DDL_HANDLER table for corresponding DDL_ID
DOC>
DOC>#

no rows selected

Elapsed: 00:00:00.01
DOC>
DOC>  *************************************************************
DOC>  SECTION 6
DOC>  *************************************************************
DOC>  E-Business Suite INVALID object-count: Object-Type wise and Total
DOC>
DOC>#

no rows selected

Elapsed: 00:00:00.00

Invalid Object Type                Count
------------------------------ ---------
PACKAGE BODY                          49
PACKAGE                                1
VIEW                                   8
                               ---------
Total Invalid Objects (EBS):          58

3 rows selected.

Elapsed: 00:00:00.10
DOC>
DOC>  *************************************************************
DOC>  SECTION 7
DOC>  *************************************************************
DOC>  List of E-Business Suite INVALID objects
DOC>
DOC>#

Owner                Type                           Name
-------------------- ------------------------------ ------------------------------
APPS                 PACKAGE                        SSS_BUDGET_UPLOAD_REVISED_PK
APPS                 PACKAGE BODY                   AP_AID_TABLE_HANDLER_PKG
APPS                 PACKAGE BODY                   AP_EMPLOYEE_UPDATE_PKG
APPS                 PACKAGE BODY                   AP_PURGE_PKG
APPS                 PACKAGE BODY                   AP_RETAINAGE_PKG
APPS                 PACKAGE BODY                   AP_RETRO_PRICING_PKG
APPS                 PACKAGE BODY                   ARP_CASHBOOK
APPS                 PACKAGE BODY                   ARP_PROCESS_CHARGEBACK
APPS                 PACKAGE BODY                   ARP_PROCESS_MISC_RECEIPTS
APPS                 PACKAGE BODY                   CST_ACCRUAL_REC_PVT
APPS                 PACKAGE BODY                   CZ_IMP_SINGLE
APPS                 PACKAGE BODY                   DB_PERFORMANCE_ANALYZER_PKG
APPS                 PACKAGE BODY                   GET_PASS
APPS                 PACKAGE BODY                   IBY_FNDCPT_EXTRACT_GEN_PVT
APPS                 PACKAGE BODY                   JAI_APEX_JSON_PKG
APPS                 PACKAGE BODY                   JAI_AP_DTC_DEFAULTATION_PKG
APPS                 PACKAGE BODY                   JAI_AP_DTC_GENERATION_PKG
APPS                 PACKAGE BODY                   JAI_AP_DTC_PREPAYMENTS_PKG
APPS                 PACKAGE BODY                   JAI_AP_IA_DTC_TRIGGER_PKG
APPS                 PACKAGE BODY                   JAI_AP_ST_REVERSE_PROCESS
APPS                 PACKAGE BODY                   JAI_AP_TDS_POP_RPST_PKG
APPS                 PACKAGE BODY                   JAI_AR_TAX_LINES_PKG
APPS                 PACKAGE BODY                   JAI_AR_TCS_REP_PKG
APPS                 PACKAGE BODY                   JAI_CMN_RG_TRANSFER_PKG
APPS                 PACKAGE BODY                   JAI_CMN_SETUP_PKG
APPS                 PACKAGE BODY                   JAI_CMN_ST_FORMS_PKG
APPS                 PACKAGE BODY                   JAI_EXTRACT_PKG
APPS                 PACKAGE BODY                   JAI_GST_INBOUND_PKG
APPS                 PACKAGE BODY                   JAI_INV_NUM_GEN_PKG
APPS                 PACKAGE BODY                   JAI_OM_RMA_PKG
APPS                 PACKAGE BODY                   JAI_OM_TAX_PROCESSING_PKG
APPS                 PACKAGE BODY                   JAI_OM_WDD_PROCESSING_PKG
APPS                 PACKAGE BODY                   JAI_PHASEIV_UPGRADE_PKG
APPS                 PACKAGE BODY                   JAI_RCV_TAX_DET_PKG
APPS                 PACKAGE BODY                   JAI_TAX_DETERMINATION_PKG
APPS                 PACKAGE BODY                   JAI_TAX_PERIOD_ENDING_PKG
APPS                 PACKAGE BODY                   JAI_TAX_PROCESSING_PKG
APPS                 PACKAGE BODY                   PA_RBS_MAPPING
APPS                 PACKAGE BODY                   PQP_AAT_INS
APPS                 PACKAGE BODY                   PQP_AAT_SHD
APPS                 PACKAGE BODY                   PQP_AAT_UPD
APPS                 PACKAGE BODY                   PSP_ENC_CREATE_LINES
APPS                 PACKAGE BODY                   PSP_SUM_TRANS
APPS                 PACKAGE BODY                   QLTTRAMB
APPS                 PACKAGE BODY                   SSS_BUDGET_UPLOAD_REVISED_PK
APPS                 PACKAGE BODY                   XLA_00200_AAD_S_000002_PKG
APPS                 PACKAGE BODY                   XLA_ACCOUNTING_ERR_PKG
APPS                 PACKAGE BODY                   XLA_MULTIPERIOD_RPRTG_PKG
APPS                 PACKAGE BODY                   XLA_THIRD_PARTY_MERGE
APPS                 PACKAGE BODY                   ZX_VALID_INIT_PARAMS_PKG
APPS                 VIEW                           AR_CUSTOMER_PROFILES1_DFV
APPS                 VIEW                           AR_CUSTOMER_PROFILES2_DFV
APPS                 VIEW                           AR_CUSTOMER_PROFILE_AMOUN1_DFV
APPS                 VIEW                           AR_CUSTOMER_PROFILE_CLASS1_DFV
APPS                 VIEW                           AR_CUSTOMER_PROFILE_CLASS2_DFV
APPS                 VIEW                           JAI_CMN_MATCH_RECEIPTS_V
APPS                 VIEW                           RA_ADDRESSES_ALL2_DFV
APPS                 VIEW                           RA_SITE_USES_ALL1_DFV

58 rows selected.

Elapsed: 00:00:00.07
DOC>
DOC>  *************************************************************
DOC>  SECTION 8
DOC>  *************************************************************
DOC>  Time taken by each PHASE
DOC>
DOC>#

Phase                    ROW_COUNT START_TIME           END_TIME             ELAPSED_TIME_MIN
-------------------- ------------- -------------------- -------------------- ----------------
COPY_TYPE                       41 2023-JAN-08 18:04:28 2023-JAN-08 18:04:30                0
COMPILE_TYPE                     1 2023-JAN-08 18:04:40 2023-JAN-08 18:04:40                0
COPY_EVOLVED_TYPE                1 2023-JAN-08 18:04:47 2023-JAN-08 18:04:47                0
FIX_COLUMN                       2 2023-JAN-08 18:04:59 2023-JAN-08 18:04:59                0
FIX_TYPE                        22 2023-JAN-08 18:05:02 2023-JAN-08 18:05:08                0
FIX_PUBLIC_SYNONYM            4486 2023-JAN-08 18:05:09 2023-JAN-08 18:05:21                0
RECREATE_AQ_OBJECT              11 2023-JAN-08 18:05:24 2023-JAN-08 18:05:24                0
DROP_OBJECT                     10 2023-JAN-08 18:05:25 2023-JAN-08 18:05:26                0
ENABLE_EDITIONING              164 2023-JAN-08 18:05:27 2023-JAN-08 18:06:32                1
UPGRADE_TABLE                18032 2023-JAN-08 18:06:47 2023-JAN-08 18:16:06                9
UPGRADE_SEED                  2608 2023-JAN-08 18:16:09 2023-JAN-08 18:19:21                3
COLLECT_STATS                 2608 2023-JAN-08 18:19:26 2023-JAN-08 18:20:08                0
UPGRADE_MVIEW                  120 2023-JAN-08 18:20:12 2023-JAN-08 18:21:05                0
CLEANUP                          1

14 rows selected.

Elapsed: 00:00:00.01

End Time: 08-01-2023 18:54:46

Total Elapsed Time:  00:00:00:26
[applcrp@sssterpdev1 afterop]$




We have verified the error from logs

Phase DDL ID Status SQL Error
-------------------- ------------ ------------ -------------------------------------------------- --------------------------------------------------
UPGRADE_MVIEW 10027 ERROR begin ORA-22818: subquery expressions not allowed here
  APPS.ad_zd_mview.upgrade('APPS','MRP_COMPANY_USERS_SN'); end;

Above can be ignored according to Enabling Online Patching During 12.2 - And Later Versions - Upgrade Fails For MRP_COMPANY_USERS_SN (Doc ID 1957323.1)


Thanks,
Satish

[ErmanArslansOracleBlog]

Seems okay. But still, please send me the documentation & steps you followed for enabling online patching, so I can check your method easily.