How to test DB Partitioning Purging feature in SOA 11g

 

This procedure should help you quickly setup DB partitioning SOA 11g , 

Refer to the URL's given below for more information on this.

http://docs.oracle.com/cd/E29505_01/admin.1111/e10226/soaadmin_partition.htm#CJHFJDII

http://www.oracle.com/technetwork/middleware/bpm/learnmore/soa11gstrategy-1508335.pdf

Step#1

First two create two table spaces so that data based on the date can go in to different partitions.

CREATE TABLESPACE soa_ts_before_2013_march
DATAFILE '/fmw11g/db/oradata/db1123/soa_ts_before_2013_march.dbf'
SIZE 1G
REUSE
AUTOEXTEND ON
NEXT 128M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE soa_ts_after_2013_march
DATAFILE '/fmw11g/db/oradata/db1123/soa_ts_after_2013_march.dbf'
SIZE 1G
REUSE
AUTOEXTEND ON
NEXT 128M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


GRANT UNLIMITED TABLESPACE TO DEV1116_SOAINFRA;

Step #2 

You have to modify the table creation scripts so that it contains partitioning information ,

This is the sample table creation script with partions , as you may have existing data , this data needs to be copied to

a temporary table and the main table has to be recreated with the partitioning information , after the table

is created you may to have to enable ENABLE ROW MOVEMENT to move the rows across partitions

CREATE TABLE WFTASK_t PARTITION BY RANGE (COMPOSITECREATEDTIME) (
  PARTITION PART1_BEFORE_2013_MARCH VALUES LESS THAN ('31-MAR-13 12.00.00.000000 AM')
TABLESPACE soa_ts_before_2013_march,
  PARTITION PART1_AFTER_2013_MARCH VALUES LESS THAN ('01-MAR-15 12.00.00.000000 AM')
TABLESPACE soa_ts_after_2013_march
)
NOLOGGING
AS
SELECT * FROM WFTASK;

RENAME WFTASK TO WFTASK_d;

RENAME WFTASK_t TO WFTASK;

DROP TABLE WFTASK_d  cascade CONSTRAINTS;

alter table WFTASK ENABLE ROW MOVEMENT;


These are the tables that needs to be partitioned for SOA Infrastructure , BPEL ,  Mediator  , Human Workflow

for the group related information on these tables , please refer to the URL ,

http://docs.oracle.com/cd/E29505_01/admin.1111/e10226/soaadmin_partition.htm#CJHFJDII

SOA Infrastructure
---------------------

COMPOSITE_INSTANCE,PARTITION_DATE
REFERENCE_INSTANCE,CPST_PARTITION_DATE
COMPOSITE_INSTANCE_FAULT,CPST_PARTITION_DATE
COMPOSITE_SENSOR_VALUE,CPST_PARTITION_DATE
COMPONENT_INSTANCE,CPST_PARTITION_DATE
REJECTED_MESSAGE,CREATED_TIME
REJECTED_MSG_NATIVE_PAYLOAD,RM_PARTITION_DATE
INSTANCE_PAYLOAD,CREATED_TIME
COMPOSITE_INSTANCE_ASSOC,CREATED_TIME

BPEL Tables
--------------

CUBE_INSTANCE,CPST_INST_CREATED_TIME
CI_INDEXES,CI_PARTITION_DATE
CUBE_SCOPE,CI_PARTITION_DATE
DOCUMENT_CI_REF,CI_PARTITION_DATE
AUDIT_TRAIL,CI_PARTITION_DATE
AUDIT_DETAILS,CI_PARTITION_DATE
DLV_SUBSCRIPTION,CI_PARTITION_DATE
WORK_ITEM,CI_PARTITION_DATE
AUDIT_COUNTER,CI_PARTITION_DATE
WI_FAULT,CI_PARTITION_DATE
DLV_MESSAGE,RECEIVE_DATE
HEADERS_PROPERTIES,DLV_PARTITION_DATE
DOCUMENT_DLV_MSG_REF,DLV_PARTITION_DATE
XML_DOCUMENT,DOC_PARTITION_DATE

Mediator Table
-----------------

MEDIATOR_INSTANCE,COMPOSITE_CREATION_DATE
MEDIATOR_CASE_INSTANCE,MI_PARTITION_DATE
MEDIATOR_CASE_DETAIL,MI_PARTITION_DATE
MEDIATOR_AUDIT_DOCUMENT,MI_PARTITION_DATE
MEDIATOR_DEFERRED_MESSAGE,CREATION_DATE
MEDIATOR_PAYLOAD,CREATION_TIME

Human Workflow
--------------------

