Thursday, May 2, 2013

Purging In Fusion Applications for the ESS Job files


Purging Fusion Applications ESS job files 

We need to purge the ess job data in the regular intervals in the fusion applications all the ess jobs details will be store in the FUSION_TX_TOOLS table space.. if your are not purging the data in the regular time interval then there will be a huge the growth of the fusion tools  table space.. In fusion we can purge the data by Specifying the application and function and programs 

Step 1

Login to the fusion applications Enterprise Manger which is your  default Domain for example if you are Under the Financial Domain then Login to the Fin Em if you are in HCM then log in to the HCM Em 


 Step 2

Navigate to the Farm_<Default_Domain> -->Scheduling Services ->ESSAPP-->
and click the essserver under 


Step 3
Select the scheduling services under the ESSAPP server from the top left side and select the Purge Policies from the drop down menu 

Step 4
It will take to the new page and you can click the setup new button to setup new purge policy


Step 5
navigate to the Purge Policy Setup page

Provide the Name for the Purge Policy for example if u want to purge all your import journal related data then provide the naming convention with that 

Step 6

Select the Particular module which need to be purged 
 
Step 7

Select the applications which the particular program resides 
for ex: we need to purge the import journal's data then we need to select the  FinancialsESSApp

 

Step8
 Select the necessary program and which it details to be purged 


Step 9
Specify  the execution type  and summited by the user name  if needed
we need to specify the no of retention period which the data will remain in the system
for example if we specify the retention period as 120 then the last 120 days records will be available in the system  we need to specify the retention period level for all the parameters like success.. failure..


Step10

Click the scheduling process for scheduling it  and you can use the existing schedule also or click once to run now with the time specified

Step 11
One the purge policy was create you can see it will be in the wait state until the Specific time

Step 12

Once the job is completed you can see the details of the request by clicking the request number

 We can check the details from the ess server also 

After This process in the Em we need to manually purge the data from the database.all the ESS job related data are store under the schema FUSION_ORA_ESS check the tables to know the details of the ESS jobs..

TABLE_NAME                               OWNER                          TABLESPACE_NAME
---------------------------------------- ------------------------------ ------------------------------
SCHEMA_INFO                              FUSION_ORA_ESS                 FUSION_TS_TOOLS
ESS_CONFIG                               FUSION_ORA_ESS                 FUSION_TS_TOOLS
ESS_APP_CONFIGS                          FUSION_ORA_ESS                 FUSION_TS_TOOLS
JOB_INCOMPATIBILITY                      FUSION_ORA_ESS                 FUSION_TS_TOOLS
ESS_APP_REGISTRATION                     FUSION_ORA_ESS                 FUSION_TS_TOOLS
ESS_COORD                                FUSION_ORA_ESS                 FUSION_TS_TOOLS
OPERATIVE_BINDINGS                       FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_HISTORY                          FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_PROPERTY                         FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_CONSTRAINT                       FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_CP                               FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_METADATA                         FUSION_ORA_ESS                 FUSION_TS_TOOLS
WAIT_QUEUE                               FUSION_ORA_ESS                 FUSION_TS_TOOLS
WORK_UNITS                               FUSION_ORA_ESS                 FUSION_TS_TOOLS
EVENT_WORK                               FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_INCOMPATIBILITY                  FUSION_ORA_ESS                 FUSION_TS_TOOLS
INCOMPATIBILITY_LOCK                     FUSION_ORA_ESS                 FUSION_TS_TOOLS
COMMAND_WORK                             FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_ATTRIBUTE                        FUSION_ORA_ESS                 FUSION_TS_TOOLS
REQUEST_CONTENT                          FUSION_ORA_ESS                 FUSION_TS_TOOLS
ESS_TEMP_REQID                           FUSION_ORA_ESS
PTEMP_REQUEST                            FUSION_ORA_ESS                 FUSION_TS_TOOLS
RECOVERY_DELETE                          FUSION_ORA_ESS                 FUSION_TS_TOOLS
FAILED_EXECUTION_HISTORY                 FUSION_ORA_ESS                 FUSION_TS_TOOLS
SCRATCH_QUERY_PRINCIPAL                  FUSION_ORA_ESS                 FUSION_TS_TOOLS
ESS_WS_ASYNC_INFO                        FUSION_ORA_ESS                 FUSION_TS_TOOLS
UNTHROTTLED_WORKASSIGNMENTS              FUSION_ORA_ESS                 FUSION_TS_TOOLS
ASYNC_ALLOCATIONS                        FUSION_ORA_ESS                 FUSION_TS_TOOLS
ASYNC_LIMITS                             FUSION_ORA_ESS                 FUSION_TS_TOOLS
CONFIGURED_BINDINGS                      FUSION_ORA_ESS                 FUSION_TS_TOOLS
EVENT_FILTER                             FUSION_ORA_ESS                 FUSION_TS_TOOLS

31 rows selected.
These are the number of tables count from each schema form the fusion which is occupied in the fusion_ts_tools table space
 
SQL> select distinct(owner),count(table_name)"no_of_tables" from dba_tables where tablespace_name ='FUSION_TS_TOOLS'
  2   group by owner
  3  order by 2;


OWNER                                    no_of_tables
---------------------------------------- ------------
FUSION_ORASDPLS                                     1
FUSION                                                           1
FUSION_ORASDPXDMS                              5
FUSION_ORASDPSDS                                  7
FUSION_PORTLET                                       12
FIN_FUSION_MDS_SOA                             14
FUSION_MDS_ESS                                      14
CRM_FUSION_MDS_SOA                          14
FUSION_MDS_SPACES                               14
PRJ_FUSION_MDS_SOA                             14
OIC_FUSION_MDS_SOA                             14
FUSION_MDS                                                14
SETUP_FUSION_MDS_SOA                        14
HCM_FUSION_MDS_SOA                           14
SCM_FUSION_MDS_SOA                            14
PRC_FUSION_MDS_SOA                             14
FUSION_ORA_ESS                                       30
FUSION_ORASDPM                                     31
FUSION_ACTIVITIES                                  42
FUSION_OTBI                                               44
FUSION_IPM                                                 49
FUSION_WEBCENTER                                66
FUSION_DISCUSSIONS                               69
FUSION_OCSERVER11G                             119
FUSION_BIPLATFORM                               152
FUSION_ODI                                                183
CRM_FUSION_SOAINFRA                         265
FIN_FUSION_SOAINFRA                            265
SETUP_FUSION_SOAINFRA                      265
SCM_FUSION_SOAINFRA                          265
HCM_FUSION_SOAINFRA                         265
PRJ_FUSION_SOAINFRA                            265
PRC_FUSION_SOAINFRA                           265
OIC_FUSION_SOAINFRA                            265

Once you have complete the Purging policy in the EM .. and successfully completed it ..the next step is to delete the metadata from the database directly

connect the database as the Fusion_ora_ess and then run the package
esspurge.purge_requests
The parameter are  
systimestamp = which you can specify the date no of days to retention [in this example we have specified 120 and it will hold last 120 days records and purge remaining data ]
The no of request can be purged : you can specify the number according to your wish [in this example we have specified 1000000 as the max number of records to be purged]

SQL> connect FUSION_ORA_ESS
Enter password:
Connected.
SQL> set serveroutput on size unlimited
SQL> set timing on
SQL> execute esspurge.purge_requests(systimestamp - 120,1000000);

PL/SQL procedure successfully completed.

Elapsed: 00:01:03.88
SQL>

No comments:

Post a Comment