This chapter describes how to archive and purge data from the OAAM database using SQL scripts.
This appendix contains the following sections:
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-1Tables Without Data Growth Issues Not Purged
"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:
Determine the retention period (usually 180 days; that is 6 months)
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."
Determine whether to purge or archive.
Figure D-3Determining to purge or archive
Deploy the purge related stored procedures into the OAAM database. This is a one-time job.
Determine what types of data must be archived and purged.
Schedule the related scripts to run on regular intervals or manually run the scripts when required.
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.
To archive and purge OAAM data, you must set up the one-time scripts.
Follow these steps to set up the scripts if you have installed OAAM database in a non-EBR (edition-based redefinition) schema:
Create a scripts directory oaam_purge_script
.
Unzip the scripts archive IDM_ORACLE_HOME
/oaam/oaam_db_scripts/oaam_db_purging_scripts.zip
to the scripts directory.
Log in to the database using the sys
or sysdba
account.
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;
Now connect to the OAAM schema using the OAAM user name and password. For example:
sqlplus oaam_db_user_name/oaam_db_password
Run the create_purge_proc.sql
script
SQL> @oracle_db/create_purge_proc.sql
When running the create_purge_proc.sql
script, the script asks for the following inputs:
Enter value for oaam_data_tbs: Enter value for oaam_indx_tbs:
Enter the value for oaam_data_tbs
: SchemaPrefix
_BRSADATA
Enter the value for oaam_indx_tbs
: SchemaPrefix
_BRSAINDX
The values can be found by executing the following query logged in as the OAAM schema user:
select tablespace_name from user_tablespaces;
Validate the stored procedures to make sure they are valid and without errors.
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:
Log in to database using the sys
or sysdba
account.
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
Log in to the OAAM Schema using the OAAM database user name and password.
For example:
sqlplus oaam_db_user_name/oaam_db_password
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
When running the create_purge_proc.sql
script, the script asks for the following inputs:
Enter value for oaam_data_tbs: Enter value for oaam_indx_tbs:
Enter the value for oaam_data_tbs
: SchemaPrefix
_BRSADATA
Enter the value for oaam_indx_tbs
: SchemaPrefix
_BRSAINDX
The values can be found by executing the following query logged in as the OAAM schema user:
select tablespace_name from user_tablespaces;
Validate the stored procedures to make sure they are valid and without errors.
To run the archive and purge scripts, proceed as follows:
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. |
Select the scripts to run based on the data that must 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 |
Log in to the OAAM database using OAAM database user name and password and execute the selected scripts.
Check the corresponding log file and see if there are any errors or warnings.
If archiving is selected, then make sure to take a backup of the archive tables so that data can be restored if needed.
Table D-3 summarizes the archive and purge criteria for data in the OAAM database.
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:
Note: The |
Transaction In-Session Based Data |
The purge process archives and purges in-session transaction data based on the following criteria:
|
Autolearning Profile Data |
Archive and purge the following tables based on a specific time period.
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
|
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:
|
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
|
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. |
In case the partitioned version of OAAM database is used, there are related scripts to drop the partitions.
To drop weekly partitions, proceed as follows:
Run this script at the end of every two weeks starting from your database creation date.
To change the default retention period, open the script Drop_Weekly_Partition_tables.sql
and set the retention period in days. The default is set to 15
days (two weeks).
Log in to the OAAM database using the OAAM database user name and password.
Execute the script Drop_Weekly_Partition_tables.sql
.
To drop monthly partitions, proceed as follows:
Run this script at the end of each month to drop partitions that are older than the sixth month.
To change the default retention period, open the script Drop_Monthly_Partition_tables.sql
and set the retention period in days. The default is set to 180
days (6 months).
Log in to the OAAM database using the OAAM database user name and password.
Execute the script Drop_Monthly_Partition_tables.sql
.
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.
|
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. |
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 used by the standard 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.
Details of data that is purged and the corresponding archived tables are presented in the following sections.
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 |
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 |
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 |
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.