8 Using the Reconciliation Archival Utility

This chapter describes how to use the Reconciliation Archival utility. It contains the following topics:

• Understanding the Reconciliation Archival Utility

• Preparing Oracle Database for the Reconciliation Archival Utility

• Preparing Microsoft SQL Server for the Reconciliation Archival Utility

• Running the Reconciliation Archival Utility

• Output Files Generated by the Reconciliation Archival Utility

Understanding the Reconciliation Archival Utility

Oracle Identity Manager stores reconciliation data from target systems in the following tables, which are called called active reconciliation tables:

• RCA

• RCB

• RCD

• RCE

• RCH

• RCM

• RCP

• RCU

• RPC

During the reconciliation process, Reconcilation Manager reconciles data in the active reconcilation tables with Oracle Identity Manager's core tables. Because Reconcilation Manager does not remove reconciled data from the active reconcilation tables, they may 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 following tables, called archive reconciliation tables, which have the same structure as the active reconcilation tables:

• ARCH_RCA

• ARCH_RCB

• ARCH_RCD

• ARCH_RCE

• ARCH_RCH

• ARCH_RCM

• ARCH_RCP

• ARCH_RCU

• ARCH_RPC

You can use the Reconciliation Archival utility to perform the following tasks:

• Archive all data or specified from the active reconciliation tables to the archive reconciliation tables

• Delete data from the archive reconciliation tables

• Delete data from the active reconciliation tables

When you archive selective data from the active reconciliation tables to the archive reconciliation tables, you must specify start date, end date, and reconcilation event status parameters to determine which data to archive. Start and end dates must be in the format YYYYMMDD. For the reconciliation event parameter, you can choose Event Linked, Event Closed, or both. The Event Linked status represents events that are successfully reconciled into Oracle Identity Manager while the Event Closed' status represents events that are manually closed with Reconciliation Manager. If you choose to archive selective data, the utility disables foreign key constraints on all active reconciliation tables. The foreign key constraints will be reenabled after the archived data is deleted from the active reconciliation tables.

When archive all data from the active reconciliation tables to the archive reconciliation tables, the Reconciliation Archival utility archives all reconciliation data with a status of Event Linked or Event Closed.

The files that make up the Oracle Database version of the Reconciliation Archival utility are located in the following directory:

installServer/xellerate/db/oracle/Utilities/ReconArchival

The files that make up the SQL Server version of the Reconciliation Archival utility are located in the following directory:

installServer/xellerate/db/sqlserver/Utilities/ReconArchival

Note:

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.

Preparing Oracle Database for the Reconciliation Archival Utility

Before you can use the Reconciliation Archival utility with Oracle Database, you must perform the following steps:

1. Start Oracle SQL*Plus and connect to Oracle Database as SYS user.

2. Create a separate table space for the archival reconciliation tables by entering the following command. Replace DATA_DIR with the directory where you want to store the data file and adjust the size and other parameters as necessary for your environment.

   CREATE TABLESPACE OIM_RECON_ARCH
       DATAFILE 'DATA_DIR\reconarch_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.

3. Connect to the Oracle Database as the Oracle Identity Manager database user.

4. Enter the following command to run the Create_recon_arch_tables.sql script, which creates the archive reconciliation tables:

   @ path/Create_recon_arch_tables.sql
   
   

5. Enter the following command to run the cr_recon_ddl_table.sql script, which creates a table named oim_recon_ddl. The oim_recon_ddl table is used by the Reconciliation Archival utility.

   @ path/cr_recon_ddl_table.sql
   
   

6. Enter the following command to run the OIM_SP_ReconArchival.sql script, which creates a stored procedure that the Reconciliation Archival utility uses to archive and delete reconcilation data:

   @path/OIM_SP_ReconArchival.sql
   
   

Preparing Microsoft SQL Server for the Reconciliation Archival Utility

Before you can use the Reconciliation Archival utility with Microsoft SQL Server, you must perform the following steps:

1. Start SQL Query Analyzer and connect to SQL Server with as a user that is a member of sysadmin, or who has a dbcreator server role or db_owner database role.

2. Enter the following commands. Replace DATA_DIR with the directory where you want to store the data file and adjust the SIZE, MAXSIZE, and FILEGROWTH parameters as necessary for your environment. These commands create the OIM_ARCH_RECON file group, which the Reconciliation Archival utility uses to store archival reconciliation data.

   USE master
   GO
   ALTER DATABASE oim_database_name
   ADD FILEGROUP OIM_RECON_ARCH
   GO
   ALTER DATABASE oim_database_name 
   ADD FILE 
    (NAME = OIM_RECON_ARCH_01,
     FILENAME = 'DATA_DIR\RECON_ARCH_01.NDF',
     SIZE = 1000MB,
     MAXSIZE = 5000MB,
     FILEGROWTH = 25MB)
   TO FILEGROUP OIM_RECON_ARCH
   GO
   
   

3. Disconnect from SQL Server and reconnect again as the Oracle Identity Manager database user.

4. Load and execute the path/Create_recon_arch_tables.sql script, which creates the archive reconciliation tables.

5. Load and execute the path/OIM_SP_ReconArchival.sql script, which creates a stored procedure that the Reconciliation Archival utility uses to archive and delete reconcilation data.

Running the Reconciliation Archival Utility

Perform the following steps to run the Reconciliation Archival utility:

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

2. On Linux/UNIX platforms, run the following commands to set execution permission for the OIM_ReconArch.sh file and to ensure that the file is a valid Linux/UNIX text file:

   chmod 755 path/OIM_ReconArch.sh
   dos2unix path/OIM_ReconArch.sh
   
   

3. On Linux/UNIX platforms, run the path/OIM_ReconArch.sh file. On Windows platforms, run the path\OIM_ReconArch.bat file.

4. 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 machine

   • Oracle Identity Manager database user name and password

   For Microsoft SQL Server installations, enter values for the following parameters when prompted:

   • Server name where the SQL Server database is running

   • Oracle Identity Manager database name

   • Oracle Identity Manager database user name and password

5. When prompted, select one of the following options:

   • 1) Archive data from active reconciliation tables

   • 2) Delete all data from archival reconciliation tables

   • 3) Delete all data from active reconciliation tables

   • 4) Exit

6. If you selected to archive data, perform the following procedures:

   1. Select one of the following archival options:

      1) Archive selective data

      2) Archive selective data by dropping and recreating indexes for faster performance

      3) Archive all data

      4) Exit

   2. If you chose to archive selective data, enter start and end dates in the format YYYYMMDD when prompted.

      WARNING:

      Be sure to enter an end date that is later than or equal to the start date or no data will be archived.

   3. Select reconciliation event status for the data that you want to archive:

      - Enter '1' for Closed

      - Enter '2' for Linked

      - Enter '3' for Closed and Linked

7. If you selected to delete data from either the archival reconciliation tables or active reconciliation tables, enter Y when prompted to confirm that you want to delete the data.

Output Files Generated by the Reconciliation Archival Utility

Table 8-1 describes the output files that are generated by the Reconciliation Archival utility.

Table 8-1 Output Files Generated by the Reconciliation Archival Utility

File	Description
Err_DB_Conn_timestamp.log	Generated when the utility is unable to connect to the database with the provided credentials
Err_Arch_Recon_timestamp.log	Generated when the archival or deletion processes fail
Arch_Recon_timestamp.log	Generated when the archival or deletion processes succeed