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

Part Number E14568-06
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
View PDF

I Setting Up Archive and Purge Procedures

This chapter presents the concepts, prerequisites, policies, and post-process procedures in archiving and purging Oracle Adaptive Access Manager-related data.

The Oracle Adaptive Access Manager-related purging scripts are in the oaam_db_purging_scripts.zip file located under IDM_ORACLE_HOME/oaam/oaam_db_scripts.

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

I.2 Archive Process

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.

I.3 Database Archive and Purge

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.

I.3.1 Archive and Purge Data Classification

Oracle Adaptive Access Manager has different sets of transactional tables that will be archived and purged. These sets are summarized in this subsection. The tables in the transaction table sets are listed in Section I.3.7, "Archive and Purge Details".

I.3.1.1 Device Fingerprinting

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.

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.3.1.2 Transaction In-Session Based Data

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.

I.3.1.3 Autolearning Profile Data

The autolearning 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 hard-coded. 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 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.

  • archive and purge profile related data that is 183 days old and profiles type 2 (Autolearning Profile) from the autolearning profiles tables.

I.3.1.4 Rule Log Data

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

  • archive and purge the rule log data that is 30 days old

I.3.2 Archive and Purge Process

The sections following provide procedures for the archive and purge process.

I.3.2.1 Archive and Purge Process - Special Recommendations for Schemas with Partitioned Objects

This subsection provides special recommendations for schemas with partitioned objects.

I.3.2.1.1 Schema with Partitioned Objects (Oracle Databases Only) Without a Separate Reporting Database

If you are using an Oracle Adaptive Access Manager schema with the partition option enabled and do not have a separate reporting and administrative environment, perform only manual purging, as described in this document. Partition drop scripts are part of the partition base package. These scripts are not shipped with the purging scripts.

Follow these steps:

  1. Set up archive and purge routines.

  2. Schedule archive and purge routines.

I.3.2.1.2 Schema with Partitioned Objects (Oracle Databases Only) With a Separate Reporting Database

If you are using an Oracle Adaptive Access Manager schema with the partition option enabled and have a separate reporting and administrative environment, you must perform manual purging, as described, as well as run the partition maintenance scripts that are shipped with the Oracle Adaptive Access Manager database setup package.

Note:

Make sure replication is not enable during the archive and purge process.

Follow these steps:

  1. Set up archive and purge routines.

  2. Schedule monthly/weekly partition drops. Refer to Section I.3.8.3, "Drop Scripts for Partitioned Tables."

  3. Schedule archive and purge routines.

I.3.2.2 Archive and Purge Process - Setting Up for Users with an Existing Process In Place

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.

If you are already using the Oracle Adaptive Access Manager Archive and Purge process, you should back up your existing archived tables (listed in Section I.3.7, "Archive and Purge Details") on disk before setting up a new archive and purge process. With 10.1.4.5.bp2, the structure of the old tables has changed; the setup scripts will recreate these tables.

I.3.2.3 Archive and Purge Process - Setting Up for the Oracle Database

The Create_purge_proc.sql script is required to set up the archive and purge routines for the Oracle Database. For more information on this script, refer to Section I.3.8.1, "Scripts for the Oracle Database."

I.3.2.3.1 Prerequisite

Important

You must ensure that the Oracle Adaptive Access Manager schema has the following privileges granted before the execution of the purging/archiving scripts and revoked after the execution of the purging/archiving scripts:

  • Create procedure

  • Execute procedure

  • Create any procedure

  • Create any table

  • Create any index

The purging/archiving scripts need 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 purge/archiving process is completed.

I.3.2.3.2 Instructions

To set up the archive and purge process for the Oracle Database, follow these steps:

  1. Create the script directory, oaam_purge_script.

  2. Unzip the Oracle Adaptive Access Manager purge package Oracle scripts to the script directory.

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

  4. Grant privileges to the Oracle Adaptive Access Manager 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 execute any procedure TO <schema_name>;
    
  5. Connect to database using the Oracle Adaptive Access Manager schema.

    For example, sqlplus <OAAMADMIN>/<PASSWORD>
    
  6. Run the create_purge_proc.sql script

    SQL>@ create_purge_proc.sql
    

I.3.3 Performing Archive and Purge

The execution of the archive and purge scripts is described in this subsection. Before starting the archive and purge process, go through the following checklist to ensure that the requirements for archive and purge are met.

  • Set up of the archive and purge scripts.

  • 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. For more information on these scripts, refer to Section I.3.8.2, "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

