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:
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.
Not all the tables are purged since many of them do not have data 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:
Determine the retention period (usually 180 days; that is 6 months)
Determine whether 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 have to 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.
The next sections describe the above in detail.
To archive and purge OAAM data, you must set up the one-time scripts.
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
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.
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.
To run the archive and purge scripts, proceed as follows:
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. |
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 |
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.
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.
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.
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.
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.
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. 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. 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.
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 |
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.
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 |
| 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 |
Archive and purge criteria is presented in the following table.
Table I-10 Archive and Purge Criteria
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.
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.
The archive and purge setup scripts for the Oracle Database are listed in this subsection.
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
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