D Setting Up Archive and Purge Procedures

This chapter describes how to archive and purge data from the OAAM database using SQL scripts.

This chapter includes the following sections:

D.1 Overview

The archive and purge process allows the releasing of data that is not required anymore for rules evaluation or fraud investigation.

  • Archiving is the process of moving data from main transactional tables to the archive tables.

  • Purging is the process of deleting obsolete data that is not required by the system from tables because of data growth issues.

    Not all the tables are purged since many of them do not have data growth issues.

Figure D-1 Tables Without Data Growth Issues Not Purged

This diagram shows data in tables without growth issues.

"Purging data" is different from "backing up data". A data backup is for the recovery of data if loss occurs; purges are for keeping the runtime tables free of old data. Regardless, to protect your data, database backups should be performed on a regular basis with the help of a database administrator.

The following data can be archived or purged using the scripts provided in the archive IDM_ORACLE_HOME/oaam/oaam_db_scripts/oaam_db_purging_scripts.zip:

  • Login and devices data

  • Rule Logs data

  • Auto Learning data

  • Transactions and Entities data

  • Profile data

Archive and purge criteria is based on the create/update timestamp of the records. This is specified using the retention period described using number of days.

The following is the overview of the archive and purge process:

  1. Determine the retention period (usually 180 days; that is 6 months)

    Figure D-2 Retention

    This diagram illustrates retention policies.

  2. If the transactions feature is used and you want to specify different retention period based on the transaction type or entity, refer to Section 20.6, "Setting Targeted Purging for Transaction Data Per Transaction Definition," and Section 19.4, "Setting Up Targeted Purging for Entity Data."

  3. Determine whether to purge or archive.

    Figure D-3 Determining to purge or archive

    This diagram shows whether to purge or archive.

  4. Deploy the purge related stored procedures into the OAAM database. This is a one-time job.

  5. Determine what types of data have to be archived and purged.

  6. Schedule the related scripts to run on regular intervals or manually run the scripts when required.

  7. Check for entries where the LOG_TYPE is 99 in the database table V_SYS_LOGS.

Note:

Rules may behave differently if the data that they look for is purged. For example, a rule is looking for 6 month data and you are purging data that is 9 days or older.

The next sections describe the above in detail.

D.2 Setting Up the Scripts in Database

To archive and purge OAAM data, you must set up the one-time scripts.

D.2.1 Non-EBR Schema

Follow these steps to set up the scripts if you have installed OAAM database in a non-EBR (edition-based redefinition) schema:

  1. Create a scripts directory oaam_purge_script.

  2. Unzip the scripts archive IDM_ORACLE_HOME/oaam/oaam_db_scripts/oaam_db_purging_scripts.zip to the scripts directory.

  3. Log in to the database using the sys or sysdba account.

  4. Grant the following privileges to the OAAM schema so that stored procedures can be created and executed:

    GRANT create any procedure TO <schema_name>;

    GRANT create any table TO <schema_name>;

    GRANT create any index TO <schema name>;

    GRANT create procedure TO <schema_name>;

    GRANT execute any procedure TO <schema_name>;

  5. Now connect to the OAAM schema using the OAAM user name and password. For example:

    sqlplus <oaam_db_user_name>/<oaam_db_password>

  6. Run the create_purge_proc.sql script

    SQL> @oracle_db/create_purge_proc.sql

  7. Validate the stored procedures to make sure they are valid and without errors.

D.2.2 EBR Schema

Edition-based redefinition (EBR) enables you to upgrade the database component of OAAM while it is in use, thereby minimizing or eliminating down time.

