How FND_INSTALL_PROCESSES created ,used and dropped



Last updated on April 15th, 2017 at 05:45 am

In previous articles, i presented about Autopatch and various ad utilities.All these need the table fnd_install_processes for the parallel processing

This is also used with adop  and other ad utilities in R12.2

General sequence of steps for parallel worker processing in Autopatch/adpatch

This is the sequence of steps that happen which is not visible in the front screen

Running SQL and EXEC commands in parallel…

Running SQL and EXEC commands in parallel…
1. CREATE TABLE fnd_install_processes
2. CREATE UNIQUE INDEX fnd_install_processes_u1 on fnd_install_processes
3. GRANT ALL ON fnd_install_processes TO APPS WITH GRANT OPTION
4. CREATE SYNONYM fnd_install_processes FOR APPLSYS.fnd_install_processes
5. CREATE TABLE AD_DEFERRED_JOBS(
6. CREATE UNIQUE INDEX AD_DEFERRED_JOBS_U1 on AD_DEFERRED_JOBS
7. GRANT ALL ON AD_DEFERRED_JOBS TO APPS WITH GRANT OPTION
8. CREATE SYNONYM AD_DEFERRED_JOBS FOR APPLSYS.AD_DEFERRED_JOBS
9. INSERT INTO fnd_install_processes (number of times depending on the no. of
workers)Reading jobs from FND_INSTALL_PROCESSES table …
Done reading jobs from FND_INSTALL_PROCESSES table …
Telling workers to read ‘todo’ restart file.
Done.[Worker Processing]Telling workers to quit…
All workers have quit.
Drop steps in sequences
10. DROP SYNONYM fnd_install_processes
11. DROP SYNONYM AD_DEFERRED_JOBS
12. DROP TABLE fnd_install_processes
13. DROP TABLE AD_DEFERRED_JOBS

The tables are actually created in APPLSYS , and the workers get the synonyms in APPS schema to fetch data from

adpatch/adadmin/adop uses the below login when processing parallel request

  1. the Process create the FND_INSTALL_PROCESSES
  2. It starts the required adworkers
  3. adpatch or adop updates the job information in the table
  4. adworker keep reading the table and completing  the information
  5. When all the job are completed, adpatch or adop signals the end of parallel processing
  6. It updates the FND_INSTALL_PROCESSES table to quit the  adworker and continue with the processing

Additional Information of FND_INSTALL_PROCESSES Columns

Meaning of FND_INSTALL_PROCESSES STATUS values

FND_INSTALL_PROCESSES

Meaning of FND_INSTALL_PROCESSES CONTROL_CODE values

FND_INSTALL_PROCESSES

Logic of adworker processing

adworker  keep polling this table until one of the following combination is found.

CONTROL_CODE == R && (STATUS == S || STATUS == X || STATUS ==J)
CONTROL_CODE == Q
CONTROL_CODE == T

Only when one of these conditions is satisfied, the worker comes out of the infinite loop of polling its row in table and begins the actual processing. Actually, if on the first poll, the worker does not find any of the above combination, it goes to sleep for 100ms. At the end of 100 ms, it repeats the polling process to see if the manager has updated its Control code or Status.This process it repeats for a maximum of 20 times. If the worker is still not able to fetch a job,it now increases its polling window. Now it starts polling at intervals of 1 sec (instead of 100ms). This poll window is used for the next 60 polls(i.e from 21st poll attempt till 80th pollattempt). If the worker is still unable to fetch a job, it further expands its poll window to 15 secs and keeps polling until it gets a job or is asked to quit by the manager.

During the poll,the worker temporarily locks its row in the table  so that the manager does not change it to a different status.If the manager changes the status to ‘Q’ before worker updates to ‘R’ then manager will wait indefinitely.This is achieved by using the FOR UPDATE OF clause in the SQL Query that performs the polling.

Here is how adworker run the job when run condition are met

If control code R and status in (S, X, J)

Following action are executed by adworker

1)It get job information

2) if status is S, it print “Time when worker started job” and set status to R in the table

3)  if status is J, it print “Restarting job that failed – will skip a single step.” and set status to Y in the table

4) if status is X,it print “Restarting job that failed and was fixed.” and then set status to Y in the table

5) It actually run the job now

6)  If job is completed succesfully, it print “Time when worker completed job”
and then set status to C

7) – if failure, then it print “Time when worker failed”  and set status to F

adctrl  also check this table to get the jobs information. When there is no patch running, you wont find this table  in  the database and if you try to run adctrl ,it will error saying this table does not exists

adctrl also update the status and control_code in this table to restart the failed job or workers

Hope you like the article on the important tables used for Ad utilities functioning. Please do provide the feedback


Leave a Reply