eMarcel.com

Oracle Database, Fusion Middleware, Linux

Delete large numbers of instances in Oracle SOA Suite 11g

Purging composites instances in Oracle SOA SUITE 11g

There are several ways to purge large amounts of instances in SOA Suite 11g database – dehydration storage;

  1. By dropping the SOA Suite repository and next, to recreate it using RCU creation utility providing exactly the same credentials.
  2. By executing a set of existing database procedures delivered by Oracle (fixed in SOA Suite 11.1.1.3.0 PS2).
  3. By truncating several tables in BPEL SOAINFRA schema.
  4. Perform a maintenance to free up some space in a database storage

1. Dropping the SOA Suite repository using RCU is straight forward. Just run the RCU utility select “Drop” instead of “Create” and follow the installer. Drop only SOAINFRA schema.

rcu_install2a

2. Examples of procedures in SOAINFRA schema that can be called to clean up dehydration storage database:

CREATE OR REPLACE PROCEDURE DELETE_OLD_INSTANCES
AS
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
MAX_INSTANCES          NUMBER;
DELETED_INSTANCES      NUMBER;
PURGE_PARTITIONED_DATA BOOLEAN := TRUE;
DAY_OFFSET             NUMBER;
BEGIN
FILTER.COMPOSITE_PARTITION_NAME:='default';
FILTER.COMPOSITE_NAME := 'My_Composite_Name';
FILTER.COMPOSITE_REVISION := '1.00.01';

DAY_OFFSET                     := 5;
FILTER.MIN_CREATED_DATE := TO_TIMESTAMP('2010-01-01','YYYY-MM-DD');
FILTER.MAX_CREATED_DATE        := SYSDATE - DAY_OFFSET;
MAX_INSTANCES                  := 50000;

DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA
);

END DELETE_OLD_INSTANCES;

 

Example of procedures 2 – purging instances of multiple composites using STATE filters.

CREATE OR REPLACE PROCEDURE DELETE_OLD_INSTANCES
AS
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
MAX_INSTANCES          NUMBER;
DELETED_INSTANCES      NUMBER;
PURGE_PARTITIONED_DATA BOOLEAN := TRUE;
DAY_OFFSET             NUMBER;
BEGIN
FILTER.COMPOSITE_PARTITION_NAME:='default';
FILTER.COMPOSITE_NAME := 'My_Composite_Name';
FILTER.COMPOSITE_REVISION := '1.00.01';

--USE either ONLY ONE filter per run;

FILTER.STATE := fabric.STATE_COMPLETED_SUCCESSFULLY;
--FILTER.STATE := fabric.STATE_RECOVERY_REQUIRED;
--FILTER.STATE := fabric.STATE_COMPLETED_SUCCESSFULLY;
--FILTER.STATE := fabric.STATE_FAULTED;
--FILTER.STATE := fabric.STATE_TERMINATED_BY_USER;
--FILTER.STATE := fabric.STATE_SUSPENDED;
--FILTER.STATE := fabric.STATE_STALE;
--FILTER.STATE := fabric.STATE_UNKNOWN;

DAY_OFFSET                     := 5;
FILTER.MIN_CREATED_DATE := TO_TIMESTAMP('2010-01-01','YYYY-MM-DD');
FILTER.MAX_CREATED_DATE        := SYSDATE - DAY_OFFSET;
MAX_INSTANCES                  := 50000;

DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA
);

END DELETE_OLD_INSTANCES;

Example of procedures 3 – purging instances filtered by name of the composites.

CREATE OR REPLACE
PROCEDURE DELETE_OLD_INSTANCES
AS
TYPE PROCESS_ARRAY_TYPE IS VARRAY(2) OF VARCHAR(50);
  FILTER INSTANCE_FILTER := INSTANCE_FILTER();
  PROCESS_ARRAY PROCESS_ARRAY_TYPE;
  MAX_INSTANCES          NUMBER;
  DELETED_INSTANCES      NUMBER;
  PURGE_PARTITIONED_DATA BOOLEAN := TRUE;
  DAY_OFFSET             NUMBER;