I.3.3.1 Manual Execution

To execute the scripts to archive and purge, follow these steps:

  1. Create the script directory, oaam_purge_script

  2. Unzip the Oracle Adaptive Access Manager archive and purge package Oracle scripts to the script directory.

  3. Log in to the database using the Oracle Adaptive Access Manager schema

    For example,

    sqlplus <OAAMADMIN>/<PASSWORD>
    
  4. 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
    

I.3.3.2 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.3.4 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.3.5 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.3.6 Purging Guidelines

Purging guidelines are presented below.

I.3.6.1 When to Perform Archive and Purge

In order to maintain data consistency and optimal system performance, users should perform the purging/archiving process routinely.

I.3.6.2 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.3.6.2.1 Device Fingerprinting Data

Minimum of 6 months or 180 days

I.3.6.2.2 In-session Transactional Tables

Minimum of 6 months or 180 days

I.3.6.2.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.3.6.2.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.3.6.3 Special Requirements

Special requirements are presented below.

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

I.3.7 Archive and Purge Details

This section contains information about the tables and their corresponding archived tables and details on the setup scripts.

Device fingerprint, autolearning transactional, transaction, and rule log tables and their corresponding tables are listed in the tables that follow.

I.3.7.1 Device Fingerprint Tables and Corresponding Archived Tables

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

I.3.7.2 Autolearning Transactional Tables and Corresponding Archive 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
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.3.7.3 Transaction Tables and Corresponding Archived 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.3.7.4 Rule Logs Tables and Corresponding Archived Tables

Rule 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.3.8 Scripts to Set Up Archive and Purge

Archive and purge setup scripts for the Oracle database are listed in the sections that follow.

I.3.8.1 Scripts for the Oracle Database

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

I.3.8.1.1 create_purge_proc.sql

The create_purge_proc.sql script creates the tables (Listed in Section I.3.7, "Archive and Purge Details") 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

I.3.8.2 Scripts to Execute Archive and Purge

The scripts to execute the archive and purge process are listed in the subsections following.

I.3.8.2.1 exec_sp_purge_tracker_data.sql

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

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.3.8.2.2 exec_sp_purge_txn_log.sql

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

I.3.8.2.3 exec_sp_purge_workflow_data.sql

This script calls stored procedures to archive and purge data from the Workflow Autolearning 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

I.3.8.2.4 exec_sp_purge_profile_data.sql

This script calls stored procedures to archive and purge data from the Autolearning 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

I.3.8.2.5 exec_sp_purge_rule_log.sql

This script calls stored procedures to archive and purge data from the Rules Engine logging tables. By running this script, the following tables will be archived and purged:

  • VR_POLICYSET_LOGS

  • VR_RULE_LOGS

  • VR_MODEL_LOGS

  • VR_POLICY_LOGS

I.3.8.3 Drop Scripts for Partitioned Tables

Two scripts to drop partitions are described in subsections that follow.

I.3.8.3.1 Drop_Monthly_Partition_tables.sql

Use this script to drop partitions for tables with the monthly frequency. Run this script at the end of each month to drop partitions that are older than sixth months as per the Oracle Adaptive Access Manager application requirement. Eventually, these tables will have six partitions at any point.

I.3.8.3.2 Drop_Weekly_Partition_tables.sql

Use this script to drop partitions for tables with the weekly frequency. Run this script at the end of every two weeks, starting from your database creation date, to drop partitions older than two weeks as per the Oracle Adaptive Access Manager application requirement.

I.4 Case Data Archive and Purge

This section presents the prerequisites and post-process procedures in archiving and purging case data in the Oracle Adaptive Access Manager database. A DBA or system administrator, who performs routine maintenance and the archiving and purging of case data in the Oracle Adaptive Access Manager database, should follow these instructions.

For a definition of purge, refer to Section I.1, "Purge Process."

For a definition of archive, refer to Section I.2, "Archive Process."

I.4.1 Archive and Purge Process for Case Data

The setup scripts are one-time scripts that are required to create objects for the archive and purge process for case data. The setup scripts will create the archived tables and store procedure required to execute during the routine archive and purge process for case data.

The procedures you will perform to archive and purge case data are:

  1. Set up archive and purge case data routines.

  2. Schedule monthly/weekly partition drops. Refer to Section I.3.8.3, "Drop Scripts for Partitioned Tables."

  3. Schedule archive and purge routines.

I.4.1.1 Set Up the Archive and Purge Script

Set up instructions are provided in the following subsections.

