Sunday, February 1, 2009

Purge B2B runtime data from the command prompt for Oracle B2B 10g

Oracle B2B 10g (10.1.2.0.2) runtime data can grow at an alarming rate in a medium usage production environment. Therefore scripting the purging of older B2B runtime data should be considered. This can be done through the command prompt or from SQL*Plus. In my last project, the database grew at a rate of 10GB per week due to this runtime data and purging took approximately 1 minute for 10,000 records on a dual CPU server.

Purging B2B runtime data

1. Set the environment by using setenv.sh. You must use the fully qualified path when invoking it (do not type $ORACLE_HOME). For example:
    . /u01/app/oracle/product/10.1.2/b2b_1/ip/install/setenv.sh
2. Change into the $ORACLE_HOME/ip/install directory before executing the command:
    cd /u01/app/oracle/product/10.1.2/b2b_1/ip/install
Examples of purging Complete and Error instances are shown below:
    java oracle.tip.repos.purge.PurgeManager purgeRuntime -start 01-JAN-2008 -end 10-JAN-2008 Complete

    java oracle.tip.repos.purge.PurgeManager purgeRuntime -start 01-JAN-2008 -end 10-SEP-2008 Error

3. Alternatively, you can SQL*Plus into the database and execute the purge_runtime procedure:
    sqlplus b2b@orcl

    SQL> exec purge_runtime('21-JAN-2008', '07-FEB-2008', 'Complete');

You need to ensure that proper purging procedures are in place and are in conformance with your IT data retention policies.

No comments: