Oracle DBAs Purge Programs
[Edition 03]
[Last Update 210304]
[email protected]
Contents
1 Introduction .........................................................................................................................................................3
2 Oracle DBAS Purge Programs ........................................................................................................................4
2.1 Concurrent Processing - Purge Concurrent Request and/or Manager Data Program
(FNDCPPUR) ............................................................................................................................................................................. 4
2.2 Purge Obsolete Workflow Runtime Data concurrent request (FNDWFPR) .......................................... 8
2.2.1 Workflow Control Queue Cleanup ...............................................................................................................................10
2.2.2 Workflow Background Process .....................................................................................................................................10
2.2.3 Workflow Mailer Statistics Concurrent Program ..................................................................................................11
2.2.4 Workflow Work Items Statistics Concurrent Program .......................................................................................11
2.2.5 Workflow Agent Activity Statistics Concurrent Program...................................................................................11
2.2.6 Gather Schema Statistics Schedule: .............................................................................................................................11
2.2.7 To Submit the Program Manually from SQLPlus ...................................................................................................12
2.3 Purge Logs and Closed System Alerts(FNDLGPRG) ...................................................................................... 12
2.4 Purge Signon Audit Data Concurrent Program(FNDSCPRG) ..................................................................... 14
2.5 Purge ATP Temp Tables ......................................................................................................................................... 14
3 Generic Documents ......................................................................................................................................... 16
[email protected]
1 INTRODUCTION
This Document covers the purge programs required to purge the obsolete data of EBS R12.2 with a
detailed explanation.
.
[email protected]
2 ORACLE DBAS PURGE PROGRAMS
Purge Programs:
1. FNDCPPUR
2. FNDWFPR
3. FNDLGPRG
4. FNDSCPRG
5. Purge ATP Temp Tables
2.1 Concurrent Processing -
Purge Concurrent Request
and/or Manager Data
Program (FNDCPPUR)
This program is used to delete:
➢ request log files, concurrent manager log files, and report output files from your product
directories maintained by the operating system
➢ records (rows) from Application Object Library database tables that contain history
information about concurrent requests and concurrent manager processes.
To run the Purge Concurrent Request and/or Manager Data program:
1. Log in to Application as System Administrator responsibility.
2. Navigate to Request> Run> Single Request
3. Query up Purge Concurrent Requests.
4. For values to be entered in the parameter screen refer to the Oracle
Administration System Administration Guide
The following tables will be purged:
Table Name Description
[email protected]
Table contains a complete history of all
FND_CONCURRENT_REQUESTS concurrent requests.
When a user submits a report set, this table
FND_RUN_REQUESTS stores information about the reports in the
report set and the parameter values for
each report.
Table records arguments passed by the
FND_CONC_REQUEST_ARGUMENTS concurrent manager to each program it
starts running.
Table for forms which only submit
FND_DUAL concurrent requests, and need to perform a
commit cycle without updating a
applications database table
Table records information about Oracle
FND_CONCURRENT_PROCESSES Applications and operating system
processes.
Table serves as a register of output files
FND_CONC_REQ_OUTPUTS generated by requests
Stores the post request processing
actions(e.g., print, notify) for each
FND_CONC_PP_ACTIONS submitted request. There's a
concurrent_request_id here for each
request_id
in the FND_CONCURRENT_REQUESTS.
Stores the post request processing
actions(e.g., print, notify) for submitted
FND_RUN_REQ_PP_ACTIONS
[email protected]
request set programs that are stored in
FND_RUN_REQUESTS
Table stores information about environment
FND_ENV_CONTEXT name and value for each of the concurrent
process
You can run the program FNDCPPUR once and automatically resubmit the program for your
specific time intervals.
Here are some sample guidelines to run the purge program. Adopt these guidelines according to
your user community's usage of Oracle Applications.
➢ every 30 days for normal usage
➢ every two weeks (14 days) for heavy usage
➢ if you are using the AGE mode, set the Mode Value to 7 or higher to retain the most recent
days of concurrent request data, log files, and report output files.
WARNING: When you purge concurrent request information, you lose audit details which are
used by the Signon Audit Concurrent Requests report.
FNDCPPUR Program options(Parameters):
➢ ENTITY = ALL
Purge of concurrent requests, concurrent managers, request log files, manager log files
and report output files.
The following tables are purged:
➢ Fnd_Concurrent_Processes
➢ Fnd_Dual
➢ Fnd_Concurrent_Requests
➢ Fnd_Run_Requests
➢ Fnd_Conc_Request_Arguments
➢ Fnd_Dual
➢ Fnd_Context_Env
➢ Deletes concurrent requests' log and out files from OS.
➢ ENTITY = MANAGER
[email protected]
Purge of concurrent managers and manager log files
The following tables are purged:
➢ Fnd_Concurrent_Processes
➢ Fnd_Dual
➢ Deletes concurrent manager log files from OS
➢ ENTITY = REQUEST
Purge of concurrent requests, request log files and output files.
The following tables are purged:
➢ Fnd_Concurrent_Requests
➢ Fnd_Run_Requests
➢ Fnd_Conc_Request_Arguments
➢ Fnd_Dual
➢ Deletes concurrent requests' log and out files from OS
➢ Mode
➢ AGE: Number of days
➢ COUNT: Number of records
➢ Mode Value: valid values are 1 – 9999999
➢ User Name: application username
➢ Oracle ID: Oracle ID
➢ Program Application: application
➢ Program: program
➢ Manager Application: application associated with the concurrent manager
➢ Manager: concurrent manager
➢ Resp. Application: application associated with the responsibility
➢ Responsibility: responsibility or "All".
➢ Report
➢ No: Run the program but do not generate a report.
➢ Yes: Run the program and generate a report.
WARNING:
The only option which purges all tables is the option "ENTITY = ALL". It is better to use this option
to synchronise the Concurrent Requests and Concurrent Processes tables.
[email protected]
2.2 Purge Obsolete Workflow
Runtime Data concurrent
request (FNDWFPR)
Description:
Purge Obsolete Workflow Runtime Data concurrent program to purge obsolete runtime work
item information, including status information and any associated notifications and Oracle XML
Gateway transactions. Use the Submit Requests form in Oracle Applications to submit this
concurrent program.
By default, this program purges obsolete runtime information associated with work items as well
as obsolete design information, such as activities that are no longer in use and expired users and
roles, and obsolete runtime information not associated with work items, such as notifications or
Oracle XML Gateway transactions that were not handled through a workflow process. You can
optionally choose to purge only core runtime information associated with work items for
performance gain during periods of high activity,and purge all obsolete information as part of your
routine maintenance during periods of low activity.
Note: If you are using the version of Oracle Workflow embedded in Oracle Applications and you
have implemented Oracle Applications Manager, you can use Oracle Workflow Manager to submit
and manage the Purge Obsolete Workflow Runtime Data concurrent program.
Navigation:
1. Navigate to the Submit Requests form in Oracle Applications to submit the Purge
Obsolete Workflow Runtime Data concurrent program. When you install and set up
Oracle Applications and Oracle Workflow, your system administrator needs to add this
concurrent program to a request security group for the responsibility that you want to
run this program from. The executable name for this concurrent program is "Oracle
Workflow Purge Obsolete Data" and its short name is FNDWFPR. See: Overview of
Concurrent Programs and Requests, Oracle Applications System Administrator's
Guide.
2. Submit the Purge Obsolete Workflow Runtime Data concurrent program as a request.
See: Running Reports and Programs, Oracle Applications User's Guide.
3. In the Parameters window, enter values for the following
parameters:
[email protected]
Item Type: Item type associated with the obsolete runtime data you want to
delete. Leave this argument null to delete obsolete runtime data for all item
types.
Item Key: A string generated from the application object's primary key. The
string uniquely identifies the item within an item type. If null, the program purges
all items in the specified item type.
Age: Minimum age of data to purge, in days, if the persistence type is set to
'TEMP'. The default is 0.
Persistence Type: Persistence type to be purged, either 'TEMP' for Temporary
or 'PERM' for Permanent. The default is 'TEMP'.
Core Workflow Only: Enter 'Y' to purge only obsolete runtime data associated
with work items, or 'N' to purge all obsolete runtime data as well obsolete design
data. The default is 'N'.
Transaction Type: The Oracle XML Gateway transaction type to purge. Leave
this argument null to purge the runtime data for all transaction types.
Transaction Subtype: The Oracle XML Gateway transaction subtype to purge.
The transaction subtype is a code for a particular transaction within the
application specified by the transaction type. Leave this argument null to purge
the runtime data for all transactions of the specified transaction type.
Commit Frequency: Enter the number of records to purge before the program
commits data. To reduce rollback size and improve performance, set this
parameter to commit data after a smaller number of records. The default is 500
records.
Signed Notifications: Enter 'N' to preserve signature evidence, including
notifications that required electronic signatures and their associated signature
information. Enter 'Y' to purge signature-related information. The default is 'N'.
Note: After performing a commit, the program resumes purging work items with the next
subsequent begin date. In some cases, if additional items have the same begin date as
the last item that was purged before a commit, the program may not purge all eligible
items. To purge these remaining work items, simply rerun the program.
4. Choose OK to close the Parameters window.
[email protected]
5. When you finish modifying the print and run options for this request, choose Submit to
submit the request.
2.2.1 WORKFLOW CONTROL QUEUE CLEANUP
Parameters: NONE
Schedule:
Frequency: Run every 12 Hours
Responsibility: System Administrator
Please tick the checkbox “From the start of the prior run”
2.2.2 WORKFLOW BACKGROUND PROCESS
This program needs to be submitted in various permutation and combination.
Type-1: which Runs Every 3 Mins for Parameters:
Process Deferred: Yes
Process Timeout: No
Process Stuck: No
Type-2: which Runs Every 30 Mins for Parameters:
Process Deferred: No
Process Timeout: Yes
Process Stuck: No
Type-3: which Runs Every 1 Hours for Parameters:
Process Deferred: No
"Process Timeout: No
Process Stuck: Yes"
Type-4: which Runs Every 2 Hours for Parameters:
Process Deferred: Yes
"Process Timeout: Yes
Process Stuck: Yes"
[email protected]
2.2.3 WORKFLOW MAILER STATISTICS CONCURRENT PROGRAM
Parameters: NONE
Schedule:
Frequency: Run every 1 Day
Responsibility: System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”
2.2.4 WORKFLOW WORK ITEMS STATISTICS CONCURRENT PROGRAM
Parameters: NONE
Schedule:
Frequency: Run every 1 Day
Responsibility: System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”
2.2.5 WORKFLOW AGENT ACTIVITY STATISTICS CONCURRENT PROGRAM
Parameters: NONE
Schedule:
Frequency: Run every 1 Day
Responsibility: System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”
2.2.6 GATHER SCHEMA STATISTICS SCHEDULE:
[email protected]
Make sure Gather Shema Stats is running on periodical basis, for XXXXX we have scheduled to
run every 15 Days.
Following points needs to be noted for the Gather Schema Statistics:
➢ Do not gather statistics excessively on entire schemas or the entire database such as
nightly or weekly.
➢ Do not gather statistics on permanent objects during peak intervals.
➢ Gathering statistics invalidates cursors .Unless you use the ‘No Invalidate’ option.
➢ Gathering statistics requires dictionary and object level locks.
➢ The option 'GATHER_AUTO' can be used, to gather statistics only on objects that have
changes above the specified 'Modification Threshold' (percentage of DML compared to the
number of rows of the table).
➢ Plans are not likely to change if the data distribution has not changed.
➢ Use only FND_STATS or the Gather Schema and Gather Table Statistics Concurrent
Programs.
➢ Do NOT USE the analyze or dbms_stats command directly. It is not supported, and results
in sub-optimal plans.
Following CONCSUB Command can be used to schedule the Gather Schema Statistics
concurrent Program:
"CONCSUB apps/${xx_appspassword} SYSADMIN 'System Administrator' SYSADMIN
CONCURRENT FND FNDGSCST START='""01-MAR-2012 02:00:00""'
REPEAT_DAYS=10 'ALL','10','18','NOBACKUP','','LASTRUN','GATHER','','Y'"
2.2.7 TO SUBMIT THE PROGRAM MANUALLY FROM SQLPLUS
To submit the program manually from SQL PLUS following commands can be used:
For One Schema: SQL> exec fnd_stats.gather_schema_statistics('MRP');
For All Schema: SQL> exec fnd_stats.gather_schema_statistics('ALL');
For One Table: SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');
2.3 Purge Logs and Closed
System Alerts(FNDLGPRG)
NAME: Purge Logs and Closed System Alerts
[email protected] SHORT CODE: FNDLGPRG
MODULE: Oracle Application Object Library
Description:
➢ Concurrent program "Purge Debug Log and System Alerts" in Release 11i and "Purge Logs
and Closed System Alerts" in Release 12 is recommended way to purge messages.
➢ This program purges all messages up to the specified date,except messages for active
transactions (new or open alerts, active ICX sessions, concurrent requests, and so on).
➢ This program is by default scheduled to run daily and purge messages older than 7 days.
Internally this concurrent program invokes FND_LOG_ADMIN APIs. Purges Logs for
expired Transactions and Closed System Alerts data by date.
Navigation:
Oracle Application Object Library Responsibility -> View -> Requests -> Submit a new request ->
Select Single Request -> Click ‘OK’ -> Select Name of concurrent program / report.
Report Parameters:
§ Last Purge Date : Rows will be purged up to and including this date
Following tables will be deleted when you run "Purge Logs and Closed System Alerts'" or
"Purge Debug Log and System Alerts" program :
➢ FND_EXCEPTION_NOTES;
➢ FND_OAM_BIZEX_SENT_NOTIF;
➢ FND_LOG_METRICS;
➢ FND_LOG_UNIQUE_EXCEPTIONS;
➢ FND_LOG_EXCEPTIONS;
➢ FND_LOG_MESSAGES;
➢ FND_LOG_TRANSACTION_CONTEXT;
➢ FND_LOG_ATTACHMENTS
These tables contain debug & error messages.
[email protected]
2.4 Purge Signon Audit Data
Concurrent
Program(FNDSCPRG)
NAME: Purge Signon Audit data
SHORT CODE: FNDSCPRG
MODULE: Oracle Application Object Library
Description:
Purges all Signon Audit information created before a given date
An examination of the file $FND_TOP/sql/FNDSCPRG.sql version 115.2, the SQL*Plus script run
by the Purge Signon Audit Data concurrent program, shows that the below tables are purged.
➢ FND_LOGIN_RESP_FORMS
➢ FND_LOGIN_RESPONSIBILITIES
➢ FND_LOGINS
➢ FND_UNSUCCESSFUL_LOGINS
In the latest version of $FND_TOP/sql/FNDSCPRG.sql (115.4) the FND_APPL_SESSIONS
table is also purged.
Navigation:
Oracle Application Object Library Responsibility -> View -> Requests -> Submit a new request ->
Select Single Request -> Click ‘OK’ -> Select Name of concurrent program / report.
Report Parameters:
§ Audit date : Signon audit information creation date ( Program will delete all Signon Audit
information created before this date)
2.5 Purge ATP Temp Tables
Description:
GOP stores temporary data (supply, demand, horizontal plan, pegging) for each ATP transaction.
The table size grows proportionally to the number of ATP transactions. This concurrent program
purges the temporary data according to the 'age of the data' you specify for deletion. We
[email protected]
recommend users to run this program Weekly, Daily or even Hourly if your ATP transaction
volume is very high.
Parameter of the concurrent program:
Age of Entry (in hours): you can specify the age of the data you want to delete.
For example, if you enter '1', this program will delete any data that is more than 1 hour old.
If 0 <zero> is entered, then the tables will be truncated. This should only be used when users are
not entering Sales Orders or using the ATP inquiry.
Tables deleted by this process are:
➢ MRP_ATP_SCHEDULE_TEMP
➢ MRP_ATP_DETAILS_TEMP
in 11.5.10.2 and above also includes MSC_ATP_SRC_PROFILE_TEMP
Navigation:
Oracle ASCP Responsibility -> View -> Requests -> Submit a new request -> Select Single
Request -> Click ‘OK’ -> Select Name of concurrent program
This section ends here.
[email protected]
3 GENERIC DOCUMENTS
1. Concurrent Processing - How To Run the Purge Concurrent Request FNDCPPUR, Which
Tables Are Purged, And Known Issues Like Files Are Not Deleted From File System (Doc
ID 822368.1)
2. Concurrent Processing - Purge Concurrent Request and/or Manager Data Program
(FNDCPPUR) (Doc ID 104282.1)
3. How to Submit a Workflow Background Process Engine (Doc ID 182936.1)
[email protected]