To set up the archive and purge process that supports EBR, proceed as follows:

  1. Log in to database using the sys or sysdba account.

  2. Grant the following privileges to the OAAM schema:

    GRANT create any procedure TO <SCHEMA_NAME>

    GRANT create any table TO <SCHEMA_NAME>

    GRANT create any index TO <SCHEMA_NAME>

    GRANT create procedure TO <SCHEMA_NAME>

    GRANT create view TO <SCHEMA_NAME>

    GRANT execute any procedure TO <SCHEMA_NAME>

    ALTER user <SCHEMA_NAME> enable editions

    GRANT use on EDITION ORA$BASE to <SCHEMA_NAME>

    Exit

  3. Log in to the OAAM Schema using the OAAM database user name and password.

    For example, sqlplus <oaam_db_user_name>/<oaam_db_password>

  4. Log in as the <schema_name> user and run the create_purge_proc.sql script.

    @ oracle_db/oracle_ebr/create_purge_proc.sql <EDITION_VERSION>

    For example, @ /oracle_db/oracle_ebr/create_purge_proc.sql ORA$BASE

  5. Validate the stored procedures to make sure they are valid and without errors.

D.3 Running the Archive and Purge Scripts

To run the archive and purge scripts, proceed as follows:

  1. Set the p_days1 and p_archived parameters using a text editor when you run the scripts. All the scripts have these two parameters that you can set. Table D-1 describes these parameters.

    Table D-1 Archive and Purge Routine Parameters

    Variable Name Default Value Description

    p_days1

    180

    Retention period in days. Data older than this many number of days will be archived or purged.

    p_archived

    Y

    Y or N for Yes and No respectively. If "Y" then data will be archived (in archive tables), otherwise data will be purged based on the retention period.

  2. Select the scripts to run based on the data that has to be archived or purged. Table D-2 lists the types of data and corresponding script name.

    Table D-2 Archive and Purge Scripts Based on Types of Data

    Type of Data Corresponding Script

    Login, Device Data

    exec_sp_purge_tracker_data.sql

    Rules, Policy Log Data

    exec_sp_purge_rule_log.sql

    Transactions, Entities Data

    exec_sp_purge_txn_log.sql

    Autolearning Data

    exec_sp_purge_workflow_data.sql

    Profile Data

    exec_sp_purge_profile_data.sql

    Cases related Data

    exec_sp_purge_case_data.sql

    Monitor Data

    exec_v_monitor_purge_proc.sql

  3. Log in to the OAAM database using OAAM database user name and password and execute the selected scripts.

  4. Check the corresponding log file and see if there are any errors or warnings.

  5. If archiving is selected, then make sure to take a backup of the archive tables so that data can be restored if needed.

Archive and purge criteria is presented in the following table.

Table D-3 Archive and Purge Criteria

Type of Data Purge Criteria

Device Fingerprinting Data

The purge process archives and purges device fingerprinting data based on the following criteria:

  • Device fingerprinting logs that are older than a specified period first.

  • User device maps that are not used after the data from the device fingerprinting logs

  • Device history that is not used after the data from the device fingerprinting logs

  • Device data that is not used after the data from the device fingerprinting logs

Note: The VT_SESSION_ACTION_MAP table is not purged using the partition drop maintenance script. This table stores the device fingerprinting session information; therefore the purging of this table is performed using the manual purge stored procedure (SP_SESS_ACT_MAP_PROC) which is called by the exec_sp_purge_tracker_data.sql script.

Transaction In-Session Based Data

The purge process archives and purges in-session transaction data based on the following criteria:

  • In-session transactional-based data that is older than a specified period first

  • Transaction data that is not used in the transaction data after the transactions logs are purged for a specific time period

  • Entity, entity profile, user entity map and entity transaction map after the transactions logs are purged for a specific time period

Autolearning Profile Data

Archive and purge the following tables based on a specific time period.

  • HOURS based tables will retain 3 days worth of data.

  • DAYS based tables will retain 32 days worth of data.

  • MONTHS based tables will retain 1 years worth of data.

  • YEARS based tables will retain 5 years worth of data.

Archive and purge fingerprint data for AUTH and TRANSACTION fingerprint types. Fingerprint data to be purged in this way is in fingerprint table and fp_map table. HOURS, DAYS, MONTHS, and YEARS tables described above also have references to fingerprint. Before purging fingerprint data, make sure that archiving and purging of HOURS, DAYS, MONTHS, and YEARS tables is performed.

vcrypt.fingerprint.type.enum.autolearning.auth=11
vcrypt.fingerprint.type.enum.autolearning.transaction=12