BEGIN
  DBMS_OUTPUT.put_line('----- BEGIN '|| SYSDATE ||'----------------'); 
  FILTER.COMPOSITE_PARTITION_NAME:='default';
  PROCESS_ARRAY           := PROCESS_ARRAY_TYPE('My_Composite_1',
						'My_Composite_2',
						'My_Composite_n');
  DAY_OFFSET              := 7;
  FILTER.MIN_CREATED_DATE := TO_TIMESTAMP('2010-01-01','YYYY-MM-DD');
  FILTER.MAX_CREATED_DATE := SYSDATE - DAY_OFFSET;
  MAX_INSTANCES           := 10000;

  FOR i  IN PROCESS_ARRAY.FIRST..PROCESS_ARRAY.LAST
  LOOP
  FILTER.COMPOSITE_NAME := PROCESS_ARRAY(i);
    DBMS_OUTPUT.put_line(' FILTER.COMPOSITE_NAME '|| FILTER.COMPOSITE_NAME); 
    DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES( 
	FILTER => FILTER, 
	MAX_INSTANCES => MAX_INSTANCES, 
	PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA);
    DBMS_OUTPUT.put_line('DELETED_INSTANCES: ' || DELETED_INSTANCES);   
  END LOOP;
  DBMS_OUTPUT.put_line('--------- BEGIN '|| SYSDATE ||'---------'); 
  END DELETE_OLD_INSTANCES;

 

3. There is also a way to truncate several tables in BPEL SOAINFRA schema and delete XML_DOCUMENTS table.

This approach is not recommended for the production environments, but in Dev/Test will do the job in a very fast way.

In SOAINFRA schema:

TRUNCATE TABLE INSTANCE_PAYLOAD DROP storage;
TRUNCATE TABLE REFERENCE_INSTANCE DROP storage;
TRUNCATE TABLE REJECTED_MSG_NATIVE_PAYLOAD DROP storage;
TRUNCATE TABLE INSTANCE_PAYLOAD DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE_FAULT DROP storage;
TRUNCATE TABLE COMPOSITE_SENSOR_VALUE DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE_ASSOC DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE DROP storage;
TRUNCATE TABLE AUDIT_TRAIL DROP storage;
TRUNCATE TABLE CUBE_INSTANCE DROP storage;
TRUNCATE TABLE CUBE_SCOPE DROP storage;
TRUNCATE TABLE AUDIT_DETAILS DROP storage;

-- DELETE OR or disable constraint and truncate XML_DOCUMENT table:
alter table "PRD_SOAINFRA"."XML_DOCUMENT" disable constraint "DOC_STORE_PK"
TRUNCATE TABLE XML_DOCUMENT;
alter table "PRD_SOAINFRA"."XML_DOCUMENT" enable constraint "DOC_STORE_PK"

DELETE FROM XML_DOCUMENT; 
COMMIT;

 

4. After all it is a good practice to do some maintenance and clean up the storage and rebuild some indexes;

ALTER TABLE XML_DOCUMENT enable row movement;
ALTER TABLE XML_DOCUMENT shrink space;
ALTER TABLE XML_DOCUMENT disable row movement;
ALTER INDEX DOC_STORE_PK rebuild online;

ALTER TABLE AUDIT_TRAIL enable row movement;
ALTER TABLE AUDIT_TRAIL shrink space;
ALTER TABLE AUDIT_TRAIL disable row movement;
ALTER INDEX AT_PK rebuild online;

ALTER TABLE HEADERS_PROPERTIES enable row movement;
ALTER TABLE HEADERS_PROPERTIES shrink space;
ALTER TABLE HEADERS_PROPERTIES disable row movement;
ALTER INDEX HEADER_PROPERTIES_PK rebuild online;

