RSA Identity Governance and Lifecycle - Purge monitor tasks
3 years ago
Originally Published: 2017-06-05
Article Number
000067674
Applies To
RSA Product Set: Identity Management and Governance
RSA Product/Service Type: Appliance
RSA Version/Condition: 6.9.1
Product Name: Identity Governance and Lifecycle
 
Issue
The System -> Monitoring tab lists summary information of job runs.

This data is stored in the database table T_DATA_RUN_TASKS.  As this table fills up and it takes Oracle longer to return the rows, the time taken to load the data into the screen above also increases. 

.
 
Tasks
The performance issue occurs due to the accumulation of data in the database table T_DATA_RUN_TASKS
Resolution
To resolve the performance issue due to excessive logging data in T_DATA_RUN_TASKS the data may be purged.  Before doing the purge the table may be backed up to a flat file using the Oracle data pump utility. 

The data pump utility requires that a directory object be defined in Oracle which it will use to write the file.  If a data pump directory object does not already exist it may be created as follows:

acm-691:~ # su - oracle
oracle@acm-691:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 11 05:56:39 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
 
SQL> create directory dpump_dir1 as '/u01/app/oracle/AVDB/dpdump';
 
Directory created.

We need to grant read and write permission on the directory object to AVUSER.

SQL> grant read,write on directory dpump_dir1 to avuser;

Grant succeeded.


The table data may now be exported with the data pump utility as follows:

oracle@acm-691:~> expdp avuser/<password> DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks
Export: Release 11.2.0.3.0 - Production on Thu May 11 07:00:14 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
Starting "AVUSER"."SYS_EXPORT_TABLE_01":  avuser/******** DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "AVUSER"."T_DATA_RUN_TASKS"                 12.56 KB       9 rows
Master table "AVUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for AVUSER.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/AVDB/dpdump/t_data_run_tasks.dmp
Job "AVUSER"."SYS_EXPORT_TABLE_01" successfully completed at 07:00:44

When the export has completed without errors the data may be purged.  The following example purges all data older than 30 days:

SQL> delete from avuser.t_data_run_tasks
     where trunc(start_time) < sysdate - 30;

9 rows deleted.

SQL> commit;

Commit complete.

Note: To purge all of the data the truncate command could be used in place of delete, it will be faster where a large data set is involved. [Deleted data can be rolled back with the rollback command before it is committed if a mistake is made.  Truncated data can only be recovered from the export backup]. 

The System -> Monitoring screen should now be empty.

If the exported data needs to be restored it can be done as follows:

oracle@acm-691:~> impdp avuser/<password> DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks TABLE_EXISTS_ACTION=REPLACE
 
Import: Release 11.2.0.3.0 - Production on Thu May 11 07:13:33 2017
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
Master table "AVUSER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "AVUSER"."SYS_IMPORT_TABLE_01":  avuser/******** DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks TABLE_EXISTS_ACTION=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AVUSER"."T_DATA_RUN_TASKS"                 12.56 KB       9 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "AVUSER"."SYS_IMPORT_TABLE_01" successfully completed at 07:13:41
 

Related Articles

Trending Articles

Don't see what you're looking for?