Table partitioning

Starting from version 20.02.01, TrueSight Server Automation supports database table partitioning to improve database management functions, such as cleanup, storage, and performance. The partitioning has the following characteristics:

• A separate partition is created for data belonging to each distinct date. 
• The tables are either range partitioned or reference partitioned.
• The existing online database cleanup utility works in the same way for the tables that are not partitioned. The partitions that contain stale data are dropped using the configured result retention policy. The cleanup log shows the names of dropped partitions.

To support partitioning, TrueSight Server Automation provides the install_partitioning.sql script, which is shipped with the installer.

Supported databases

TrueSight Server Automation supports partitioning on the following databases:

Oracle 12c R1, Enterprise Edition or later

Prerequisites

Make sure that the following prerequisites are met:

1. The result retention time period is set as follows:
   
   • You can set different retention time for different job types. However, BMC recommends that you set the same retention time for all job types.
   • An override is not set for the retention time at individual job instance level. For example, at the property dictionary level, global result retention time for all jobs is set to 90 days. It can be overridden at the Compliance job type level to 60 days. However, it cannot be further overridden at individual Compliance job level. For example, for the Compliance Jobs, ComplianceJob_1 and ComplianceJob_2 you cannot set the result retention time to 20 and 30 days respectively.

2. A separate license for Oracle partitioning is purchased, and the partitioning option is enabled. For more information, see Enabling and Disabling Database Options After Installation.

3. Run the database cleanup on the following domains that are relevant to your use cases. For more information, see Cleaning-up-TrueSight-Server-Automation-data.
   • Compliance
   • Job Run Event
4. Run the data cleansing script to get the tables to be partitioned ready for partitioning.
   
   1. Obtain the data cleansing script (data_cleansing.sql) from the following directory in the product installation files, TSSA<version>-<platform>64\TSSA<version>-<platform>\Disk1\files\configurations\db_scripts\oracle\utility\partition.
   2. Run the data cleansing script:
      1. Connect to the database as the TrueSight Server Automation database user.

      2. Run the following command:

         @data_cleansing.sql
5. Obtain the partitioning script (install_partitioning.sql) from the following directory in the product installation files, TSSA<version>-<platform>64\TSSA<version>-<platform>\Disk1\files\configurations\db_scripts\oracle\utility\partition.
6. Plan the downtime required for partitioning. The downtime varies with the database size. BMC recommends that you perform partitioning in a test environment first, using a copy of the production database to better estimate the downtime window.

7. Ensure that tablespaces have sufficient free space required for partitioning.
   The partitioning script provides the CHECK option to validate whether the database meets the following prerequisites:

   • Installed version of the database
   • Availability of the free space required to perform partitioning

   Do the following to run the script: 

   1. Connect to the database as the TrueSight Server Automation database user.

   2. Run the following command: 

      @install_partitioning.sql CHECK 

      The following output indicates that free space is not sufficient for partitioning.
      

Running the table partitioning script

1. Connect to the database as the TrueSight Server Automation database user.

2. Run the following command:

   @install_partitioning.sql FORCE

   If partitioning is successful, the command output looks like the following:
   
   

Partitioned tables

The following tables are partitioned after you run the partitioning script on Oracle:

After you run the partitioning script

The scheduled database cleanup process drops stale partitions. Once the partitions are dropped, global indexes on the partitioned tables have orphan entries referring to the data in the dropped partitions. You can remove these orphan entries using any of the following methods:

• Schedule the database cleanup. For details, see Scheduling automatic data cleanup.

• Use the following BLCLI command to recreate the indexes marked with orphan entries:

  Delete cleanupHistoricalData UpdateGlobalIndex

  For more information, see Delete - cleanupHistoricalData.