Upgrade of OSM table OM_ORDER_INSTANCE
(Doc ID 2304297.1)
Last updated on JULY 29, 2019
Applies to:Oracle Communications Order and Service Management - Version 7.2.4 to 220.127.116.11.0 [Release 7.2 to 7.3.0]
Information in this document applies to any platform.
Oracle Communications Order and Service Management - Version 18.104.22.168 and later patch levels for that release as well as version 22.214.171.124 and later.
Does not apply to OSM 7.3.0 or 7.3.1 releases.
The OM_ORDER_INSTANCE table is the largest table in OSM and contains several very large indexes. These indexes can cause high demands on storage, IO, CPU and SGA. In addition, when using row-based purging a significant portion of time is used updating these indexes, and fragmentation of these indexes may require regular index rebuilding.
To improve the performance of these indexes they are replaced by function based indexes, and will only contain a small subset of the tables records. This requires an upgrade to the OM_ORDER_INSTANCE table and its indexes, since this table is the largest table in OSM the upgrade can take a long time.
To help shorten the offline upgrade maintenance window a pre-upgrade database procedure is available that can be executed online, days or weeks before the actual upgrade. The pre-upgrade script prepares the OM_ORDER_INSTANCE table so that the required offline upgrade window is shorter.
The following article contain the following sections:
- Description of the schema changes made in pre-upgrade
- Installing the OM_ORDER_INSTANCE pre-upgrade
- Executing the OM_ORDER_INSTANCE pre-upgrade
- Monitoring the OM_ORDER_INSTANCE pre-upgrade
- Stopping/Resuming the OM_ORDER_INSTANCE pre-upgrade
- Post Pre-Upgrade Steps
During upgrade testing it took approximately 1 minute per hash subpartition of the OM_ORDER_INSTANCE table to execute. Therefore, if a system has 50 full order range partitions and 32 subpartitions each, it would take about 27 hours total to upgrade the table (50 order partitions x 32 subpartitions x 1 minute per partition / 60 minutes per hour). This time can vary based on several factors (parallelism, partition size, hardware, cartridge/order data). To improve the execution times it is best to purge/drop as many of the older order partitions as possible before running the pre-upgrade.
The pre-upgrade should be executed during off-peak order volumes because it is resource intensive. Reducing the parallelism used by pre-upgrade can reduce the resources required, but lengthens the execution time. The pre-upgrade only updates data in older, full partitions, the active partitions are updated as part of the actual offline upgrade. The pre-upgrade can be stopped at any time, then started again and it resumes where it left off.
Applies to customers upgrading to OSM 126.96.36.199.0 (and later patch levels) or OSM 188.8.131.52.0 (as well as later patch levels and subsequent releases) from prior releases. Customers already on 184.108.40.206.0 (and subsequent patch levels) would not require this pre-upgrade step , as this schema migration will have already been performed. All other releases, including OSM 7.2.x, 7.3.0.x and 7.3.1.x will undergo this schema migration and hence would benefit from this pre-upgrade step.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|Description of the schema changes made in pre-upgrad|
|Installing the OM_ORDER_INSTANCE pre-upgrade|
|Executing the OM_ORDER_INSTANCE pre-upgrade|
|Monitoring the OM_ORDER_INSTANCE pre-upgrade|
|Monitoring Partitioned Schema OM_ORDER_INSTANCE pre-upgrade|
|Monitoring Non-Partitioned Schema OM_ORDER_INSTANCE pre-upgrade|
|Stopping/Resuming the OM_ORDER_INSTANCE pre-upgrade|
|Post Pre-Upgrade Steps|