In previous articles, i presented about Autopatch and various ad utilities.All these need the table fnd_install_processes for the parallel processing
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…
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.
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
- the Process create the FND_INSTALL_PROCESSES
- It starts the required adworkers
- adpatch or adop updates the job information in the table
- adworker keep reading the table and completing the information
- When all the job are completed, adpatch or adop signals the end of parallel processing
- 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
Meaning of FND_INSTALL_PROCESSES CONTROL_CODE values
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