Skip Headers
Oracle® Identity Manager Tools Reference
Release 9.1.0.2

Part Number E14763-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

15 Bulk Load Utility

Oracle Identity Manager may be one among many repositories of user data in your organization. When you start using Oracle Identity Manager, you might want to load data from the other repositories into Oracle Identity Manager. The Bulk Load utility offers a solution to this requirement.

The Bulk Load utility is aimed at automating the process of loading a large amount of data into Oracle Identity Manager. It helps reduce the downtime involved in loading data. You can use this utility either immediately after you install Oracle Identity Manager or at any time during the production lifetime of Oracle Identity Manager.

The Bulk Load utility can use either CSV files or database tables as the source of data. Data imported into Oracle Identity Manager is automatically converted into OIM Users or accounts provisioned to OIM Users.

This document is divided into the following sections:

15.1 Features of the Bulk Load Utility

The following are features of the bulk load utility:

15.2 Installing the Bulk Load Utility

To install the utility:

  1. Copy the following directory from the installation package into a directory on the Oracle Identity Manager database host computer:

    InstallServer/Xellerate/db/oracle/Utilities/oimbulkload

    Note:

    You cannot use the utility to load data to a remote database.
  2. Extract the contents of the ZIP file.

    The oimbulkload directory is created when you extract the contents of the ZIP file. The following directories are created inside this directory:

    • sqls: This directory contains SQL scripts used during bulk load operations.

    • scripts: This directory contains the .sh and .bat scripts used during bulk load operations.

    • csv_files: If you are going to use a single or multiple CSV files as the input source, then the CSV files must be stored in this directory.

    • lib: The directory contains the oimBulkLoad.jar and ojdbc5.jar files. These files are used by the utility during bulk load operations.

      Sample Data: This directory contains the following sample CSV files:

      • For OIM User load operations:

        master.txt

        OIDusers.csv

        HRusers.csv

      • For account load operations:

        parentAD.csv

        childAD.csv

    • Logs_ YYYYMMDD_hhmi: The log directory contains the log files that store the summary of the bulk load operation. This directory is created at run time.

The following sections provide additional information about the utility and bulk load operations:

15.2.1 Scripts That Constitute the Utility

The following are the main scripts that constitute the utility:

  • oim_blkld.bat and oim_blkld.sh

    This script contains the code to perform bulk load operations. When it is run, this script calls other scripts and stored procedures.

  • oim_blkld_setup.sql

    This script is used to add a datafile in the Oracle Identity Manager tablespace. The "Creating a Datafile in the Oracle Identity Manager Tablespace" section of this document provides more information.

15.2.2 Options Offered by the Utility

When you run the bulk load utility, it prompts you to select one of the following options:

Note:

The utility prompts for more input depending on the option you select.
  • Load User Data

    You select this option if you want the utility to load OIM User data. In other words, data is imported into the USR table of Oracle Identity Manager. In addition, you can select the input source, CSV files or database tables, for the data that you want to load.

  • Load Account Data

    You select this option if you want the utility to load account data. In other words, data is imported into the relevant UD_ tables of Oracle Identity Manager. In addition, you can select the input source, CSV files or database tables, for the data that you want to load.

  • Generate Audit Snapshot

    You select this option if you want the utility to generate an audit snapshot of data that you have loaded.

15.3 Temporary Tables Used During a Bulk Load Operation

The following temporary tables are used during a bulk load operation:

15.4 Loading OIM User Data

The following is a summary of the steps involved in loading OIM User data:

  1. If required, create a tablespace for the temporary tables used during the bulk load operation.

  2. If required, create a datafile in the Oracle Identity Manager tablespace.

  3. Create the OIM User whose password will be used as the default password for all OIM Users created during the bulk load operation.

  4. Create the input source for the bulk load operation.

    If you want to use a database table as the input source, then create the table and copy user data into the table.

    If you want to use CSV files as the input source, then create the CSV files and copy user data into the files. In addition, create a master.txt file containing the names of the files in the sequence in which you want to load data from them.

  5. Determine values for the input parameters of the utility.

  6. Stop Oracle Identity Manager.

  7. Run the oim_blkld script.

  8. Monitor the progress of the bulk load operation.

  9. Determine the outcome of the bulk load operation.

  10. If required, reload data that was not loaded during the first run.

  11. Restart Oracle Identity Manager.

  12. Verify the outcome of the bulk load operation.

  13. Gather performance data from the operation.

  14. Remove temporary tables and files created during the operation.

  15. Generate an audit snapshot.

The following sections provide detailed information about the steps involved in loading OIM User data:

15.4.1 Creating a Tablespace for Temporary Tables

As mentioned in "Temporary Tables Used During a Bulk Load Operation", temporary database tables are used during the bulk load operation. It is recommended that you create a tablespace to accommodate these temporary tables instead of using the default tablespace of the Oracle Identity Manager database.

Follow the instructions in the database documentation to create a tablespace.

15.4.2 Creating a Datafile in the Oracle Identity Manager Tablespace

The default size of the datafile in the Oracle Identity Manager tablespace created during Oracle Identity Manager installation is 500 MB. You may need to add space to this datafile to accommodate the data that you are going to load. The alternative is to create a datafile.