ALTER TABLE CUBE_SCOPE enable row movement;
ALTER TABLE CUBE_SCOPE shrink space;
ALTER TABLE CUBE_SCOPE disable row movement;
ALTER INDEX CS_PK rebuild online;

ALTER INDEX REFERENCE_INSTANCE_CDN_STATE rebuild online;
ALTER INDEX REFERENCE_INSTANCE_CO_ID rebuild online;
ALTER INDEX REFERENCE_INSTANCE_ECID rebuild online;
ALTER INDEX REFERENCE_INSTANCE_ID rebuild online;
ALTER INDEX REFERENCE_INSTANCE_STATE rebuild online;
ALTER INDEX REFERENCE_INSTANCE_TIME_CDN rebuild online;

ALTER TABLE DLV_MESSAGE enable row movement;
ALTER TABLE DLV_MESSAGE shrink space;
ALTER TABLE DLV_MESSAGE disable row movement;
ALTER INDEX DM_CONVERSATION rebuild online;
ALTER INDEX DM_PK rebuild online;
ALTER INDEX STATE_TYPE_DATE rebuild online;

ALTER TABLE CUBE_INSTANCE enable row movement;
ALTER TABLE CUBE_INSTANCE shrink space;
ALTER TABLE CUBE_INSTANCE disable row movement;
ALTER INDEX CI_CREATION_DATE rebuild online;
ALTER INDEX CI_CUSTOM3 rebuild online;
ALTER INDEX CI_ECID rebuild online;
ALTER INDEX CI_NAME_REV_STATE rebuild online;
ALTER INDEX CI_PK rebuild online;

ALTER TABLE INSTANCE_PAYLOAD enable row movement;
ALTER TABLE INSTANCE_PAYLOAD shrink space;
ALTER TABLE INSTANCE_PAYLOAD disable row movement;
ALTER INDEX INSTANCE_PAYLOAD_KEY rebuild online;

ALTER TABLE XML_DOCUMENT_REF enable row movement;
ALTER TABLE XML_DOCUMENT_REF shrink space;
ALTER TABLE XML_DOCUMENT_REF disable row movement;
ALTER INDEX XML_DOC_REFERENCE_PK rebuild online;

ALTER INDEX COMPOSITE_INSTANCE_CIDN rebuild online;
ALTER INDEX COMPOSITE_INSTANCE_CO_ID rebuild online;
ALTER INDEX COMPOSITE_INSTANCE_CREATED rebuild online;
ALTER INDEX COMPOSITE_INSTANCE_ECID rebuild online;
ALTER INDEX COMPOSITE_INSTANCE_ID rebuild online;
ALTER INDEX COMPOSITE_INSTANCE_STATE rebuild online;

ALTER TABLE DOCUMENT_DLV_MSG_REF enable row movement;
ALTER TABLE DOCUMENT_DLV_MSG_REF shrink space;
ALTER TABLE DOCUMENT_DLV_MSG_REF disable row movement;
ALTER INDEX DOC_DLV_MSG_GUID_INDEX rebuild online;

ALTER TABLE DLV_SUBSCRIPTION enable row movement;
ALTER TABLE DLV_SUBSCRIPTION shrink space;
ALTER TABLE DLV_SUBSCRIPTION disable row movement;
ALTER INDEX DS_CONV_STATE rebuild online;
ALTER INDEX DS_CONVERSATION rebuild online;
ALTER INDEX DS_FK rebuild online;
ALTER INDEX DS_PK rebuild online;

ALTER TABLE AUDIT_DETAILS enable row movement;
ALTER TABLE AUDIT_DETAILS shrink space;
ALTER TABLE AUDIT_DETAILS disable row movement;
ALTER INDEX AD_PK rebuild online;

