Oracle® Adaptive Access Manager Installation and Configuration Guide Release 10g (10.1.4.5) Part Number E12050-03 |
|
|
View PDF |
This appendix contains information about the archive and purge process.
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:
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.
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.
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".
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.
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.
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.
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.
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
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.
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.
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
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
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.
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
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.
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.
Please contact Oracle Support if any data restoration is required.
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 |
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 |
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 |
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
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.
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.
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.
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.
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.
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
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
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
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
In order to maintain data consistency and optimal system performance, users should perform the purging/archiving process routinely.
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.
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.