E Archive and Purge

This appendix contains information about the archive and purge process.

E.1 Overview

This section presents the concepts, prerequisites, policy, and post-process procedures in archiving and purging the Oracle Adaptive Access Manager database. A DBA or system administrator, who performs routine maintenance and the archiving and purging of the Oracle Adaptive Access Manager database, should follow the instructions in this chapter.

The overview section contains the following topics:

Purge Process
Archive Process
Archive and Purge Data Classification

E.1.1 Purge Process

Purging is the process of freeing up space in the database or of deleting obsolete data that is not required by the system. The purge process can be based on the age of the data or the type of data.

E.1.2 Archive Process

Archiving is the process of backing up the obsolete data that will be deleted during the purge process. During the archive process, data will be moved from the main transactional tables to the backup tables. By default the Oracle Adaptive Access Manager purge scripts will archive data that will be deleted during the purge process.

E.1.3 Archive and Purge Data Classification

Oracle Adaptive Access Manager has different sets of transactional tables that will be archived and purged. These sets are documented below. The tables in the transaction table sets are listed in "List of Tables and the Corresponding Archived Tables".

E.1.3.1 Device Fingerprinting

The device fingerprinting data is archived and purged based on the following criteria:

archive and purge the device fingerprinting logs that are older than a specified period first.
archive and purge user device maps that are not used after the data from the device fingerprinting logs is purged.
archive and purge the device history that is not used after the data from the device fingerprinting logs is purged.
archive and purge the device data that is not used after the data from the device fingerprinting logs is purged.

E.1.3.2 Transaction In-Session Based Data

The in-session transaction data is archived and purged based on the following criteria:

archive and purge the in-session transactional-based data that is older than a specified period first.
archive and purge transaction data that is not used in the transaction data after the transactions logs are purged for a specific time period.
archive and purge the entity, entity profile, user entity map and entity transaction map after the transactions logs are purged for a specific time period.

E.1.3.3 Auto-learning Profile Data

The Auto-learning and profile data is archived and purged based on the following criteria:

archive and purge the Workflow tables based on a specific time period.
- HOURS based Workflow tables will retain 3 days' worth of data.
- DAYS based Workflow tables will retain 32 days' worth of data.
- MONTHS based Workflow tables will retain 1 year's worth of data.
- YEARS based Workflow tables will retain 5-years' worth of data.
These values are hardcoded. The profile data value can be changed in the execution script for no of days.
archive and purge fingerprinting data with fingerprint type 11, 12, and no child records in the Workflow tables
```
vcrypt.fingerprint.type.enum.autolearning.auth=11
vcrypt.fingerprint.type.enum.autolearning.transaction=12
```
- 11 is the enum value for the Auto-learning AUTH type. Change these values in the script if another value was used during integration.
- 12 is enum value for the Auto-learning TRANSACTION type. Change these values in the script if another value was used during integration.
archive and purge profile related data that is 183 days old and profiles type 2 (Auto-learning Profile) from the Auto-learning profiles tables.

E.1.3.4 Rule Log Data

The rule log transaction data is archived and purged based on the following criteria:

archive and purge the rule log data that is 30 days old

E.2 Archive and Purge

E.2.1 Setting Up for Archive and Purge

The setup scripts are one-time scripts that are required to create objects for the archive and purge process. The setup scripts will create the archived tables and store procedure required to execute during the routine archive and purge process.

E.2.1.1 Setting Up for Archive and Purge for the Oracle Database

The required scripts to setup the archive and purge routines for the Oracle database are listed below. For more information on these scripts, refer to "Scripts for the Oracle Database"

To set up the archive and purge process for the Oracle database, follow the steps below:

Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package Oracle scripts to the script directory.
Login to the database using the Oracle Adaptive Access Manager schema
```
For example, sqlplus <OAAMADMIN>/<PASSWORD>
```
Run the create_purge_proc.sql script
```
SQL>@ create_purge_proc.sql
```

E.2.1.2 Setting Up for Archive and Purge for the SQL Server Database

The required scripts to setup the archive and purge routines for the SQL Server database are listed below. For more information on these scripts, refer to "Scripts for the SQL Server Database".

cr_vcrypt_purge_tables.sql
cr_sp_arch_purge_tracker_data.sql
cr_sp_arch_purge_txn_logs.sql
cr_sp_arch_purge_workflow_data.sql
cr_sp_arch_purge_profile_data.sql
cr_sp_arch_purge_rules_log.sql