To create a datafile in the Oracle Identity Manager tablespace:

  1. Start a SQL*Plus session.

  2. Connect to the Oracle Identity Manager database as SYSDBA.

  3. Run the oim_blkld_setup.sql script. The script will prompt for the following:

    • Name of the Oracle Identity Manager tablespace

    • Full path and name for the datafile to be added in the Oracle Identity Manager tablespace

    • Oracle Identity Manager database user name

15.4.3 Setting a Default Password for OIM Users Added by the Utility

The utility does not encrypt passwords that it assigns to OIM Users created during the bulk load operation. Instead, it assigns the password of an existing OIM User to all OIM Users that are created during the operation.

Note:

Each OIM User is required to change the password at first login.

When you run the utility, it prompts for the login name of the existing OIM User whose password you want to use as the default password for the new OIM Users. Before you run the utility, create this OIM User as follows:

See Also:

Oracle Identity Manager Administrative and User Console Guide for detailed information about creating OIM Users
  1. Log in to the Oracle Identity Manager Administrative and User Console as a user with Create User privileges.

  2. On the left navigation pane, click Users and then click Create.

  3. Specify values for the following fields:

    • User ID

    • First Name

    • Last Name

    • Organization: Select Xellerate Users.

    • Password

    • Confirm Password

  4. Click Create User.

15.4.4 Creating the Input Source for the Bulk Load Operation

Depending on the input source that you want to use, apply the guidelines given in one of the following sections:

15.4.4.1 Using CSV Files As the Input Source

