Guardium - Steps to troubleshoot old partitions on a Guardium appliance

Troubleshooting

Problem

The appliance has old partitions that need to be removed.

Symptom

Some possible symptoms of having old partitions in the appliance are:

Health Check patch (eg. patch SqlGuard_<version>p9997, or SqlGuard_<version>p9998) where <version> is a Guardium version, fail and the patch output file health_check-<timestamp>.log contains the following message:

ERROR: Old partitions found. The oldest partition is while expected oldest date is <date>. Please run support must_gather patch_install_issues and contact support to clean up old partitions

Aggregation Archive or Purge performance is poor
Machine has not been running aggregation processes for a long time. (For example, disk space issues)
Aggregation/Archive Log shows finding X days to purge where X is a large number, outside the retention period

Note: The existence of old partitions might not always match one of these symptoms. Sometimes the symptoms can be different. Unfortunately, it is impossible to make a comprehensive list of all possible symptoms that can result from having old partitions. In some situations, there might not even be symptoms.

Cause

There are several conditions that can lead to old partitions. Here are some:

Old data has not been archived or exported, thus it has not been purged.
Purge has not been executed for those dates.
Sessions have not been marked as closed in the Guardium GDM_SESSION table (can occur under certain abnormal scenarios, such as, the session was never closed on the application side and the Session Inference Job is not running in Guardium). The purge process will not purge a session that has not been closed.
The Data is a result of a Data Restore and the expiration date for the data has not been reached.
Issues at MySQL level that can lead to MySQL having wrong metadata for the table due to how MySQL handles the tables.
Table corruption.

Note: This list is not comprehensive; there might be other reasons that can lead to old partitions.

Environment

Any version of a Guardium appliance.

Diagnosing The Problem

Verify whether there are old partitions by using one of these methods:

Run the "Days not exported or archived" report (under Manage > Maintenance > General on the GUI) and check the output. Note that this report is only available starting with v11.3. You will need to use the other method discussed below, based on old_partitions_<timestamp>.log, with older versions of Guardium.
Run one of the following diagnostics and review the old_partitions_<timestamp>.log included in the resulting file:

support must_gather system_db_info
support must_gather sniffer_issues
support must_gather agg_issues

If any of those methods show old dates that have not been archived or exported or old partitions, you are in presence of the problem described in this technote.

Resolving The Problem

1.- Get the list of the dates that have not been archived or exported

Run the "Days not exported or archived" report on the GUI (available for v11.3, v11.4, v11.5) at:

Manage > Maintenance > General > Days not exported or archived on the GUI

Related links:

https://www.ibm.com/docs/en/guardium/11.3?topic=mdarasb-viewing-days-whose-data-was-not-archived-exported

https://www.ibm.com/docs/en/guardium/11.4?topic=mdarasb-viewing-days-whose-data-was-not-archived-exported

https://www.ibm.com/docs/en/guardium/11.5?topic=mdarasb-viewing-days-whose-data-was-not-archived-exported

Notes:

This report lists dates that have not been exported or archived, which might include dates that have already been purged (but were never exported or archived.)
If the report is not available on the GUI, run support must_gather sniffer_issues, and locate old_partitions_<timestamp>.log included the resulting file. Use this log file instead of the report output (the log file will list the partitions corresponding to dates that have not been archived or exported, although in a different format than the report.)

2.- Take appropriate action to either archive, export, or purge the data for old dates in the list:

If there is at least one date that has not been exported or archived which was supposed to be exported or archived, do one of the following:

If you do not care about the old data and want to purge it, follow steps in the following link in the documentation to purge the data:
https://www.ibm.com/docs/en/gdp/12.x?topic=system-purging-data
Move to step 3.- after that.
If you do care about the data, move to step 2.1.- below.

2.1.- Examine the list of dates

This is an example after running the Days not exported or archived report:

2.2- Configure Data Archive to archive (or export) the data you do not want to lose

