paupg103.sql waits in adworker log Enter value for 1

While applying a merged patch which contains patches Projects Family Pack M(PJ_PF.M) and PJ_PF.M Rollup 2, PJ_PF.M Rollup 3 and PJ_PF.M Rollup 4(5644830) one adworker showed this message in logs:

Time when worker started job: Thu Oct 11 2007 15:34:58

Start time for file is: Thu Oct 11 2007 15:34:58

sqlplus -s APPS/***** @$PA_TOP/11.5.0/patch/115/sql/paupg103.sql
Enter value for 1:

Trying to run this sql manually also gave the same result. However this sql was run correctly by other workers:

adwork001.log:sqlplus -s APPS/***** @$PA_TOP/patch/115/sql/paupg103.sql &un_pa &batchsize 10 12

We tried restarting the worker with no success. So we skipped it and the merged patch completed successfully. I logged an SR to find out why this was happening and how we can avoid this in the next iteration. Oracle Development replied:

If the following SQL

select count(*)
from pa_proj_fp_options pfo,
pa_fin_plan_types_b fino,
pa_fin_plan_types_b finwp
where NVL(pfo.fin_plan_type_id,-99)
NOT IN (fino.fin_plan_type_id,finwp.fin_plan_type_id)
and pfo.fin_plan_type_id is not null
and pfo.use_planning_rates_flag is null
and fino.fin_plan_type_code = 'ORG_FORECAST'
and finwp.use_for_workplan_flag='Y';
.
returns count>0 then it means that there are some rows which are not yet upgraded by paupg103.sql

In that case the script paupg103.sql uploaded to bugftp can be given to the customer and it can be run at SQL prompt. This does not require any parameters. After the script is executed, the above SQL should return 0 records

As to why this occurred, this is what they said:

parrallel processing feature is added to paupg103.sql in PJ_PF.M RUP4 and with this the udriver command of the ARU patch is changed. The merged patch contains RUP4 patch as well as other patches where the parallel processing feature is not added to paupg103.sql. Hence the merged patch contains 2 different uDriver commands for the same file and it would have resulted in the above said error. Not merging PJ_PF.M RUP4 patch with other patches is a solution for this.

I decided to test this, by removing the PJ_PF.M RUP4 patch from the merged patch and remerge it. But the error still occurred after removing PJ_PF.M RUP4 patch. I have now removed PJ_PF.M RUP2 and PJ_PF.M RUP3 from the merged patch as they are already included in RUP4, which we are planning to apply in isolation as per Development's previous advice. I removed the base PJ_PF.M patch and the error stopped. However, I was not convinced as I did all this testing without refreshing the instance. So I did a fresh install and applied the merged patch which had the base PJ_PF.M patch. No errors. If I apply PJ_PF.M RUP4 patch separately, no errors. So development was right. I have asked them to fix this in RUP5 but they are non-commital with only the promise of documenting this behavior in a new metalink note. The created a note 555001.1, but with incorrect information. Instead of mentioning PJ_PF.M RUP4 patch 5150810, they have mentioned Projects Family Pack M 3485155 as the patch which should not be merged with other patches. I have asked them to correct it, but they haven't done so yet.

Since it is very important for us to minimize downtime during patching, I am proceeding ahead with another plan. I have asked Oracle whether we can follow this plan:


1. We merge PJ_PF.M RUP4 5150810 with merged patch
2. We comment out paupg103.sql from the u driver of the merged patch
3. We run paupg103.sql manually after the completion of merged patch.