Purging Strategies for Dehydration Store

Dehydration Store Tablespace Issue

java.sql.BatchUpdateException: ORA-01691: unable to extend lob segment TABLESPACE.SYS_LOB0000059665C00009$$ by 8 in tablespace TABLESPACE

Solution:

The problem was caused by insufficient space to extend a segment for the tablespace. Adding a datafile or adjusting the size of the current datafile of the tablespace can act as short-term solution.

Adding datafile:
ALTER TABLESPACE <tablespace_name> ADD DATAFILE ‘<path_of_datafile>’ SIZE 10M;

Enlarging the current datafile:
ALTER DATABASE DATAFILE ‘<path_to_datafile>’ RESIZE 10M;

(Assuming you are adding a 10MB datafile and resizing the datafile to 10MB respectively.)

You may run the following SQL statement to check the status of various datafiles:
SELECT file_name, tablespace_name, bytes/1024/1024 MB, AUTOEXTENSIBLE FROM dba_data_files;

and use the following SQL to turn on AUTOEXTEND option:
ALTER DATABASE DATAFILE ‘<path_to_datafile>' AUTOEXTEND ON;

Purging Dehydration Store

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.

2. Examples of procedures in SOAINFRA schema that could clean up dehydration storage:

CREATE OR REPLACE PROCEDURE DELETE_OLD_INSTANCES

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

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

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 FROM XML_DOCUMENT; COMMIT;

Oracle Fusion Middleware

Main Menu

Friday, February 4, 2011

Purging Strategies for Dehydration Store

0 comments:

Post a Comment

Total Pageviews

Followers

Categories