Configure Data Archive to archive the dates you do not want to lose by using one of the methods discussed below.

Note: If the appliance is a collector, instead of configuring Data Archive, you can configure Data Export to export the data to the Aggregator. Note that in this case, the Aggregator may then face the issue with old partitions and you might need to address that issue on the Aggregator with a Data Archive.

METHOD 1: To archive only one day on next run of the Data Archive job.

Note: Use this method if you want to archive one day at a time.

Find the date you want to archive in the report
Get the values for columns “Archive older than” and “Ignore older than” (last two columns) in the report. For example, in the screen capture above, for date 2022-05-14, these values are 204 and 205, respectively.
Use these values to configure the Data Archive panel on the GUI to archive that date

For example, to archive the data for 2022-05-14, set Archive data older than, and Ignore data older than fields in the Data Archive panel on the GUI like this:

image-20221204210551-2

If there are more than one consecutive dates you want to archive, you can adjust the “Archive data older than”, and the “Ignore data older than” values to archive a set of contiguous dates of data.

METHOD 2: To archive the entire set of dates on next run of the Data Archive job.

Note: There might be some days within this range that have already been archived. Use this method if you want the convenience of running the job only once to cover all dates, but be aware that it could take much longer.

Sort the report output by the first column “From Date”
Get the oldest date as well as the newest date from the first column “From Date”
From the row corresponding to the newest date, get the value of column “Archive older than”. If the value is 0, use 1 instead. For example, newest date in above screen capture is 2022-12-04, with “Archive older than” value of 0, so we will use 1 for “Archive older than”.
From the row corresponding to the oldest date, get the value of column “Ignore older than”. For example, oldest date in screen capture above is 2022-05-14 with “Ignore older than” value of 205.
Use the values obtained in the previous two points to configure the Data Archive panel on the GUI. For example, to archive all the dates in the screen capture above (from 2022-05-14 to 2022-12-04), set Archive data older than, and Ignore data older than fields in the Data Archive panel on the GUI like this:

image-20221204210701-3

Note: A variation of this method that can also be used is to run the Data Archive several times, archiving chunks of days each time.

2.3- Configure the Purge settings:

Select the checkbox for “Purge”
Clear the checkbox “Allow purge without exporting or archiving” (otherwise, it will purge other dates despite not being archived yet)
Set “Data older than” to the desired Purge Age

For example, for Purge Age of 30 days:

image-20221204210755-4

2.4- Run a Data Archive job

2.5- Repeat steps 2.1 through 2.4 until you have archived all the days you want to archive

Note: If a date that is supposed to been purged is still not been purged even after following the applicable recommendations mentioned above, jump to step 4.- below to check for additional scenarios that may cause this.

3.- Run the Days not exported or archived report again:

Rerun the report from steps 1 and 2 again until you have archived all the data you care about.

4.- Generate the old_partitions_<timestamp>.log log to check for old partitions not reported in the Days not exported or archived report, or if the report is not available on the version of your appliance:

4.1.- Generate a must gather

Run a must gather that includes the old_partitions_<Timestamp>.log. Some of the diagnostics that include this file since v11.3 are:
support must_gather system_db_info
support must_gather sniffer_issues
support must_gather agg_issues

4.2.- Explaining the old_partitions_<Timestamp>.log

Review the old_partitions_<Timestamp>.log for any remaining dates outside of the retention period. The log has 4 main sections that provide details about old partitions found on the appliance, as well as additional information about these partitions: if there is any data in them, the table names in these partitions, and suggestions to clean up the partitions. The 3 sections in the log are:

LIST OF FIRST PARTITIONS OLDER THAN PURGE AGE

LIST OF FIRST 20 NOT ARCHIVED PARTITIONS OLDER THAN PURGE AGE

LIST OF FIRST 20 NOT EMPTY TABLES WHERE DATA WAS SUPPOSED TO BE DELETED

