12c SOA - Auto Purging SOA Infra setups

Use the Auto Purge page in Oracle Enterprise Manager Fusion Middleware Control to schedule and execute jobs that automatically remove older flow instances, adapter reports, and fault alerts data from the database.

Step1: Under your soa-infra open the menu SOA Infrastructure⇾SOA Administration⇾Auto Purge

Step2: In the Auto Purge option you will have to define multiple sections.

Pick your "Auto Purge Job":

• SOA Flow Purge Job 1
• SOA Flow Purge Job 2
• SOA In-Memory Flow Purge Job

Activate the "Enable" button

Define your "Job Schedule"

Set your "Retain Data"

Hit the Apply Button

Auto Purge Job:

Run delete_instances_auto_job1 on a schedule appropriate for weekdays (Monday through Friday at midnight). This job is automatically enabled.

Run delete_instances_auto_job2 on a weekend schedule (Saturday and Sunday) that may be more aggressive. This job is not automatically enabled.

Enabled:

Select to enable automatic database purging with the database purge job selected from the Auto Purge Job list.

The purge interval begins when it is enabled. For example, If you specify seven days in the Retain Data field, data is retained from the day you re-enabled this checkbox. Newer data is retained for seven days from when it was created.

Calendaring Expression icon:

Click to view examples of job scheduling syntax. Copy and paste the syntax appropriate to your environment into the Job Schedule field and modify as necessary. Click More Info to access documentation about how to configure job frequency by setting the repeat_interval attribute.

Job Schedule:

Specify a job execution schedule to purge instances. The default schedule is to perform a daily purge at midnight. This is a required field. To specify the schedule, use valid calendaring expressions. Click the information icon or the Calendaring Expression icon to view examples of commonly used expressions. The scheduling syntax is not case sensitive.

Purge Type:

Single: Single, loop purged script that performs a batched purge.

Parallel: Functionality is the same as the single, looped purge script. However, this option enables the dbms_scheduler package to spawn multiple purge jobs, with each job working on subset data.

Retain Data:

Specify the time interval in days for which to retain data. Data within this interval is not purged when the job runs. The default value is seven days.

Maximum Flows to Purge:

Select the maximum number of instance flows to purge in a single job run.

Batch Size:

Select the maximum number of business flows to delete at a time. The default value is 20000.

Degree of Parallel:

Select the number of job executions to run in parallel. The default value is 4.

Step3: To view and configure advanced configuration properties in the System MBean Browser, click More Auto Purge Configuration Properties.

Step4: Click PurgeJobDetails.

The two predefined database purge jobs are displayed.

delete_instances_auto_job1

delete_instances_auto_job2

Expand a job to display all properties for single and parallel purges. When either the single or parallel purge type is executed, the appropriate property values for the selected type are executed.

Scheduler Calendaring Syntax:

Specify an execution schedule or a repeat interval schedule as shown in the examples below.

Run every Day at Midnight

FREQ=DAILY; BYHOUR=00;

Run every weekday at midnight

FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=00;

Run every weekend at midnight and 05:00AM

FREQ=DAILY; BYDAY=SAT,SUN; BYHOUR=00,05;

Run every weekday 30 minutes past midnight

FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=00; BYMINUTE=30;

Run every weekend 30 minutes past midnight and 5AM

FREQ=DAILY; BYDAY=SAT,SUN; BYHOUR=00,05; BYMINUTE=30;

Run every friday at midnight

FREQ=DAILY; BYDAY=FRI; BYHOUR=0;

Run every other friday at midnight

FREQ=DAILY; BYDAY=FRI; BYHOUR=0;

Setup is done.
Note: when all the setup done, you can also execute the purge job pressing the RUN NOW button if you don't want to wait to the next scheduled execution.

Check Executions of Auto Purge Jobs:
Option1: You can check the executions of your Auto Purge Jobs from scheduler jobs under your SOAINFRA DB schema.

Option2: In order to check the executions of your Auto Purge Jobs, you can easily query the table SOA_PURGE_HISTORY under your SOAINFRA schema. Under the columns START_TIME and END_TIME you will see the start and end time of the Purge Job, under the column T you can see if your Job execution was single or parallel loop, under the column THREAD you will find the number of parallel threads and under the column S you will find the status of your Job execution, C = Completed, R = Running

Use the following query to check used space and Free space in SOA infra.

Query:

SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"

FROM

(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99')

 AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED

INNER JOIN

(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99')

 AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE

ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);