Special Instructions

Case-related data purging is not intended for all the customers. The following instructions should only be used by the customers who have business requirement to purge old case-related data.

I.4.1.1.1 Prerequisite

You must ensure that the Oracle Adaptive Access Manager schema has the following privileges granted before the execution of the purging/archiving script:

  • Create procedure

  • Execute procedure

  • Create any procedure

  • Create any table

  • Create any index

I.4.1.1.2 Set Up Archive and Purge Script

The create_case_purge_proc.sql script is required to set up the archive and purge routines for the Oracle database. For more information on this script, refer to Section I.4.1.5.2, "create_case_purge_proc.sql - Setup Script for Archive and Purge."

To set up the archive and purge process, follow these steps:

  1. Create the script directory, oaam_purge_script.

  2. Unzip the Oracle Adaptive Access Manager purge package, case_purge_scripts, to the script directory.

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

  4. Grant privileges to the Oracle Adaptive Access Manager 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 execute any procedure TO <schema_name>;
    
  5. Connect to database using the Oracle Adaptive Access Manager schema.

    For example, sqlplus <OAAMADMIN>/<PASSWORD>
    
  6. Run the create_case_purge_proc.sql script

    SQL>@ create_case_purge_proc.sql
    

I.4.1.2 Execute Archive and Purge Script

The execution of the archive and purge script is described in this subsection. Prior to starting the archive and purge process, go through the following checklist to ensure that the requirements for archive and purge are met.

  • Setup of the archive and purge script. Refer to Section I.4.1.1.2, "Set Up Archive and Purge Script."

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

For information on the required script to execute archive and purge routines for case data, refer to Section I.4.1.5.3, "exec_purge_case_data.sql - Execution Script for Archive and purge execution script."

Archive and purge periods are set based on the business requirement specified for retention periods.

By default, the archive and purge script has 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 exec_sp_purge_case_data.sql.

I.4.1.2.1 Manual Execution

To execute the script to archive and purge, follow these steps:

  1. Create the script directory, oaam_purge_script.

  2. Unzip the Oracle Adaptive Access Manager purge package, case_purge_scripts, to the script directory.

  3. Log in to the database using the Oracle Adaptive Access Manager schema

    For example,

    sqlplus <OAAMADMIN>/<PASSWORD>
    
  4. Run the exec_sp_purge_case_data.sql script

    SQL>@ exec_sp_purge_case_data.sql
    
I.4.1.2.2 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.4.1.3 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, you can also query the transactional log and its related purged tables to validate that the data was archived and purged.

I.4.1.4 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.4.1.5 Case Data Archive and Purge Details

This section contains information about the tables and their corresponding archived tables and details on the setup script.

I.4.1.5.1 Case-Related Tables and Their Corresponding Archived Tables

For your reference case-related tables and their corresponding archived tables are listed:

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.4.1.5.2 create_case_purge_proc.sql - Setup Script for Archive and Purge

The create_case_purge_proc.sql script creates the tables listed in Section I.4.1.5.1, "Case-Related Tables and Their Corresponding Archived Tables" and 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.4.1.5.3 exec_purge_case_data.sql - Execution Script for Archive and purge execution script

The exec_purge_case_data.sql script calls the stored procedures to archive and purge data from device fingerprinting tables. By running this script, the following tables will be archived and purged:

  • V_CASE

  • V_CASE_HIST

  • V_ACTION_LOG_SESS_MAP

  • V_ACTION_LOG_SESS

  • V_CASE_MAP

  • V_CASE_MAP_HIST

I.5 Monitor Data Archive and Purge

This section presents the prerequisites and post-process procedures in archiving and purging monitor data in the Oracle Adaptive Access Manager database. A DBA or system administrator, who performs routine maintenance and the archiving and purging of data in the Oracle Adaptive Access Manager database, should follow these instructions.

For a definition of purge, refer to Section I.1, "Purge Process."

For a definition of archive, refer to Section I.2, "Archive Process."

I.5.1 Archive and Purge Process for Monitor Data

The setup scripts are one-time scripts that are required to create objects for the archive and purge process for monitor data. The setup scripts will create the archived tables and store procedure required to execute during the routine archive and purge process for monitor data.

The procedures to perform to archive and purge monitor data are:

  1. Set up archive and purge monitor data routines.

  2. Schedule monthly/weekly partition drops. Refer to Section I.3.8.3, "Drop Scripts for Partitioned Tables."

  3. Schedule archive and purge routines.