LIST OF RESTORED DATES

Each of these sections, as their titles indicate, contain information that will help understand the state of the partitions and how to clear them up.

4.3.- Review the old_partitions_<Timestamp>.log

Review the log and take appropriate action. Below is a sample old_partitions.log file followed by the most common scenarios you may find:

Sample old_partitions_<timestamp>.log:

LIST OF FIRST PARTITIONS OLDER THAN PURGE AGE

+----------------+-------------------------------+------+---------------------------+-----------------------------------------+

| PARTITION_NAME | TABLE_NAME                    | ROWS | STATUS                    | RECOMMENDATION                          |

+----------------+-------------------------------+------+---------------------------+-----------------------------------------+

| p20220616      | GDM_POLICY_VIOLATIONS_LOG     |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_POLICY_VIOLATIONS_COUNTER |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_EXCEPTION                 |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_CONSTRUCT_VALUES          |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_CONSTRUCT_TEXT            |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_CONSTRUCT_INSTANCE        |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_APP_EVENT                 |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_APP_DATA                  |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | MESSAGE_TEXT                  |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | CAS_AUDIT_HOST_EVENT          |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | MESSAGE                       |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

| p20220616      | GDM_SESSION                   |    0 | Partition is NOT Archived | Consider to Allow Purge without Archive |

+----------------+-------------------------------+------+---------------------------+-----------------------------------------+

LIST OF FIRST 20 NOT ARCHIVED PARTITIONS OLDER THAN PURGE AGE

LIST OF FIRST 20 NOT EMPTY TABLES WHERE DATA WAS SUPPOSED TO BE DELETED

+----------------+---------------+------+
| PARTITION_NAME | TABLE_NAME | ROWS |
+----------------+---------------+------+
| p20220617 | GDM_EXCEPTION | 6 |
| p20220804 | GDM_EXCEPTION | 217 |
| p20220805 | GDM_EXCEPTION | 217 |
| p20220806 | GDM_EXCEPTION | 217 |
| p20220807 | GDM_EXCEPTION | 217 |
| p20220808 | GDM_EXCEPTION | 217 |
| p20220809 | GDM_EXCEPTION | 217 |
| p20220810 | GDM_EXCEPTION | 216 |
| p20220811 | GDM_EXCEPTION | 217 |
| p20220812 | GDM_EXCEPTION | 217 |
+----------------+---------------+------+

LIST OF RESTORED DATES

How to review this information:

For each partition listed, review the status of the partition (in first and second sections/tables), as well as the tables, if any, with data in that partition (in third section/table). The most common scenarios are below:

CASE 1 ( "Partition is NOT Archived", RECOMMENDATION="Consider to Allow Purge without Archive", and "Partition is Empty"):

STATUS="Partition is NOT Archived" (usually in Section/Table 1)

RECOMMENDATION="Consider to Allow Purge without Archive" (usually in Section/Table 1)

STATUS="Partition is Empty" (usually in section/Table 2)

What to do:

Since the partition is empty, there is no data in it to be lost. On the Data Archive configuration screen on the GUI, check the Purge checkbox. Also check the "Allow purge without exporting or archiving" checkbox, then run a Data Archive and this should delete the old partition.

Note: Make sure how you configure Purge Data Older than, and Ignore Data older than correctly so the particular date for this partition (as well as for any other partition that you want to be purged) are purged.

CASE 2 ( "Partition is NOT Archived", RECOMMENDATION="Consider to Allow Purge without Archive", and "Partition is NOT Empty"):

STATUS="Partition is NOT Archived" (usually in Section/Table 1)

RECOMMENDATION="Consider to Allow Purge without Archive" (usually in Section/Table 1)

STATUS="Partition is NOT Empty" (usually in section/Table 2)

What to do:

The partition contains data.

Review the table in Section/Table 3, identify which tables contain data in the partition. Use this information to decide what do you want to do with the data: decide if you want to purge it without archiving it first, or if you want to archive it first then purge it. Possible Outcomes:

