Skip Headers
Oracle® Fusion Middleware Administrator's Guide for Oracle Adaptive Access Manager
11g Release 1 (11.1.1)

Part Number E14568-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

I Setting Up Archive and Purge Procedures

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.

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.

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

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.

This chapter contains the following sections:

I.1 Overview

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

"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:

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)

  2. Determine whether to purge or archive.

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

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

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

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

The next sections describe the above in detail.

I.2 Setting Up the Scripts in Database

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

  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.

Note:

The purging/archiving scripts need the CREATE Any privilege to create and execute purge related stored procedures.

Since the purging/archiving scripts use custom rebuild index stored procedures for a given table, this stored procedure requires CREATE Any Table and Create Any index privileges granted to the Oracle Adaptive Access Manager schema. If these privileges are not granted, the rebuild_oaam_index stored procedure will not work.

These privileges must be granted to set up and execute the Oracle Adaptive Access Manager purging/archiving routines and must be revoked after the purging/archiving process is completed.

I.3 Best Practices/Guidelines for Running Purge Scripts

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

I.4 Running the Scripts

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

  1. Set the p_days1 and p_archived parameters. All the scripts have these two parameters that you can set. Table I-1 describes these parameters.

    Table I-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 I-2 lists the types of data and corresponding script name.

    Table I-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.

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.

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.

I.5 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.

I.6 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.

Contact Oracle Support Services if any data restoration is required.

I.7 Running Partition Maintenance Scripts

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

I.7.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.

I.7.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.

I.8 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.

I.8.1 Login and Device Data

Table I-3 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


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.

I.8.2 Rules and Policy Log Data

Table I-4 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


I.8.3 Transactions and Entities Data

Table I-5 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


I.8.4 Autolearning Data

Table I-6 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


I.8.5 Profile Data

Table I-7 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


I.8.6 Cases-Related Data

Table I-8 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


I.8.7 Monitor Data

Table I-9 Monitor Data Tables

Transaction Table Corresponding Archived Table

V_MONITOR_DATA

V_MONITOR_DATA_PURGE


I.9 Archive and Purge Criteria

Archive and purge criteria is presented in the following table.

Table I-10 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

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.

This retention value should be set based on the customer care requirements. If the reporting database is used, then, rule logging data retention should be less than 30 days.

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.


I.9.1 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.

I.9.1.1 Device Fingerprinting Data

Minimum of 6 months or 180 days

I.9.1.2 In-session Transactional Tables

Minimum of 6 months or 180 days

I.9.1.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.

I.9.1.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.

I.10 List of Related Stored Procedures

The archive and purge setup scripts for the Oracle Database are listed in this subsection.

I.10.1 create_purge_proc.sql

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

I.10.2 create_case_purge_proc.sql

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

I.10.3 create_v_monitor_purge_proc.sql

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