23 Using the Offline Data Purge Framework

Oracle Identity Governance provides a new Offline Data Purge Framework to purge huge data sets in a few iterations and reclaim huge storage space with the same operation.

Oracle Identity Governance has been providing real-time and continuous data purge solution to meet the standards of performance and scalability by maintaining the data generated for the life cycle management of various entities. However, there is a need for an Offline Data Purge Framework to purge huge data sets in few iterations, and also reclaim huge storage space with the same operation.

This chapter contains the following sections:

• About the Offline Data Purge Framework

• Prerequisites for Running the Offline Data Purge Framework

• Configuring and Running the Offline Data Purge Operation

23.1 About the Offline Data Purge Framework

The Offline Data Purge Framework helps to purge huge volume of data sets in fewer iterations.

Using the Offline Data Purge Framework, you can purge non-purgeable data, for example, data that is in In-Progress status for Oracle Identity Governance entities such as Reconciliation, Provisioning Task, and Orchestration.

Oracle recommends using this framework in the following situations:

• When you have not executed the default Online Data Purge scheduled task, and accumulated huge volume of purgeable data

• When you want to purge non-purgeable data for Oracle Identity Governance entities: reconciliation, provisioning task, and orchestration

• When you want to run the default scheduled task for online data purge, but the volume of purgeable data is exceptionally high

• When you want to purge all data for Oracle Identity Governance entities, reconciliation, provisioning task, and orchestration, based on retention period

Some of the key features of the Offline Data Purge Framework are:

• The Offline Data Purge Framework is disabled in Oracle Identity Governance by default.

• The Create Table As Select (CTAS) approach is used to purge data and also auto-reclaims storage space for the purged data set.

• The Offline Data Purge Framework supports data purge based on Oracle Identity Governance entity type, purge criteria, and purge retention period.

• The Table 23-1 lists the purge criteria (based on purge retention period) that are supported for the Oracle Identity Governance entities.

  Table 23-1 Supported Purge Criteria for Oracle Identity Governance Entities

  Entity	Purgeable Events	Non-Purgeable Events	All Data
  Reconciliation	Event Closed, Creation Succeeded, Update Succeeded, and Delete Succeeded	ALL events except the Purgeable Events	Yes (based on retention period)
  Provisioning Task	Completed and Cancelled	NA	NA
  Orchestration	Completed, Failed, Cancelled, Compensated, and Cancelled_With_Compensation	ALL events except the Purgeable Events	Yes (based on retention period)

• You can perform the offline data purge operation by running the Oracle DBMS scheduler job.

• The Offline Data Purge Framework uses the existing PL/SQL diagnostic logging and debugging framework to track the progress of the offline data purge operation.

• The summary and detailed information for the offline data purge operation is captured in two separate diagnostic logging tables, DIAG_LOG and DIAG_LOG_DTLS.

23.2 Prerequisites for Running the Offline Data Purge Framework

The following prerequisites must be met before running the offline data purge operation:

• Before running the offline data purge operation, create a backup of the data to be purged.

• Ensure that you do not have a business use for the data identified for purging in the mainstream Oracle Identity Governance operations.

• It is recommended to have sufficient space in OIM, UNDO, and TEMP tablespaces. To calculate extra space required in these tablespaces:

  • Calculate the cumulative size of the tables from which data is required to be purged. The tables are:

    • For orchestration: ORCHPROCESS

    • For provisioning task: OSI, OSH, SCH, OTI

    • For reconciliation: RA_XELLERATE_ORG, RA_LDAPROLEHIERARCHY, RA_LDAPROLEMEMBERSHIP, RA_MLS_LDAPROLE, RA_LDAPROLE, RA_MLS_LDAPUSER, RA_LDAPUSER, RECON_ROLE_MEMBER_MATCH, RECON_ROLE_HIERARCHY_MATCH, RECON_ROLE_MATCH, RECON_ORG_MATCH, RECON_CHILD_MATCH, RECON_ACCOUNT_MATCH, RECON_USER_MATCH, RECON_HISTORY, RECON_EVENT_ASSIGNMENT, RECON_BATCHES, RECON_JOBS, RECON_EVENTS, and other RA_* tables

  • Based on the result of the previous step, double the size can be allocated to OIM tablespace. The UNDO and TEMP tablespaces require lesser space than the OIM tablespace.

• Make sure to collect latest statistics for OIM database schema.

• Ensure that Oracle Identity Governance server is down during the purge operation.

