Managing Resequencer Tables

17 Managing Resequencer Tables

This chapter provides information about cleaning up the database tables that store resequencing data. When Service Bus projects use resequencing to re-order incoming messages, the message payload and metadata, as well as resequencing group information, are stored in the resequencing database tables. Service Bus provides scripts to help you manage the size of these tables and clean up old records.

This chapter includes the following sections:

About the Resequencer Database Tables

The resequencer relies on a database to store, group, and re-order the messages it processes. This database contains tables for storing message and group information for the resequencer.

This database is automatically created when you run Repository Creation Utility (RCU) for a domain. You can configure the resequencer to automatically purge messages that are processed successfully, but even so there will be times when you need to manually manage the resequencing data in the database. Service Bus provides SQL purge scripts to let you purge resequencing data and clean up the database.

Database Table Purge Scripts

The scripts to purge Service Bus resequencer data are located with the other SOA Suite component purge scripts in /MW_HOME/soa/common/sql/soainfra/sql/oracle/122100/soa_purge12. When you run the main SOA Suite purge script, which purges data for all components, the Service Bus resequencing data is also purged. Group information for resequencing groups is not deleted because it includes the necessary information about the next sequence ID for that group. Purging this information is the same as starting the group from the initial sequence ID, which may not be your intent.

Note that the SOA Suite purge procedures can be run in parallel or looped mode. The Service Bus procedure can be run in looped only.

Automatic Purging of Completed Resequencer Messages

Service Bus provides a global setting, Purge Completed Messages, that determines whether resequenced messages are purged automatically once they are successfully processed. Processed messages are purged from the database using the following guidelines:

Messages that are successfully processed are deleted automatically from the database only if Purge Completed Messages is selected. This setting is selected by default. If you do not want messages to be automatically purged, you must clear the setting's check box.
For all types of resequencers, message metadata is automatically purged if Purge Completed Messages is selected.
Failed messages are never purged.
Group information is not purged.

For cases where messages and metadata are not automatically purged, Service Bus provides scripts to purge and manage the resequencer tables in the database.

The Datasource for Resequencing

The resequencer uses the default Oracle SOA Suite datasource, jdbc/SOADataSource, to connect to the resequencing tables in the database. This datasource is automatically created when you install SOA Suite or Service Bus and create a WebLogic Server domain. For the resequencer datasource, the JNDI name must be jdbc/SOADataSource, and it connects to the soainfra database created by Repository Creation Utility (RCU).

Purge Scripts and Resequenced Message Purge States

The purge scripts include purge commands to purge the information persisted in the Service Bus resequencer tables (osb_msg, osb_group_status, and osb_resequencer_message). The following information is purged from the resequencer tables when you run the purge scripts:

Completed and aborted messages for all resequencer types
Timed out messages for standard resequencers
Groups in a ready state for best effort and FIFO (first in/first out) resequencers (these are the only groups that can be purged)

To allow fault recovery and message processing to be completed, the purge scripts do not purge all resequenced message information. In addition, standard resequencer groups store information that should not be purged. The following are not purged when you run the purge scripts:

Faulted messages for all resequencer types
Running messages for all resequencer types
Group information for standard resequencers
Groups in a state other than ready for best effort and FIFO resequencers

Note:

The purge scripts remove messages first and then move on to groups. If there are messages for a group in the osb_resequencer_message table, the group cannot be deleted.

The above describes the processing of the purge scripts, regardless of whether instance tracking is enabled or disabled. Before any sequence groups are purged, a check is performed to verify that all messages associated with the group are processed.

Below is a list of group state codes used in the resequencer tables:

0: Ready
1: Locked
2: Error
4: Timed out
6: Group error

Below is a list of message state codes used in the resequencer tables:

0: Ready
1: Locked
2: Completed
3: Error
4: Timed out (this is ignored)
5: Aborted

Purging Oracle Service Bus Resequencer Data

You can run the resequencer purge scripts for Service Bus as part of the overall SOA Suite database management scripts, or you can run scripts just for Service Bus.

For more information about the overall SOA Suite database strategy, see Managing Database Growth in Administering Oracle SOA Suite and Oracle Business Process Management Suite.

Note:

SOA Suite uses an Auto Purge feature in Fusion Middleware Control, which automatically purges data from the database tables. This feature does not purge any data from the Service Bus resequencer tables.

Configuring the Resequencer to Automatically Purge Completed Messages

Service Bus provides a global setting that lets you specify whether messages are automatically removed from the resequencing database tables once they are completely processed. This does not purge any group information or any faulted, running, or aborted messages; it only purges successfully processed messages. You cannot retrieve messages that have been purged.

To automatically purge completed messages:

In Fusion Middleware Control, expand SOA and select service-bus.
Click the Global Settings tab.
In the Resequencing section, select Purge Completed Messages.

Note that this is the default setting.
Click Apply.

Messages that are processed successfully by the resequencer will be purged upon completion.

Using SQL Scripts to Purge Resequencer Tables

