Wednesday, February 17, 2010

How-to: Archiving Oracle FMW B2B 11g run-time data using data pumps

In Oracle Fusion Middleware B2B 11g enhanced procedures are introduced to archive and/or purge the B2B 11g run-time data.

The procedures that take care of archiving/purging live in the SOAINFRA database schema of your FMW 11g database repository:
  • B2B_ARCHIVE_PROCEDURE
  • B2B_EXPORT_JOB

The B2B_EXPORT_JOB procedure does the actual archiving and is invoked from the B2B_ARCHIVE_PROCEDURE procedure. The B2B_EXPORT_JOB procedure uses a data pump to archive the run-time data to a file on the file system.

To make use of data pumps, you first have to grant SOAINFRA with DIRECTORY object privileges in order to allow SOAINFRA to create a DIRECTORY object that points to the location on the file system in which the data pump will write the archive file (the name of the DIRECTORY object should be B2B_EXPORT_DIR):

-- I tested it on my development installation using XE, hence the 'dev' prefix
GRANT create any directory TO dev_soainfra;
GRANT drop any directory TO dev_soainfra;

after granting the right priviliges you can create the directory object with:

create or replace directory "B2B_EXPORT_DIR" as '(absolute patch to the location on the file system)'

The following script will archive and purge all the run-time data for completed messages from 19-02-2009 until 19-02-2010 and will archive the data in the file called 'b2b_runtime_export.dat':

begin
b2b_archive_procedure(to_date('19-02-2009', 'dd-mm-yyyy'),to_date('19-02-2010', 'dd-mm-yyyy'),'MSG_COMPLETE','b2b_runtime_export.dat','Y');
end;

The standard archiving script provided by Oracle FMW B2B 11g can easily be extended to fit your specific needs or to be merged into existing archiving procedures.

References:
- Oracle FMW 11g documentation