ALTER TABLE WI_FAULT enable row movement;
ALTER TABLE WI_FAULT shrink space;
ALTER TABLE WI_FAULT disable row movement;
ALTER INDEX WF_CRDATE_CIKEY rebuild online;
ALTER INDEX WF_CRDATE_TYPE rebuild online;
ALTER INDEX WF_FK2 rebuild online;
ALTER INDEX WIFAULT_PK rebuild online;

ALTER TABLE WORK_ITEM enable row movement;
ALTER TABLE WORK_ITEM shrink space;
ALTER TABLE WORK_ITEM disable row movement;
ALTER INDEX WI_EXPIRED rebuild online;
ALTER INDEX WI_KEY_CRDATE_STATE rebuild online;
ALTER INDEX WI_PK rebuild online;
ALTER INDEX WI_STRANDED rebuild online;

--LOBs

ALTER TABLE XML_DOCUMENT MODIFY lob (DOCUMENT) (shrink space);
ALTER TABLE AUDIT_DETAILS MODIFY lob (bin) (shrink space);
ALTER TABLE WI_FAULT MODIFY lob (MESSAGE) (shrink space);
ALTER TABLE CUBE_SCOPE MODIFY lob (SCOPE_BIN) (shrink space);
ALTER TABLE REFERENCE_INSTANCE MODIFY lob (STACK_TRACE) (shrink space);
ALTER TABLE REFERENCE_INSTANCE MODIFY lob (ERROR_MESSAGE) (shrink space);
ALTER TABLE COMPOSITE_INSTANCE_FAULT MODIFY lob (STACK_TRACE) (shrink space);
ALTER TABLE COMPOSITE_INSTANCE_FAULT MODIFY lob (ERROR_MESSAGE) (shrink space);
ALTER TABLE TEST_DEFINITIONS MODIFY lob (DEFINITION) (shrink space);

Your comments would be much appreciated folks.

References:
http://download.oracle.com/docs/cd/E14571_01/integration.1111/e10226/soacompapp_mang.htm

