Actulaize_all stuck

[sravan]

HI ,

1)Actualize_all is stuck and not proceeding.

2)Checked @$AD_TOP/sql/ADZDSHOWOBJS.sql  stub objects and actual objects from all the editions should move to latest edition and it is same. It been more than 12 hours and it it stuck at same step

Below is the adop status

===============================================================
Node Name       Node Type  Phase           Status          Started              Finished             Elapsed
--------------- ---------- --------------- --------------- -------------------- -------------------- ------------
loc4au01        master     PREPARE         COMPLETED       xxxxx7  xxxxxxx  0:27:58
                           APPLY           NOT STARTED
                           FINALIZE        NOT STARTED
                           CUTOVER         NOT STARTED
                           CLEANUP         NOT STARTED



File System Synchronization Type: Full

3)adzdshowlog shows below

 149413824 11:16:47 00:00:00 ad.bin.adop                                EVENT      Populating actualize actions in DDL handler table.
 149413824 11:16:47 00:00:00 ad.bin.adop                                EVENT      Clearing old rows from DDL handler table.
 149413824 11:16:47 00:00:00 ad.plsql.ad_zd_parallel_exec.cleanup       STATEMENT  Cleanup DDL handler, phase=ACTUALIZE_PARENT_OBJS
 149413824 11:16:47 00:00:00 ad.plsql.ad_zd_parallel_exec.cleanup       STATEMENT  Cleanup DDL handler, phase=ACTUALIZE_CHILD_OBJS
 149413824 11:16:47 00:00:00 ad.bin.adop                                EVENT      Populating DDLs.
 149413824 11:16:47 00:00:00 ad.bin.adop                                EVENT      Log: @ADZDSHOWLOG.sql "2020/12/01 11:16:47"
      5930 11:16:47 00:00:00 ad.plsql.ad_zd_sys.actualize_all           PROCEDURE  begin
      5930 11:16:47 00:00:00 ad.plsql.ad_zd_sys.actualize_all           EVENT      Actualize AD_ZD% packages
      5930 11:16:48 00:00:00 ad.plsql.ad_zd_sys.actualize_all           EVENT      Actualize all editioned objects

4) No errors in db alert log

5)adopmon shows as below

Timestamp           Node name           Message Type Message Text
---------------------------------------------------------------------------------------------------------------------------
2020/11/24 12:41:33 abcd             EVENT        Log: xxxxxapps/fs_ne/EBSapps/log/adop/60/20201124_123224/va
                                                     lidate/adopConsole.log
2020/11/24 12:41:33 loc4au01            EVENT        ======================================================================
                                                     =====

2020/11/24 12:41:33 abcd            EVENT        Checking for existing patching cycle.
2020/11/24 12:41:33 abcd             EVENT        Existing patching cycle exists with session ID: 60
2020/11/24 12:41:34 abcd            EVENT        Running adop validations on node: [loc4au01].
2020/11/24 12:41:34 abcd            EVENT        Log: loc4au01: xxxxapps/fs_ne/EBSapps/log/adop/60/20201124
                                                     _123224/validate/loc4au01
2020/11/24 12:41:36 abcd            EVENT        Running adop validations.
2020/11/24 12:41:36 abcd           EVENT        Node registry is valid.
2020/11/24 12:41:47 abcd            EVENT        Running TXK validations.

6) select count(*) from dba_editions;

  COUNT(*)
----------
        27

Please assist

[ErmanArslansOracleBlog]

This may be related with the optimizer.
Did you collect stats recently?
If it is waiting in the db, then you should check the db first.. Check the session and try to find the wait first..
It may be related with the system stats.. This is mentioned in here ->
https://www.oracle.com/webfolder/community/oracle_e-business_suite/4344959.html
There is a unapproved solution and it is done by deleting the SYS stats.. But this is a risk move, but it may help.. I didnt test it.. The risk is yours.

[sravan]

HI

Please let me know what stats we need to collect to check this.

Regards
Sindhuja

[ErmanArslansOracleBlog]

It is the system_stats.. deleting those stats.. It is mentioned here -> https://community.oracle.com/mosc/discussion/4344959/e-business-suite-r12-19c-database-upgrade-analyzer-whats-on-your-wish-list
Please have a look...
But this is risky as I said..
If it is a TEST environment, you may take the risk.. But if it is PROD, you should evaluate it carefully.

[sravan]

HI

Here the customer is on 12.1.0.2 Database. The suggested community link is for  Oracle Database 19c Upgrade Analyzer for Oracle E-Business Suite.

Any advise would be highly helpful.

Regards

[ErmanArslansOracleBlog]

Yes.. But it still seems related..
The problem given there is the same.. actualize_all...
You may check the db session related to "adop actualize_all", during its run and check the wait of it + the execution plan of it.. we may give some more comments according to those findings.. But that removing sys stats thing (which is risky on PROD environments) is currently what we have.. (what we can offer as a workaround..)