To setup the archive and purge process for the SQL Server database, follow the steps below:

Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package SQL Server scripts to the script directory.
Login to the Oracle Adaptive Access Manager database using SQL Server Management Studio.
Open the script files, which are listed below, using File ->Open ->File. Then, navigate to the script directory.
- cr_vcrypt_purge_tables.sql
- cr_sp_arch_purge_tracker_data.sql
- cr_sp_arch_purge_txn_logs.sql
- cr_sp_arch_purge_workflow_data.sql
- cr_sp_arch_purge_profile_data.sql
- cr_sp_arch_purge_rules_log.sql
In the Query window, change the following line for every script:
```
USE [DATABASE_NAME] to USE < your OAAM Database>
```
Execute the scripts.
In the message window of SQL Server Management Studio, save the results to a file.

E.2.2 Performing Archive and Purge

The execution of the archive and purge scripts is described below. Prior to starting the archive and purge process, go through the checklist, which is documented below, to ensure that the requirements for archive and purge are met.

Setup of the archive and purge scripts. Refer to "Setting Up for Archive and Purge".
Enough space is available on the database server to store the archived data, if archive is enabled for the purge.
Archive and purge could be resource (like CPU) intensive. Oracle recommends running these during off peak load hours.

E.2.2.1 Oracle Databases

The required scripts to execute archive and purge routines for the Oracle database are listed below. For more information on these scripts, refer to "Scripts to Execute Archive and Purge".

Archive and purge periods are set based on the business requirement specified for retention periods.

By default, the archive and purge scripts/routines have the following two parameters set:

p_days1 =no of days for data retention
p_archived= archived flag

To change these values per the business requirement, modify the following scripts:

exec_sp_purge_tracker_data.sql
exec_sp_purge_txn_log.sql
exec_sp_purge_workflow_data.sql
exec_sp_purge_profile_data.sql
exec_sp_purge_rule_log.sql

E.2.2.1.1 Manual Execution

To execute the scripts to archive and purge, follow the steps below:

Create the script directory, oaam_purge_script
Unzip the Oracle Adaptive Access Manager archive and purge package Oracle scripts to the script directory.
Login to the database using the Oracle Adaptive Access Manager schema

For example,
```
sqlplus <OAAMADMIN>/<PASSWORD>
```

Run the purging execution scripts:

SQL>@ exec_sp_purge_tracker_data.sql
SQL>@ exec_sp_purge_txn_log.sql
SQL>@ exec_sp_purge_workflow_data.sql
SQL>@ exec_sp_purge_profile_data.sql 
SQL>@ exec_sp_purge_rule_log.sql

E.2.2.1.2 Automatic Scheduling

Archive and purge jobs should be part of a routine schedule. These jobs can be scheduled using database jobs or OS-based scheduling utilities (crontab, at) or scheduler software (autosys, appworx).

It is recommended that these scripts are scheduled to run on regular intervals and only during off-peak hours.

E.2.2.2 SQL Server Database

The required scripts to execute archive and purge routines are listed below. For more information about these scripts, refer to "Scripts to Execute Archive and Purge".

Archive and purge periods are set based on the business requirement specified for retention periods.

By default, the required scripts for archive and purge routines have the following two parameters set:

p_days1 =no of days for data retention
p_archived= Archived flag

To change these values per the business requirement, modify the following scripts:

exec_sp_purge_tracker_data.sql
exec_sp_purge_txn_log.sql
exec_sp_purge_workflow_data.sql
exec_sp_purge_profile_data.sql
exec_sp_purge_rule_log.sql

E.2.2.2.1 Manual Execution

To execute the scripts to archive and purge, follow the steps below:

Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager archive and purge package SQL Server scripts to the script directory.
Login to the Oracle Adaptive Access Manager database using SQL Server Management Studio.

Open the script files listed below using File ->Open ->File. Then, navigate to the script directory.

exec_sp_purge_tracker_data.sql 
exec_sp_purge_txn_log.sql
exec_sp_purge_workflow_data.sql
exec_sp_purge_profile_data.sql   
exec_sp_purge_rule_log.sql

In the Query window, change the following line for every script:
```
USE [DATABASE_NAME] to USE < your OAAM Database>
```
Execute the scripts.
In the message window of the SQL Server Management Studio, save the results to a file.

E.2.2.2.2 Automatic Scheduling

Archive and purge jobs should be part of a routine schedule. These jobs can be scheduled using database jobs or OS-based scheduling utilities (crontab, at) or scheduler software (autosys, appworx).

It is recommended that these scripts are scheduled to run on regular intervals and only during off-peak hours.

E.3 Validating Archive and Purge

To determine if the archive and purge was successful, check the log files (for example scheduler log, script output log, and others) for any errors. When the archive and purge process has completed, users can also query the transactional log and its related purged tables to validate that the data was archived and purged.

E.4 Restoring Archived Data

As recommended, users should take an export backup of archived tables after the archive process has completed in case they should need to perform troubleshooting in the future.