If you want to use CSV files as the input source for the bulk load operation, then apply the following guidelines while creating the CSV files:

  • The CSV files must be placed in the oimbulkload/csv_files directory.

  • The first line in the CSV file is called the control line. This line must contain a comma-separated list of column names of the USR table in the Oracle Identity Manager database.

    Note:

    Ensure that the Password column or any other encrypted column is not included in the list of columns. As mentioned earlier in this document, the utility assigns the password of an existing OIM User that you specify to all OIM Users that it loads into Oracle Identity Manager.
  • From the second line onward, the file must contain values for the columns in the control line. The order of columns in the first line and the values in the rest of the lines must be the same.

    The following are sample contents of a CSV file:

    USR_LOGIN,USR_FIRST_NAME,USR_LAST_NAME
    john_doe, John, Doe
    jane_doe, Jane, Doe
    richard_roe, Richard, Roe
    
  • If the value in any column contains a comma, then that value must be enclosed in double quotation marks (").

  • The CSV file must contain values for all columns that are designated as mandatory in the USR table.

  • Each row in the CSV file must have a unique value for the USR_LOGIN column in the USR table. If there are multiple files, you must ensure that USR_LOGIN values are unique across the CSV files. This check for uniqueness of USR_LOGIN values must also cover existing OIM Users in Oracle Identity Manager.

    Ensuring that USR_LOGIN values are unique can be a time-consuming exercise. As an alternative, you can first perform the bulk load operation, fix USR_LOGIN values that are not unique, and then retry the loading operation for the modified user records. This is possible because the utility checks for uniqueness of USR_LOGIN values at run time and copies records that fail this check into the OIM_BLKLD_EX table. Later in this document, there are instructions on retrying the bulk load operation for records that are not loaded during the first run.

  • If you want to include an organization name in each user record, then add ORG_NAME in the control line and enter the organization name for each user from the second line onward.

    Note:

    All organization names listed under the ORG_NAME column in the CSV file must exist in Oracle Identity Manager.
  • If you want to include a manager name in each user record, then add MANAGER_NAME in the control line and enter the USR_LOGIN value of the manager for each user from the second line onward.

    The utility looks up the USR_LOGIN values for managers after all user data, from all CSV files, is loaded into Oracle Identity Manager. If a USR_LOGIN value given in the MANAGER_NAME column does not exist in Oracle Identity Manager, then the lookup for that user record fails and the record is copied into the exception table, OIM_BLKLD_EX. At the end of the bulk load operation, you can perform the procedure described in "Fixing Exceptions and Reloading Data Records" to reload user records that fail the first run.

  • Note that the following default values are inserted into Oracle Identity Manager if the CSV file does not contain values for these columns:

    ORG_NAME: Xellerate Users

    USR_TYPE: End-User

    USR_STATUS: Active

    USR_EMP_TYPE: Full-Time

  • Create a master TXT file containing the names of the CSV files containing user data to be loaded. You can specify any name for the file, for example, master.txt. Save the master file in the oimbulkload/csv_files directory.

    If you want to load multiple CSV files, then enter the name of each data CSV file on a separate line in the master file. Order the list of CSV file names in the sequence in which you want the utility to load data from the files. For example, suppose you have created three data CSV files, London_Users.csv, NewYork_Users.csv, and Tokyo_Users.csv. In the master file, you enter the names of the data CSV files in the following order:

    Tokyo_Users.csv
    London_Users.csv
    NewYork_Users.csv
    

    When you run the utility, data is loaded in this order.

15.4.4.2 Creating Database Tables As the Input Source

If you want to use a database table as the input source for loading OIM User data, then apply the following guidelines while creating the database table:

  • Create the table in the Oracle Identity Manager database.

  • The table must contain the following primary key column:

    OIM_BLKLD_USRSEQ NUMBER(19)

    The utility uses this column as the primary key. If required, you can use a database sequence to populate this column.

  • The rest of the columns must be the same as the ones in the USR table that you want to use. In other words, ignore optional USR_ columns that you do not want to include in the table that you create.

    Note:

    The USR_LOGIN column is a mandatory column.
  • Note that the following default values are inserted into Oracle Identity Manager if the table does not contain values for these columns:

    ORG_NAME: Xellerate Users

    USR_TYPE: End-User

    USR_STATUS: Active

    USR_EMP_TYPE: Full-Time

Table 15-1 shows the structure of a sample database table.

Table 15-1 Structure of a Sample Database Table

Name Null? Type

USR_LOGIN

NOT NULL

VARCHAR2(256)

USR_FIRST_NAME

 

VARCHAR2(80)

USR_LAST_NAME

 

VARCHAR2(80)

. . .

. . .

. . .

OIM_BLKLD_USRSEQ

NOT NULL

NUMBER(19)


15.4.5 Determining Values for the Input Parameters of the Utility

The following are input parameters of the utility:

  • Oracle Home

    Value of the ORACLE_HOME environment variable on the host computer for the Oracle Identity Manager database

  • Oracle SID

    SID of the Oracle Identity Manager database. In an Oracle RAC environment, you run the utility on one of the nodes. Provide the SID of that node.

  • Port Number

    Port on which the Oracle Identity Manager database is running. In an Oracle RAC environment, you run the utility on one of the nodes. Provide the port number of that node.

  • OIM DB User

    Database login ID of the Oracle Identity Manager database user

  • OIM DB Pwd

    Password of the Oracle Identity Manager database user

  • IP Address

    IP address of the computer on which you run the utility. In an Oracle RAC environment, you run the utility on one of the nodes. Provide the IP address of that node.

  • Master file name

    Name of the file containing names of the CSV data files to be loaded

    This parameter is used only if the input source is a single or multiple CSV files. You place the master file and CSV data files in the oimbulkload/csv_files directory. See "Using CSV Files As the Input Source" for more information.

  • Tmp table name

    Name of the temporary table to be used as the input source

    This parameter is used only if the input source for the bulk load operation is a database table. See "Creating Database Tables As the Input Source" for more information.

  • Control Line

    Comma-separated list of names of columns to be loaded from the database table into Oracle Identity Manager

    This parameter is used only if the input source for the bulk load operation is a database table.

  • Tablespace Name

    Name of the tablespace in which temporary tables are to be created during the bulk load operation

    See "Creating a Tablespace for Temporary Tables" for more information.

  • Date format

    Date format used by date columns in the CSV files

    This parameter is used only if the input source is a single or multiple CSV files.

  • Batch Size

    Number of user records that must be processed by the utility as a single transaction

    The batch size can influence the performance of the bulk load operation. The default value of this parameter is 10000.

  • Debug Flag

    You can specify Y or N as the value of this parameter. If this parameter is set to Y, then the utility records detailed information about events that occur during the bulk load operation. See "Data Recorded During the Operation" for more information.

  • User ID for default password

    Login name of the OIM User that you create by performing the procedure described in "Setting a Default Password for OIM Users Added by the Utility".

15.4.6 Running the Utility

Note:

If there are name conflicts with existing tables, then the utility overwrites existing temporary tables at the start of each run. If required, rename temporary database tables created during an earlier run of the utility.

To run the utility:

  1. Stop Oracle Identity Manager.

  2. Run one of the following scripts on the computer on which the Oracle Identity Manager database is configured:

    • On UNIX computers:

      OIMBulkload/script/oim_blkld.sh

    • On Microsoft Windows computers:

      OIMBulkload\script\oim_blkld.bat

  3. From the main menu, select Load User data.

  4. From the second menu:

    • Select CSV File if you are using CSV files as the input source.

    • Select DB Table if you are using a database table as the input source.

  5. When prompted, provide values for the input parameters described earlier in this document.

  6. Monitor the performance of the operation by following the steps given in "Monitoring the Progress of the Operation".

15.4.7 Monitoring the Progress of the Operation

The following sections provide information that you can apply to monitor the progress of the operation:

15.4.7.1 Data Recorded During the Operation

During the bulk load operation, the utility inserts progress and error messages in the OIM_BLKLD_LOG table. Data in this table is not deleted at the start of a new bulk load operation. One of the columns in this table holds the time stamp at which messages are recorded in the table.

Table 15-2 describes the structure of the OIM_BLKLD_LOG table.

Table 15-2 Structure of the OIM_BLKLD_LOG Table

Name Null? Type Description

MSG_SEQ_NO

NOT NULL

NUMBER(19)

This column stores the number that denotes the order in which messages are inserted in this table. The column is populated using the OIM_BLKLD_LOG_SEQ sequence. You can use this column to query for messages in the order in which they are recorded in the table.

MODULE

NOT NULL

VARCHAR2(20)

This column stores one of the following values:

USER: This value indicates that the message has been recorded while loading OIM User data.

ACCOUNT: This value indicates that the message has been recorded while loading account data.

LOG_LEVEL

NOT NULL

VARCHAR2(20)

This column stores one of the following values:

ERROR

DEBUG

PROGRESS_MSG

LOAD_SOURCE

NOT NULL

VARCHAR2(40)

This column indicates the source of data for the bulk load operation during which the row was inserted. The value can be one of the following:

CSV File: filename

DB Table

MSG

NOT NULL

VARCHAR2(4000)

This column stores a message corresponding to the value stored in the LOG_LEVEL column.

CREATE_DATE

 

DATE

This column holds the time stamp at which the record was created. The format for entries in this column is as follows:

yyyy/mm/dd hh24:mi:ss

For example:

2008/06/23 21:49:16:32


15.4.7.2 Querying the OIM_BLKLD_LOG Table for Progress and Error Messages

During the bulk load operation, you can query the OIM_BLKLD_LOG table for information about the progress of the operation. For example, you can run the following query to see progress messages generated during the bulk load operation to load OIM User data:

SELECT MSG FROM OIM_BLKLD_LOG
WHERE MODULE = 'USER' AND LOG_LEVEL = 'PROGRESS_MSG'
ORDER BY MSG_SEQ_NO;

Errors encountered during the bulk load operation can be viewed by querying the OIM_BLKLD_LOG table. The following is an example of the query to retrieve error messages:

SELECT MSG FROM OIM_BLKLD_LOG 
WHERE MODULE = 'USER' AND LOG_LEVEL = 'ERROR'
ORDER BY MSG_SEQ_NO;

15.4.8 Handling Exceptions Recorded During the Operation

At the end of a bulk load operation, the utility records statistics related to the operation in the following file:

oimbulkload/logs_YYYYMMDD_hhmm/oim_blkld_user_load_summary.log

To determine if there were exceptions during the operation, open this log file and look for the number against the Number of Records Rejected label. If the number of rejected records is greater than zero, then exceptions were thrown during the operation. User records that are rejected by the utility are recorded in the exception table (OIM_BLKLD_EX_SUFFIX). For each rejected record, the EXCEPTION_MSG column in the OIM_BLKLD_EX_SUFFIX table stores information about the reason the record could not be loaded.

Example 15-1 shows sample statistics recorded in the log file at the end of a bulk load operation to store OIM User data.

Example 15-1 Sample Log File Generated After Loading OIM User Data

****************************************************************

Processing File: u10.csv
================================================================
U S E R    L O A D    S T A T I S T I C S    F O R   F I L E : u10.csv
================================================================
Start Time:   08-AUG-08 11.44.12.228000 AM
End Time:     08-AUG-08 11.44.13.368000 AM
Number of Records Processed:  10
Number of Records Loaded:     8
Number of Records Rejected:   2
================================================================
The name of the TMP table used during the load:
OIM_BLKLD_TMP_U101

The name of the Exception table used during the load:
OIM_BLKLD_EX_U101

****************************************************************
Processing File: u10b.csv

================================================================
U S E R    L O A D    S T A T I S T I C S    F O R   F I L E : u10b.csv
================================================================
Start Time:   08-AUG-08 11.44.15.368000 AM
End Time:     08-AUG-08 11.44.15.540000 AM
Number of Records Processed:  16
Number of Records Loaded:     15
Number of Records Rejected:   1
================================================================
The name of the TMP table used during the load:
OIM_BLKLD_TMP_U10B2

The name of the Exception table used during the load:
OIM_BLKLD_EX_U10B2
================================================================

================================================================
Time taken in re-building indexes and enabling FK constraints
================================================================
Start time:      08-AUG-08 11.44.15.556000 AM
End Time:        08-AUG-08 11.46.50.586000 AM
================================================================

In this sample, the number of rejected records is 2. If the log file shows that any records were rejected by the utility, then see "Fixing Exceptions and Reloading Data Records" for information about retrying the load operation for these records.

Note:

At the end of each bulk load operation, it is recommended that you create a backup of the exception tables.

15.4.9 Fixing Exceptions and Reloading Data Records

As mentioned earlier, errors encountered during the bulk load operation can be viewed by querying the OIM_BLKLD_LOG table. The following is an example of the query to retrieve error messages:

SELECT MSG FROM OIM_BLKLD_LOG 
WHERE MODULE = 'USER' AND LOG_LEVEL = 'ERROR'
ORDER BY MSG_SEQ_NO;

An exception table OIM_BLKLD_EX_SUFFIX is created for each data table used as the input source during the bulk load operation. Records that do not meet the criteria for the operation are copied into this exception table. The suffix appended to the name of each exception table is the same as suffix appended to the name of the corresponding data table.

To reload rejected records:

  1. Create a backup of the exception table in which rejected records are stored.

    Note:

    Although this is an optional step, it is recommended that you create a backup.
  2. Review each record in the exception table, and fix errors in the data based on the message recorded in the EXCEPTION_MSG column.

  3. After you fix errors in all the rejected records in an exception table, rename the table to OIM_BLKLD_TMP_SUFFIX and then use it as the input source.

  4. Load records from the OIM_BLKLD_TMP_SUFFIX table by running the utility. See "Running the Utility" for more information.

  5. Repeat Steps 1 through 4 until the Number of Records Rejected label in the oim_blkld_account_load_summary.log file shows the value 0.

  6. Restart Oracle Identity Manager.

15.4.10 Verifying the Outcome of the Bulk Load Operation

To verify the outcome of the bulk load operation, check if you are able to perform the following steps for one of the OIM User added by the utility:

See Also:

Oracle Identity Manager Administrative and User Console Guide for detailed information about these procedures
  • Log in as the OIM User. The system should prompt you to change the password.

  • Provision a resource for the OIM User.

  • Add the OIM User to a group.

  • Modify the account profile of the OIM User.

  • Revoked the resource provisioned to the OIM User.

  • Unassign the OIM User from the group to which the user was added earlier.

  • Modify the account profile again to restore the profile to its original state.

  • Check if the User Resource Access report (an operational report) and the User Resource Access History report can be generated for the user.

  • Create an Attestation and check its status using the Diagnostic Dashboard.

15.4.11 Gathering Performance Data from the Bulk Load Operation

As mentioned earlier in this document, the following log file is created during the bulk load operation:

oimbulkload/logs_YYYYMMDD_hhmm/oim_blkld_account_load_summary.log

Data recorded in this file can be used to collate performance-related information about the bulk load operation. The following information can be collected after the bulk load operation:

  • Start time

  • Input source

  • Number of records in the system before the load

  • Number of records successfully loaded

  • Number of records rejected

  • Total time taken

You can use this information during future runs of the utility. If you want to retain information about the bulk load operation, then move this file to a permanent location.

15.4.12 Cleaning Up After a Bulk Load Operation

If you do not want to save the results of a bulk load operation, then:

  • Remove the OIM_BLKLD_TMP_SUFFIX, OIM_BLKLD_EX_SUFFIX, and OIM_BLKLD_LOG tables.

  • Remove any files that you created or used during the operation.

  • If you created a tablespace for the operation, then remove the tablespace.

  • See "Gathering Performance Data from the Bulk Load Operation" before you remove log files created in the logs_timestamp directory.

    Note:

    At this point, you can restart Oracle Identity Manager if you have not already done so.

15.4.13 Generating an Audit Snapshot

If required, you can generate an audit snapshot of Oracle Identity Manager data after a bulk load operation. The utility uses the audit utility shipped with Oracle Identity Manager release 9.1.0. Internally, the GenerateSnapshot script is called when you run the audit utility. Similarly, the GenerateSnapshot script is called when you select the option to generate an audit snapshot.

Note:

Oracle Identity Manager must be up and running when you run the audit utility.

Before you generate an audit snapshot:

  1. Open the OIM_HOME/bin/GenerateSnapshot.sh (or GenerateSnapshot.bat) file in a text editor.

  2. In this file, search for the following line:

    • In the GenerateSnapshot.bat file:

      SET XEL_HOME=..

    • In the GenerateSnapshot.sh file:

      XEL_HOME=..

  3. Replace this line with the following line:

    • In the GenerateSnapshot.bat file:

      SET XEL_HOME=<Full path of the OIM_HOME directory>

    • In the GenerateSnapshot.sh file:

      XEL_HOME=<Full path of the OIM_HOME directory>

  4. Save and close the file.

See Oracle Identity Manager Audit Report Developer's Guide for information about the procedure to generate audit snapshots.

15.5 Loading Account Data

The following is a summary of the steps involved in loading account data:

  1. If required, create a tablespace for the temporary tables used during the bulk load operation.

  2. If required, create a datafile in the Oracle Identity Manager tablespace.

  3. Create the input source for the bulk load operation.

    If you want to use a database table as the input source, then create the table and copy account data into the table.

    If you want to use CSV files as the input source, then create the CSV files and copy account data into the files.

  4. Determine values for the input parameters of the utility.

  5. Stop Oracle Identity Manager.

  6. Run the oim_blkld script.

  7. Monitor the progress of the bulk load operation.

  8. Determine the outcome of the bulk load operation.

  9. If required, reload data that was not loaded during the first run.

  10. Restart Oracle Identity Manager.

  11. Verify the outcome of the bulk load operation.

  12. Gather performance data from the operation.

  13. Remove temporary tables and files created during the operation.

  14. Generate an audit snapshot.

Requirements and Features of the Bulk Load Operation for Account Data

The following are requirements and features of the bulk load operation for account data:

The following sections provide detailed information about the steps involved in loading account data:

15.5.1 Creating a Tablespace for Temporary Tables

As mentioned in "Temporary Tables Used During a Bulk Load Operation", temporary database tables are used during the bulk load operation. It is recommended that you create a tablespace to accommodate these temporary tables instead of using the default tablespace of the Oracle Identity Manager database.

Follow the instructions in the database documentation to create a tablespace.

15.5.2 Creating a Datafile in the Oracle Identity Manager Tablespace

The default size of the datafile in the Oracle Identity Manager tablespace created during Oracle Identity Manager installation is 500 MB. You may need to add space to this datafile to accommodate the data that you are going to load. The alternative is to create a datafile.

To create a datafile in the Oracle Identity Manager tablespace:

  1. Start a SQL*Plus session.

  2. Connect to the Oracle Identity Manager database as SYSDBA.

  3. Run the oim_blkld_setup.sql script. The script will prompt for the following:

    • Name of the Oracle Identity Manager tablespace

    • Full path and name for the datafile to be added in the Oracle Identity Manager tablespace

    • Oracle Identity Manager database user name

15.5.3 Creating the Input Source for the Bulk Load Operation

Depending on the input source that you want to use, apply the guidelines given in one of the following sections:

15.5.3.1 Using CSV Files As the Input Source

If you want to use CSV files as the input source for the bulk load operation, then apply the following guidelines while creating the CSV files:

  • The CSV files must be placed in the oimbulkload/csv_files directory.

  • The first line in the CSV file is called the control line. This line must contain a comma-separated list of column names in the account (UD_*) table into which you want to load the account data.

    Note:

    Ensure that the Password column or any other encrypted column is not included in the list of columns.
  • From the second line onward, the file must contain values for the columns in the control line. The order of columns in the first line and the values in the rest of the lines must be the same.

  • If the value in any column contains a comma, then that value must be enclosed in double quotation marks (").

  • The CSV file must contain values for all columns that are designated as mandatory in the account table.

  • If you want to load account data into parent and child tables, then you must create one parent CSV file and one child CSV file for each child table. For example if you are loading data into one parent table and three child tables, then you must create one parent CSV file and three child CSV files.

  • If you want to load account data into parent and child tables, then at least one column must be the same in both tables. This column corresponds to the link attribute between the parent and child CSV files. The following example illustrates this:

    The following are sample contents of a parent CSV file:

    UD_ADUSER_UID,UD_ADUSER_ORGNAME,UD_ADUSER_FNAME,UD_ADUSER_LNAME,UD_ADUSER_MNAME,UD_ADUSER_FULLNAME
    jdoe,Finance,John,Doe,M,John M Doe
    rroe,Accounting,Richard,Roe,,Richard Roe
    

    The following are sample contents of a child CSV file:

    UD_ADUSER_UID,UD_ADUSRC_GROUPNAME
    jdoe,group1
    jdoe,group2
    jdoe,group3
    rroe,group1
    rroe,group2
    

    The UD_ADUSER_UID column is common to both the parent file and the child file.

15.5.3.2 Creating Database Tables As the Input Source

If you want to use a database table as the input source for loading account data, then apply the following guidelines while creating the database table:

  • Create the table in the Oracle Identity Manager database.

  • The table must contain the following primary key column:

    OIM_BLKLD_USRSEQ NUMBER(19)

    The utility uses this column as the primary key. If required, you can use a database sequence to populate this column.

  • The rest of the columns must be the same as the ones in the account (UD_) table that you want to use. In other words, ignore optional UD_ columns that you do not want to include in the table that you create.

Table 15-3 shows the structure of a sample parent table.

Table 15-3 Structure of a Sample Database Table

Name Null? Type

UD_ADUSER_UID

 

VARCHAR2(20)

UD_ADUSER_ORGNAME

 

VARCHAR2(256)

UD_ADUSER_FNAME

 

VARCHAR2(80)

UD_ADUSER_LNAME

 

VARCHAR2(80)

UD_ADUSER_MNAME

 

VARCHAR2(80)

UD_ADUSER_FULLNAME

 

VARCHAR2(240)

OIM_BLKLD_SEQ

NOT NULL

NUMBER(19)


Table 15-4 shows the structure of a sample child table.

Table 15-4 Structure of a Sample Child Database Table

Name Null? Type

UD_ADUSER_UID

 

VARCHAR2(20)

UD_ADUSER_ORGNAME

 

VARCHAR2(256)

UD_ADUSRC_GROUPNAME

 

VARCHAR2(32)

OIM_BLKLD_SEQ

NOT NULL

NUMBER(19)


15.5.4 Determining Values for the Input Parameters of the Utility

The following are input parameters of the utility:

  • Oracle Home

    Value of the ORACLE_HOME environment variable on the host computer for the Oracle Identity Manager database

  • Oracle SID

    SID of the Oracle Identity Manager database. In an Oracle RAC environment, you run the utility on one of the nodes. Provide the SID of that node.

  • Port Number

    Port on which the Oracle Identity Manager database is running. In an Oracle RAC environment, you run the utility on one of the nodes. Provide the port number of that node.

  • OIM DB User

    Database login ID of the Oracle Identity Manager database user

  • OIM DB Pwd

    Password of the Oracle Identity Manager database user

  • IP Address

    IP address of the computer on which you run the utility. In an Oracle RAC environment, you run the utility on one of the nodes. Provide the IP address of that node.

  • Object name (OBJ_NAME)

    Name of the resource object corresponding to the account data to be loaded

  • CSV file names

    Names of the CSV files to be used as the input source

    This parameter is used only if the input source is CSV files. See "Using CSV Files As the Input Source" for more information. If you are loading data from parent and child CSV file, then use a comma-delimited list to enter the names of the files. The name of the parent CSV file must be provided first, and it must be followed by the names of the child CSV files.

  • Tmp table name

    Name of the temporary table to be used as the input source

    This parameter is used only if the input source for the bulk load operation is a database table. See "Creating Database Tables As the Input Source" for more information.

  • Control Line

    Comma-separated list of names of columns to be loaded from the database table into Oracle Identity Manager

    This parameter is used only if the input source for the bulk load operation is a database table.

  • Tablespace Name

    Name of the tablespace in which temporary tables are to be created during the bulk load operation

    See "Creating a Tablespace for Temporary Tables" for more information.

  • Date format

    Date format used by date columns in the CSV files

    This parameter is used only if the input source is a single or multiple CSV files.

  • Batch Size

    Number of user records that must be processed by the utility as a single transaction

    The batch size can influence the performance of the bulk load operation. The default value of this parameter is 10000.

  • Debug Flag

    You can specify Y or N as the value of this parameter. If this parameter is set to Y, then the utility records detailed information about events that occur during the bulk load operation. See "Data Recorded During the Operation" for more information.

  • IT Resource Name

    Name of the IT resource created for the target system

15.5.5 Running the Utility

Note:

If there are name conflicts with existing tables, then the utility overwrites existing temporary tables at the start of each run. If required, rename temporary database tables created during an earlier run of the utility.

To run the utility:

  1. Stop Oracle Identity Manager.

  2. Run one of the following scripts on the computer on which the Oracle Identity Manager database is configured:

    • On UNIX computers:

      OIMBulkload/script/oim_blkld.sh

    • On Microsoft Windows computers:

      OIMBulkload\script\oim_blkld.bat

  3. From the main menu, select Load User data.

  4. From the second menu:

    • Select CSV File if you are using CSV files as the input source.

    • Select DB Table if you are using a database table as the input source.

  5. When prompted, provide values for the input parameters described earlier in this document.

  6. Monitor the performance of the operation by following the steps given in "Monitoring the Progress of the Operation".

15.5.6 Monitoring the Progress of the Operation

The following sections provide information that you can apply to monitor the progress of the operation:

15.5.6.1 Data Recorded During the Operation

During the bulk load operation, the utility inserts progress and error messages in the OIM_BLKLD_LOG table. Data in this table is not deleted at the start of a new bulk load operation. One of the columns in this table holds the time stamp at which messages are recorded in the table.

Table 15-5 describes the structure of the OIM_BLKLD_LOG table.

Table 15-5 Structure of the OIM_BLKLD_LOG Table

Name Null? Type Description

MSG_SEQ_NO

NOT NULL

NUMBER(19)

This column stores the number that denotes the order in which messages are inserted in this table. The column is populated using the OIM_BLKLD_LOG_SEQ sequence. You can use this column to query for messages in the order in which they are recorded in the table.

MODULE

NOT NULL

VARCHAR2(20)

This column stores one of the following values:

USER: This value indicates that the message has been recorded while loading OIM User data.

ACCOUNT: This value indicates that the message has been recorded while loading account data.

LOG_LEVEL

NOT NULL

VARCHAR2(20)

This column stores one of the following values:

ERROR

DEBUG

PROGRESS_MSG

LOAD_SOURCE

NOT NULL

VARCHAR2(40)

This column indicates the source of data for the bulk load operation during which the row was inserted. The value can be one of the following:

CSV File: <filename>

DB Table

MSG

NOT NULL

VARCHAR2(4000)

This column stores a message corresponding to the value stored in the LOG_LEVEL column.

CREATE_DATE

 

DATE

This column holds the time stamp at which the record was created. The format for entries in this column is as follows:

yyyy/mm/dd hh24:mi:ss

For example:

2008/06/23 21:49:16:32


15.5.6.2 Querying the OIM_BLKLD_LOG Table for Progress and Error Messages

During the bulk load operation, you can query the OIM_BLKLD_LOG table for information about the progress of the operation. For example, you can run the following query to see progress messages generated during the bulk load operation to load account data:

SELECT MSG FROM OIM_BLKLD_LOG
WHERE MODULE = 'ACCOUNT' AND LOG_LEVEL = 'PROGRESS_MSG'
ORDER BY MSG_SEQ_NO;

For example, you can run the following query to see progress messages generated during the bulk load operation to load account data:

SELECT MSG FROM OIM_BLKLD_LOG
WHERE MODULE = 'ACCOUNT' AND LOG_LEVEL = 'PROGRESS_MSG'
ORDER BY MSG_SEQ_NO;

Errors encountered during the bulk load operation can be viewed by querying the OIM_BLKLD_LOG table. The following is an example of the query to retrieve error messages:

SELECT MSG FROM OIM_BLKLD_LOG 
WHERE MODULE = 'ACCOUNT' AND LOG_LEVEL = 'ERROR'
ORDER BY MSG_SEQ_NO;

15.5.7 Handling Exceptions Recorded During the Operation

At the end of a bulk load operation, the utility records statistics related to the operation in the following file:

oimbulkload/logs_YYYYMMDD_hhmm/oim_blkld_account_load_summary.log

To determine if there were exceptions during the operation, open this log file and look for the number against the Number of Records Rejected label. If the number of rejected records is greater than zero, then exceptions were thrown during the operation. User records that are rejected by the utility are recorded in the exception table (OIM_BLKLD_EX_SUFFIX). For each rejected record, the EXCEPTION_MSG column in the OIM_BLKLD_EX_SUFFIX table stores information about the reason the record could not be loaded.

Example 15-2 shows sample statistics recorded in the log file at the end of a bulk load operation to store account data.

Example 15-2 Sample Log File Generated After Loading Account Data

=============================================================
A C C O U N T    L O A D    S T A T I S T I C S
=============================================================
Start Time:   22-JUL-08 03.59.30.206000 PM
End Time:     22-JUL-08 04.03.21.126000 PM
Number of Records Processed:  100026
Number of Records Loaded:     100000
Number of Records Rejected:   26
=============================================================
 
The names of the TMP tables used during the load:
OIM_BLKLD_TMP_P100001
OIM_BLKLD_TMP_C100002
The names of the Exception tables used during the load:
OIM_BLKLD_EX_P100001
OIM_BLKLD_EX_C100002

In this sample, the number of rejected records is 26. If the log file shows that any records were rejected by the utility, then see "Fixing Exceptions and Reloading Data Records" for information about retrying the load operation for these records.

Note:

At the end of each bulk load operation, it is recommended that you create a backup of the exception tables.

15.5.8 Fixing Exceptions and Reloading Data Records

Note:

If you want to load data from CSV files for multiple target systems, then you can apply one of the following approaches:
  • Approach 1: Run the utility for all the sets of CSV files, and then perform the procedure described in this section.

  • Approach 2: Run the utility for one set of CSV files, and perform the procedure described in this section. Then, repeat this procedure for the next set of CSV files.

As mentioned earlier, errors encountered during the bulk load operation can be viewed by querying the OIM_BLKLD_LOG table. The following is an example of the query to retrieve error messages:

SELECT MSG FROM OIM_BLKLD_LOG 
WHERE MODULE = 'ACCOUNT' AND LOG_LEVEL = 'ERROR'
ORDER BY MSG_SEQ_NO;

An exception table OIM_BLKLD_EX_SUFFIX is created for each data table used as the input source during the bulk load operation. Records that do not meet the criteria for the operation are copied into this exception table. The suffix appended to the name of each exception table is the same as suffix appended to the name of the corresponding data table.

To reload rejected records:

  1. Create a backup of the exception table in which rejected records are stored.

    Note:

    Although this is an optional step, it is recommended that you create a backup.
  2. Review each record in the exception table, and fix errors in the data based on the message recorded in the EXCEPTION_MSG column.

  3. After you fix errors in all the rejected records in an exception table, rename the table to OIM_BLKLD_TMP_SUFFIX and then use it as the input source.

  4. Load records from the OIM_BLKLD_TMP_SUFFIX table by running the utility. See "Running the Utility" for more information.

  5. Repeat Steps 1 through 4 until the Number of Records Rejected label in the oim_blkld_account_load_summary.log file shows the value 0.

  6. Restart Oracle Identity Manager.

15.5.9 Verifying the Outcome of the Bulk Load Operation

To verify the outcome of the bulk load operation, check if you are able to perform the following steps for one of the OIM Users for whom an account has been added by the utility:

See Also:

Oracle Identity Manager Administrative and User Console Guide for detailed information about these procedures
  • Log in as the OIM User, and check if the newly created account is displayed in the resource profile of the user.

  • Log in to the target system by using the credentials of the newly created account.

15.5.10 Gathering Performance Data from the Bulk Load Operation

As mentioned earlier in this document, the following log file is created during the bulk load operation:

oimbulkload/logs_YYYYMMDD_hhmm/oim_blkld_account_load_summary.log

Data recorded in this file can be used to collate performance-related information about the bulk load operation. The following information can be collected after the bulk load operation:

  • Start time

  • Input source

  • Number of records in the system before the load

  • Number of records successfully loaded

  • Number of records rejected

  • Total time taken

You can use this information during future runs of the utility. If you want to retain information about the bulk load operation, then move this file to a permanent location.

15.5.11 Cleaning Up After a Bulk Load Operation

If you do not want to save the results of a bulk load operation, then:

  • Remove the OIM_BLKLD_TMP_SUFFIX, OIM_BLKLD_EX_SUFFIX, and OIM_BLKLD_LOG tables.

  • Remove files that you created or used during the operation.

  • If you created a tablespace for the operation, then remove the tablespace.

  • See "Sect1: Gathering Performance Data from the Bulk Load Operation" before you remove log files created in the logs_timestamp directory.

    Note:

    At this point, you can restart Oracle Identity Manager if you have not already done so.

15.5.12 Generating an Audit Snapshot

If required, you can generate an audit snapshot of Oracle Identity Manager data after a bulk load operation. The utility uses the audit utility shipped with Oracle Identity Manager release 9.1.0. Internally, the GenerateSnapshot script is called when you run the audit utility. Similarly, the GenerateSnapshot script is called when you select the option to generate an audit snapshot.

Note:

Oracle Identity Manager must be up and running when you run the audit utility.

Before you generate an audit snapshot:

  1. Open the OIM_HOME/bin/GenerateSnapshot.sh (or GenerateSnapshot.bat) file in a text editor.

  2. In this file, search for the following line:

    • In the GenerateSnapshot.bat file:

      SET XEL_HOME=..

    • In the GenerateSnapshot.sh file:

      XEL_HOME=..

  3. Replace this line with the following line:

    • In the GenerateSnapshot.bat file:

      SET XEL_HOME=<Full path of the OIM_HOME directory>

    • In the GenerateSnapshot.sh file:

      XEL_HOME=<Full path of the OIM_HOME directory>

  4. Save and close the file.

See Oracle Identity Manager Audit Report Developer's Guide for information about the procedure to generate audit snapshots.