This chapter describes how to use the various archival utilities in the following sections:
Note:
You can use the Reconciliation Archival utility, the Task Archival utility, and the Requests Archival utility in both offline and online modes.This section describes how to use the Reconciliation Archival utility. It contains the following topics:
Oracle Identity Manager stores reconciliation data from target systems in Oracle Identity Manager tables called active reconciliation tables:
During the reconciliation process, Reconciliation Manager reconciles data in the active reconciliation tables with the Oracle Identity Manager core tables. Because Reconciliation Manager does not remove reconciled data from the active reconciliation tables, they might eventually grow very large, resulting in decreased performance during the reconciliation process. You can use the Reconciliation Archival utility to archive data that has been reconciled with Oracle Identity Manager. The Reconciliation Archival utility stores archived data in the archive reconciliation tables, which have the same structure as the active reconciliation tables.
Table 19-1 lists the active reconciliation tables with the corresponding archive reconciliation tables in which data from the active reconciliation tables are archived.
Table 19-1 Active and Archive Reconciliation Tables
| Active Reconciliation Tables (Oracle Identity Manager Tables) | Archive Reconciliation Tables |
|---|---|
|
RECON_EVENTS |
ARCH_RECON_EVENTS |
|
RECON_JOBS |
ARCH_RECON_JOBS |
|
RECON_BATCHES |
ARCH_RECON_BATCHES |
|
RECON_EVENT_ASSIGNMENT |
ARCH_RECON_EVENT_ASSIGNMENT |
|
RECON_EXCEPTIONS |
ARCH_RECON_EXCEPTIONS |
|
RECON_HISTORY |
ARCH_RECON_HISTORY |
|
RECON_USER_MATCH |
ARCH_RECON_USER_MATCH |
|
RECON_ACCOUNT_MATCH |
ARCH_RECON_ACCOUNT_MATCH |
|
RECON_CHILD_MATCH |
ARCH_RECON_CHILD_MATCH |
|
RECON_ORG_MATCH |
ARCH_RECON_ORG_MATCH |
|
RECON_ROLE_MATCH |
ARCH_RECON_ROLE_MATCH |
|
RECON_ROLE_HIERARCHY_MATCH |
ARCH_RECON_ROLE_HIER_MATCH |
|
RECON_ROLE_MEMBER_MATCH |
ARCH_RECON_ROLE_MEMBER_MATCH |
|
RA_LDAPUSER |
ARCH_RA_LDAPUSER |
|
RA_MLS_LDAPUSER |
ARCH_RA_MLS_LDAPUSER |
|
RA_LDAPROLE |
ARCH_RA_LDAPROLE |
|
RA_MLS_LDAPROLE |
ARCH_RA_MLS_LDAPROLE |
|
RA_LDAPROLEMEMBERSHIP |
ARCH_RA_LDAPROLEMEMBERSHIP |
|
RA_LDAPROLEHIERARCHY |
ARCH_RA_LDAPROLEHIERARCHY |
|
All horizontal tables mentioned under RECON_TABLES |
"ARCH_" + substr(HTnames,1,25) |
You can use the Reconciliation Archival utility to perform the following tasks:
Archive all or specific data from the active reconciliation tables to the archive reconciliation tables
Delete all data from the active reconciliation tables
When you archive data by moving it from the active reconciliation tables to the archive reconciliation tables, you must specify the date in the YYYYMMDD format, such as all records on or before this date will be archived, and a reconciliation event status parameter value, which defines the data that you want to archive. For information about these archiving criteria, refer to "Archival Criteria".
If you choose to archive selective data, then the utility archives reconciliation data based on selected event status that have been created on or before the specified date and event status.
When you archive all data from the active reconciliation tables to the archive reconciliation tables, the Reconciliation Archival utility archives all reconciliation data that have been created on or before the specified date.
The files that constitute the Oracle Database version of the Reconciliation Archival utility are located in the following directory:
OIM_ORACLE_HOME/server/db/oim/oracle/Utilities/Recon11gArchival
You can run the Reconciliation Archival utility in offline mode with Oracle Identity Manager stopped, or in online mode with Oracle Identity Manager running.
Before running the utility in offline mode, you must stop Oracle Identity Manager.
Before running the Reconciliation Archival utility, the OIM_RECON_ARCH tablespace must be created in the database. To do so, you can run the following sample command as a DBA privilege user, for instance SYS or SYSTEM.
CREATE TABLESPACE OIM_RECON_ARCH
LOGGING DATAFILE 'ORADATA/OIM_RECON_ARCH.dbf'
SIZE 500M REUSE AUTOEXTEND ON NEXT 10M;
Note:
You must replace ORADATA in the preceding sample command with the full path to your ORADATA directory.
You must set LD_LIBRARY_PATH to start Oracle utilities such as SQL*Plus in the environment where you want to run Oracle Identity Manager utilities.
Data that has been archived from the active reconciliation tables to the archive reconciliation tables will no longer be available through Oracle Identity Manager. To access this data, you must query the archive reconciliation tables in your Oracle Identity Manager database.
If you are using ASM, Exadata (ASM) or Oracle Managed Files (OMF), then follow the instructions described here.
If you are using ASM, then you can use the name of a diskgroup say DATA 1 to create the tablespace in the database as follows:
CREATE TABLESPACE OIM_RECON_ARCH
LOGGING DATAFILE '+DATA1'
SIZE 500M AUTOEXTEND ON NEXT 10M;
If you are using Oracle Managed Files, then you can omit the datafile and run the command as follows:
CREATE TABLESPACE OIM_RECON_ARCH
LOGGING DATAFILE
SIZE 500M AUTOEXTEND ON NEXT 10M;
If you want to run the utility in offline mode, then you must stop Oracle Identity Manager before running the utility.
To select reconciliation data to archive, provide the following criteria. Data with matching values will be archived.
Date must be in the format YYYYMMDD. All records on or before this date that match the specified reconciliation event parameter value will be archived.
Select Closed, Linked, Closed or Linked, or All for the reconciliation event parameter.
Closed describes events that have been manually closed in Reconciliation Manager.
Linked describes events that were reconciled in Oracle Identity Manager, including the following states:
Creation Succeeded
Update Succeeded
Delete Succeeded
Creation Failed
Update Failed
Delete Failed
Closed or Linked
Select status for reconciliation events to be archived.
Enter 1 for Closed
Enter 2 for Linked
Enter 3 for Closed and Linked
Enter 4 for Exit
To run the Reconciliation Archival utility:
Ensure that the Oracle Identity Manager database is available and that no reconciliation processes are running.
Note:
Oracle recommends that you run the Reconciliation Archival utility during off-peak hours.If you want to run the utility in offline mode, then stop Oracle Identity Manager by following the instructions in the "Starting and Stopping Servers" chapter.
To run the utility in online mode, ignore this step and proceed to step 3.
On Microsoft Windows platforms, you must specify the short date format as M/d/yyyy. In addition, you must specify the time format as H:mm:ss. To customize the date and time formats, use the Regional and Language Options command in Control Panel.
Note:
When you change the date and time format, the change is applied to all the applications running on the Microsoft Windows platform.
Minimal validation is done on date before calling the utility, and you can scan logs files for any ORA-18xx errors for invalid date-related errors.
On Linux or UNIX platforms, run the following commands to set execution permission for the oim_recon_archival.sh file and to ensure that the file is a valid Linux or UNIX text file:
chmod 755 path/oim_recon_archival.sh dos2unix path/oim_recon_archival.sh
On Linux or UNIX platforms, run the path/oim_recon_archival.sh file to run the utility.
On Microsoft Windows platforms, run the path\oim_recon_archival.bat file to run the utility.
For Oracle Database installations, enter values for the following parameters when prompted:
Oracle home directory
Oracle Identity Manager database user name and password
Enter the reconciliation creation date in the YYYYMMDD format. All records on or before this date with required status value will be archived.
When prompted, select a reconciliation event status for the data that you want to archive:
Enter 1 for Closed
Enter 2 for Linked
Enter 3 for Closed or Linked
Enter 4 for Exit
When prompted to specify the mode of running the utility, enter 1 if you want to run the utility in online mode. Otherwise, enter 2 to run the utility in offline mode.
Enter the batch size for processing.
The default batch size is 5000.
Note:
Batch size is a value for the number of records to be processed in a single iteration of archival/purge, also as an internal commit at the database level. You must provide the batch size as an input parameter value while starting the operation of Archival Utilities at run time.This batch size by default is 5000. When purging greater than few hundred thousand recon_events, a higher batch size can be opted for. This may need more resources from RDBMS, such as more space from the TEMP and UNDO tablespaces.
The utility archives the reconciliation data and provides an execution summary in a log file.
On Microsoft Windows platforms, reset the short date format to the date format for your region or locale after you run the utility. Use the Regional and Language Options command in Control Panel to reset the date format.
Because the data from active reconciliation tables are removed, your DBA must analyze the active reconciliation tables and their indexes in order to update the statistics. Perform this step only if you are using Oracle Database as the database for Oracle Identity Manager.
After running the Reconciliation Archival utility, the following log file is generated:
./logs/oim_recon_archival_summary_TIMESTAMP.log
If running the utility fails, then the log file records the batch number at which the utility fails along with the error messages.
This section describes how to use the Task Archival utility. It contains the following topics:
In Oracle Identity Manager, a task refers to one or more activities that comprise a process, which handles the provisioning of a resource. For example, a process for requesting access to a resource may include multiple provisioning tasks. Oracle Identity Manager stores task data in the following tables, which are called active task tables:
OSI
OSH
SCH
By default, Oracle Identity Manager does not remove completed tasks from the active task tables. As the size of the active task tables increases, you might experience a reduction in performance, especially when managing provisioning tasks. After a task executes successfully, you can use the Task Archival utility to archive the task data and remove it from the active task tables. Archiving task data with the Task Archival utility improves performance and ensures that the data is safely stored.
The Task Archival utility stores archived task data in the following archive task tables, which have the same structure as the active task tables:
ARCH_OSI
ARCH_OSH
ARCH_SCH
You can use the Task Archival utility to archive the following types of tasks:
Provisioning tasks for resource instances that have been revoked for disabled or deleted users
Provisioning tasks for resource instances that have been revoked
When you archive tasks with the Task Archival utility, you can specify the type of archive operation, the user status, the task execution date, and the number of records above which to drop the indexes before archiving. The archive operation represents the type of task data to archive and the user status determines whether to archive data for users who have been deleted, disabled, or both. The task execution date represents the date on which a task is executed and must be in the format YYYYMMDD.
All executed tasks, up to the task execution date you specify, will be archived. To reduce the time that the archiving process takes, the utility drops the indexes on all active task tables when the number of records to be archived is greater than 200000. The indexes are re-created after the archived data is deleted from the active task tables. You can change the value 200000 to your preferred value. You can change the value in the following lines of code in the OIM_TasksArch.bat file or in the OIM_TasksArch.sh file:
In the .bat file, set INDXRESP=200000
In the .sh file, indxopt=200000
The files that constitute the Oracle Database version of the Task Archival utility are located in the following directory:
OIM_HOME/db/oim/oracle/Utilities/TaskArchival
Note:
Data that has been archived from the active task tables to the archive task tables will no longer be available through Oracle Identity Manager. To access this data, you must query the archive task tables in your Oracle Identity Manager database.You can run the Task Archival utility in offline mode with Oracle Identity Manager stopped, or in online mode with Oracle Identity Manager running.
Before running the utility in offline mode, you must stop Oracle Identity Manager.
Before you can use the Task Archival utility with Oracle Database, you must perform the following steps:
Start SQL*Plus and connect to Oracle Database as a SYS user.
Create a separate tablespace for the archival task tables by entering the following command. Replace DATA_DIR with the directory in which you want to store the data file and adjust the size and other parameters as necessary for your environment.
CREATE TABLESPACE TasksArch
DATAFILE 'DATA_DIR\tasksarch_01.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Note:
Oracle recommends that you allocate a large UNDO tablespace when archiving large amounts of data. In addition, turn on parallel execution by configuring the parallel_max_servers and parallel_min_servers initialization parameters. Parallel execution helps improve the performance of the archival process.Connect to Oracle Database as the Oracle Identity Manager database user.
Enter the following command to run the cr_taskarchival_ddl_table.sql script, which creates a table named OIM_TASK_ARCH_DDL. This table is used by the Task Archival utility.
@ path/cr_taskarchival_ddl_table.sql
Enter the following command to run the Create_TasksArch_Tables.sql script, which creates the archive task tables:
@ path/Create_TasksArch_Tables.sql
Enter the following command to run the OIM_SP_TASKS_ARCHIVAL.sql script, which creates a stored procedure that the Task Archival utility uses to archive and delete task data:
@ path/OIM_SP_TASKS_ARCHIVAL.sql
Note:
You must set LD_LIBRARY_PATH to start Oracle utilities such as SQL*Plus in the environment where you want to run Oracle Identity Manager utilities.Perform the following steps to run the Task Archival utility:
Ensure that the Oracle Identity Manager database is available but it is not open to other Oracle Identity Manager transactions.
Note:
Oracle recommends that you run the Task Archival utility during off-peak hours.Ensure that you have created a backup of the OSI, SCH, and OSH tables.
If you want to run the utility in offline mode, then stop Oracle Identity Manager by following the instructions in the "Starting and Stopping Servers" chapter.
To run the utility in online mode, ignore this step and proceed to step 4.
On Microsoft Windows platforms, you must specify the short date format as dddd M/d/yyyy. In addition, you must specify the time format as H:mm:ss. To customize the date and time formats, select the Regional and Language Options command in the Control Panel.
Note:
When you change the date and time format, the change is applied to all the applications running on the Microsoft Windows platform
Minimal validation is done on date before calling the utility, and you can scan logs files for any ORA-18xx errors for invalid date-related errors
On Linux and UNIX platforms, run the path/OIM_TasksArch.sh file. On Microsoft Windows platforms, run the path\OIM_TasksArch.bat file.
For Oracle Database installations, enter values for the following parameters when prompted:
Oracle home directory
Oracle Identity Manager database name or TNS string if the Oracle Identity Manager database is running on a remote computer
Oracle Identity Manager database user name and password
When prompted, select one of the following options:
Archive all provisioning tasks on resource instances that have been revoked for disabled or deleted users.
Archive all provisioning tasks on resource instances that have been revoked.
Exit.
When prompted to specify the mode of running the utility, enter 1 if you want to run the utility in online mode. Otherwise, enter 2 to run the utility in offline mode.
If you chose to archive all provisioning tasks for resource instances that have been revoked for disabled or deleted users, select one of the following options:
Users at Deleted status
Users at Disabled status
Users at Deleted and Disabled status
Go back to Main Menu
Enter a task execution date in the format YYYYMMDD when prompted. All executed tasks, up to the task execution date you specify, will be archived. To archive all tasks that were executed on or before the current date, press Enter without entering a date.
Summary information is displayed before the utility starts the archival process. The summary information gives you the total number of tasks to be archived. Read the summary information carefully and make sure your database can support the delete volume listed in the summary.
Enter a value of y or Y when prompted to archive the tasks. Otherwise, enter a value of n or N to exit the utility.
Note:
You must enter the value of Y or N when prompted. If you press Enter without selecting a value, then the utility again counts the number of tasks to be archived and prompts you without beginning the archive.On Microsoft Windows platforms, reset the short date format to the date format for your region or locale after the Task Archival utility finishes running. Use the Regional and Language Options command in the Control Panel to reset the date format.
Note:
You must analyze the active task tables and their indexes for updated statistics, because the data from active task tables is removed. Perform this step only if you are using Oracle Database as the database for Oracle Identity Manager.Table 19-2 describes the output files that are generated by the Task Archival utility.
Table 19-2 Output Files Generated by the Task Archival Utility
| File | Description |
|---|---|
|
|
Generated when the utility is unable to connect to the database with the specified credentials |
|
|
Generated when the archival or deletion processes fail |
|
|
Generated when the archival or deletion processes succeed |
Note:
These error log files are deleted when you run the utility again.This section describes how to use the Requests Archival utility. It contains the following topics:
By default, Oracle Identity Manager does not remove closed or withdrawn requests from the active request tables. To archive these requests and free up the disk space and thereby enhance database performance, the Requests Archival utility is used. You can archive request data based on request creation date and request status. Archiving requests based on the request status is optional. By using request status, you can archive:
Completed requests such as requests with status Withdrawn, Closed, and Completed. This is specified by selecting the 1 for Completed option.
Completed and failed requests such as requests with status Withdrawn, Closed, Completed, Failed, and Partially Failed. This is specified by selecting option 2 for Completed and Failed.
All requests based on request creation date. This is specified by selecting option 3 for All.
Table 19-3 lists the names of the tables which are to be archived and the corresponding archival table names.
| Main Table | Archival Table |
|---|---|
|
REQUEST |
ARCH_REQUEST |
|
REQUEST_HISTORY |
ARCH_REQUEST_HISTORY |
|
REQUEST_APPROVALS |
ARCH_REQUEST_APPROVALS |
|
REQUEST_ENTITIES |
ARCH_REQUEST_ENTITIES |
|
REQUEST_ENTITY_DATA |
ARCH_REQUEST_ENTITY_DATA |
|
REQUEST_BENEFICIARY |
ARCH_REQUEST_BENEFICIARY |
|
REQUEST_BENEFICIARY_ENTITIES |
ARCH_REQUEST_BE |
|
REQUEST_BENEFICIARY_ENTITYDATA |
ARCH_REQUEST_BED |
|
REQUEST_TEMPLATE_ATTRIBUTES |
ARCH_REQUEST_TA |
|
WF_INSTANCE |
ARCH_WF_INSTANCE |
|
REQUEST_COMMENTS |
ARCH_REQUEST_COMMENTS |
The files that constitute the Oracle Database version of the Requests Archival utility are located in the following directory:
OIM_HOME/db/oim/oracle/Utilities/RequestArchival
You can run the Requests Archival utility in offline mode with Oracle Identity Manager stopped, or in online mode with Oracle Identity Manager running.
Before running the utility in offline mode, you must stop Oracle Identity Manager.
Before running the Requests Archival utility:
Note:
You must set LD_LIBRARY_PATH to start Oracle utilities such as SQL*Plus in the environment where you want to run Oracle Identity Manager utilities.Create the OIM_REQUEST_ARCH tablespace. When the Requests Archival utility is run for the first time, a corresponding archival table is created for all the tables that are to be archived. The archival tables are created in a separate tablespace named OIM_REQUEST_ARCH. This tablespace must be created before running the utility.
Create the required archival tables for the request tables by running the oim_create_request_arch_tables.sql script. This is the PL/SQL script to create archival tables against all tables that are to be archived.
If you want to run the utility in offline mode, then you must stop Oracle Identity Manager before running the utility.
Table 19-4 lists the input parameters used by the Requests Archival utility:
| Parameter | Description |
|---|---|
|
Oracle Home |
The value of ORACLE_HOME environment variable on the system. |
|
Oracle SID |
The SID of the Oracle Identity Manager database, which is a TNS name or TNS alias. |
|
OIM DB User |
The database login ID of the Oracle Identity Manager database user. |
|
OIM DB Pwd |
The password of the Oracle Identity Manager database user. |
|
Request Status |
The request status based on the user inputs 1, 2, or 3. |
|
Request Creation Date |
The utility archives all requests created on or before this request creation date with the required request status. |
|
Batch Size |
The utility processes a group of records or batch as a single transaction. The batch size can influence the performance of the utility. Default value of Batch Size is |
|
Utility Running Mode |
The mode in which you want to run the utility, online or offline. You must enter 1 for online mode, or 2 for offline mode. The utility runs faster when you run it in offline mode than online mode. However, running the utility in offline mode requires downtime. The archival operation can be speeded up by running in offline mode, but Oracle Identity Manager is not usable until the utility completes the archival operation. Therefore, make sure that Oracle Identity Manager is not running before choosing this option. |
To run the Requests Archival utility:
Ensure that the Oracle Identity Manager database is available.
Note:
It is recommended that you run the Requests Archival utility during off-peak hours.If you want to run the utility in offline mode, then stop Oracle Identity Manager by following the instructions in the "Starting and Stopping Servers" chapter.
To run the utility in online mode, ignore this step and proceed to step 3.
On Microsoft Windows platform, you must specify the short date format as dddd M/d/yyyy. In addition, you must specify the time format as H:mm:ss. To customize the date and time formats, use the Regional and Language Options command in Control Panel.
Note:
When you change the date and time format, the change is applied to all the applications running on the Microsoft Windows platform.
Minimal validation is done on date before calling the utility, and you can scan logs files for any ORA-18xx errors for invalid date-related errors.
On UNIX platform, run the following commands to set execution permission for the OIM_request_archival.sh file and to ensure that the file is a valid UNIX text file:
chmod 755 path/OIM_request_archival.sh dos2unix path/OIM_request_archival.sh
On UNIX platform, run the path/OIM_request_archival.sh file. On Microsoft Windows platform, run the path\OIM_request_archival.bat file.
The oim_request_archival script validates the database input and establishes a connection with the database. It then calls the oim_request_archival.sql script, the script is used to compile PL/SQL procedures related to the utility.
For Oracle Database installations, enter values for the following parameters when prompted:
Oracle home directory.
Oracle Identity Manager database name or TNS string if the Oracle Identity Manager database is running on a remote computer. Otherwise, enter ORACLE SID.
Oracle Identity Manager database user name and password.
When prompted, enter one of the following options:
Enter 1 to archive the requests with status Request Withdrawn, Request Closed, or Request Completed, and requests with creation date on or before the request creation date specified by the user in the format YYYYMMDD.
Enter 2 to archive the requests with status Request Withdrawn, Request Closed, Request Completed, or Request Partially Failed, and requests with creation date on or before the request creation date specified by the user in the format YYYYMMDD.
Enter 3 to archive all the requests with request creation date on or before the request creation date specified by the user in the format YYYYMMDD.
When prompted to specify the mode of running the utility, enter 1 if you want to run the utility in online mode. Otherwise, enter 2 to run the utility in offline mode.
Specify the batch size, when prompted.
Note:
Batch size is a value for the number of records to be processed in a single iteration of archival/purge also an internal commit at the database level. You must provide the batch size as an input parameter value while starting the operation of Archival Utilities at run time.This batch size by default is 2000. A higher batch size can be opted for, but this might require more resources from the database, such as more space from the TEMP and UNDO tablespaces.
The utility archives the request data and provides an execution summary in a log file.
On Microsoft Windows platforms, reset the short date format to the date format for your region or locale after you run the utility. Use the Regional and Language Options command in Control Panel to reset the date format.
Because the data from active request tables are removed, your DBA must analyze the active request tables and their indexes in order to update the statistics. Perform this step only if you are using Oracle Database as the database for Oracle Identity Manager.
All the logs are written to the logs/ directory created in the current folder. Table 19-5 lists the log files generated by the utility.
Table 19-5 Logs Generated by the DB Archival Utility
| Log File | Description |
|---|---|
|
oim_create_request_arch_tables.log |
Created when the utility fails to create the archival tables |
|
oim_request_archival.log |
Created when the utility fails to create the procedures required for archival |
|
validate_date.log |
Created when the input REQUEST_CREATION_DATE is invalid |
|
oim_request_archival_summary_TIMESTAMP.log |
Contains the summary of the run |
|
Err_DB_Conn_TIMESTAMP_ATTEMPTNUMBER.log |
Created when the utility is unable to connect to the database with the credentials provided |
This section describes how to use the Audit Archival and Purge utility. It contains the following topics:
Continuous data generation in the Oracle Identity Manager database schema and the audit data growth results in a gradual increase in the storage consumption of the database server. The audit data is populated in the UPA table. The growth of data in the UPA table can pose disk space and maintenance issues. Therefore, old audit data in the UPA table must be cleaned or archived.
To keep this disk space consumption in control, you can use the Audit Archival and Purge utility. This utility controls the growth of the audit data by purging the data in a logical and consistent manner.
Note:
The audit archival and purge solution is only applicable to the UPA table. It is not applicable to audit reporting tables, which are tables with the UPA_ prefix.
The utility is compatible with Oracle Identity Manager release 9.1.0 and later.
You must shut down Oracle Identity Manager to fetch the latest data, which is to retrieve EFF_TO_DATE as null records. You can retrieve the remaining data later when Oracle Identity Manager is running with the new partitioned UPA.
Oracle recommends partitioning of the UPA table on the basis of calendar year, which allows you to archive or drop partitions. The advantage of partitioning is that the old partitions can be archived or purged because Oracle Identity Manager does not use old audit data lying in those partitions. Oracle Identity Manager uses the latest audit data and the current calendar year data. Therefore, the UPA table is partitioned based on date range-partitioning approach by calender year using EFF_TO_DATE column. After partitioning, the latest audit data where EFF_TO_DATE is NULL, can be grouped in one partition, and there will be one partition for each calendar year. Oracle Identity Manager do not read or write into any other partitions except the latest and current year partitions.
For instance, if you are using Oracle Identity Manager audit feature since 2005 and implementing the audit archive and purge solution in calendar year 2011, then you will have seven partitions after this exercise, assuming that you create a partition for each calendar year. In those seven partitions, Oracle Identity Manager will only read or write the following partitions:
The latest partition
The partition for the current year, for example 2011
All the previous year partitions can be archived and then purged. If you do not want to archive, then you can purge those old partitions. You can reclaim the space by archiving and purging those old partitions. You must keep the latest and current year partitions untouched for Oracle Identity Manager to continue working.
The following prerequisites must be met before or when using the Audit Archival and Purge utility:
Database partitioning is supported only on Enterprise Edition of Oracle Database. Therefore, to implement the audit archival and purge solution, you must run Enterprise Edition of Oracle Database.
The UPA table must be range-partitioned. Time interval can be any value as per data distribution. Other modes of partition methods are not supported.
Make sure that the latest backup of the UPA table is available. Creating a backup of the UPA table is a compulsory prerequisite before applying this solution. It is recommended to try out this solution in the development or staging environment before implementing it on the production database.
Decide how many previous year's of audit data you require to keep online before implementing this solution. This helps in creating partitions beforehand.
Each partition should be placed on its own tablespace. Do not share the tablespace between partitions of different year or with some other data.
During partitioning, the audit data for each calendar year is copied into a table before it is moved into a final destination. You must have provision for disk space to hold the copied data.
To prepare the UPA table for the audit and purge solution:
Make sure that Oracle Identity Manager database has no transaction against it until the UPA table is partitioned.
Query the UPA table to get the minimum and maximum calendar year for the audit data. Following queries can help you get the minimum and maximum year. The maximum year should be the current calendar year.
SELECT EXTRACT (YEAR FROM MIN (eff_to_date)) min_year,EXTRACT (YEAR FROM MAX (eff_to_date)) running_year FROM upa;
This helps in deciding the partitions for each calendar year starting from minimum year.
Make sure that Oracle Identity Manager is not running and is not available for off-line utilities.
Create a new partition table.
Assuming 2005 as minimum year and 2011 as running or current calendar year, the following decisions are to be made before creating a newly partition table:
How many years of old audit data you want to keep? If it is important to keep only three years of audit data, then you have to create newly partitioned table starting from year 2008. The data older than 2008 will get cleaned up when the original UPA table gets dropped.
After deciding the years of old data to keep, the next question is how and where the old data should be kept? Do you want to keep all the old data partitions in the active UPA table, or create backup of the old partitions and then drop the old partitions? Oracle recommends moving the old partitions into tapes and then purging them from the UPA table. As stated earlier, you must keep the latest and running calendar year partition untouched.
The following sample assumes that you want to keep three years of audit data in UPA table and current calendar year is 2011:
SQL> SELECT 'Create Table UPA_PART
(
UPA_KEY NUMBER (19) Not Null,
USR_KEY NUMBER (19) Not Null,
EFF_FROM_DATE TIMESTAMP (6) Not Null,
EFF_TO_DATE TIMESTAMP (6),
SRC VARCHAR2 (4000),
SNAPSHOT CLOB,
DELTAS CLOB,
SIGNATURE CLOB
)
PARTITION BY RANGE (EFF_TO_DATE)
(PARTITION UPA_2008 VALUES LESS THAN (TO_DATE(''01/01/2009'', ''DD/MM/YYYY'')) Tablespace upa_2008,
PARTITION UPA_2009 VALUES LESS THAN (TO_DATE(''01/01/2010'', ''DD/MM/YYYY'')) Tablespace upa_2009,
PARTITION UPA_2010 VALUES LESS THAN (TO_DATE(''01/01/2011'', ''DD/MM/YYYY'')) Tablespace upa_2010,
PARTITION UPA_2011_PART1 VALUES LESS THAN (TO_DATE('''||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS')||''',''DD/MM/YYYY HH24:MI:SS'')) TABLESPACE UPA_2011_PART1,
PARTITION UPA_2011_PART2 VALUES LESS THAN (TO_DATE(''01/01/2012'',''DD/MM/YYYY'')) TABLESPACE UPA_2011_PART2,
PARTITION UPA_LATEST VALUES LESS THAN (MAXVALUE) TABLESPACE UPA_MAX
)
ENABLE ROW MOVEMENT;' FROM DUAL;
Create another non-partitioned table with similar structure as the UPA table, by running the following statement:
SQL> Create table upa_non_part Tablespace TBS_NAME as select * from upa where 1=2;
Here, TBS_NAME is the name of the same tablespace as of partition, which is to be exchanged.
This table is temporary in nature. The purpose of this table is to facilitate the loading of audit data to a newly partitioned UPA table.
Note:
UPA_NON_PART or temporary non-partitioned table must be created on same tablespace as the partition to be exchanged.Load the latest audit data into the non-partitioned UPA table, as shown:
SQL> Insert /*+ parallel */ into upa_non_part select /*+ parallel */ * from upa where eff_to_date is null; SQL> COMMIT;
Note:
Using hint /*+parallel*/ in the INSERT statement is optional and you can use other hints also to improve performance according to the available resources.Swap the data into the partitioned table by using the ALTER TABLE command, as shown:
SQL> ALTER TABLE upa_part EXCHANGE PARTITION UPA_LATEST WITH TABLE UPA_NON_PART WITH VALIDATION UPDATE GLOBAL INDEXES;
Drop the upa_non_part table, as shown:
SQL> DROP TABLE upa_non_part;
While exchanging partitions, the data dictionary is updated instead of writing data physically. Therefore, it is necessary to drop and re-create the temporary non-partitioned UPA_NON_PART table in the same tablesapce associated to the partition to be exchanged.
Rename the original non-partitioned UPA table to UPA_OLD, as shown:
SQL> ALTER TABLE upa rename TO upa_old;
Rename the newly partitioned UPA_PART table to UPA:
SQL> RENAME UPA_PART to UPA;
Manage the constraints for the new UPA table. To do so:
Rename the constraint from old UPA table to some other name, as shown:
ALTER TABLE UPA_old RENAME CONSTRAINT PK_UPA TO PK_UPA_old; ALTER INDEX IDX_UPA_EFF_FROM_DT RENAME TO IDX_UPA_EFF_FROM_DT_old; ALTER INDEX IDX_UPA_EFF_TO_DT RENAME TO IDX_UPA_EFF_TO_DT_old; ALTER INDEX IDX_UPA_USR_KEY RENAME TO IDX_UPA_USR_KEY_old; ALTER INDEX PK_UPA RENAME TO PK_UPA_OLD;
Create the necessary indexes and primary key constraint on the newly partitioned UPA table. Make sure to add storage characteristics, such as tablespace and size. To do so, run the following SQL query:
SQL>create index IDX_UPA_EFF_FROM_DT on UPA (EFF_FROM_DATE) Local; SQL>create index IDX_UPA_EFF_TO_DT on UPA (EFF_TO_DATE) Local; SQL>create index IDX_UPA_USR_KEY on UPA (USR_KEY) Local; SQL>ALTER TABLE UPA add constraint PK_UPA primary key (UPA_KEY) using index;
Note:
The global non-partitioned index is created to support the primary key. Global index becomes unusable every time a partition is touched. You must rebuild the index when required.Run the statistics collection for the UPA table, as shown:
SQL>Exec dbms_stats.gather_table_stats(ownname => 'SCHEMA_NAME',tabname => 'UPA',cascade => TRUE,granularity => 'GLOBAL and PARTITION');
Note:
Global statistics must be gathered by default. Oracle 11g includes improvements to statistics collection for partitioned objects so untouched partitions are not rescanned. This significantly increases the speed of statistics collection on large tables where some of the partitions contain static data. When a new partition is added to the table, you need to collect statistics only for the new partition. The global statistics is automatically updated by aggregating the new partition synopsis with the existing partitions synopsis.Start Oracle Identity Manager. The database is ready to be opened for transactions. Test and make sure that applications are running as expected.
Bring current year data in UPA_2011_PART1 to have all data and maintain consistency for current year. To do so, run the following SQL queries in sequence:
SQL> CREATE TABLE upa_non_part Tablespace TBS_NAME AS SELECT * FROM upa WHERE 1=2;
Here, TBS_NAME is the same tablespace name as of the partition, which is to be exchanged.
SQL> Alter Table UPA_NON_PART add constraint PK_UPA_NON_PART primary key (UPA_KEY) using index;
.............
.............
SQL> Insert into upa_non_part select * from upa_old where eff_to_date >= to_date('01/01/2011', 'mm/dd/yyyy');
.............
.............
SQL> COMMIT;
.............
.............
SQL> ALTER TABLE upa_part exchange partition UPA_2011_PART1 WITH table upa_non_part WITH VALIDATION UPDATE GLOBAL INDEXES;
.............
.............
SQL> Drop table upa_non_part;
If required, bring previous year's data into the newly partitioned UPA table. To do so:
Run the following SQL queries in sequence:
SQL> CREATE TABLE upa_non_part Tablespace TBS_NAME AS SELECT * FROM upa WHERE 1=2;
Here, TBS_NAME is the same tablespace as of the partition, which is to be exchanged.
.............
.............
SQL> Alter Table UPA_NON_PART add constraint PK_UPA_NON_PART primary key (UPA_KEY) using index;
.............
.............
SQL> Insert into upa_non_part select * from upa_old where eff_to_date >= to_date('01/01/YEAR', 'mm/dd/yyyy') and eff_to_date < to_date('01/01/<YEAR+1>', 'mm/dd/yyyy');
Here, YEAR is the year for which you want to bring the data into newly partitioned UPA table.
.............
.............
SQL>COMMIT;
.............
.............
SQL> Alter table upa exchange partition UPA_<year> with table upa_non_part with validation Update global indexes;
Rebuild indexes if they are unusable. The Following SQL query shows the indexes that are unusable:
SQL> Select index_name, partition_name, tablespace_name, status from user_ind_partitions;
Drop the table upa_non_part, as shown:
SQL> Drop table upa_non_part;
Note:
Repeat step 15 for each old year.All partition operations against UPA table are done and all the data is brought into. Run the statistics collection for the UPA table, as shown:
SQL>Exec dbms_stats.gather_table_stats(ownname => '<Schem_name>',tabname => 'UPA',cascade => TRUE,granularity => 'GLOBAL and PARTITION');
Drop the UPA_OLD table if it is not required. You can create a backup of this table before dropping.
Archiving and purging the UPA table is described in the following sections:
Oracle Identity Manager always requires the latest and the current calendar year audit data. The following are the names of latest and calendar year partitions:
UPA_LATEST: The latest partition
UPA_2011_PART1 and UPA_2011_PART2: Partitions for the current year if current year is 2011
You must keep these two partitions untouched for Oracle Identity Manager to continue working. These two partitions should never be archived or purged.
A new partition must be added to the UPA table before the new calendar year arrives. To do so, use the following SQL template:
SQL> Alter table UPA split partition UPA_LATEST at (TO_DATE('01/01/YEAR+1','DD/MM/YYYY')) into (partition UPA_YEAR tablespace UPA_YEAR,partition UPA_LATEST tablespace UPA_MAX) update global indexes;
Here, YEAR in the TO_DATE function represents the new calendar year plus one. YEAR for partition name and tablespace name represents new upcoming calendar year.
An example of SQL statement for adding new partition for new calendar year 2012 is as follows:
SQL> Alter table UPA split partition UPA_LATEST at (TO_DATE('01/01/2013','DD/MM/YYYY')) into (partition UPA_2012 tablespace UPA_2012,partition UPA_LATEST tablespace UPA_MAX) update global indexes;
Oracle recommends adding new partition with the given SQL template before the new calendar year arrives. However, if you do not add the same before the arrival of the next calender year, then the same can be done after the next year has started by using the same SQL command.
To archive or purge partitions in the UPA table:
If you use the attestation feature of Oracle Identity Manager, then make sure that the partition to be archived or purged does not have any active attestation records. You can use the following SQL to verify that.
SQL> SELECT COUNT(1) FROM UPA PARTITION(<PARTITION_TO_BE_DROPPED>) WHERE UPA_KEY IN (select distinct (upa_key) from apt apt, atr atr, atd atd where apt.atr_key=atr.atr_key and atr.atr_completion_time is NULL and apt.apt_key = atd.apt_key);
This query should return zero records, which means there are no active attestation records. If this returns non-zero value, then it means that there are still active attestations pointing to the partition to be dropped. This is not common, but you must make sure that there are no active attestation records before dropping an old year partition.
Make sure that there are no custom reports or queries that needs the data from partition to be dropped.
Archive the partition to be dropped to tape or any other media. There are many ways to archive a partition. One of the ways is to use data pump or export utility to archive the partition to be dropped. Choose a way that works best in your environment.
Purge the partition. To do so:
SQL> Alter table UPA drop partition PARTITION_NAME UPDATE GLOBAL INDEXES; SQL>Drop tablespace TBS_NAME including contents and datafiles;
Here, TBS_NAME is the tablespace associated with the partition to be dropped, and it must not contain any other data.
Note:
The current year contains two partitions named UPA_2011_PART1 and UPA_2011_PART2. When current year becomes an old year and the data for that is ready to be archived or purged, make sure to archive or purge these two partitions.
It is your responsibility to restore the archived data later, if required.