OIM Bulk Load Utility

Description: The Oracle Identity Manager Bulk Load Utility can be used to load large amount of users, accounts, roles, role hierarchies, role memberships, or role categories directly to the database. The data can be loaded from a database table or CSV file. It is required that the Oracle Identity Manager managed servers are shut down in the process of running the bulk load utility.
Tested on Oracle Identity Manager Version: 11.1.2.2.0
Reference: Oracle Identity Manager Bulk Load Utility

Prerequisites
1. It is recommended to create a tablespace for temporary tables instead of using the default Oracle Identity Manager tablespace.

2. If you are loading massive amount of data, you may need to extend the Oracle Identity Manager tablespace in order to accommodate the data that you are going to load.

Execute "$MW_HOME/Oracle_IDM1/server/db/oim/oracle/Utilities/oimbulkload/sqls/oim_blkld_setup.sql" on the database as SYS. The script will prompt for the following:

• Name of Oracle Identity Manager tablespace
  E.g. DEV_OIM
• Full path for the datafile to be added to Oracle Identity Manager tablespace
  E.g. /home/oracle/db/app/oracle/oradata/ext/dev_oim_ext.dbf
• Oracle Identity Manager database user name
  E.g DEV_OIM

OR

Manually extend the OIM tablespace using SQL:

alter tablespace dev_oim add datafile '/home/oracle/db/app/oracle/oradata/ext/dev_oim_ext.dbf' size 1024M autoextend on;

3. Create a CSV file or database table of the data to be loaded. Given below are examples for loading users.

Using CSV file:

• Create a file called master.txt in "$MW_HOME/Oracle_IDM1/server/db/oim/oracle/Utilities/oimbulkload/csv_files/"directory with each CSV file name on a separate line.
  
  

  users.csv

• Place all your CSV files in the "csv_files" directory. Sample CSV files are given in "$MW_HOME/Oracle_IDM1/server/db/oim/oracle/Utilities/oimbulkload/sample_data" directory. Below is a CSV file example for users (The first row is the header which contains the USR attribute column names. The remaining rows are users' data).
  
  

  USR_LOGIN,USR_FIRST_NAME,USR_LAST_NAME
  vyu,Victoria,Yu
  ntaylor,Nick,Taylor
  osantos,Oliver,Santos
  lboyes,Lauren,Boyes
  wbrozas,William,Brozas
  jgodfrey,Jason,Godfrey
  kmurphy,Kyle,Murphy
  aamori,Amanda,Amori

Using database table:
This approach is essentially creating the temporary table for the bulk load utility (Table structure is identical to the temp table created when using CSV file). The table is to be created within the OIM Schema. Below are SQL statements to create a sample staging table.

CREATE TABLE seeding (USR_LOGIN VARCHAR2(256 CHAR), USR_FIRST_NAME VARCHAR2(150 CHAR), USR_LAST_NAME VARCHAR2(150 CHAR), OIM_BLKLD_USRSEQ NUMBER(19,0));
INSERT INTO seeding (USR_LOGIN, USR_FIRST_NAME, USR_LAST_NAME, OIM_BLKLD_USRSEQ) VALUES ('kevans','Kyle','Evans','1');
INSERT INTO seeding (USR_LOGIN, USR_FIRST_NAME, USR_LAST_NAME, OIM_BLKLD_USRSEQ) VALUES ('byee','Brittany','Yee','2');
INSERT INTO seeding (USR_LOGIN, USR_FIRST_NAME, USR_LAST_NAME, OIM_BLKLD_USRSEQ) VALUES ('mkazim','Mo','Kazim','3');
INSERT INTO seeding (USR_LOGIN, USR_FIRST_NAME, USR_LAST_NAME, OIM_BLKLD_USRSEQ) VALUES ('jhubner','John','Hübner','4');
INSERT INTO seeding (USR_LOGIN, USR_FIRST_NAME, USR_LAST_NAME, OIM_BLKLD_USRSEQ) VALUES ('mfernandez','Mike','Fernández','5');

Side Notes:
- Below are mandatory fields and fields with default values if no value is provided.

• ORG_NAME: Xellerate Users
• USR_TYPE: End-User
• USR_STATUS: Active
• USR_EMP_TYPE: Full-Time
• USR_LOGIN
• USR_LAST_NAME
• USR_DISPLAY_NAME: {Calculated}

- For boolean values use 0 = false, 1 = true (USR boolean columns are 1 char, hence a y or n can be inserted but OIM uses 0,1. y or n would render properly but it really should not be used)

- Clean up the CSV file. Use vi with the b option to see if there are any weird characters (E.g. vi -b FILENAME). For example, you may see ^M carriage return characters in the CSV file. Use dos2linux utility to convert the file to UNIX format (E.g. dos2unix FILENAME).

- Ensure the CSV file character encoding matches character set on the database. "file -bi FILENAME" to check the file character encoding. If the character charsets are mismatched, the data will not be stored properly in the database (E.g. Special characters such as letters with accents).

- If any value in the CSV file has a comma, enclose the value in double quotes.

Executing the Bulk Load Utility

1. Stop the Oracle Identity Manager servers.

2. Navigate to "/home/oracle/Oracle/Middleware/Oracle_IDM1/server/db/oim/oracle/Utilities/oimbulkload/scripts/" directory and execute "oim_blkld.sh" script.

3. The following are input parameters for the bulk load utility:

• Select the operation to perform:
  
  1) 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. You can select the input source, CSV files or database tables, for the data that you want to load.
  
  2) 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. You can select the input source, CSV files or database tables, for the data that you want to load.
  
  3) Load Role Data: You select this option if you want the utility to load role data. In other words, data is imported into the UGP table of Oracle Identity Manager. You can select the input source, CSV files, or database tables, for the data that you want to load.
  
  4) Load Role Hierarchy: You select this option if you want the utility to load role hierarchy data. In other words, data is imported into the GPG table of Oracle Identity Manager. You can select the input source, CSV files, or database tables, for the data that you want to load
  
  5) Load Role Membership: You select this option if you want the utility to load role membership data. In other words, data is imported into the USG table of Oracle Identity Manager. You can select the input source, CSV files or database tables, for the data that you want to load.
  
  6) Load Role Category: You select this option if you want the utility to load role data. In other words, data is imported into the ROLE_CATEGORY tables of Oracle Identity Manager. You can select the input source, CSV files, or database tables, for the data that you want to load
  
  7) Generate Audit Snapshot: You select this option if you want the utility to generate an audit snapshot of users that you have loaded.
  
  8) Exit
  ## Enter your option [1-8] :
  
  
