Skip to content
October 29, 2012 / danielsoaresmartins

How to recover stale composite instances and their components

Today we will demonstrate how to recover stale composite instances and their soa components. We had this problem in one of our customer’s production environment when one of the composites was undeployed by mistake. Given that there were no backups of the SOA/BPM database (SOAINFRA scheme) and that there aren’t any recovery scripts from Oracle we had to build our own and at our risk (Oracle does not support this kind of recovery and advises to keep regular backups).

The first step we took was to redeploy the composite which evidently shown all instances as stale. When a composite is deployed all the files are stored in the Metadata Services Repository (MDS) and it is tagged with a unique MDS label. So when we redeployed the composite, even though it was the same version, it was associated with a new MDS label.

You can see the label in the composite distinguished name (Composite DN) using Enterprise Manager in the composite information dialog:

For the undeployed composite we had to check in the stale composite instances (value of the composite_dn column in the composite_instance table of those instances).

The script we created requires some knowledge about the state of the various components we wish to recover, in our case Composites, BPM/BPEL Processes and Human Tasks. Fortunately there was some very useful information in the A-Team’s Soa blog about the state of SOA components namely the COMPOSITE_INSTANCE table for composite instances, the CUBE_INSTANCE table for BPEL/BPM processes, etc. This helped us understand what some of the states meant given that at the database level they are represented by numbers.

After performing a considerable number of tests we were successful in developing a plsql script that recovered the stale instances:

  -- Script Parameters:
  -- Composite Name
  cmpstName VARCHAR2(200) := 'TestZComposite';
  -- Composite Version
  cmpstVersion VARCHAR(20) := '1.2';
  -- Composite old distinguished name
  old_composite_dn VARCHAR2(500) := 'default/TestZComposite!1.2*soa_87ad6db9-5a3e-4e9a-8940-f9b2885121b4';
  -- Composite new distinguished name (new mds label => soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97)
  new_composite_dn VARCHAR2(500) := 'default/TestZComposite!1.2*soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97';
  -- Composite new MDS label
  new_mds_label VARCHAR2(200) := 'soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97';  
  -- Step 1
  -- update compositedn (new mds label) and reactivate stale composites 
  -- (state 64 = stale, state 0 = running)
  update composite_instance ci
  set ci.composite_dn = new_composite_dn,
      ci.state = CASE
                   WHEN (ci.state is not null and ci.state = 64) 
                     THEN 0
                     ELSE ci.state                    
  where ci.composite_dn = old_composite_dn; 
  -- Step 2
  -- update mds label and compositedn in triggered messages
  update dlv_message dlvm
    set dlvm.composite_label = new_mds_label,
        dlvm.res_subscriber = CASE
                                 WHEN dlvm.res_subscriber IS NOT NULL 
                                     THEN replace(dlvm.res_subscriber, 
                                     ELSE dlvm.res_subscriber                            
  where dlvm.composite_name = cmpstName and 
        dlvm.composite_revision = cmpstVersion;
  -- Step 3
  -- reactivate callbacks and update mds label and compositedn 
  -- (state 3 = stale/cancelled, state 0 = unresolved)
  update dlv_subscription dlv 
  set dlv.composite_label = new_mds_label,
      dlv.subscriber_id = replace(dlv.subscriber_id, old_composite_dn, new_composite_dn),
      dlv.state = CASE
                   WHEN (dlv.state IS NOT NULL AND dlv.state = 3)
                       THEN 0
                       ELSE dlv.state             
  where dlv.composite_name = cmpstName and
        dlv.composite_revision = cmpstVersion;
  -- Step 4
  -- reactivate references and update compositedn 
  -- (state 0 = successful, state 6 = stale)
  update reference_instance t 
  set t.state = 0,
      t.composite_dn = new_composite_dn
  where t.composite_dn = old_composite_dn and t.state = 6;       	
  -- Step 5
  -- reactivate bpel/bpmn processes and update compositedn 
  -- (state 1 = open running, state 9 = closed stale)
  update cube_instance cui 
  set   cui.composite_label = new_mds_label,
        cui.state = CASE
                       WHEN (cui.state IS NOT NULL AND cui.state = 9)
                           THEN 1
                           ELSE cui.state             
  where cui.composite_name = cmpstName and cui.composite_revision = cmpstVersion;
  -- Step 6
  -- reactivate human tasks and update compositedn
                              THEN 'ASSIGNED'
                              THEN 'WITHDRAWN'
                              THEN 'ASSIGNED'
                              THEN 'ALERTED'
                              THEN 'SUSPENDED'
                              THEN 'ERRORED'
                              THEN NULL
                         ELSE TBL_NOTFS.STATE                            
         TBL_NOTFS.COMPOSITEDN = new_composite_dn,
         TBL_NOTFS.MDSLABEL = new_mds_label
     and TBL_NOTFS.COMPOSITEVERSION = cmpstVersion;

This script worked for us, however if you wish to apply it, I recommend you test it in controlled environment to make sure it works for you. For instance the composite we recovered didn’t have Mediator or Business Rules components, so if yours does you probably have to add additional instructions to the script in order to recover those components.

Best regards,
Daniel Martins


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: