Auto Purge

Introduction

Data volumes in Oracle Health Insurance applications typically grow when more data is processed. Oracle Health Insurance application has different PL/SQL packages for different purging processes, to name a few: purging data from technical tables, purging operational data, Log data purge, and Datafile purge; these processes do not run automatically. Instead, a user must configure the purge jobs to run on regular schedules during the planned maintenance window. So, the Oracle Health Insurance application has introduced an auto purge job to prevent manual maintenance of scheduler jobs.

Package OHI_AUTO_PURGE_PKG

This procedure invokes all the purging processes from the following tables:

ohi_auto_purge_pkg.purge_all;

Table 1. Structure of the Autopurgemetadata
Field	Description
Package Name	Name of the PLSQL package for a specific purging process.
Procedure Name	Name of the PLSQL procedure which implements the specific purging process.
Description	Description of the purging process: Tells about what exactly the procedure purges.
Retention Days	The number of days the data will be retained, after that, it will be purged.
Minimum Days	The minimum number of days data must be retained. (i.e., always retention days >= minimum days).
Maximum Days	The maximum number of days data can be retained (i.e., always retention days <= maximum days).
Enabled	The flag used to include/exclude a specific purging package from the auto purge process.
Remarks	Any remarks specific to the purging process (for example, a brief explanation for why a certain retention days is set).

The following table lists all the purge jobs that will be part of auto purge package:

Table 2. Purge Jobs Part of Auto Purge Package
Purge job	Default Retention Days	Enabled?	Description
OHI_ACT_PURGE_PKG.PURGE_ACT	60	Y	This package is used to purge activities.
OHI_FIN_PURGE_PKG.PURGE_ALL	60	N	This package is used to purge financial data.
OHI_REPL_PURGE_PKG.PURGE_DATA	60	Y	This package is used to purge replication data.
OHI_DATA_PURGE_PKG.PURGE_DATA	60	Y	This package is used to purge tasks and batch data.
OHI_DFP_PURGE_PKG.PURGE_DATAIMPORT_DATA	60	Y	This package is used to purge import data.
OHI_PURGE_DATAFILESET_PKG.PURGE_DATA	60	N	This package is used to purge datafile sets.
OHI_LOG_PURGE_PKG.PURGE_PHI_LOGS	2560	Y	This package is used to purge PHI logs.
OHI_LOG_PURGE_PKG.PURGE_DYLO_LOGS	30	Y	This package is used to purge dynamic logic logs.
OHI_LOG_PURGE_PKG.PURGE_APPLICATION_LOGS	30	Y	This package is used to purge application logs.
CLA_PURGE_OPERATIONAL_DATA_PKG.PURGE_CLAIMS_DATA	2560	N	This package is used to purge all claims that meet the configured retention period, regardless of the claim form, form type, or brand. The auto purge also removes the Financial details and limits consumptions and claims.
CLA_PURGE_OPERATIONAL_DATA_PKG.PURGE_AUTHORIZATIONS_DATA	2560	N	This package is used to purge all the authorization data irrespective of the claim form or dynamic usages, which meet the configured retention period.
CLA_DATA_PURGE_PKG.PURGE_DATA	60	N	This package is used to purge claims technical data.

Read and Update Auto Purge Metadata

Use the HTTP API resource generic/autopurgemetadata to manage retention periods for various purge processes, such as datafileset purge, exchange purge, log purge, etc.

To get an overview of specific purging processes, execute a GET request to generic/autopurgemetadata/{id}.
To get an overview of all available purging processes, perform a search operation on generic/autopurgemetadata/search.
To update the following for a specific purging process, execute a PUT/PATCH request to generic/autopurgemetadata/{id}:
- retentionDays
- enabled
- remarks

For example, to get a list of all available purging processes, POST the following JSON payload on generic/autopurgemetadata/search:

{
    "resource": {
        "q": ""
    }
}

For example, to set a retention period of 90 days for purging application logs. Patch the following JSON payload on generic/autopurgemetadata/{id}:

{
    "retentionDays": 90,
    "enabled": true,
    "remarks": "Increasing the amount from 60 to 90 days because of business requirements"
}

Initially, the purging process for operational data is disabled by default.
A daily job will trigger this auto purge, and once a specific purging process is enabled, all data exceeding retention days will be lost.
The initial purge job might take longer if the volume of the data is very high (for example, if the system has two years of activities and if the retention days for technical data are set to 60 days). So, the user should set a higher retention period (for example, one year and 11 months) for the initial run (so only 30 days of data is purged in the first run). The retention days can be reduced gradually until the historical data is purged.

Use DBMS_SCHEDULER for Frequent Auto Purge

Oracle recommends frequent auto-purge of technical data through a DBMS_SCHEDULER job. For example, the following SQL script creates a job that executes the auto-purge every day at 04:00.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'auto_purge_daily',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                          ohi_auto_purge_pkg.purge_all;
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Executes Auto-purge every day at 04:00');
END;

A CREATE JOB privilege is essential for creating this script.

Logging

The execution time of each procedure is logged in the ohi$install_log table. A select query on the table can retrieve the execution time for each procedure. To obtain execution time for an activity purge is demonstrated in the following example:

select log_date
,      log_action
,      log_text
from   ohi$install_log
where  log_action = 'OHI_ACT_PURGE_PKG.PURGE_ACT'
;

Table 3. Execution Time of a Procedure
Log Date	Log Action	Log Text
05-OCT-12 04.05.47.026881000 PM	OHI_ACT_PURGE_PKG.PURGE_ACT	execution time is 0.149 s

The following table is used to store specific information related to the purging job:

Table 4. Purge Execution Log Details
Column Name	Description
Log_date	To store the timestamp at which the entries are logged.
Log_action	It is used to store information on the executed purge job.
Log_text	It is used to store the execution time (in seconds) of the corresponding purge job.