R12.2 : Patch seems to hang in adobjcmp.sql or appostimportaad.sql or fapostimportaad.sql

Issue:

While applying a patch or Maintenance Pack the adpatch worker does not seem to finish when running one of the following jobs :

adobjcmp.sql
appostimportaad.sql
fapostimportaad.sql

This typically happens on smaller systems that are low on memory

Cause:

Compiling XLA packages while init.ora parameter plsql_optimize_level has value 2 (default) consumes a considerable amount of memory.

Solution :

First run the following query to make sure there is an invalid XLA_00200_AAD_S_0000 object by running the following query :

select object_name from dba_objects where object_name like ‘XLA_00200_AAD_S_0000%’ and status=’INVALID’;

Only if this returns one or more rows continue with the following procedure:

1. Make sure to have a valid backup

2. Using option 4 in adctrl, fail the worker(s) that are running adobjcmp.sql or appostimportaad.sql or fapostimportaad.sql

3. Stop and start the database instance – do not restart adpatch yet

4. Set the Applications environment

$ sqlplus <APPS-username>/<APPS-password>
SQL> alter session set plsql_optimize_level=1;
If the failed job is adobjcmp.sql :

Compile the XLA packages manually using command:
alter package xla… compile;
If the failed job is NOT adobjcmp.sql :
Run the jobs that were marked as Failed in Step 2
@$AP_TOP/patch/115/sql/appostimportaad.sql
@$FA_TOP/patch/115/sql/fapostimportaad.sql

5. Do not continue until all jobs/compiles of Step 4 completed successfully

6. Start adpatch again and have it continue with its previous session

7. If the failed job is adobjcmp.sql :
Using option 2 in adctrl, restart its worker job
If the failed job is not adobjcmp.sql :
Using (hidden) option 8 in adctrl, skip the worker job(s) that were run manually in Step 4