• It is recommended to perform the offline data purge operation in a lower environment with representative data and similar configurable Oracle DBMS scheduled job parameters, to get an idea about how much downtime would be required in production environment beforehand.

• Enable diagnostic logging during the offline data purge operation, by setting the diagnostic level as the value of the OIM.DBDiagnosticLevelOffPurge system property. See Default System Properties in Oracle Identity Governance for information about this system property.

  After the diagnostic data is collected, reset the value of the system properties from FINISH to the default value of NONE. See Editing System Properties for information about modifying the values of system properties.

23.3 Configuring and Running the Offline Data Purge Operation

To run the offline data purge operation:

Note:

Before beginning the configuration, ensure that all the prerequisites are met.

1. Login to Oracle Identity Governance Database schema by using SQLPLUS shell, SQL Developer, or any other interface tool.
2. Configure the OIM_OFFLINE_DATAPURGE Oracle DBMS scheduled job.

   Table 23-2 lists the configurable parameters of the OIM_OFFLINE_DATAPURGE Oracle DBMS scheduled job.

   Table 23-2 Configuration Parameters for OIM_OFFLINE_DATAPURGE DBMS Scheduled Job

   Parameter	Description	Default Value
   OIG Entity	This takes the following values: 1 for orchestration 2 for provisioning task 3 for reconciliation	NULL
   Recon Entity
   Purge Criteria	This takes the following values: 1 for purging purgeable events 2 for purging non-purgeable events 3 for all data	NULL
   Purge Retention period (in days)	This indicates the retention period in days for the purge. Based on the value recon_events.re_create.	NULL
   Orchestration Entity
   Purge Criteria	This takes the following values: 1 for purging purgeable events 2 for purging non-purgeable events 3 for all data	NULL
   Purge Retention period (in days)	This indicates the retention period in days for the purge. Based on the value orchprocess.modifiedon.	NULL
   Prov Task Entity
   Purge Criteria	This takes the following values: 1 OR 2 OR 3 for purging purgeable/non-purgeable/All data events	NULL
   Purge Retention period (in days)	This indicates the retention period in days for the purge. Based on the value 'sch.sch_create.	NULL

   1. Replace the following attributes of the Oracle DBMS scheduled job with desired values by using the following syntax:

      • <OIM_ENTITY>: OIG entity type (1,2, or 3)

      • <PURGE_CRITERIA>: Purge criteria (1,2, or 3)

      • <RETENTION_PERIOD>: Retention period (in days)

      SQL> BEGIN dbms_scheduler.set_attribute( name => 'OIM_OFFLINE_DATAPURGE', attribute => 'job_action', value => '
      BEGIN oim_pkg_offline_datapurge.oim_sp_offline_dataprg_wrapper(p_oim_entity => <OIM_ENTITY>, p_purge_criteria => <PURGE_CRITERIA>, p_retention_period => <RETENTION_PERIOD>); 
      END;'
       );
       END;
      /
      

      For example, in the following scenario, all the non-purgeable data for orchestration entity type that is older than 365 days is purged from the database.

      SQL> BEGIN 
      	    dbms_scheduler.set_attribute
      	    (
      	        name => 'OIM_OFFLINE_DATAPURGE',
      	        attribute => 'job_action',
      	        value => 'BEGIN oim_pkg_offline_datapurge.oim_sp_offline_dataprg_wrapper(p_oim_entity => 1, p_purge_criteria => 1, p_retention_period => 365); END;'
      	    );
      	END;
      	/
      

   2. Run the following SQL command to run the Oracle DBMS scheduled job:

      SQL> EXEC dbms_scheduler.run_job('OIM_OFFLINE_DATAPURGE');

   3. Run the following SQL command to track the purge operation progress by using the existing PL/SQL diagnostic logging and debugging tables:

      SQL> SELECT * FROM diag_log ORDER BY 1 DESC;
      SQL> SELECT * FROM diag_log_dtls ORDER BY 2 DESC, 1;

   After successful completion of the offline data purge operation:

   • The active database tables show only the data that needs to be retained after the purge operation.

   • The parameter value of the OIM_OFFLINE_DATAPURGE DBMS scheduled job is reset to NULL.

   See Also:

   • Understanding the Data Captured by PL/SQL Diagnostic Logging Tables for information about how data is captured by PL/SQL diagnostic logging tables.

   • Collecting Data Captured by PL/SQL Diagnostic Logging Tables for information about how the diagnostic data is collected during the offline data purging operation.