1. Using Oracle B2B
  2. Appendices
  3. Database Partitioning

L Database Partitioning

This appendix discusses the partitioning of the Oracle B2B instance tables.

The appendix contains the following sections:

L.1 Introduction

Oracle B2B allows you to partition the Oracle B2B instance tables based on the CPST_INST_CREATED_TIME column.

The following tables can be partitioned:

  • B2B_APP_MESSAGE

  • B2B_BUSINESS_MESSAGE

  • B2B_DATA_STORAGE

  • B2B_EXT_BUSINESS_MESSAGE

  • B2B_WIRE_MESSAGE

L.2 Partitioning Requirements

In this section are the requirements that you need for successful partitioning of the Oracle B2B tables.

  • Use range or interval partitioning to partition the tables.

  • Ensure that the date ranges specified across all the Oracle B2B tables are the same. This is a prerequisite for purge to function properly.

  • When using range partitioning, it is recommended to use a default partition to avoid runtime failures.

  • Re-creation of tables:

    • The Oracle B2B system materialized view has dependencies on the Oracle B2B instance tables (B2B_BUSINESS_MESSAGE, B2B_EXT_BUSINESS_MESSAGE) and materialized view logs are created on these tables. During the creation of the partitions, the materialized view logs need to be re-created and the system materialized view needs to be fully refreshed.

    • Post database table partitioning, the materialized view B2B_SYSTEM_MV is in STALE. This would have happened after partitioning the DB as any DDL operation on one of the materialized view's dependencies (DROP, ALTER...) will invalidate it. Even though the corresponding master tables (B2B_BUSINESS_MESSAGE, B2B_EXT_BUSINESS_MESSAGE) now after partitioning are FRESH, this view has to be updated.

  • After database table partitioning, you need to recreate the B2B indexes, otherwise the throughput of the system can be drastically reduced.

  • At the time of upgrade:

    • It is recommended to perform the partitioning only after the application instances are upgraded to the required version of Oracle SOA Suite, because upgrading the Oracle SOA Suite schema needs to be completed before partitioning. This is done to avoid NULL values being introduced in the CPST_INST_CREATED_TIME column of the database from the older versions of the runtime.

    • In case there are existing data in these tables that contains NULL values for the CPST_INST_CREATED_TIME columns, you need to update the records to non-NULL values for appropriate partitioning.

L.2.1 Setting Up Partitioning

To support purge based on partitioning the instance tables of B2B need to be partitioned.

You need to partition the following tables:

  • B2B_APP_MESSAGE

  • B2B_BUSINESS_MESSAGE

  • B2B_WIRE_MESSAGE

  • B2B_EXT_BUSINESS_MESSAGE

  • B2B_DATA_STORAGE

By default these tables will be set up in the schema with referential constraints. The DBA's should disable the constraints in the environment where the partitions need to be setup.

These tables will have to be partitioned based on the CPST_INST_CREATED_TIME column present in these tables.

The DBAs can use either range or interval partitioning to set up the partitions.

The ranges of the partitions should be the same across all the B2B instance tables.

The current purge supports an all or none partition mechanism where it expects all the B2B tables to be partitioned for the partition based purge to take effect.

L.2.2 Migrating Data

For migrating data from the existing tables to the partitioned tables, you can choose a strategy that is applicable based on the volume of data which needs to be migrated. Some of the more popular strategies employed include

  • Create Table as Select (CTAS)

    • Creating a new partitioned table by copying over the required data from the existing tables using the CTAS statement

    • Once the tables are created, the DBAs can create indexes on the new tables and rename them to the original table name.

  • Exchange partition

    • Exchange partition achieves data movement, by exchanging data segments between the existing table and the partitioned table.

Note:

Prior versions of B2B messages (pre-11.1.1.7 releases) might have the CPST_INST_CREATED_TIME field as null in some cases (due to resubmits).

You can clean these up by populating these fields with values from the CREATED_TIME column of the B2B_BUSINESS_MESSAGE table.

L.2.2.1 Purge Process

To accomplish the purge process:

  • You can use the command line purge -Dpartitioned=true parameter to invoke the partition based purge.

  • The partition-based purge is triggered only if date range is the only criteria provided. If any other date range is provided, the purge falls back to the normal purge mechanism.