Log Management Procedure

This article describes the procedure for managing log data in the OvalEdge database. It covers two scenarios:

Retaining only the last two months of logs without keeping a full backup.
Creating a full backup of logs while reducing the active table to the last two months of records.

The procedure ensures optimal database performance, reduces storage usage, and supports compliance with log retention policies.

Prerequisites

System Access

Root or sudo privileges on the database server
SQL access to the MySQL database

Application Status

Stop the Tomcat server to prevent log writes during the operation

Disk Space

Verify sufficient storage is available for creating backup tables

Database Configuration

Confirm InnoDB buffer pool size is adequate for large operations
Increase temporarily if needed

Retention Requirement

Identify the customer’s log retention policy (default: 2 months)

Case 1: Retain Only the Last 2 Months (No Full Backup Required)

Stop the Tomcat Server

Ensure the application is not writing new logs during database operations

Create a Temporary Backup

Create a backup table with the last two months of logs:
```
CREATE TABLE log_backup_14062024 AS
SELECT * FROM log WHERE logdate >= '2024-05-14';
```
- 2024-05-14 = two months before current date
- Naming convention: log_backup_currentdate (e.g., log_backup_14062024)

Handle Buffer Size Errors (if any)

If memory/buffer errors occur, increase the InnoDB buffer pool size:
```
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1 GB  
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4 GB
```
Note: Changes apply only to the current session unless configured permanently.

Verify Record Counts

Check that the last 2 months of records in the original log table match the records in the log_backup table.
```
SELECT COUNT(*) FROM log WHERE logdate >= '2024-05-14';  
SELECT COUNT(*) FROM log_backup_14062024;
```
- Both counts should match to confirm that the backup table contains the correct subset of data.

Truncate the Original Log Table

This will remove all records from the log table

TRUNCATE TABLE log;

Reinsert Data

Populate the log table with only the last two months of recent data.

INSERT INTO log SELECT * FROM log_backup_14062024;

Analyze the Log Table

Update the database statistics for the log table to optimize query performance.

ANALYZE TABLE log;

Refresh and Validate

Use the database management tool or run the following query to confirm the reduced size of the log table:
```
SELECT COUNT(*) FROM log;
```

Restart Tomcat

Start the Tomcat server and confirm in the OvalEdge job logs that only the last 2 months of logs are visible

Set Audit Archival Policy in OvalEdge

Open the OvalEdge application and check the job logs to ensure only the last 2 months of logs are visible.

Go to Administration → System Settings → Audit Data Archival
Click Add Archival Policy
Select logs, set retention = 60 days
Choose archival format (CSV or SQL)

Case 2: Create Full Backup and Retain Last 2 Months

Stop the Tomcat Server

Ensure the application is not writing new logs

Create a Full Backup Table

CREATE TABLE log_backup_14062024 AS
SELECT * FROM log WHERE logdate <= '2024-06-14';

2024-06-14 = current date
Naming convention: log_backup_currentdate

Handle Buffer Size Errors

Use the same buffer pool adjustment steps as in Case 1

Verify Record Counts

Check that the total number of records in the original log table matches the records in the log_backup table.

SELECT COUNT(*) FROM log;  
SELECT COUNT(*) FROM log_backup_14062024;

Both counts should match to confirm the backup table contains all data.

Truncate the Original Log Table

Remove all records from the log table.

TRUNCATE TABLE log;

Reinsert Data

Insert only the last two months of records back into the log table from the backup while keeping the full log_backup_14062024 table for archival.

INSERT INTO log 
SELECT * FROM log_backup_14062024 
WHERE logdate >= '2024-05-14';

The condition logdate >= '2024-05-14' ensures only records from the last two months (from June 14, 2024) are restored.

Analyze the Log Table

Update the database statistics to optimize query performance.

ANALYZE TABLE log;

Refresh and Validate

Check the table size using a database management tool or run:

SELECT COUNT(*) FROM log;

Restart Tomcat

Start Tomcat service

Verify Job Logs in OvalEdge

Confirm job logs display only for last 2 months

Set Audit Archival Policy in OvalEdge

Go to Administration → System Settings → Audit Data Archival
Click Add Archival Policy
Select logs, set retention = 60 days
Choose archival format (CSV or SQL)

Was this helpful?