Problem/Situation: How to enable or make it work purging in SOA 12c (12.2.1.3).
SOA 12c has automated jobs at EM console level to do instance purging. These Jobs can be found at “Target Navigation –>SOA–> soa-infra–>SOA AdministrationàAuto Purge.”
List of purge Jobs available:
-
- SOA Flow Purge Job 1
- SOA Flow Purge Job 2
- SOA In-Memory flow purge Job
- Integration Workload statistics Purge Job
- Health Check purge Job
Each job has its schedule, and we can have them to run whenever we wanted them to run.
To purge instances, enabling “SOA Flow Purge Job1” should work! However we identified, simply enabling purge job is not deleting instances or free up the database.
How to make the purge job to do its work:
Step 1: Make sure that Purge Job is running!
To know the purge job is running or not, go to “Auto Purge” page in EM console and click on “Run Now.” It should run without giving us any troubles. If not, please look at error/warning messages in logs or on EM screen.
Typical Error message you will see:
Error Code: 2292
Call: BEGIN soa.run_auto_purge_job(job_name_v=>?); END;
bind => [1 parameter bound]
Query: ResultSetMappingQuery(name=”soa.run_auto_purge_job” )
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:902)
at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.executeStoredProcedure(DatabasePlatform.java:2368)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
Caused By: java.sql.SQLIntegrityConstraintViolationException: ORA-02292: integrity constraint (<<schemaName>>.FA_CORR_FLOW_INSTANCE_FK) violated – child record found
How to Fix:
-
- Drop constraint FA_CORR_FLOW_INSTANCE_FK at Database level.
“alter table sca_flow_assoc drop constraint FA_CORR_FLOW_INSTANCE_FK;”
Step 2: Make sure that the purge job is deleting data.
Take note of <schemaName>>_SOAINFRA tablespace size before running purge job and compare the size after running purge job.
Surprisingly you don’t see any changes in size!! Means, purging is not happening.
How to fix:
To make sure purging is happening, apart from Enabling purge jobs, we need to tweak a few settings for purge job.
Change values for “maxCreationPeriodDays” and “minCreationPeriodDays” as per your retention period.
In below example, Retention data mentioned as 7 days. We need to set “maxCreationPeriodDays” and “minCreationPeriodDays” to reflect this retention period.
Steps to set “maxCreationPeriodDays” and “minCreationPeriodDays.”
Login to EM console–>Target Navigation–>SOA–>soa-infra–>SOA Administration–>Auto Purge–>Click on “More Auto Purge Configuration Properties…” –>PurgeJobDetails–>DELETE_INSTANCES_AUTO_JOB1
Set values as specified below:
-
- maxCreationPeriodDays: 8 <–Deletes data older than seven days
- minCreationPeriodDays: 60 <–Delete 60days of data, keeping the latest seven days data!
Figure: 1.1
If you are using Large Database type (this we set while creating schemas using RCU), then we need to set partition purging to “true.”
Login to EM console –> Target Navigation –> SOA –> soa-infra –> SOA Administration –> Auto Purge –> Click on “More Auto Purge Configuration Properties…” –> PurgeJobDetails –> DELETE_INSTANCES_AUTO_JOB1 –> purgePartitionedComponent –> Set to True(Refer Figure: 1.1)
After making these changes, run the job manually from EM console and verify <schemaName>>_SOAINFRA tablespace size. Now you will notice a decrease in tablespace size.
