Oracle® Fusion Middleware Administrator's Guide for Oracle Adaptive Access Manager Release 11g (11.1.1) E14568-06 |
|
Previous |
Next |
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.
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.
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.
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".
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: TheVT_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. |
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 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.
The sections following provide procedures for the archive and purge process.
This subsection provides special recommendations for schemas with partitioned objects.
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:
Set up archive and purge routines.
Schedule archive and purge routines.
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:
Set up archive and purge routines.
Schedule monthly/weekly partition drops. Refer to Section I.3.8.3, "Drop Scripts for Partitioned Tables."
Schedule archive and purge routines.
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.
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."
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.
To set up the archive and purge process for the Oracle Database, follow these steps:
Create the script directory, oaam_purge_script
.
Unzip the Oracle Adaptive Access Manager purge package Oracle scripts to the script directory.
Log in to the database using the system
or sys
account.
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>;
Connect to 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 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
To execute the scripts to archive and purge, follow these steps:
Create the script directory, oaam_purge_script
Unzip the Oracle Adaptive Access Manager archive and purge package Oracle scripts to the script directory.
Log in 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.
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.
Purging guidelines are presented below.
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.
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.
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.
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 |
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 |
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 |
Archive and purge setup scripts for the Oracle database are listed in the sections that follow.
The archive and purge setup scripts for the Oracle Database are listed in this subsection.
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
The scripts to execute the archive and purge process are listed in the subsections following.
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. |
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 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
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
Two scripts to drop partitions are described in subsections that follow.
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.
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.
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."
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:
Set up archive and purge case data routines.
Schedule monthly/weekly partition drops. Refer to Section I.3.8.3, "Drop Scripts for Partitioned Tables."
Schedule archive and purge routines.
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.
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
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:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package, case_purge_scripts, to the script directory.
Log in to the database using the system
or sys
account.
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>;
Connect to database using the Oracle Adaptive Access Manager schema.
For example, sqlplus <OAAMADMIN>/<PASSWORD>
Run the create_case_purge_proc.sql script
SQL>@ create_case_purge_proc.sql
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.
To execute the script to archive and purge, follow these steps:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package, case_purge_scripts, to the script directory.
Log in to the database using the Oracle Adaptive Access Manager schema
For example,
sqlplus <OAAMADMIN>/<PASSWORD>
Run the exec_sp_purge_case_data.sql script
SQL>@ exec_sp_purge_case_data.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.
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.
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.
This section contains information about the tables and their corresponding archived tables and details on the setup script.
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 |
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
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
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."
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:
Set up archive and purge monitor data routines.
Schedule monthly/weekly partition drops. Refer to Section I.3.8.3, "Drop Scripts for Partitioned Tables."
Schedule archive and purge routines.
Set up instructions are provided in the following subsections.
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:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package, monitor_purge, to the script directory.
Log in to the database using the system
or sys
account.
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>;
Connect to database using the Oracle Adaptive Access Manager schema.
For example, sqlplus <OAAMADMIN>/<PASSWORD>
Run the purging execution script
SQL>@ drop_monitor_partition.sql
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
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:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package, monitor_purge, to the script directory.
Log in to the database using the system
or sys
account.
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>;
Connect to database using the Oracle Adaptive Access Manager schema.
For example, sqlplus <OAAMADMIN>/<PASSWORD>
Run the create script
SQL>@ create_v_monitor_purge_proc.sql
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.
To execute the script to archive and purge, follow these steps:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package, monitor_purge, to the script directory.
Log in to the database using the Oracle Adaptive Access Manager schema
For example,
sqlplus <OAAMADMIN>/<PASSWORD>
Run the exec_v_monitor_purge_proc.sql script
SQL>@ exec_v_monitor_purge_proc.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.
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.
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.
This section contains information about the tables and their corresponding archived tables and details on the setup script.
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 |
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.
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.