1. Administration Guide
  2. Business Data Purge and Archive

Business Data Purge and Archive

To maintain best performance, it's necessary to periodically purge or archive data from the operational database. Oracle Transportation and Global Trade Management uses multiple methods for purging and archiving data. This section explains these concepts in more detail.

Purging old data helps maintain best performance in the operational system. Archiving is used with purging for critical business objects which need to be accessible for an extended period of time. When data is archived, it's moved to an archive database schema and compressed. Data in the archive schema can be queried, but can't be modified.

Note: Templates aren't deleted when you purge data.
These business objects support archiving, with the following settings:
  • Operational Retention Period: How long the data is kept in the operational database. If the data hasn't been updated for the period specified by Operational Retention Period, then it's archived. The Operational Retention Period is 2 years for Shipment, Invoice, Work Invoice, Order Release, OB Order Base, DM Transaction, GTM Campaign, Device and P_BID entities. The Operational Retention Period is 5 years for GTM Transaction entity.
  • Frequency: How often the job runs to archive data. The Frequency is weekly.
  • Archive Retention Period: How long the data is kept before it's permanently purged from the system. The Archive Retention Period is 10 years.

Purge Only Option

The Business Data Purge process stores deleted data in the archive database schema by default. A domain can be marked to skip archiving of the purged data by selecting the Purge Only (No Archive) checkbox on the Domain Settings page. To enable the Purge Only option, you must open a service request with Oracle Support. Oracle Support also offers an option to skip archiving for all domains, in which case the Purge Only (No Archive) checkbox isn't functional. Likewise, if you haven't contacted Oracle Support about enabling Purge Only, the checkbox isn'tfunctional on the Domain Settings page.

Note: If you select the Purge Only (No Archive) checkbox on the Domain Settings page, domain data will be completely lost when the two-year purge starts.

Business Objects that Support Archiving

All objects found in Configuration and Administration > Process Management > Mark for Purge in the Purge Type field support archiving.

Archived data are found in the Report Manager in the "Archive" reports section. The archive reports take a single parameter, which is the ID of the object to be retrieved from the archive. The search for the ID lets you query the archive schema business objects using flexible criteria. Some sample archive reports are provided upon installation.

Documents associated with the business objects above are archived when the business objects are archived. A DBA or Admin user role may access the archived documents using the Archived Documents UI, via Business Process Automation > Document Management > Archived Documents. Not all documents are associated with business objects, and those which aren't associated aren't archived or purged.

Note: There are no indexes on the archive schema database tables, thus the performance of the search is expected to be slower than the operational database. If the data returned by the sample report is not sufficient, it's recommended to copy the default report and change it as needed.

Scheduled Purges

Scheduled purges are used for purging miscellaneous transient and diagnostic data. The following table defines the retention period and purge frequency. The timing and frequency of these processes should not be altered.

Retention period and purge frequency

Entity Retention Period Purge Frequency
Action Log 7 days Weekly
Audit 30 days Weekly
Bulk Plan Results 30 days Weekly
Bulk Reporting 30 days Weekly

Business Intelligence Publisher (BIP) Schedule Job History

Note: The retention period of 7 days is applicable only for jobs scheduled in Business Intelligence Publisher and not for jobs scheduled in Oracle Transportation Management via Business Process Automation > Reporting > View Scheduled Jobs.
 7 days During upgrade
Demurrage Transaction 2 years Weekly
Device Association 2 years Weekly
Document Content 30 days Weekly
Email Delivery Suppression 30 days Daily
GTM Data Content 30 days Weekly
GL User Authentication 30 days Weekly
GTM Campaign 2 years Weekly
GTM Shipment 5 years Weekly
GTM TIP Inventory 5 years Weekly
GTM Transaction 5 years Weekly
Invoice 2 years Weekly
Mail 7 days Weekly
Object Lock 30 days Weekly
Order Base 2 years Weekly
Order Release 2 years Weekly
Planning Diagnostics 30 days Weekly
QD Logs 7 days Weekly
RPLS Ad hoc Audit 60 days Every two months
Sell Side Shipment 2 years Weekly
Shipment 2 years Weekly
Tracked Files 1 hour Hourly
Work Invoice 2 years Weekly

Partitioned Purges

Oracle Transportation and Global Trade Management contains several integration and logging tables that can become quite large very quickly; these tables have been partitioned to allow for quick purges of older data. By partitioning the tables, a particular partition (segment) can be truncated, instead of records being individually deleted, which is inefficient for large amounts of data. The following table explains the time period which is used to create the partitions and the number of partitions for each entity.

Time period which is used to create the partitions and the number of partitions

Entity Time Period Partitions
Data Queue - LIFETIME Monthly 12
Data Queue - VISIBILITY Monthly 12
Data Queue - NOTIFY 14 days 12
Data Queue - INTEGRATION_IN Daily 12
Data Queue - INTEGRATION_OUT Monthly 12
Data Queue - NS_CONTACT_POINT_OVERRIDE 3 days 12
Data Queue - ADHOC_NOTIFY 3 days 12
Data Queue - VISIBILITY_SHIPMENT 11 days 12
Data Queue - VISIBILITY_INVOLVED_PARTY 11 days 12
Data Queue - VISIBILITY_PIECE 11 days 12
Explanation Daily 7
Integration Logging Monthly 4
Integration Logging(Mobile) Daily 7
Login History Monthly 4
Mobile Messages Daily 7
Object Lock Daily 7
Problem Monthly 4
Process Control History Quarterly 4
Transaction(Mobile) Daily 7
Transaction(Inbound) Bi-Weekly 4
Transaction(Outbound) Weekly 4
Transaction(Mobile) Daily 7
Transmission(Inbound) Bi-Weekly 4
Transmission(Outbound) Weekly 4
Tender Transmission(Outbound) Quarterly 4

These jobs are set to run at 1 AM on the last day of the cycle. Every table reuses its partitions, because the intention is that before the end of the cycle, the oldest partition is purged in preparation for the new cycle. For example, for a monthly table, on April 30th, partition 1 should be purged to remove January’s data, which will then be used for May. For example:

If the time period of the table is monthly, then the data is segmented as follows:

  • Jan – partition 1
  • Feb – partition 2
  • Mar – partition 3
  • Apr – partition 4
  • May - partition 5
  • June – partition 6, etc.

Schedule at which oldest partition is purged in preparation for the new cycle

Time Period Oldest Partition Purge Schedule
Daily Every day at 10 PM UTC
Weekly Every Monday at 4 AM UTC
Bi-Weekly Every other Sunday at 4:30 AM UTC
Monthly Every last day of the month at 5 AM UTC
Quarterly Every last day of the quarter at 6 AM UTC