When performing a restoration, the user should restore the desired date's data to a temporary table using Oracle's database Import feature.

Please contact Oracle Support if any data restoration is required.

E.5 List of Tables and the Corresponding Archived Tables

E.5.1 Device Fingerprint Tables and Corresponding Archived Tables

Device Fingerprint Transaction Tables	Corresponding Archived Tables
VCRYPT_TRACKER_NODE	VCRYPT_TRACKER_NODE_PURGE
VCRYPT_TRACKER_NODE_HISTORY	VCRYPT_TRACKER_NODE_HISTORY_PURGE
VCRYPT_TRACKER_USERNODE_LOGS	VCRYPT_TRACKER_USERNODE_LOGS_PURGE
VT_DYN_ACT_EXEC_LOG	VT_DYN_ACT_EXEC_LOG_PURGE
VT_SESSION_ACTION_MAP	VT_SESSION_ACTION_MAP_PURGE
VT_USER_DEVICE_MAP	VT_USER_DEVICE_MAP_PURGE

E.5.2 Auto-learning Transactional Tables and Corresponding Archive Tables

Auto-learning Transactional Tables	Corresponding Archived Tables
VT_WF_DAYS	VT_WF_DAYS_PURGE
VT_WF_HOURS	VT_WF_HOURS_PURGE
VT_WF_MONTHS	VT_WF_MONTHS_PURGE
VT_WF_YEARS	VT_WF_YEARS_PURGE
V_FPRINTS	V_FPRINTS_PURGE
V_FP_MAP	V_FP_MAP_PURGE
VT_USER_PROFILE	VT_USER_PROFILE_PURGE
VT_DEVICE_PROFILE	VT_DEVICE_PROFILE_PURGE
VT_IP_PROFILE	VT_IP_PROFILE_PURGE
VT_STATE_PROFILE	VT_STATE_PROFILE_PURGE
VT_CITY_PROFILE	VT_CITY_PROFILE_PURGE
VT_COUNTRY_PROFILE	VT_COUNTRY_PROFILE_PURGE

E.5.3 Transaction Tables and Corresponding Archived Tables

Transaction Tables	Corresponding Archived Tables
VT_ENTITY_ONE	VT_ENTITY_ONE_PURGE
VT_ENTITY_ONE_PROFILE	VT_ENTITY_ONE_PROFILE_PURGE
VT_ENT_TRX_MAP	VT_ENT_TRX_MAP_PURGE
VT_TRX_DATA	VT_TRX_DATA_PURGE
VT_TRX_LOGS	VT_TRX_LOGS_PURGE

E.5.4 Rule Logs Tables and Corresponding Archived Tables

Rule Log Tables	Corresponding Archived Tables
VR_POLICYSET_LOGS	VR_POLICYSET_LOGS_PURGE
VR_RULE_LOGS	VR_RULE_LOGS_PURGE
VR_MODEL_LOGS	VR_MODEL_LOGS_PURGE
VR_POLICY_LOGS	VR_POLICY_LOGS_PURGE

E.6 Scripts to Set Up Archive and Purge

E.6.1 Scripts for the Oracle Database

E.6.1.1 create_purge_proc.sql

The create_purge_proc.sql script creates the tables (Listed in "List of Tables and the Corresponding Archived Tables") and the following stored procedures to archive and purge data from the transaction tables:

SP_RULE_ PROC
SP_MODEL_ PROC
SP_POLICYSET_ PROC
SP_POLICY_ PROC
SP_NODE_HISTORY_ PROC
SP_NODE_PROC
SP_USER_NODE_PROC
SP_USER_DVC_PROC
SP_SESS_ACT_MAP_PROC
SP_WF_YEARS_PROC
SP_WF_MONTHS_PROC
SP_WF_DAYS_PROC
SP_WF_HOURS_PROC
SP_V_FPRINTS_PROC
SP_V_FP_MAP_PROC
SP_VT_TRX_LOGS_PROC
SP_VT_TRX_DATA_PROC
SP_VT_ENT_TRX_MAP_PROC
SP_VT_ENT_ONE_PRF_PROC
SP_VT_ENT_ONE_PROC
SP_VT_ENT_ONE_MAP_PROC
SP_VT_USER_PRF_PROC
SP_VT_DEVICE_PRF_PROC
SP_VT_IP_PRF_PROC
SP_VT_BASE_IP_PRF_PROC
SP_VT_CITY_PRF_PROC
SP_VT_COUNTRY_PRF_PROC
SP_VT_STATE_PRF_PROC

E.6.2 Scripts for the SQL Server Database

E.6.2.1 cr_vcrypt_purge_tables.sql

