Concurrent Processing – Purge Concurrent Request and/or Manager Data Program (FNDCPPUR)
Use this program 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.
Use this program to compute performance statisics for each of the concurrent programs, if the Concurrent: Collect Request Statistics profile option is set to “Yes”.
Tables that are updated when Oracle Applications Concurrent Program is started
FND_CONCURRENT_REQUESTS
This table contains a complete history of all concurrent requests. This table should ideally be kept at around 4 – 5k records and should be regularly purged as part of normal ‘housekeeping’ by running the standard FNDCPPUR program.This is a part of basic system administration and it is recommended
that for the average instance, it should be run every 30 days or so. Allowing the table ( and others ) to increase in size can affect performance.
FND_RUN_REQUESTS
When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.
FND_CONC_REQUEST_ARGUMENTS
This table records arguments passed by the concurrent manager to each program it starts running.
FND_DUAL
This table records when requests do not update database tables.
FND_CONCURRENT_PROCESSES
This table records information about Oracle Applications and operating system processes.
FND_CONC_STAT_LIST
This table collects runtime performance statistics for concurrent requests.
FND_CONC_STAT_SUMMARY
This table contains the concurrent program performance statistics generated by the Purge.
FND_ENV_CONTEXT
This table stores information about environment name and value for each of the concurrent process
To avoid running out of space on your disk drives, you should periodically delete Oracle Applications log files, output files and purge these tables with the FNDCPPUR program.
The FNDCPUR purge program maintains the number of log and output files the operating system retains, and manage tables that store information about concurrent requests and concurrent manager processes.
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 :
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 : 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 Nuber 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..
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. For more information, please refer to the Oracle Applications Manager online help.
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:
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 itemtype.
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.
4. Choose OK to close the Parameters window.
5. When you finish modifying the print and run options for this request, choose Submit to submit the request.
Purge Logs and Closed System Alerts
NAME: Purge Logs and Closed System Alerts
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.
Purge Signon Audit Data Concurrent Program
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 tables
FND_LOGIN_RESP_FORMS
FND_LOGIN_RESPONSIBILITIES
FND_LOGINS
FND_UNSUCCESSFUL_LOGINS
are purged.
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 )
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 recommend users to run this program Weekly, Daily or even Hourly if your ATP transaction volume is very high (see Notes).
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
APPENDIX:
ADDITIONAL INFORMATION:
Program: Purge Logs and Closed System Alerts
Queries for the “Purge Logs and Closed System Alerts” Program
1. Which concurrent program should run to purge FND_LOG_MESSAGES in Oracle Application Release 11i and Release 12?
2. On 12.0.4 in Production: When attempting to run concurrent program “Purge Diagnostic and Log Messages”, following error occurs:
Error:
ORACLE error 6550 in FDPSTP
Cause: FDPSTP failed due to ORA-06550: line 1, column 20:
PLS-00302: component ‘PURGE_BUSINESS_EXCEPTIONS’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
3. FND_LOG_MESSAGES table is not getting purged when running “FNDLGPRG module: Purge Debug Log and System Alerts”
Solution for the above queries:
First Query Solution:
For Purging data in “FND_LOG_MESSAGES” table, below concurrent program has to run successfully.
In Oracle Application Release 11i :
========================
Please run concurrent program “Purge Debug Log and System Alerts” (short name: FNDLGPRG).
In Oracle Application Release 12 :
=====================
Please run concurrent program “Purge Logs and Closed System Alerts” (short name: FNDLGPRG).
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.
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. Details for each table can be found in eTRM.
Second Query Solution:
“Purge Debug Log and System Alerts” is name of old purge program that was used in Oracle Application Release 11i, “Purge Logs and Closed System Alerts” is name of new one introduced in Release 12. Both of them do basically same thing, that is purge logs and alerts. “Purge Diagnostic and Log Messages” is no longer used in R12.
If someone runs concurrent program “Purge Diagnostic and Log Messages” then it will error out with below error message in concurrent program logfile :
Error:
ORACLE error 6550 in FDPSTP
Cause: FDPSTP failed due to ORA-06550: line 1, column 20:
PLS-00302: component ‘PURGE_BUSINESS_EXCEPTIONS’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
To overcome the above error “Purge Logs and Closed System Alerts [FNDLGPRG]” should be run instead of “Purge Debug Log and System Alerts” in Release 12.
To disable Concurrent program “Purge Diagnostic and Log Message” in Release 12 do the following:
1. Navigate to System Administrator => Concurrent => Program => Define.
2. Query for “Purge Diagnostic and Log Messages”.
3. Uncheck the check box Enable.
4. Save.
Third Query Solution:
This issue is due to some of the records in FND_LOG_MESSAGES not having associated
records in fnd_log_transaction_context.
Run the query below to confirm if FND_LOG_MESSAGES table is not having corresponding rows for transaction_context_id in table FND_LOG_TRANSACTION_CONTEXT.
SQL> select module, transaction_context_id from fnd_log_messages where transaction_context_id not in (select distinct TRANSACTION_CONTEXT_ID from FND_LOG_TRANSACTION_CONTEXT);
If the above query returns any rows then you are experiencing the issue stated above.
Solution: In the new file versions recommended below, a delete statement was added to remove any records in fnd_log_messages where the transaction_context_id does not exist in fnd_log_transaction_context.
To implement the solution, please execute the following steps:
1. For Oracle Application Release 11i download and review the readme and pre-requisites for Patch 8989384 .
For Oracle Application Release 12 download and review the readme and pre-requisites for Patch 9869868 .
For Oracle Application Release 12.1 download and review the readme and pre-requisites for Patch 10022143 .
Note : Version of AFUTLGAB.pls-120.2.12010000.2 is delivered with Oracle Application release 12.1.2 and 12.1.3 and hence Patch 10022143 is not required on the top of Oracle Application release 12.1.2 or 12.1.3.
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Confirm the following file versions:
Release 11i
==================
AFUTLGAB.pls 115.34
You can use the commands like the following:
strings -a $FND_TOP/patch/115/sql/AFUTLGAB.pls |grep ‘$Header’
Release 12
==================
AFUTLGAB.pls 120.1.12000000.3
You can use the commands like the following:
strings -a $FND_TOP/patch/115/sql/AFUTLGAB.pls |grep ‘$Header’
Release 12.1
==================
AFUTLGAB.pls-120.2.12010000.2
You can use the commands like the following:
strings -a $FND_TOP/patch/115/sql/AFUTLGAB.pls |grep ‘$Header’
5. Retest the issue and run concurrent program “Purge Logs and Closed System Alerts”. After successful run of concurrent program, above SQL should not return any rows and it should able to purge those records from FND_LOG_MESSAGES table which does not have corresponding rows for transaction_context_id in table FND_LOG_TRANSACTION_CONTEXT.
6. Migrate the solution as appropriate to other environments
Program: Purge ATP Temp Tables
Notes:
1. This data is throw away data populated for the purpose of providing data for Scheduling results (MRP_ATP_SCHEDULE_TEMP) and that is viewed in the ATP Details window (MRP_ATP_DETAILS_TEMP).
— Once the Availability window is closed, the data has no purpose unless debugging an ATP issue and data is requested by Support or DEV to debug the issue.
2. More data will be present in the table MRP_ATP_DETAILS_TEMP if profile MRP: Calculate Supply/Demand = Yes, since we write extra data for the ATP Details and other screens.
When the profile = Yes, then MRP_ATP_DETAILS_TEMP gets rows with record_type = 1, 2 and 3.
Record Type 1 and 2 are used to show detailed information available when you use rt-click functionality in the Pegging window of the ATP Details to view Supply Demand, or Horizontal Plan or other information.
Record_Type = 3 is the pegging information seen in the ATP details screen.
When the profile = No, we still insert record_type = 3 into the table so we can display the pegging information in the ATP Details screen.
For example, if the item is a large configuration with many rows in the pegging view, then we could still insert many records into the table with only record_type = 3
3. This data has no effect on ASCP planning.
BUT – if you have separate EBS Source and APS Destination, then you need to run this request on BOTH the EBS Source and APS Destination.
4. Many customers will set this to run once per week with parameter 250 (hours).
OR In higher volume operations, it may run once per day with parameter 24,
OR in very high volume operations, it may run even once or twice per hour with parameter = 1
Note: Before setting up the request to run on a schedule, it is important to truncate the tables (run with parameter = 0) to make sure the space is recovered
5. Support may request that this be put on hold during an investigation of an ATP issue, but this is not always required.
6. If the number of rows in MRP_ATP_DETAILS_TEMP exceeds 1 million rows, then it would be desirable for the DBA to truncate the table (using <zero> for the program parameter) during either;
A) An application downtime event
or
B) When no users are entering sales orders on the system. This will prevent excessive use of rollback which can happen using the standard Purge concurrent program with many rows in the table. This can also help to recover space which cannot be accomplished using delete statements of the concurrent program.
— Even if a user was scheduling an order at the time of the truncate and received an error, then they could try scheduling again and will be successful.
1. How can I check the tables to see if we need to truncate them?
Ans:Run the following SQL to check sizes tables and indexes used for this process.
select DS.OWNER, OBJECT_NAME, OBJECT_TYPE, BYTES/1024/1024 SIZE_IN_MB
from DBA_SEGMENTS DS, DBA_objects DT
where DS.OWNER=DS.OWNER
and DS.SEGMENT_NAME=DT.OBJECT_NAME
and dt.object_name like ‘MRP%ATP%TEMP%’;
— When Decentralized installation, Then
— MRP objects are populated on EBS Source
— MSC objects are populated on APS Destination
2: Why are the tables very large when we have been running Purge ATP Temp Tables regularly
Ans: They may not have been truncated before you started running the program. When we delete from tables, then the RDBMS will not recover the space used and table size can continue to grow, even though there may not be many rows in the table.
Suggest that if this is encountered, then run Purge ATP Temp Tables with parameter = 0 when users are not entering orders and this will recover the space.
In very high volume operations, we have seen customers report that tables sizes grow to several GB in a single day. In this case, we recommend that you truncate the tables using Purge ATP Temp Tables every hour with parameter = 0, then schedule the request to run every hour with parameter = 1.
Use this program 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.
Use this program to compute performance statisics for each of the concurrent programs, if the Concurrent: Collect Request Statistics profile option is set to “Yes”.
Tables that are updated when Oracle Applications Concurrent Program is started
FND_CONCURRENT_REQUESTS
This table contains a complete history of all concurrent requests. This table should ideally be kept at around 4 – 5k records and should be regularly purged as part of normal ‘housekeeping’ by running the standard FNDCPPUR program.This is a part of basic system administration and it is recommended
that for the average instance, it should be run every 30 days or so. Allowing the table ( and others ) to increase in size can affect performance.
FND_RUN_REQUESTS
When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.
FND_CONC_REQUEST_ARGUMENTS
This table records arguments passed by the concurrent manager to each program it starts running.
FND_DUAL
This table records when requests do not update database tables.
FND_CONCURRENT_PROCESSES
This table records information about Oracle Applications and operating system processes.
FND_CONC_STAT_LIST
This table collects runtime performance statistics for concurrent requests.
FND_CONC_STAT_SUMMARY
This table contains the concurrent program performance statistics generated by the Purge.
FND_ENV_CONTEXT
This table stores information about environment name and value for each of the concurrent process
To avoid running out of space on your disk drives, you should periodically delete Oracle Applications log files, output files and purge these tables with the FNDCPPUR program.
The FNDCPUR purge program maintains the number of log and output files the operating system retains, and manage tables that store information about concurrent requests and concurrent manager processes.
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 :
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 : 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 Nuber 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..
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. For more information, please refer to the Oracle Applications Manager online help.
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:
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 itemtype.
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.
4. Choose OK to close the Parameters window.
5. When you finish modifying the print and run options for this request, choose Submit to submit the request.
Purge Logs and Closed System Alerts
NAME: Purge Logs and Closed System Alerts
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.
Purge Signon Audit Data Concurrent Program
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 tables
FND_LOGIN_RESP_FORMS
FND_LOGIN_RESPONSIBILITIES
FND_LOGINS
FND_UNSUCCESSFUL_LOGINS
are purged.
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 )
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 recommend users to run this program Weekly, Daily or even Hourly if your ATP transaction volume is very high (see Notes).
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
APPENDIX:
ADDITIONAL INFORMATION:
Program: Purge Logs and Closed System Alerts
Queries for the “Purge Logs and Closed System Alerts” Program
1. Which concurrent program should run to purge FND_LOG_MESSAGES in Oracle Application Release 11i and Release 12?
2. On 12.0.4 in Production: When attempting to run concurrent program “Purge Diagnostic and Log Messages”, following error occurs:
Error:
ORACLE error 6550 in FDPSTP
Cause: FDPSTP failed due to ORA-06550: line 1, column 20:
PLS-00302: component ‘PURGE_BUSINESS_EXCEPTIONS’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
3. FND_LOG_MESSAGES table is not getting purged when running “FNDLGPRG module: Purge Debug Log and System Alerts”
Solution for the above queries:
First Query Solution:
For Purging data in “FND_LOG_MESSAGES” table, below concurrent program has to run successfully.
In Oracle Application Release 11i :
========================
Please run concurrent program “Purge Debug Log and System Alerts” (short name: FNDLGPRG).
In Oracle Application Release 12 :
=====================
Please run concurrent program “Purge Logs and Closed System Alerts” (short name: FNDLGPRG).
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.
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. Details for each table can be found in eTRM.
Second Query Solution:
“Purge Debug Log and System Alerts” is name of old purge program that was used in Oracle Application Release 11i, “Purge Logs and Closed System Alerts” is name of new one introduced in Release 12. Both of them do basically same thing, that is purge logs and alerts. “Purge Diagnostic and Log Messages” is no longer used in R12.
If someone runs concurrent program “Purge Diagnostic and Log Messages” then it will error out with below error message in concurrent program logfile :
Error:
ORACLE error 6550 in FDPSTP
Cause: FDPSTP failed due to ORA-06550: line 1, column 20:
PLS-00302: component ‘PURGE_BUSINESS_EXCEPTIONS’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
To overcome the above error “Purge Logs and Closed System Alerts [FNDLGPRG]” should be run instead of “Purge Debug Log and System Alerts” in Release 12.
To disable Concurrent program “Purge Diagnostic and Log Message” in Release 12 do the following:
1. Navigate to System Administrator => Concurrent => Program => Define.
2. Query for “Purge Diagnostic and Log Messages”.
3. Uncheck the check box Enable.
4. Save.
Third Query Solution:
This issue is due to some of the records in FND_LOG_MESSAGES not having associated
records in fnd_log_transaction_context.
Run the query below to confirm if FND_LOG_MESSAGES table is not having corresponding rows for transaction_context_id in table FND_LOG_TRANSACTION_CONTEXT.
SQL> select module, transaction_context_id from fnd_log_messages where transaction_context_id not in (select distinct TRANSACTION_CONTEXT_ID from FND_LOG_TRANSACTION_CONTEXT);
If the above query returns any rows then you are experiencing the issue stated above.
Solution: In the new file versions recommended below, a delete statement was added to remove any records in fnd_log_messages where the transaction_context_id does not exist in fnd_log_transaction_context.
To implement the solution, please execute the following steps:
1. For Oracle Application Release 11i download and review the readme and pre-requisites for Patch 8989384 .
For Oracle Application Release 12 download and review the readme and pre-requisites for Patch 9869868 .
For Oracle Application Release 12.1 download and review the readme and pre-requisites for Patch 10022143 .
Note : Version of AFUTLGAB.pls-120.2.12010000.2 is delivered with Oracle Application release 12.1.2 and 12.1.3 and hence Patch 10022143 is not required on the top of Oracle Application release 12.1.2 or 12.1.3.
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Confirm the following file versions:
Release 11i
==================
AFUTLGAB.pls 115.34
You can use the commands like the following:
strings -a $FND_TOP/patch/115/sql/AFUTLGAB.pls |grep ‘$Header’
Release 12
==================
AFUTLGAB.pls 120.1.12000000.3
You can use the commands like the following:
strings -a $FND_TOP/patch/115/sql/AFUTLGAB.pls |grep ‘$Header’
Release 12.1
==================
AFUTLGAB.pls-120.2.12010000.2
You can use the commands like the following:
strings -a $FND_TOP/patch/115/sql/AFUTLGAB.pls |grep ‘$Header’
5. Retest the issue and run concurrent program “Purge Logs and Closed System Alerts”. After successful run of concurrent program, above SQL should not return any rows and it should able to purge those records from FND_LOG_MESSAGES table which does not have corresponding rows for transaction_context_id in table FND_LOG_TRANSACTION_CONTEXT.
6. Migrate the solution as appropriate to other environments
Program: Purge ATP Temp Tables
Notes:
1. This data is throw away data populated for the purpose of providing data for Scheduling results (MRP_ATP_SCHEDULE_TEMP) and that is viewed in the ATP Details window (MRP_ATP_DETAILS_TEMP).
— Once the Availability window is closed, the data has no purpose unless debugging an ATP issue and data is requested by Support or DEV to debug the issue.
2. More data will be present in the table MRP_ATP_DETAILS_TEMP if profile MRP: Calculate Supply/Demand = Yes, since we write extra data for the ATP Details and other screens.
When the profile = Yes, then MRP_ATP_DETAILS_TEMP gets rows with record_type = 1, 2 and 3.
Record Type 1 and 2 are used to show detailed information available when you use rt-click functionality in the Pegging window of the ATP Details to view Supply Demand, or Horizontal Plan or other information.
Record_Type = 3 is the pegging information seen in the ATP details screen.
When the profile = No, we still insert record_type = 3 into the table so we can display the pegging information in the ATP Details screen.
For example, if the item is a large configuration with many rows in the pegging view, then we could still insert many records into the table with only record_type = 3
3. This data has no effect on ASCP planning.
BUT – if you have separate EBS Source and APS Destination, then you need to run this request on BOTH the EBS Source and APS Destination.
4. Many customers will set this to run once per week with parameter 250 (hours).
OR In higher volume operations, it may run once per day with parameter 24,
OR in very high volume operations, it may run even once or twice per hour with parameter = 1
Note: Before setting up the request to run on a schedule, it is important to truncate the tables (run with parameter = 0) to make sure the space is recovered
5. Support may request that this be put on hold during an investigation of an ATP issue, but this is not always required.
6. If the number of rows in MRP_ATP_DETAILS_TEMP exceeds 1 million rows, then it would be desirable for the DBA to truncate the table (using <zero> for the program parameter) during either;
A) An application downtime event
or
B) When no users are entering sales orders on the system. This will prevent excessive use of rollback which can happen using the standard Purge concurrent program with many rows in the table. This can also help to recover space which cannot be accomplished using delete statements of the concurrent program.
— Even if a user was scheduling an order at the time of the truncate and received an error, then they could try scheduling again and will be successful.
1. How can I check the tables to see if we need to truncate them?
Ans:Run the following SQL to check sizes tables and indexes used for this process.
select DS.OWNER, OBJECT_NAME, OBJECT_TYPE, BYTES/1024/1024 SIZE_IN_MB
from DBA_SEGMENTS DS, DBA_objects DT
where DS.OWNER=DS.OWNER
and DS.SEGMENT_NAME=DT.OBJECT_NAME
and dt.object_name like ‘MRP%ATP%TEMP%’;
— When Decentralized installation, Then
— MRP objects are populated on EBS Source
— MSC objects are populated on APS Destination
2: Why are the tables very large when we have been running Purge ATP Temp Tables regularly
Ans: They may not have been truncated before you started running the program. When we delete from tables, then the RDBMS will not recover the space used and table size can continue to grow, even though there may not be many rows in the table.
Suggest that if this is encountered, then run Purge ATP Temp Tables with parameter = 0 when users are not entering orders and this will recover the space.
In very high volume operations, we have seen customers report that tables sizes grow to several GB in a single day. In this case, we recommend that you truncate the tables using Purge ATP Temp Tables every hour with parameter = 0, then schedule the request to run every hour with parameter = 1.
Recommended Posts