, , , ,

  • httPants

    This is very helpful thanks. Just thinking you’d have to be careful about how many instances you were deleting since this would be done in a single transaction that could consume lots of redo log space right and result in a long running transaction. I guess that’s why you showed the truncate option for dev and test environments.

  • httPants

    Thought I’d comment here about a way you can use the enterprise manager console to delete a large number of instances as well.

    With the default configuration, using EM you quickly encounter the limit on the number of instances you can delete, which turns out to be not very many, before you get an error message. The error is caused by a transaction timeout which by default I think is set to 60 seconds. I found that i hit this timeout value when deleting maybe 1000 – 3000 instances.

    You can configure the transaction timeout to be much bigger so you can successfully delete big numbers of composite instances in the console. This timeout value is configured in the settings for the FacadeFinderBean which is in the soa-infra deployment. I set mine to 600 seconds which allows me to delete roughly around 25,000 instances.

    To set this, open the weblogic console. Click on the Deployments link and scroll down the list to the ‘soa-infra’ deployment. Click on the + next to soa-infra to expand the EJBs and then click on the FacadeFinderBean. In the Configuration tab is where you find the Transaction Timeout value that you can change to enable you to start deleting large numbers of instances.

    • httPants! I have been looking for solution for this issue form a while. It is so annoying when you end up with error message when trying to delete a large number of instances from within EM console! I tested and it works. Great tip – thanks for that!
      Cheers!

  • @httPants
    Yes, you are right! I did my tests in a database with is up to 60GB in size (SOAINFRA Tablespace) with up to ~2 millions of instances and I was deleting instances in amount of 100.000 per run. This took ~10-15 minutes per run for one composite in a regular database located in a datacenter of the test environment.

  • Alfredozn

    Hi eMarcel, I’m trying to delete just a few instances in development mode, but I always get a NullPointerException:

    oracle.bpm.bpmn.engine.service.audit.persistence.AuditPersistenceException: java.lang.NullPointerException
    at oracle.bpm.bpmn.engine.service.audit.impl.AuditService.publishToMeasurement(AuditService.java:731)
    at oracle.bpm.bpmn.engine.service.audit.impl.AuditService.markAuditAsAborted(AuditService.java:332)
    at oracle.bpm.bpmn.engine.instancemanagement.impl.InternalInstanceManagementService.markAuditAsAborted(InternalInstanceManagementService.java:349)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)

    And more….
    Do you have any idea what is happening here???

  • DV

    Just one question:
    Why delete on xml_document?
    Why not truncate also?

    • You can’t truncate xml_documents due to constraints enabled on that table. Try to disable all constraints and then truncate a table. Don’t forget to stop the environment first.Cheers!!

  • Tom R

    Hi! nice article, very useful. I truncated most of these tables a while back and now i need to do it again. I noticed CUBE_INSTANCES , CUBE_SCOPE, AUDIT_DETAILS are not present in the list for option 3. i understand the first two save the instances´s data and they should be cleaned.
    What are your thoughts?

    thanks

    • HI Tom,

      Good finding, I’ve checked this and successfully tested. Those tables also can be truncated:
      CUBE_INSTANCE, CUBE_SCOPE, AUDIT_DETAILS. Above script has also been updated.

      Thanks for a hint!
      Cheers!c

  • Shanthi Viswanathan

    From all the other postings, I gather that everyone is able to successfully purge instances. However, I get a strange issue. I am running the following:
    DELETED_INSTANCES := FABRIC.DELETE_ALL(
    FILTER => FILTER,
    MAX_INSTANCES => MAX_INSTANCES
    );
    My Filter object is set as:
    FILTER.MIN_CREATED_DATE := to_timestamp(’01-JAN-00′,’DD-MON-YY’);
    FILTER.MAX_CREATED_DATE := to_timestamp(’17-Dec-10′,’DD-MON-YY’);
    FILTER.STATE := 3;

    I get PK constraint error as below:
    Error report:
    ORA-00001: unique constraint (SOA_SOAINFRA.T_CI_DOC_PK) violated
    ORA-06512: at “SOA_SOAINFRA.FABRIC”, line 368
    ORA-06512: at “SOA_SOAINFRA.FABRIC”, line 453
    ORA-06512: at “SOA_SOAINFRA.FABRIC”, line 603
    ORA-06512: at “SOA_SOAINFRA.PURGE_INSTANCES”, line 49
    ORA-06512: at line 3
    00001. 00000 – “unique constraint (%s.%s) violated”
    *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
    For Trusted Oracle configured in DBMS MAC mode, you may see
    this message if a duplicate entry exists at a different level.
    *Action: Either remove the unique restriction or do not insert the key.
    20-DEC-10 03.15.17.074024000 AM +00:00 : insert into composite_instance_purge select id from composite_instance where bitand(state,6) = 2 and created_time>=’01-JAN-00 12.00.00.000000 AM’ and created_time<='17-DEC-10 12.00.00.000000 AM'

    Any help is appreciated. I do see multiple rows with the same document_id in the document_ci_ref table. I have a clustered SOA environment. Could anything be wrong with my cluster that it is creating same document_id for multiple records. My DB is not RAC.

    • HI Shanthi,

      First, you would make sure that composite_instance_purge table is empty before you run procedure. Maybe there are some left overs from previous none successful purge actions?

      However, for troubleshooting I’m inviting you to the Oracle’s forum http://forums.oracle.com

      Cheers!!

  • Marc Kelderman

    Hi Marcin! I improved the purging a bit. I created an extreme edtion of the purging. I am able to delete all the data from SOA11g based on a date. It is a straight forwared version:  http://orasoa.blogspot.com/2011/07/purging-soa-suite-11g-extreme-edition.html

    • Hi Marc!

      Great to hear from you. Many thanks for info about it!

      Cheers!!
      Marcin

  • ks

    FILTER.COMPOSITE_PARTITION_NAME

1 Shares
Share1
+1
Tweet
Share

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close