The cr_vcrypt_purge_tables.sql script creates the tables ("List of Tables and the Corresponding Archived Tables") to archive and purge data from the transaction tables.

E.6.2.2 cr_sp_arch_purge_tracker_data.sql

The cr_vcrypt_purge_tables.sql script creates the stored procedure sp_archive_purge_tracker_data to archive and purge data from device fingerprinting transaction tables.

E.6.2.3 cr_sp_arch_purge_txn_logs.sql

The cr_vcrypt_purge_tables.sql script creates the stored procedure sp_archive_purge_txn_logs_data to archive and purge data from in-session transaction tables.

E.6.2.4 cr_sp_arch_purge_workflow_data.sql

The cr_vcrypt_purge_tables.sql script creates the stored procedure sp_archive_purge_wf_data to archive and purge data from work flow transaction tables.

E.6.2.5 cr_sp_arch_purge_profile_data.sql

The cr_vcrypt_purge_tables.sql script stored procedure sp_archive_purge_profile_data to archive and purge data from Auto-learning profile transaction tables.

E.6.2.6 cr_sp_arch_purge_rules_log.sql

The cr_vcrypt_purge_tables.sql script creates the stored procedure sp_archive_purge_rule_log to archive and purge data from rule logs transaction tables.

E.7 Scripts to Execute Archive and Purge

E.7.1 exec_sp_purge_tracker_data.sql

This script calls stored procedures to archive and purge data from device fingerprinting tables. By running this script, the following tables will be archived and purged:

VCRYPT_TRACKER_NODE
VCRYPT_TRACKER_NODE_HISTORY
VCRYPT_TRACKER_USERNODE_LOGS
VT_USER_DEVICE_MAP
VT_DYN_ACT_EXEC_LOG
VT_SESSION_ACTION_MAP

E.7.2 exec_sp_purge_txn_log.sql

This script calls stored procedures to archive and purge data from in-session transaction tables. By running this script, the following tables will be archived and purged:

VT_ENTITY_ONE
VT_ENTITY_ONE_PROFILE
VT_ENT_TRX_MAP
VT_TRX_DATA
VT_TRX_LOGS
VT_USER_ENTITY1_MAP

E.7.3 exec_sp_purge_workflow_data.sql

This script calls stored procedures to archive and purge data from the Workflow Auto-learning tables. By running this script, the following tables will be archived and purged:

VT_WF_DAYS
VT_WF_HOURS
VT_WF_MONTHS
VT_WF_YEARS
V_FPRINTS
V_FP_MAP

E.7.4 exec_sp_purge_profile_data.sql

This script calls stored procedures to archive and purge data from the Auto-learning profile tables. By running this script, the following tables will be archived and purged:

VT_BASE_IP_PROFILE
VT _IP_PROFILE
VT_DEVICE_PROFILE
VT_COUNTRY_PROFILE
VT_CITY_PROFILE
VT_STATE_PROFILE
VT_USER_PROFILE

E.7.5 exec_sp_purge_rule_log.sql

This script calls stored procedures to archive and purge data from the Rules Engine logging tables. By running this script, the following tables will be archived and purged:

VR_POLICYSET_LOGS
VR_RULE_LOGS
VR_MODEL_LOGS
VR_POLICY_LOGS

E.8 Purging Guidelines

E.8.1 When to Perform Archive and Purge

In order to maintain data consistency and optimal system performance, users should perform the purging/archiving process routinely.

E.8.2 Minimum Data Retention Policy

Based on the Oracle Adaptive Access Manager system requirement, the minimum data retention policy for various OLTP tables are shown below, but users should determine the data retention period based on their business requirements. For more information, review the information in this chapter.

E.8.2.1 Device Fingerprinting Data

Minimum of 6 months or 180 days

E.8.2.2 In-session Transactional Tables

Minimum of 6 months or 180 days

E.8.2.3 Auto-learning and Workflow Tables

HOURS based Workflow tables will retain 3 days' worth of data.
DAYS based Workflow tables will retain 32 days' worth of data.
MONTHS based Workflow tables will retain 1 year's worth of data.
YEARS based Workflow tables will retain 5-years' worth of data.

E.8.2.4 Rule Log Data

The script will archive and purge all rule log data that is 30 days older (This value should be set based on the customer care requirement. If the reporting database is used, then, rule logging data retention should be less than 30 days.

E.8.3 Special Requirements

Set up of the archive and purge scripts.
Ensure that enough space is available on the database server to store the archived data, if archival is enabled during purging.
Archival and purging could be resource (like CPU) intensive. Oracle recommends running these during off peak load hours.

E.8.4 Purging Validation

To determine if the archive and purge had been successful, check the log files (for example scheduler log, script output log, and others) for any errors. Transactional log tables can also be queried to validate the data for the purging process.