• Enter the ORACLE HOME directory. ORACLE_HOME being the value for the Oracle Identity Manager database home directory or path to the Oracle Database client home directory. If your database is on a separate machine from OIM, follow "Running OIM Bulk Load Utility Remotely" to install the Oracle Database client in order to run the bulk load utility.
  E.g. /home/oracle/db/app/oracle/product/11.2.0/dbhome_1
  
  
• Enter the OIM database connect string in following format //HostIPAddress:Port/ServiceName
  E.g. //localhost:1521/orcl
  
  
• Enter Oracle Identity Manager(OIM) database user name
  E.g. DEV_OIM
  
  
• Enter password for Oracle Identity Manager (DEV_OIM) database user. You will need to enter OIM database user password twice.
  
  
• Select the input for user load
  1) DB Table
  2) CSV File
  3) Exit
  Enter your option (1, 2 or 3):
  
  
• [CSV File] Enter the name of the master file containing names of all CSV file to be loaded. 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.
  E.g. master.txt
  
  
• [DB Table]: Enter the name of the DB table, to be used as load source. This table is to be created within the OIM Schema.
  E.g. seeding
  
  
• [DB Table]: Enter the comma separated column names, to be loaded, of table {TABLE_NAME}.
  E.g. USR_LOGIN,USR_FIRST_NAME,USR_LAST_NAME



• Enter the Name of tablespace to be used for creating custom objects, by the utility. Tablespace Name = Name of the tablespace in which temporary tables are to be created during the bulk load operation. If the user does not provide the tablespace name, then it will pick the default tablespace. See "Preparing Your Database for a Bulk Load Operation" for more information.
  E.g. DEV_OIM
  
  
• [CSV File] Enter the date format for data load. Date format used by date columns in the CSV file.
  E.g. yyyy-mm-dd hh24:mi:ss
  dd-mm-yyyy
  MM-DD-YYYY
  
  
• Enter the batch size for processing ( default size is 10000 ). 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.
  
  
• Utility expects you to create a User using OIM web console and enter a password for this user, which will be used as initial password for all users loaded using Bulk Load utility.
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".
E.g. xelsysadm

4. Validate the data by inspecting the logs, staging table, and target table.

"$MW_HOME/Oracle_IDM1/server/db/oim/oracle/Utilities/oimbulkload

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.

Temporary Tables Created

OIM_BLKLD_TMP_{FILE_SUFFIX} : Loads data from csv into this table

OIM_BLKLD_EX_{FILE_SUFFIX}: Stores data records that fail during bulk load

OIM_BLKLD_LOG: Logs errors and monitor progress