You can purge data from Service Bus resequencer tables as part of running the full SOA Suite purge procedures, or, if you want to purge just Service Bus resequencer tables on their own, you can run the Service Bus resequencer procedure, soa_osb.deleteOSBResequencerInstances. The purge scripts are located in /MW_HOME/soa/common/sql/soainfra/sql/oracle/122100/soa_purge12, and the Service Bus scripts are located in the /osb subdirectory.

Setting up the Environment and Scripts

The following steps provide general steps for running the purge scripts. For more information, see Deleting Large Numbers of Instances with SQL*Plus in Administering Oracle SOA Suite and Oracle Business Process Management Suite. Note that the above link includes information about the parallel processing, which does not apply to Service Bus.

To set up the database and load the purge scripts:

Create a directory named PurgeLogs in the scripts directory (/MW_HOME/soa/common/sql/soainfra/sql/oracle/122100/soa_purge12).

For diagnostics, the logs are written to this directory (called SOA_PURGE_DIR in the database), which must exist on the file system.
Connect to the database with a SQL editor as SYSDBA.
Run the following commands to grant privileges to the user who executes the scripts:
```
GRANT EXECUTE ON DBMS_LOCK TO USER_NAME;
GRANT CREATE JOB TO USER_NAME;
```
Caution:

Do not use the DEV_MDS user to run the purge scripts. Doing so results in errors.

Run the following commands to define the diagnostic log directory and grant privileges to the above user:

CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS 'SCRIPT_LOCATION/PurgeLogs';
GRANT READ, WRITE ON DIRECTORY SOA_PURGE_DIR TO USER_NAME;

Connect to the database with a SQL editor using the user name to which you granted privileges in the previous steps. Do this from the location of the purge scripts so the scripts are easily available.

The scripts are located in /MW_HOME/soa/common/sql/soainfra/sql/oracle/122100/soa_purge12.
Run the following command to load the purge scripts:
```
@soa_purge_scripts.sql
```
You are now ready to purge the data, as described in Running the Oracle Service Bus Purge Procedure.

Running the Oracle Service Bus Purge Procedure

The following steps describe how to purge only Service Bus data. Running the Service Bus Purge Scripts provides an example of running the Service Bus purge scripts, but Running the SOA Suite Purge Scripts (In Looped Mode) does provide an example of running the procedure for the full SOA Suite. For information and instructions on using the SOA Suite purge procedures, see Deleting Large Numbers of Instances with SQL*Plus in Administering Oracle SOA Suite and Oracle Business Process Management Suite.

To run the Service Bus purge procedure:

Complete the steps under Setting up the Environment and Scripts, and connect to the database with a SQL editor using same the user name as in step 5 in that section.
Run the following commands to log debug information during the purge:
```
@common/debug_on.sql
SET SERVEROUTPUT ON;
```

Run the following command to capture the output in a spool file:

spool '/MW_HOME/soa/common/sql/soainfra/sql/oracle/122100
/soa_purge12/PurgeLogs/spool.log'

Run the following command to purge Service Bus resequencer data:
```
execute soa_osb.deleteOSBResequencerInstances (batch_size,
min_creation_date,max_creation_date);
```
Where:
- batch_size is the maximum number of records to delete at a time. The default value is 20000.
- min_creation_date is the earliest creation date for the records to be removed.
- max_creation_date is the latest creation date for the records to be removed.
Run the following command to stop writing information to the spool file:
```
spool off
```

Running the Service Bus Purge Scripts

The following example purges resequencer records that were created starting on 01/01/2014 and ending on /3/31/2014, in batches of 10000 records. Records are purged for Service Bus only.

execute soa_osb.deleteOSBResequencerInstances ( 10000,
to_timestamp('2014-01-01','YYYY-MM-DD'),
to_timestamp('2014-03-31','YYYY-MM-DD'));

Running the SOA Suite Purge Scripts (In Looped Mode)

The following example clears SOA Suite data, including Service Bus resequencing data, with creation dates starting on January 1, 2011 and ending on January 31, 2011 and with a retention for all instances updated by January 31, 2011, a batch size of 20000 instances, and a runtime of 60 minutes.

execute soa.delete_instances ( to_timestamp('2010-01-01','YYYY-MM-DD'),
to_timestamp('2010-01-31','YYYY-MM-DD'),20000,60,
to_timestamp('2010-01-31','YYYY-MM-DD'),false);

Reconfiguring an Active Resequencer is not Supported

Resequencing configuration should not be modified while the resequencer is active and processing messages. Changing the configuration of an active resequencer can result in unexpected behavior, including messages and metadata being left in the database even if Service Bus is configured for automatic purging of resequenced messages.

For example, if you remove a resequencer from a pipeline, messages that were not yet processed remain in the database. If you modify the resequencer configuration while it is processing messages, messages might not be processed and could remain in the database. For more information about working with an active resequencer, see How Deployment Activities Affect Resequencing and How Server Shutdown Affects Resequencing.

To change the resequencing strategy:

Stop the resequencer for the component you are reconfiguring.
Clean up the database tables using the scripts described in this chapter.
Reconfigure resequencing for the component.
Re-activate or redeploy the component.