WFTASK,COMPOSITECREATEDTIME
WFTASK_TL,COMPOSITECREATEDTIME
WFTASKHISTORY,COMPOSITECREATEDTIME
WFTASKHISTORY_TL,COMPOSITECREATEDTIME
WFCOMMENTS,COMPOSITECREATEDTIME
WFMESSAGEATTRIBUTE,COMPOSITECREATEDTIME
WFATTACHMENT,COMPOSITECREATEDTIME
WFASSIGNEE,COMPOSITECREATEDTIME
WFREVIEWER,COMPOSITECREATEDTIME
WFCOLLECTIONTARGET,COMPOSITECREATEDTIME
WFROUTINGSLIP,COMPOSITECREATEDTIME
WFNOTIFICATION,COMPOSITECREATEDTIME
WFTASKTIMER,COMPOSITECREATEDTIME
WFTASKERROR,COMPOSITECREATEDTIME
WFHEADERPROPS,COMPOSITECREATEDTIME
WFEVIDENCE,COMPOSITECREATEDTIME
WFTASKASSIGNMENTSTATISTIC,COMPOSITECREATEDTIME
WFTASKAGGREGATION,COMPOSITECREATEDTIME


I created a simple java program to create the sql that can be used to recreate the tables.

Use this java program to generate the table scripts ,



public class GeneratePartionTable {
    public GeneratePartionTable() {
        super();
    }

    public static void main(String[] args) {
        GeneratePartionTable generatePartionTable = new GeneratePartionTable();
        String p2 = "COMPOSITE_INSTANCE,PARTITION_DATE\n" + 
        "REFERENCE_INSTANCE,CPST_PARTITION_DATE\n" + 
        "COMPOSITE_INSTANCE_FAULT,CPST_PARTITION_DATE\n" + 
        "COMPOSITE_SENSOR_VALUE,CPST_PARTITION_DATE\n" + 
        "COMPONENT_INSTANCE,CPST_PARTITION_DATE\n" + 
        "REJECTED_MESSAGE,CREATED_TIME\n" + 
        "REJECTED_MSG_NATIVE_PAYLOAD,RM_PARTITION_DATE\n" + 
        "INSTANCE_PAYLOAD,CREATED_TIME\n" + 
        "COMPOSITE_INSTANCE_ASSOC,CREATED_TIME\n" + 
        "CUBE_INSTANCE,CPST_INST_CREATED_TIME\n" + 
        "CI_INDEXES,CI_PARTITION_DATE\n" + 
        "CUBE_SCOPE,CI_PARTITION_DATE\n" + 
        "DOCUMENT_CI_REF,CI_PARTITION_DATE\n" + 
        "AUDIT_TRAIL,CI_PARTITION_DATE\n" + 
        "AUDIT_DETAILS,CI_PARTITION_DATE\n" + 
        "DLV_SUBSCRIPTION,CI_PARTITION_DATE\n" + 
        "WORK_ITEM,CI_PARTITION_DATE\n" + 
        "AUDIT_COUNTER,CI_PARTITION_DATE\n" + 
        "WI_FAULT,CI_PARTITION_DATE\n" + 
        "DLV_MESSAGE,RECEIVE_DATE\n" + 
        "HEADERS_PROPERTIES,DLV_PARTITION_DATE\n" + 
        "DOCUMENT_DLV_MSG_REF,DLV_PARTITION_DATE\n" + 
        "XML_DOCUMENT,DOC_PARTITION_DATE\n" + 
        "MEDIATOR_INSTANCE,COMPOSITE_CREATION_DATE\n" + 
        "MEDIATOR_CASE_INSTANCE,MI_PARTITION_DATE\n" + 
        "MEDIATOR_CASE_DETAIL,MI_PARTITION_DATE\n" + 
        "MEDIATOR_AUDIT_DOCUMENT,MI_PARTITION_DATE\n" + 
        "MEDIATOR_DEFERRED_MESSAGE,CREATION_DATE\n" + 
        "MEDIATOR_PAYLOAD,CREATION_TIME\n" + 
        "WFTASK,COMPOSITECREATEDTIME\n" + 
        "WFTASK_TL,COMPOSITECREATEDTIME\n" + 
        "WFTASKHISTORY,COMPOSITECREATEDTIME\n" + 
        "WFTASKHISTORY_TL,COMPOSITECREATEDTIME\n" + 
        "WFCOMMENTS,COMPOSITECREATEDTIME\n" + 
        "WFMESSAGEATTRIBUTE,COMPOSITECREATEDTIME\n" + 
        "WFATTACHMENT,COMPOSITECREATEDTIME\n" + 
        "WFASSIGNEE,COMPOSITECREATEDTIME\n" + 
        "WFREVIEWER,COMPOSITECREATEDTIME\n" + 
        "WFCOLLECTIONTARGET,COMPOSITECREATEDTIME\n" + 
        "WFROUTINGSLIP,COMPOSITECREATEDTIME\n" + 
        "WFNOTIFICATION,COMPOSITECREATEDTIME\n" + 
        "WFTASKTIMER,COMPOSITECREATEDTIME\n" + 
        "WFTASKERROR,COMPOSITECREATEDTIME\n" + 
        "WFHEADERPROPS,COMPOSITECREATEDTIME\n" + 
        "WFEVIDENCE,COMPOSITECREATEDTIME\n" + 
        "WFTASKASSIGNMENTSTATISTIC,COMPOSITECREATEDTIME\n" + 
        "WFTASKAGGREGATION,COMPOSITECREATEDTIME";
        
        String[] lines = p2.split("\n");
        for (int i = 0 ; i < lines.length ; i++ ) {
        String p1 = "CREATE TABLE --tablename--_t PARTITION BY RANGE (--key--) (\n" + 
                    "  PARTITION PART1_BEFORE_2013_MARCH VALUES LESS THAN ('01-MAR-13 12.00.00.000000 AM') \n" + 
                    "TABLESPACE soa_ts_before_2013_march,\n" + 
                    "  PARTITION PART1_AFTER_2013_MARCH VALUES LESS THAN ('01-MAR-15 12.00.00.000000 AM') \n" + 
                    "TABLESPACE soa_ts_after_2013_march \n" + 
                    ")\n" + 
                    "NOLOGGING\n" + 
                    "AS\n" + 
                    "SELECT * FROM --tablename--;\n" + 
                    "\n" + 
                    "RENAME --tablename-- TO --tablename--_d;\n" + 
                    "\n" + 
                    "RENAME --tablename--_t TO --tablename--;\n" + 
                    "\n" + 
                    "DROP TABLE --tablename--_d  cascade CONSTRAINTS;\n" +
            "\n" + 
                    "alter table --tablename-- ENABLE ROW MOVEMENT;";
            String[] lines2 = lines[i].split(",");
           
            p1= p1.replaceAll("--tablename--", lines2[0]);
            p1 = p1.replaceAll("--key--", lines2[1]) ; 
            
            System.out.println(p1);
            System.out.println();
        }
   
    }    
}