-If you do not care about the data on the partition and want to purge it, configure the Purge on the GUI and check the "Allow purge without exporting or archiving" checkbox, then run a Purge and this should delete the old partition.
-If you do care about the data on the partition and want to archive it, follow steps in 2.2.- to archive the data.

CASE 3 (RECOMMENDATION says "try to analyze table" and ROWS=0):

For some partitions and tables, the partition is empty (ROWS=0) and you may see the recommendation "Try to analyze table". For example, this is a fragment from an old_partitions_<timestamp>.log with this recommendation for 3 tables in partition p20210430:

+----------------+-------------------------------+------+---------------------------+----------------------+
| PARTITION_NAME | TABLE_NAME                    | ROWS | STATUS                    | RECOMMENDATION       |
+----------------+-------------------------------+------+---------------------------+----------------------+
| p20210430      | GDM_APP_DATA                  |    0 | Partition is NOT Archived | Try to analyze table |
| p20210430      | MESSAGE_TEXT                  |    0 | Partition is NOT Archived | Try to analyze table |
| p20210430      | CAS_AUDIT_HOST_EVENT          |    0 | Partition is NOT Archived | Try to analyze table |

What to do:

1.- Optimize the table (note that Optimize is different than the Analyze the log suggests, this is intentional). If the appliance is a collector, stop the sniffer first to avoid conflict; you can restart it later:

support stop inspection-core (if a collector)

support optimize tables <Database> <Table>

support start inspection-core (if a collector)

For example:

support optimize tables TURBINE GDM_APP_DATA

support optimize tables TURBINE MESSAGE_TEXT

support optimize tables TURBINE CAS_AUDIT_HOST_EVENT

2.- In the Data Archive configuration screen on the GUI:

a) Set Archive older than and Ignore older than so the date in the partition is archived

b) Check the Purge checkbox

c) Run a Data Archive

CASE 4 (Data in partition was restored, partition is listed in section LIST OF RESTORED DATES):

If a partition is listed in 4th section LIST OF RESTORED DATES, it means this data was restored via a System Data Restore. For this type of data, there is an additional condition that must be met (in addition to the usual conditions for Purge) in regards to the restored data expiration date. When data is restored you must indicate the expiration date of the restored data, which means until when the data will remain on the appliance. Even if the data is older than the Purge Age, it will not be purged if the expiration date has not been reached. Consequently, if it has not been purged, the partition will not be deleted either until the expiration date for the restored data has passed.

Because of this design, dates and partitions listed in section 4th LIST OF RESTORED DATES, will not be purged if the expiration date has not passed. You can use this grdapi command to see all restored dates and their expiration date:

grdapi list_expiration_dates_for_restored_days

If a date you want to purge is not being purged and it is listed, that is the reason why it was not purged.

What to do:

If you do not care about keeping the restored data anymore:
a) change the expiration date for the restored data using this grdapi command:
grdapi set_expiration_date_for_restored_days
b) repeat steps in this technote, from step 1, and analyze the partitions for the restored dates in particular.
If you want to keep the restored data on the appliance:

Do nothing. Be aware that this data/partition will not be purged until the expiration date to be reached (next Purge job after that should purge the data, assuming all other purge conditions are met)

5.- What to do if none of the steps mentioned above resolve the issue:

Open a support case with Technical support for investigation if old partitions persist after performing the steps mentioned in this technote.

Related Information

Viewing days whose data was not archived or exported

Purging System Data

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB76","label":"Data Platform"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSZSXX","label":"Guardium Database Activity Monitor"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Tips

Guardium - Steps to troubleshoot old partitions on a Guardium appliance

Troubleshooting

Problem

Symptom

Cause

Environment

Diagnosing The Problem

Resolving The Problem

Related Information

Document Location

Was this topic helpful?

Document Information

UID

Share your feedback

Need support?