The particular composite was listed in the deployed composites list, but it lacked the artifacts and therefor marked as invalid. Using the tip in this blog I was able to remove the composite. In fact it comes down to do an export of the MDS, openup the zip and browse to the folder ‘deployed—composites’. This contains an xml file named ‘deployed-composites.xml’. This gives a summary of deployed composites. Delete the entry of the composite you want to have removed. Besides the xml file, there is a folder per soa-partition with a sub-folder per composite containing the artefacts. In our case however the particular composite was stated in the deployed-composites.xml but did not have a folder here. If the folder consists removed it. Pack the zip again (or if you use a tool like TotalCommander, 7-Zip, WinRar or the like, you could do the removal directly from the tool. The resulting zip can then be imported. After a server restart the composite version is removed.
So far so good, however, we still had the composite instances and the cases. Well the flow-instances can be purged from Enterprise Manager. It turned out however that we still had cases in the database, without flow instances. You could check this with the query:
select count(*) from cm_case cse join sca_partition ptn on ptn.id = cse.sca_partition_id where not exists (select fie.flow_id from sca_flow_instance fie where fie.flow_id = cse.flow_id);This got me 213 cases. Possibly this is kind of designed, however we could get not into the caselist in the workspace without errors anymore. The caselist was empty, but we got a list of errors.
In the diagnostics log we got errors like:
[2015-01-19T14:34:37.764+01:00] [bpm_server1] [ERROR] [] [oracle.bpm.casemgmt.instance] [tid: [ACTIVE].ExecuteThread: '14' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: hans] [ecid: 60f546bd-50c5-445a-9a6d-1a72f364d5a9-00004adc,0] [APP: soa-infra] [DSID: 0000Kg2NM3f5uX05zzS4yW1KjBH400000x] Exception[[
BPM-72802
Missing resource bundle in project.
The resource bundle {2} was not found in case project identified by metadata Id default/{composite-name}!1.1-MA/{case-name} for nl locale .
Create a resource bundle for nl locale in project and re-deploy the project to server.
at oracle.bpm.casemgmt.resource.Casei18nUtil.getCaseResourceBundle(Casei18nUtil.java:142)
at oracle.bpm.casemgmt.resource.Casei18nUtil.getValue(Casei18nUtil.java:55)
at oracle.bpm.casemgmt.resource.Casei18nUtil.getValue(Casei18nUtil.java:40)
at sun.reflect.GeneratedMethodAccessor1371.invoke(Unknown Source)
...
I apparently have cases that lack composite instances and I indeed removed the composite artifacts, because of the invalid composite.To solve this I created the following delete script:
declare
cursor c_cse
is select cse.case_id
, cse.flow_id
, cse.sca_partition_id
, cse.partition_date
, ptn.name partition_name
from cm_case cse
join sca_partition ptn on ptn.id = cse.sca_partition_id
where not exists ( select flow_id from sca_flow_instance fie where fie.flow_id = cse.flow_id);
-- and flow_id = 20006;
procedure pl(p_text in varchar2)
is
begin
dbms_output.put_line(p_text);
end;
procedure del_case_header(p_flow_id in number)
is
cursor c_hdr
is SELECT ID,
CASE_NUMBER,
TITLE,
SHORT_SUMMARY,
LONG_SUMMARY,
CASE_NAMESPACE,
CASE_DEFINITION_ID,
CASE_DEFINITION_NAME,
composite_name,
composite_version
FROM CM_CASE_HEADER hdr
where hdr.flow_id = p_flow_id;
r_hdr c_hdr%rowtype;
begin
open c_hdr;
fetch c_hdr into r_hdr;
close c_hdr;
pl('Delete case header for flow_id '||p_flow_id||' case_number '||r_hdr.case_number||', from definition: '||r_hdr.CASE_DEFINITION_NAME||', composite_name: '||r_hdr.composite_name ||', composite_version: '||r_hdr.composite_version||'.');
delete from cm_case_header where flow_id = p_flow_id;
end;
procedure del_case_data(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_data where flow_id = p_flow_id;
pl('Delete '||l_count||' case_data rows for flow_id '||p_flow_id||'.');
delete from cm_case_data where flow_id = p_flow_id;
end;
procedure del_case_object(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_object where flow_id = p_flow_id;
pl('Delete '||l_count||' case_object rows for flow_id '||p_flow_id||'.');
delete from cm_case_object where flow_id = p_flow_id;
end;
procedure del_case_document(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_document where flow_id = p_flow_id;
pl('Delete '||l_count||' case_document rows for flow_id '||p_flow_id||'.');
delete from cm_case_document where flow_id = p_flow_id;
end;
procedure del_case_milestones(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_milestone where flow_id = p_flow_id;
pl('Delete '||l_count||' case_milestone rows for flow_id '||p_flow_id||'.');
delete from cm_case_milestone where flow_id = p_flow_id;
end;
procedure del_case_events(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_event where flow_id = p_flow_id;
pl('Delete '||l_count||' case_event rows for flow_id '||p_flow_id||'.');
delete from cm_case_event where flow_id = p_flow_id;
end;
procedure del_case_flex_fields(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_flex_field where flow_id = p_flow_id;
pl('Delete '||l_count||' case_flex_field rows for flow_id '||p_flow_id||'.');
delete from cm_case_flex_field where flow_id = p_flow_id;
end;
procedure del_case_stake_holders(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_stake_holder where flow_id = p_flow_id;
pl('Delete '||l_count||' case_stake_holder rows for flow_id '||p_flow_id||'.');
delete from cm_case_stake_holder where flow_id = p_flow_id;
end;
procedure del_case_stake_holder_members(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case_stake_holder_member where flow_id = p_flow_id;
pl('Delete '||l_count||' case_stake_holder_member rows for flow_id '||p_flow_id||'.');
delete from cm_case_stake_holder_member where flow_id = p_flow_id;
end;
procedure del_case_comments(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_comment where flow_id = p_flow_id;
pl('Delete '||l_count||' case_comment rows for flow_id '||p_flow_id||'.');
delete from cm_comment where flow_id = p_flow_id;
end;
procedure del_fabric_header_property(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_fabric_header_property where flow_id = p_flow_id;
pl('Delete '||l_count||' fabric_header_property rows for flow_id '||p_flow_id||'.');
delete from cm_fabric_header_property where flow_id = p_flow_id;
end;
procedure del_linked_cases(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_linked_case where flow_id = p_flow_id;
pl('Delete '||l_count||' linked_case rows for flow_id '||p_flow_id||'.');
delete from cm_linked_case where flow_id = p_flow_id;
end;
procedure del_property_bag(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_property_bag where flow_id = p_flow_id;
pl('Delete '||l_count||' property_bag rows for flow_id '||p_flow_id||'.');
delete from cm_property_bag where flow_id = p_flow_id;
end;
procedure del_translation(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_translation where flow_id = p_flow_id;
pl('Delete '||l_count||' translation rows for flow_id '||p_flow_id||'.');
delete from cm_translation where flow_id = p_flow_id;
end;
procedure del_case(p_flow_id in number)
is
l_count number;
begin
select count(*) into l_count from cm_case where flow_id = p_flow_id;
pl('Delete '||l_count||' case rows for flow_id '||p_flow_id||'.');
delete from cm_case where flow_id = p_flow_id;
end;
begin
pl('Purge cases without SCA flow');
for r_cse in c_cse loop
pl(r_cse.case_id || ' - ' || r_cse.flow_id|| ' - '||r_cse.partition_name||' - '||r_cse.partition_date);
del_case_header( r_cse.flow_id);
del_case_data( r_cse.flow_id);
del_case_object( r_cse.flow_id);
del_case_document( r_cse.flow_id);
del_case_milestones( r_cse.flow_id);
del_case_events( r_cse.flow_id);
del_case_flex_fields( r_cse.flow_id);
del_case_stake_holders( r_cse.flow_id);
del_case_stake_holder_members( r_cse.flow_id);
del_case_comments( r_cse.flow_id);
del_fabric_header_property( r_cse.flow_id);
del_linked_cases( r_cse.flow_id);
del_property_bag( r_cse.flow_id);
del_translation( r_cse.flow_id);
del_case( r_cse.flow_id);
end loop;
end;
/
A few remarks: - Use this at your own risk: make sure you have database backup. I must admit I actually didn't (shame), but I have to mention it.
- This script is created by analysing the datamodel of SOA BPM 12c. It is not supported by Oracle of course. I won't take any responsibility of damage caused by using this script (of course). See my advise to take a backup in the previous bullet.
- I assume that it is as designed that cases aren't deleted at purging of the instances: a case is an administrative entity containing data and audit-logs that are related to customer interacts.You might adapt the driving query with an extra where-condition to only select the cases relating to the removed composites.
- Case definitions are not removed.
- Case documents are not removed from WebCenter Content, only the register in the CM_CASE_DOCUMENT table.
create table cm_case_definition_bck as
select * from cm_case_definition
where composite_name = '{your composite}'
and composite_version != '{composite version you want to keep}';
delete from cm_case_definition
where composite_name = '{your composite}'
and composite_version != '{composite version you want to keep}';;
Are there automated test for plsql to make sure the stored proc works?
ReplyDeleteNo, this is what I created myself by looking into the datamodel.
ReplyDeleteJust restart your server and see if the cases and the error-message are gone.