During a patching of EBS 12.1.3 to RPC5, I encountered an unexpected problem.
A while into the patching, the patching procedure failed with an error message like this:
Deferred: file csrrsreg.sql on worker 1 for product csr username CSR. (Deferment number 1 for this job)
Assigned: file csrrsreg.sql on worker 1 for product csr username CSR.
FAILED: file csrrsreg.sql on worker 1 for product csr username CSR.
Deferred: file csrrsreg.sql on worker 1 for product csr username CSR. (Deferment number 2 for this job)
Assigned: file csrrsreg.sql on worker 1 for product csr username CSR.
FAILED: file csrrsreg.sql on worker 1 for product csr username CSR.
ATTENTION: All workers either have failed or are waiting:
FAILED: file csrrsreg.sql on worker 1.
ATTENTION: Please fix the above failed worker(s) so the manager can continue.
Open another ssh session to the server.
Check the logfile for the worker:
cd $APPL_TOP/admin/$ORACLE_SID/log
ls -la adwork*.log
-rw-r--r-- 1 ebsadm dba 123777231 Jun 5 12:58 adwork001.log
-rw-r--r-- 1 ebsadm dba 115207185 Jun 5 12:58 adwork002.log
-rw-r--r-- 1 ebsadm dba 119484153 Jun 5 12:58 adwork003.log
-rw-r--r-- 1 ebsadm dba 119051681 Jun 5 12:57 adwork004.log
Check the logfile for worker 1:
vi adwork001.log
The content could look something like this:
Creating the XML Schema Directory
CSR Product Top is
PL/SQL procedure successfully completed.
Registing Rules XML Schema Document
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1
Your workers are now waiting.
You can see their status by using the utility "adctrl".
Open another ssh session.
Execute adctrl, and answer the questions along the way.
At the end, you'll see a menu like this:
AD Controller Menu
---------------------------------------------------
1. Show worker status
2. Tell worker to restart a failed job
3. Tell worker to quit
4. Tell manager that a worker failed its job
5. Tell manager that a worker acknowledges quit
6. Restart a worker on the current machine
7. Exit
Enter your choice [1] : 1
Control
Worker Code Context Filename Status
------ -------- ----------------- -------------------------- --------------
1 Run AutoPatch R120 csrrsreg.sql FAILED
2 Run AutoPatch R120 Wait
3 Run AutoPatch R120 Wait
4 Run AutoPatch R120 Wait
The solution to this particular error can be found in the note 1263895.1 "Advanced Scheduler Error:' ORA-06502: PL/SQL: numeric or value error: host bind array too small' In CSRRSREG.sql When Upgrading To 12.1.3".
You need to pull up the file csrrsreg.sql in a text editor and change
dbms_output.put_line('Registering Scheduler Rules XML Schema failed with an error ' || SQLERRM);
to
dbms_output.put_line(SUBSTR(('Registering Scheduler Rules XML Schema failed with an error ' || SQLERRM), 1, 250));
The file is placed in the patch stage area 22644544/csr/patch/115/sql, but it is copied to $APPL_TOP/csr/12.0.0/patch/115/sql during installation.
So I changed the file in both places, in case of a rerun.
Then, according to note 1263895.1, run the $APPL_TOP/csr/12.0.0/patch/115/sql/csrrsreg.sql manually.
Open the file first, and read the instructions. You need the passowrds for APPS and CSR, as well as the value for $CSR_TOP.
Run it and supply the parameters as they are requested by the script:
$1 = 'APPS'
$2 = password for APPS
$3 = 'CSR'
$4 = password for CSR
$5 = value for $CSR_TOP
sqlplus /nolog @/u01/app/apps/apps_st/appl/csr/12.0.0/patch/115/sql/csrrsreg.sql
Go back to your session where you are running adctrl.
Restart the worker process:
AD Controller Menu
---------------------------------------------------
1. Show worker status
2. Tell worker to restart a failed job
3. Tell worker to quit
4. Tell manager that a worker failed its job
5. Tell manager that a worker acknowledges quit
6. Restart a worker on the current machine
7. Exit
Enter your choice [1] : 2
Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu : 1
Status changed to 'Fixed, restart' for worker 1.
You should now check the worker status again:
Review the messages above, then press [Return] to continue.
AD Controller Menu
---------------------------------------------------
1. Show worker status
2. Tell worker to restart a failed job
3. Tell worker to quit
4. Tell manager that a worker failed its job
5. Tell manager that a worker acknowledges quit
6. Restart a worker on the current machine
7. Exit
Enter your choice [1] : 1
Control
Worker Code Context Filename Status
------ -------- ----------------- -------------------------- --------------
1 Run AutoPatch R120 pa_inv_exception_rpt_tbl_t Running
2 Run AutoPatch R120 pa_perid_profile_tbl_typ.x Assigned
3 Run AutoPatch R120 Wait
4 Run AutoPatch R120 Wait
Review the messages above, then press [Return] to continue.
Enter your choice [1] : 1
Control
Worker Code Context Filename Status
------ -------- ----------------- -------------------------- --------------
1 Run AutoPatch R120 BEN_REP_ACTIONS_TAB.xdf Running
2 Run AutoPatch R120 BEN_REP_ARCHIVE_TAB.xdf Running
3 Run AutoPatch R120 BEN_DPNT_SICVRD_GT_TAB.xdf Running
4 Run AutoPatch R120 BEN_ACA_CBR_TAB.xdf Running
The workers are proceeding as before.
If you check your initial ssh session where you run the patching, things should move along.