Get the o/p of this to a text file and run this sql file as the user 
DEV1116_SOAINFRA ,

Step#3

Run some composites , like a simple helloworld , a simple workflow application etc (wanted to keep this composite in the running state), 

Run the soa_exec_verify.sql to verify the partionins

You have to modify the soa_exec_verify.sql script , 
with the partition name , i.e.
mySoa_drv_list(1) := 'PART1_BEFORE_2013_MARCH';

CREATE DIRECTORY PART_DIR AS '/tmp/verify'

grant read,write on directory PART_DIR to public;

Run the script , 

sqlplus dev1116_soainfra/Welcome1 @soa_exec_verify.sql

If you are running the script , you may get an error like this ,

ORA-20000: ERROR :TEMP TABLES NOT EMPTY. PLEASE TRUNCATE TEMP TABLES AFTER EXECUTING ROW MOVEMENT PROCEDURES(IF APPLICABLE)
ORA-06512: in "PROD_SOAINFRA.VERIFY_SOA", line 244

Please run the pl/sql code below to remove the temporary tables ,

DECLARE
BEGIN
verify_soa.trunc_verify1_temp_tables;
END;

/

DECLARE
BEGIN
verify_soa.trunc_verify2_temp_tables;
END;

/

Open the file /temp/verify/SOA_PART1_BEFORE_2013_MARCH_LOG_1 and look for the string "FAIL"
if you see ant FAIL word that means there are some running instances which you need to move to
another parition.

Moving Active, Long Running Instances to a Different Partition
 
Immediately after running the soa_exec_verify.sql run the exec_row_movement_1 movement scripts to move the running instances to
another parition ,

declare
new_partition_date timestamp; 
partition_name varchar2(100);
BEGIN
new_partition_date := to_timestamp('2013-04-09','YYYY-MM-DD'); 
partition_name := 'PART1_BEFORE_2013_MARCH' ;
verify_soa.exec_row_movement_1(partition_name => partition_name, 
new_partition_date => new_partition_date); 
commit;
END;

Please note that you should always run the exec_row_movement_1 scripts immediately after running the soa_exec_verify.sql
scripts.exec_row_movement_1 scripts uses the temporary tables created by soa_exec_verify.sql scripts.

Simlarly run the script soa_exec_verify.sql with the input as 2 to run against Group 2 tables , 
and run the exec_row_movement_2 procedure to move the rows of group2 to another partition.

declare
new_partition_date timestamp; 
partition_name varchar2(100);
BEGIN
new_partition_date := to_timestamp('2013-04-09','YYYY-MM-DD'); 
partition_name := 'PART1_BEFORE_2013_MARCH' ;
verify_soa.exec_row_movement_2(partition_name => partition_name, 
new_partition_date => new_partition_date); 
END;