I.5.1.1 Set Up the Archive and Purge Script

Set up instructions are provided in the following subsections.

I.5.1.1.1

Special Instructions - Dropping the Monitor Data

Customers who are using the Oracle table partitioning option and have no reporting database should run the drop_monitor_partition.sql script before setting up purging routine for monitor data.

To do so, follow these steps:

  1. Create the script directory, oaam_purge_script.

  2. Unzip the Oracle Adaptive Access Manager purge package, monitor_purge, to the script directory.

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

  4. Grant privileges to the Oracle Adaptive Access Manager 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 execute any procedure TO <schema_name>;
    
  5. Connect to database using the Oracle Adaptive Access Manager schema.

    For example, sqlplus <OAAMADMIN>/<PASSWORD>
    
  6. Run the purging execution script

    SQL>@ drop_monitor_partition.sql
    
I.5.1.1.2 Prerequisite - Privileges Granted to Schema

You must ensure that the Oracle Adaptive Access Manager schema has the following privileges granted before the execution of the purging/archiving script:

  • Create procedure

  • Execute procedure

  • Create any procedure

  • Create any table

  • Create any index

I.5.1.1.3 Set Up Archive and Purge Instructions

The create_v_monitor_purge_proc.sql script is required to set up the archive and purge routines for the Oracle database. For more information on this script, refer to Section I.5.1.5.2, "create_v_monitor_purge_proc.sql - Setup Script for Archive and Purge."

To set up the archive and purge process, follow these steps:

  1. Create the script directory, oaam_purge_script.

  2. Unzip the Oracle Adaptive Access Manager purge package, monitor_purge, to the script directory.

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

  4. Grant privileges to the Oracle Adaptive Access Manager 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 execute any procedure TO <schema_name>;
    
  5. Connect to database using the Oracle Adaptive Access Manager schema.

    For example, sqlplus <OAAMADMIN>/<PASSWORD>
    
  6. Run the create script

    SQL>@ create_v_monitor_purge_proc.sql
    

I.5.1.2 Execute Archive and Purge Script

The execution of the archive and purge script is described in this subsection. Prior to starting the archive and purge process, go through the following checklist to ensure that the requirements for archive and purge are met.

  • Setup of the archive and purge script. Refer to Section I.5.1.1.3, "Set Up Archive and Purge Instructions."

  • 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 script to execute archive and purge routines for the monitor data is the exec_v_monitor_purge_proc.sql script. For a description, refer to Section I.5.1.5.3, "exec_v_monitor_purge_proc.sql - Execution Script for Archive and purge execution script."

Archive and purge periods are set based on the business requirement specified for retention periods.

By default, the archive and purge script has 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 exec_v_monitor_purge_proc.sql.

I.5.1.2.1 Manual Execution

To execute the script to archive and purge, follow these steps:

  1. Create the script directory, oaam_purge_script.

  2. Unzip the Oracle Adaptive Access Manager purge package, monitor_purge, to the script directory.

  3. Log in to the database using the Oracle Adaptive Access Manager schema

    For example,

    sqlplus <OAAMADMIN>/<PASSWORD>
    
  4. Run the exec_v_monitor_purge_proc.sql script

    SQL>@ exec_v_monitor_purge_proc.sql
    
I.5.1.2.2 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.1.3 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, you can also query the transactional log and its related purged tables to validate that the data was archived and purged.

I.5.1.4 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.5.1.5 Monitor Data Archive and Purge Details

This section contains information about the tables and their corresponding archived tables and details on the setup script.

I.5.1.5.1 Monitor Data-Related Table and the Corresponding Archived Table

For your reference the monitor data-related table and its corresponding archived table is listed.

Transaction Table Corresponding Archived Table
V_MONITOR_DATA V_MONITOR_DATA_PURGE

I.5.1.5.2 create_v_monitor_purge_proc.sql - Setup Script for Archive and Purge

The create_v_monitor_purge_proc.sql script creates the V_MONITOR_DATA_PURGE table listed in Section I.5.1.5.1, "Monitor Data-Related Table and the Corresponding Archived Table" and the following stored procedure, SP_V_MON_DATA_PURGE_PROC, to archive and purge data from the transaction table.

I.5.1.5.3 exec_v_monitor_purge_proc.sql - Execution Script for Archive and purge execution script

The exec_v_monitor_purge_proc.sql script calls the stored procedures to archive and purge data from device fingerprinting tables. By running this script, the V_MONITOR_DATA table will be archived and purged.