11 is the enumeration value for the autolearning AUTH type. Change these values in the script if another value was used during integration.

12 is enumeration value for the autolearning TRANSACTION type. Change these values in the script if another value was used during integration.

Rule Log Data

The rule log transaction data that is 30 days old is archived and purged.

Targeted Purging of Entity and Transaction Data

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

  • Number of days of retention.

    Purges data older than the number of days of retention based on the update time. If not configured in the entity or transaction definition, then the data will not be purged or archived

    Default value: 180

  • Purge data

    If you want to purge entity data, deselect the option, "Do not purge any entity data." If you do not want to purge entity data, select "Do not purge any entity data."

    If you want to purge transaction data, deselect the option, "Do not purge any transaction data." If you do not want to purge transaction data, select "Do not purge any transaction data."

    You cannot selectively choose to only archive the data since archiving is part of the purge process.

    Note: Entity definition and transaction definitions are retained even though the data is being purged.

    The purging mechanism is hierarchical. Data is purged from transaction down to entity and then related entities.

    Group members are purged if the member "entity" in the group is being purged.

Question/Problem: Does running the purge process remove registration of "safe" device?

Answer/Solution: There is no special treatment for safe device. Active devices will not be purged.

6 Months device purge policy

  • Device is safe not used in last 6 months - candidate for purge

  • Device is not safe and not used in last 6 months - candidate for purge

  • Device is safe and used within last 6 months - not candidate for purge. It will not be purged even if device is created more than 6 months back.

  • Device is not safe and used within last 6 months - not candidate for purge. It will not be purged even if device is created more than 6 months back.

Registration of Safe Devices

The OAAM purge/archive process does not remove registration of "safe" devices and cause users to have to re-register safe devices unless the device has not been used for six months.

Purge scripts unregister the devices when the devices are purged (as part of tracker_purge_job.sql). As part of tracker_purge_job.sql, all the unused devices (that are not referred by any record in VCRYPT_TRACKER_USERNODE_LOGS) are purged and also the related records in VT_USER_DEVICE_MAP are purged.

D.4 Running Partition Maintenance Scripts

In case the partitioned version of OAAM database is used, there are related scripts to drop the partitions.

D.4.1 Dropping Weekly Partitions

To drop weekly partitions, proceed as follows:

  1. Run this script at the end of every two weeks starting from your database creation date.

  2. To change the default retention period, open the script Drop_Weekly_Partition_tables.sql and set the retention period in days. Default is set to 15 days (two weeks).

  3. Log in to the OAAM database using the OAAM database user name and password.

  4. Execute the script Drop_Weekly_Partition_tables.sql.

D.4.2 Dropping Monthly Partitions

To drop monthly partitions, proceed as follows:

  1. Run this script at the end of each month to drop partitions that are older than the sixth month.

  2. To change the default retention period, open the script Drop_Monthly_Partition_tables.sql and set the retention period in days. Default is set to 180 days (6 months).

  3. Log in to the OAAM database using the OAAM database user name and password.

  4. Execute the script Drop_Monthly_Partition_tables.sql.

D.5 Minimum Data Retention Policy for OLTP (Online Transaction Processing) Tables

Based on the Oracle Adaptive Access Manager system requirement, the minimum data retention policy for various OLTP (online transaction processing) tables are shown below, but users should determine the data retention period based on their business requirements.

Table D-4 Minimum Data Retention Policies

Data Retention Policy

Device Fingerprinting Data

Minimum of 6 months or 180 days

In-Session Transactional Tables

Minimum of 6 months or 180 days

Transaction and Entity Data

Data that has not been updated in the last 180 days is purged by default.

Auto-learning and Workflow Tables

Retention for hours, days, months, and years is listed below.

  • 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 years worth of data.

  • YEARS based Workflow tables will retain 5 years worth of data.

Rule Log Data

The archive and purge 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.

D.6 Best Practices/Guidelines for Running Purge Scripts

