Oracle SOA Suite 11.1.1.2: purge dehydration store

Recently, while working on one major, mission critical system, we have discovered problem in Oracle SOA Suite 11.1.1.2 purging scripts.
After a day spent on Metalink searching for patches and solutions we opened SR. Oracle Support recommended patch no. 9645701 (note ID 1131775.1; this patch is part of 11.1.1.3).

The main problem was purging of couple of major tables: xml_document, dlv_message, document_dlv_msg_ref.

However, patching didn't bring solution for orphaned messages left behind by buggy 11.1.1.2 scripts.

Since I was in-charge for the database (in general), role to dive into into purge scripts which come with Repository Creation Utility was mine.
After couple of queries, few major tests (God bless big teams: lot of developers' schemes for testing!), I released purge bridge: one-time shot script which should be used to remove "junk" left by .2 scripts.

In order to check critical tables affected by instance growth in your DB, execute diagnostic.sql (I'd be happy to get results from your environment; feel free to mail them to me).



-- extract distinct states
select distinct (case when state=0 then 'running'
when state=1 then 'recovery required'
when state=2 then 'completed successfully'
when state=3 then 'faulted'
when state=4 then 'terminated by user'
when state=5 then 'suspended'
when state=6 then 'stale'
else state || ''
end) as states_from_comp_inst
from composite_instance;

-- extract number of instances per state
select case when state=0 then 'running'
when state=1 then 'recovery required'
when state=2 then 'completed successfully'
when state=3 then 'faulted'
when state=4 then 'terminated by user'
when state=5 then 'suspended'
when state=6 then 'stale'
else state || ''
end, count(*) as num_of_comp_inst
from composite_instance group by state;


-- extract distinct states
select distinct (case when state=1 then 'running'
when state=2 then 'open suspended'
when state=3 then 'open faulted'
when state=5 then 'completed'
when state=6 then 'closed faulted'
when state=8 then 'aborted'
when state=9 then 'closed stale'
else state || ''
end) as state_from_cube_inst
from cube_instance;

-- extract number grouped by state
select case when state=1 then 'running'
when state=2 then 'open suspended'
when state=3 then 'open faulted'
when state=5 then 'completed'
when state=6 then 'closed faulted'
when state=8 then 'aborted'
when state=9 then 'closed stale'
else state || ''
end, count(*) as num_of_cube_inst
from cube_instance group by state;


select 'AUDIT_TRAIL', count(*) as audit_trail_num from audit_trail;
select 'CUBE_SCOPE', count(*) as cube_scope_num from cube_scope;
select 'CI_INDEXES', count(*) as ci_indexes_num from ci_indexes where cikey not in ( select cikey from cube_instance );
select 'WORK_ITEM', count(*) as work_item_num from work_item;
select 'WI_FAULT', count(*) as wi_fault_num from wi_fault;
select 'AUDIT_DETAILS', count(*) as audit_details_num from audit_details;
select 'TEST_DETAILS', count(*) as test_details_num from test_details;
select 'DOCUMENT_CI_REF', count(*) as document_ci_ref_num from document_ci_ref;
select 'AG_INSTANCE', count(*) as ag_instance_num from ag_instance;
select 'DLV_SUBSCRIPTION', count(*) as dlv_subscription_num from dlv_subscription;



select 'XML_DOCUMENT' as n, a.xml_document_num as total, b.xml_document_orph_num as orph, 100*b.xml_document_orph_num/a.xml_document_num as "%"
from
( select count(*) as xml_document_num from xml_document ) a,
(select count(*) as xml_document_orph_num from xml_document where document_id not in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
and document_id not in ( select payload_key from instance_payload ))b
union all
select 'DOCUMENT_DLV_MSG_REF' as n, a.xml_document_dlv_msg_ref_num, b.xml_doc_dlv_msg_ref_orp_num, 100*b.xml_doc_dlv_msg_ref_orp_num/a.xml_document_dlv_msg_ref_num as perc_of_orph
from
(select count(*) as xml_document_dlv_msg_ref_num from document_dlv_msg_ref) a,
(select count(*) as xml_doc_dlv_msg_ref_orp_num from document_dlv_msg_ref where message_guid not in
(select m.message_guid from dlv_message m, cube_instance c
where m.cikey=c.cikey)) b
union all
select 'XML_DOCUMENT_REF' as n, a.xml_document_ref_num, b.xml_document_ref_orph_num, 100*b.xml_document_ref_orph_num/a.xml_document_ref_num as perc_of_orph
from
(select count(*) as xml_document_ref_num from xml_document_ref) a,
(select count(*) as xml_document_ref_orph_num from xml_document_ref where document_id not in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
and document_id not in ( select payload_key from instance_payload )) b
union all
select 'HEADERS_PROPERTIES' as n, a.headers_properties_num, b.headers_properties_orph_num, 100*b.headers_properties_orph_num/a.headers_properties_num as perc_of_orph
from
(select count(*) as headers_properties_num from headers_properties) a,
(select count(*) as headers_properties_orph_num from headers_properties where message_guid in
(select m.message_guid from dlv_message m where m.cikey not in ( select cikey from cube_instance ))) b
union all
select 'DLV_MESSAGE' as n, a.dlv_message_num, b.dlv_message_orph_num, 100*b.dlv_message_orph_num/a.dlv_message_num as perc_of_orph
from
(select count(*) as dlv_message_num from dlv_message) a,
(select count(*) as dlv_message_orph_num from dlv_message where cikey not in ( select cikey from cube_instance )) b;



After this, you can execute clean.sql.



/*
*
Script which purges the tables impacted by instance data growth as documented on:
http://download.oracle.com/docs/cd/E14571_01/core.1111/e10108/bpel.htm#BABFBDGF

The script removes junk left over by 11.1.1.2 purge scripts

*/

create table xml_document_x as
select * from xml_document where document_id in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
or document_id in ( select payload_key from instance_payload );
alter table b2b_data_storage disable constraint b2b_ds_doc_id_fk;
truncate table xml_document;
alter table b2b_data_storage enable constraint b2b_ds_doc_id_fk;
insert into xml_document select * from xml_document_x;
commit;
drop table xml_document_x;



create table xml_document_ref_x as
select * from xml_document_ref where document_id in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
or document_id in ( select payload_key from instance_payload );
truncate table xml_document_ref;
insert into xml_document_ref select * from xml_document_ref_x;
commit;
drop table xml_document_ref_x;



create table document_dlv_msg_ref_x as
select * from document_dlv_msg_ref where message_guid in
(select m.message_guid from dlv_message m, cube_instance c
where m.cikey=c.cikey);
truncate table document_dlv_msg_ref;
insert into document_dlv_msg_ref select * from document_dlv_msg_ref_x;
commit;
drop table document_dlv_msg_ref_x;




create table headers_properties_x as
select * from headers_properties where message_guid in (select m.message_guid from dlv_message m where m.cikey in ( select cikey from cube_instance ));
commit;
truncate table headers_properties;
insert into headers_properties select * from headers_properties_x;
commit;
drop table headers_properties_x;




create table dlv_message_x as
select * from dlv_message where cikey in ( select cikey from cube_instance );
commit;
truncate table dlv_message;
insert into dlv_message select * from dlv_message_x;
drop table dlv_message_x;



purge recyclebin;





Note: make backup :)


Cheers.

Delete cascade - PL/SQL version

Lets suppose that:

• foreign key constraints are created without "CASCADE" option enabled


• all primary keys are simple numeric(19) fields named 'ID'


• no cyclic references (i.e. tableA -> tableB and tableB -> tableA)

You got request from developer to delete "one problematic row" in XY table.
Unfortunately, your row is referenced by multiple child rows in other tables, those child rows are referenced by other rows and so on, and so on ...
This scenario is not the worst one if you made the database design. But if you are just
simple DBA on complicated project, this can spoil your daily routine.

create or replace procedure delete_cascade( table_name_ in varchar2, id_ in numeric ) is
/*
 Generates set of DELETE statements which have to be executed prior to final entry deletion.
 
 Stored procedure have to be invoked with parameters:
  TABLE_NAME_ : name of the table from which record have to be deleted
  ID_ : id of the record
 
 Stored procedure assumes:
  - that all primary keys are named 'ID' and that pk's data type is 'NUMERIC'
  - that cycle references (i.e. tableA -> tableB and tableB -> tableA) do not exist
  
 Author: Milos Babic 
 
 */
    TYPE refcur IS REF CURSOR;
    
    childid_ numeric(19);
    fetch_sql_ varchar2(2000);
    cur_child_tables refcur;
    
    cursor ref_tables is 
        select fk.table_name as child_table_name_, fk_c.column_name as child_column_name_
        from  user_constraints fk, 
          user_constraints pk, 
          user_cons_columns fk_c 
        where fk.constraint_type='R' and fk.r_constraint_name=pk.constraint_name 
        and pk.table_name=table_name_ and fk_c.constraint_name=fk.constraint_name;
        
begin

    for iterator in ref_tables loop
    
  fetch_sql_ := 'select id from ' || iterator.child_table_name_ || ' where  ' || iterator.child_column_name_ || '=' || id_;

        open cur_child_tables for fetch_sql_;
        
        loop 
            fetch cur_child_tables into childid_;
            exit when cur_child_tables%notfound;
            delete_cascade ( iterator.child_table_name_, childid_ );
        end loop;
        
        close cur_child_tables;
   
        insert into delete_log values (delete_log_seq.nextval, 'delete from ' || iterator.child_table_name_ || ' where ' || iterator.child_column_name_ || ' = ' || id_);
 
   end loop;
    
end delete_cascade;

This procedure expects that you have created table DELETE_LOG and sequence DELETE_LOG_SEQ:

create sequence delete_log_seq;
create table delete_log (
    id numeric(19),
    statement varchar2(2000)
);

Upon successful execution, you can obtain list of delete statements from DELETE_LOG table.

select statement from delete_log order by id

In case you want to try, this is sample DB

create table a (
    id numeric(19),
    a_log varchar2(100),
    constraint pk_a primary key (id)
) tablespace user_data;


create table a_a (
    id numeric(19),
    a_id numeric(19),
    a_a_log varchar2(100),
    constraint pk_a_a primary key (id)
) tablespace user_data;
alter table a_a
   add constraint fk_a_a_a foreign key (a_id)
      references a (id)
      initially immediate deferrable;


create table a_b (
    id numeric(19),
    a_id numeric(19),
    a_b_log varchar2(100),
    constraint pk_a_b primary key (id)
) tablespace user_data;
alter table a_b
   add constraint fk_a_b_a foreign key (a_id)
      references a (id)
      initially immediate deferrable;

create table a_a_a (
    id numeric(19),
    a_a_id numeric(19),
    a_a_a_log varchar2(100),
    constraint pk_a_a_a primary key (id)
) tablespace user_data;
alter table a_a_a
   add constraint fk_a_a_a_a foreign key (a_a_id)
      references a_a (id)
      initially immediate deferrable;


  insert into a values (1,'1');
  insert into a values (2,'2');
  insert into a values (3,'3');
  
  insert into a_a values (1,1,'1');
  insert into a_a values (2,1,'1');
  insert into a_a values (3,2,'1');
  insert into a_a values (4,3,'1');

  insert into a_b values (1,1,'1');
  insert into a_b values (2,1,'1');
  insert into a_b values (3,2,'1');
  insert into a_b values (4,3,'1');
  
  
  insert into a_a_a values (1,1,'1');
  insert into a_a_a values (2,1,'1');
  insert into a_a_a values (3,2,'1');
  insert into a_a_a values (4,2,'1');