Adop Prepare/Abort Phase Hung While Executing AD_ZD_SEED.PREPARE.FND_PROFILE_OPTION_VALUES

Adop Prepare/Abort Phase Hung While Executing AD_ZD_SEED.PREPARE.FND_PROFILE_OPTION_VALUES

Error:

adop prepare/abort phase is hanging and not completing even after 52 hours.

From the ADZDSHOWLOG.out file we can notice the below where it is stuck while executing synchronize task: FND_PROFILE_OPTION_VALUES_DELETE.

 489121669 12:58:59 00:00:00 ad.plsql.ad_zd_seed.run_in_parallel        PROCEDURE  begin: FND_PROFILE_OPTION_VALUES_DELETE

 489121669 12:58:59 00:00:00 ad.plsql.ad_zd_seed.run_in_parallel        STATEMENT  Create Task FND_PROFILE_OPTION_VALUES_DELETE

 489121669 12:59:00 00:00:01 ad.plsql.ad_zd_seed.run_in_parallel        STATEMENT  Executing synchronize task: FND_PROFILE_OPTION_VALUES_DELETE, chunks: 5

 489121649 12:59:51 00:00:50 ad.bin.adop                                EVENT      preparing the seed tables needed for autoconfig execution.

 489121649 12:59:51 00:00:00 ad.bin.adop                                PROCEDURE  [START] Preparing autoconfig related seed data tables

 489121649 12:59:52 00:00:00 ad.bin.adop                                STATEMENT  Sourcing Patch edition environment <APPL_TOP>/APPSSBOX_curtsriebsap3.env

 489121671 12:59:52 00:00:00 ad.plsql.ad_zd_seed.prepare                PROCEDURE  begin: FND_PROFILE_OPTION_VALUES

 489122494 10:23:19 21:23:26 ad.plsql.ad_zd.set_edition                 STATEMENT  Setting current edition to V_20230430_XXXX

Even when I checked for abort the session was stuck at

BEGIN

    ad_zd_seed.abort ('APPLSYS', 'FND_PROFILE_OPTION_VALUES');

END;

Solutions:

1. Verify the value of parameter SCHEDULER_DISABLED at both CDB and PDB level using the below query:

SQL> SELECT * FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME = 'SCHEDULER_DISABLED';

2. Set SCHEDULER_DISABLED to FALSE at CDB level. Ensure that it is set to false at both CDB and PDB.

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

3. The prepare phase will progress after making the changes.

If the session has already been cancelled or if it does not progress, then bounce the database and retry prepare phase again.

OR

This was my case when the above solution was already place but the issues was the below.

the parameter "job_queue_processes" is not same for both CDB and PDB.

When checked for CDB the job_queue_processes was 0

And for PDB he job_queue_processes was 2

PDB

NAME TYPE VALUE

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

job_queue_processes integer 2

CDB

NAME TYPE VALUE

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

job_queue_processes integer 0

I changed the value as same for CDB and PDB level

Alter system set job_queue_processes=10;

Please restart your database once.

I did following afterwards

1. Run adop phase=abort

2. Run adop phase=cleanup

3. Run adop phase=fs_clone

4. Run adop phase=prepare.

If you like please follow and comment