Best/practices guidelines for running purge scripts are as follows:

  • Determine the retention period based on the business requirements and rules and policies used

  • Perform regular purge/archive

  • Make sure replication is not enabled during the window when these scripts are run

  • Run these during off peak load hours which Oracle recommends you do. Archive and purge could be resource (like CPU) intensive.

  • If archiving is required, make sure there is enough disk space available on the database server since the data would be moved to archive tables instead of simply purging. Archival space should be equal to or greater than the current table's storage.

  • Plan your purging strategy since purging requires a significant amount of time if there are millions of rows that need to be deleted or copied from the database.

  • In a multi-data center, it is recommended that you run purges at low data flow since the data in tables is replicated. You should consult your database administrator if you have multidimensional clustering (MDC) set up and require purging.

  • Oracle recommends that custom purging scripts only include the tables utilized by the out of the box purging scripts provided. The alterations to the provided purge scripts can include parameterization for user ID. Such alterations should be thoroughly tested before being used in production to ensure they function as expected.

D.7 Details of Data that is Archived and Purged

Details of data that is purged and the corresponding archived tables are presented in the following sections.

D.7.1 Login and Device Data

Table D-5 Login and Device

Login and Device 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

VCRYPT_ALERT

VCRYPT_ALERT_PURGE

VCRYPT_USERS_HIST

VCRYPT_USERS_HIST_PURGE

V_USER_QA_HIST

V_USER_QA_HIST_PURGE

D.7.2 Rules and Policy Log Data

Table D-6 Rules and Policy Log Data Tables

Rules, Policy 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

D.7.3 Transactions and Entities Data

Table D-7 Transactions and Entity Data Tables

Transaction Tables Corresponding Archived Tables

VT_ENTITY_ONE

VT_ENTITY_ONE_PURGE

VT_ENTITY_ONE_PROFILE

VT_ENTITY_ONE_PROFILE_PURGE

VT_USER_ENTITY1_MAP

VT_USER_ENTITY1_MAP_PURGE

VT_ENT_TRX_MAP

VT_ENT_TRX_MAP_PURGE

VT_TRX_DATA

VT_TRX_DATA_PURGE

VT_TRX_LOGS

VT_TRX_LOGS_PURGE

D.7.4 Autolearning Data

Table D-8 Autolearning Data Tables

Autolearning 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

D.7.5 Profile Data

Table D-9 Profile Data Tables

Transactional Tables Corresponding Archived Tables

VT_USER_PROFILE

VT_USER_PROFILE_PURGE

VT_DEVICE_PROFILE

VT_DEVICE_PROFILE_PURGE

VT_BASE_IP_PROFILE

VT_BASE_IP_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

D.7.6 Cases-Related Data

Table D-10 Case-Related Data Tables

Transaction Tables Corresponding Archived Tables

V_CASE

V_CASE_PURGE

V_CASE_HIST

V_CASE_HIST _PURGE

V_ACTION_LOG_SESS_MAP

V_ACTION_LOG_SESS_MAP_PURGE

V_ACTION_LOG_SESS

V_ACTION_LOG_SESS

V_CASE_MAP

V_CASE_MAP_PURGE

V_CASE_MAP_HIST

V_CASE_MAP_HIST_PURGE

D.7.7 Monitor Data

Table D-11 Monitor Data Tables

Transaction Table Corresponding Archived Table

V_MONITOR_DATA

V_MONITOR_DATA_PURGE

D.8 List of Related Stored Procedures

The create_purge_proc.sql script creates the 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_DY_ACT_EX_LOG_PRO

  • 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

  • SP_ARCHIVE_PURGE_VCRYPT_ALERT

  • SP_ARCHPURGE_VCRYPTUSERSHIST

  • SP_ARCH_PURGE_V_USER_QA_HIST

The create_case_purge_proc.sql script creates the following stored procedures to archive and purge data from the transaction tables:

  • SP_V_CASE_PROC

  • SP_V_CASE_HIST_PROC

  • SP_V_CASE_MAP_PROC

  • SP_V_CASE_MAP_HIST_PROC

  • SP_V_ACTION_LOG_SESS_MAP_PROC

  • SP_V_ACTION_LOG_SESS_PROC

The create_v_monitor_purge_proc.sql script creates SP_V_MON_DATA_PURGE_PROC to archive and purge data from the transaction table.