9 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

9.1 Understanding the Reconciliation Archival Utility

Note:

The Reconciliation Archival utility is backward-compatible from Oracle Identity Manager release 8.5.3.x onward.

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

• RCA

• RCB

• RCD

• RCE

• RCH

• RCM

• RCP

• RCU

• RPC

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 following tables, called archive reconciliation tables, which have the same structure as the active reconciliation 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 or specific data from the active reconciliation tables to the archive reconciliation tables

• Delete all data from the archive reconciliation tables

• Delete all 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 reconciliation event status parameters. 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, whereas the Event Closed status represents events that are manually closed with Reconciliation Manager.

To reduce the time that the archiving process takes, the utility drops the indexes on all active reconciliation 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 tables. If required, you can change the value 200000 to any other value by editing the following line:

• In the OIM_ReconArch.bat file, change the following line:

  set INDXRESP=200000
  

• In the OIM_ReconArch.sh file, change the following line:

  INDXOPT=200000
  

If you choose to archive selective data, then the utility archives data that falls in the specified date range 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 with event status of Event Linked or Event Closed.

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

installServer/xellerate/db/oracle/Utilities/ReconArchival

The files that constitute the Microsoft 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.

9.2 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 SQL*Plus and connect to Oracle Database as SYS user.

2. Create a separate tablespace 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.

   If your Oracle Database instance is running in ARCHIVELOG mode, you must switch to NOARCHIVELOG mode before running the Recon Archival utility. See Oracle Database Administrator's Guide for information about changing the database archiving mode.

3. To be able to use the utility, the Oracle Identity Manager database user must be explicitly granted the CREATE TABLE privilege. To grant this privilege to the database user, replace OIM_DB_USER with the Oracle Identity Manager database user ID in the following command, and then run the command:

   GRANT CREATE TABLE TO OIM_DB_USER
   

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

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

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

7. 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 reconciliation data:

   @ path/OIM_SP_ReconArchival.sql
   

9.3 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 Microsoft SQL Server 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 in which 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_RECON_ARCH file group, which the Reconciliation Archival utility uses to store date from archival reconciliation tables.

   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 Microsoft 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 reconciliation data.

9.4 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. In addition, ensure that the Oracle Identity Manager database is not open to transactions for other sessions.

   Note:

   Oracle recommends that you run the Reconciliation Archival utility during off-peak hours.

2. Stop the Oracle Identity Manager by following the instructions in the Oracle Identity Manager installation guide for your application server.

3. 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, use the Regional and Language Options command in Control Panel.

   Note:

   When you change the date and time format, the change will be applied to all the applications running on the Microsoft Windows platform.

4. On Linux and 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 and UNIX text file:

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

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

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

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

   • Server name on which the Microsoft SQL Server database is running

   • Oracle Identity Manager database name

   • Oracle Identity Manager database user name and password

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

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

   1. Select one of the following archival options:

      • Archive selective data

      • Archive all data

      • Exit

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

      Caution:

      You must enter an end date that is later than or equal to the start date. Otherwise, data will not be archived.

   3. 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 and Linked

9. Enter a value of y or Y when prompted to archive the data. Alternatively, enter a value of n or N to exit the utility.

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

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

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

9.5 Output Files Generated by the Reconciliation Archival Utility

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

Table 9-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

Note:

These error log files are deleted when you run the utility again.