|Oracle® Fusion Middleware Integration Guide for Oracle Identity Management
11g Release 1 (11.1.1)
Part Number E10031-01
This chapter explains how to synchronize data from tables in a relational database with Oracle Internet Directory. The synchronization can be either incremental—for example, one database table row at a time—or all the database tables at once.
Note:Multi-valued attribute synchronization from database to Oracle Internet Directory is not supported.
The process of synchronization with a database involves executing a directory integration profile and includes the following steps:
Retrieving the data from the database. This involves executing a SQL
SELECT statement that retrieves the specified data records from the database.
Writing the data into the directory. This involves converting the retrieved data records to LDAP attribute values and performing the LDAP operation on the directory.
Note:Before reading this chapter, be sure to familiarize yourself with the introductory chapters about Oracle Directory Integration Platform—specifically:
Oracle Internet Directory 11g Release 1 (11.1.1) does not support exporting data from Oracle Internet Directory to a relational database.
This chapter contains these topics:
During synchronization from a relational database to Oracle Internet Directory, the additional configuration information file governs the retrieval of data from the database. It provides the Oracle Directory Integration Platform with the following information:
SELECT statement to execute
Either the attributes or the database columns to be used in incremental synchronization. Generally, this is either an attribute that contains a timestamp or a change sequence number that the next SQL statement should use to retrieve incremental data.
To configure this file, use the sample file DBReader.cfg.master in the $ORACLE_HOME/ldap/odi/conf directory, and edit it to your specifications.
Formatting the Additional Configuration Information File
It is very important to follow the correct format of this file. The various sections are divided using TAG names. Every TAG section has a list of parameters and their respective values. The general layout is as follows:
[TAG] PARAMETER1: value PARAMETER2: value [TAG] PARAMETER1: value PARAMETER2: value\ VALUE continuation\ value continuation\ end of value continuation [TAG] PARAMETER1: value PARAMETER2: value\ end of value continuation
For example, following this format, the DBReader.cfg.master file looks like this:
[DBQUERY] SELECT: SELECT\
EMPNO EmpNum,\ ENAME,\ REPLACE(EMAIL),'@ACME.COM','') UID,\ EMAIL,\ TELEPHONE,\ TO_CHAR(LAST_UPDATE_DATE,'YYYYMMDDHH24MISS') Modified_Date\
ORDER BY\ LAST_UPDATE_DATE [SYNC-PARAMS] CHANGEKEYATTRS: Modified_Date
Note that the entire
SELECT statement is put as a value in the
SELECT parameter in the section represented by the tag
DBQUERY. Because it is a lengthy value, the value continuation character is put as the last character in every line until the
SELECT statement ends.
CHANGEKEYATTRS parameter value is the name of the columns to be used while performing incremental synchronization. The values of these columns are always stored in the
orclodipcondirlastappliedchgnum attribute of the profile. Every time the
SELECT statement is executed, the current values of this attribute are put into the SQL statement accordingly. This ensures that the data is always retrieved incrementally.
If there are multiple column names in the
column1:column2—then the value in the
orclodipcondirlastappliedchgnum attribute of the profile is stored as
value1~value2 and so on, with
value1 corresponding to
Column names are retrieved into Oracle Directory Integration Platform as attribute value pairs and subsequently mapped into LDAP attribute values according to set mapping rules. For this reason, all columns names retrieved in the
SELECT statement must be simple names rather than expressions. For example, you can have the expression
REPLACE(EMAIL),'@ACME.COM',''), but it retrieves the expression value as
In this example, the
Modified_Date is the key for incremental synchronization. Because it is a date, it must be presented in string format.
When the profile is created, the
orclodipcondirlastappliedchgnum attribute must be set to some value. All changes after this date—that is, rows in the table with
LAST_UPDATE_DATE greater than this value—are retrieved. For example, if the
orclodipcondirlastappliedchgnum attribute is set to
20000101000000, then all employee changes since January 1, 2000 are retrieved.
Because of the
ORDER BY clause, all the database rows returned are in the order of
LAST_UPDATE_DATE—that is, the changes retrieved and applied to the directory are in chronological order. Once the last change is retrieved and applied:
orclodipcondirlastappliedchgnum attribute value is set to the
Modified_Date from the last row retrieved.
The profile is updated.
Whenever the Oracle Directory Integration Platform executes the profile again, it uses the previously stored value.
To configure the mapping rules, follow the instructions in "Mapping rules and formats".
You can create the directory integration profile by using the Oracle Enterprise Manager Fusion Middleware Control. When you use Oracle Enterprise Manager Fusion Middleware Control, you must upload the additional configuration information file and the mapping file by using the
update operation of the
To configure the directory integration profile, follow the general instructions in "Registering Connectors in Oracle Directory Integration Platform", but with these specific instructions in mind:
Do not set a value for the agent execution command (
Set the interface type (
orclodipDataInterfaceType) attribute to
This section demonstrates how to synchronize a relational database table with Oracle Internet Directory. It contains these topics:
In this example, the following relational database table containing employee data is synchronized with Oracle Internet Directory:
You can find a sample profile (DBReader.properties), configuration, and mapping files for this example in the $ORACLE_HOME/ldap/odi/conf directory. In this example:
The name of the table is
The Profile Name is
The employee number (
EMPNO) is used to join a database record with a directory entry. It is specified in the OID Matching Filter (
orclOdipOIDMatchingFilter) attribute described in the attributes reference chapter of the Oracle Identity Management User Reference.
This table is present in the
testsyncpwd schema in a database. The database is located on the host
machine.acme.com, the database listener port is
1526, and the SID is
iasdb. The database URL is
Appropriate read/write permissions were given explicitly to this profile, namely,
orclodipagentname=testdbimport, cn=subscriber profile, cn=changelog subscriber, cn=oracle internet directory
The profile is created in configuration set 1.
This example uses the same Additional Configuration Information file described earlier in "Preparing the Additional Configuration Information File".
The mapping file for this example contains the following:
DomainRules NONLDAP:dc=testdbsync,dc=com:uid=%,dc=testdbsync,dc=com AttributeRules ename: : : :cn: :person ename : : : :sn: :person uid : : : :uid: :inetOrgperson: EMail: : : :mail: :inetOrgperson Telephone: : : :telephonenumber: :inetOrgperson empnum: : : :employeenumber: :inetOrgperson
This mapping file specifies the following:
Directory entries are created as
uid=%,dc=testdbsync,dc=com. The percent sign (
%) is a placeholder for the actual value of
uid must be present in the mapping rules so that it has a value after the mapping. Otherwise, the DN construction fails.
sn attributes need to have the same value as
uid element must have the value of the
empnum attribute becomes
employeenumber in the directory entry.
telephone attributes becomes
telephone number in the directory entry.
The directory integration profile for this example contains the attribute values as described in Table 9-1. A sample integration profile with these values populated and the corresponding mapping and configuration files are available in $ORACLE_HOME/ldap/odi/conf directory. You can create the profile by using Oracle Enterprise Manager Fusion Middleware Control or following the instructions described in "Creating Synchronization Profiles".
Table 9-1 Directory Integration Profile for TESTDBIMPORT
Profile Name (
Synchronization Mode (
Profile Status (
Agent Execution Command (
Advanced Configuration Information (
As shown in the preceding file.
Connected Directory Account (
Connected Directory Account Password (
Connected Directory URL (
Interface Type (
Mapping File (
To be uploaded from a file.
OID Matching Filter (
This means that
Once a database row is retrieved, the Oracle Directory Integration Platform searches the directory for that
Last Applied Change Number (
This means that the first time the profile executes, it retrieves and synchronizes all four rows. Subsequently, it retrieves rows only when the
update operation of the manageSyncProfiles command to update the additional configuration information and mapping files, as follows:
manageSyncProfiles update -h HOST -p PORT -D WLS_USER -pf PROFILE_NAME -file FILE_NAME
In this example, the sequence of steps in the synchronization process is:
The Oracle Directory Integration Platform starts a new profile thread for the TESTDBIMPORT profile every time the value specified in the scheduling interval (
odip.profile.schedinterval) attribute expires.
The profile thread reads the additional configuration information to get the SQL to execute, and then runs the SQL.
For every row retrieved from the database, the mapping rules are applied to the record, and LDAP attributes are created.
Depending on the OID Matching Filter (
odip.profile.oidfilter) attribute, the Oracle Directory Integration Platform determines whether a matching entry exists in Oracle Internet Directory. If it exists, then it is updated. If not, then a new entry is created. After the directory operation, the last applied change number (
odip.profile.lastchgnum) attribute is updated.
When a row is retrieved from the database, it is in the following form:
EmpNum: 98357 EName: JOHN DOE UID: JOHN.DOE EMAIL: JOHN.DOE@ACME.COM TELEPHONE: 435-324-3455 Modified_Date: 20000102000000
After the mapping is performed on this record, the output is in the following form:
dn: uid=john.doe,dc=testdbsync,dc=com uid: JOHN.DOE cn: JOHN DOE sn: JOHN DOE mail: JOHN.DOE@ACME.COM employeenumber: 98357 telephonenumber: 435-324-3455 objectclass: person objectclass: inetorgperson
A subtree search is made in the directory with the filter
employeenumber=98357 under the domain
dc=testdbsync,dc=com. If the search yields an existing entry, then that entry is updated. Otherwise, a new entry is created. Because the OID Matching Filter (
odip.profile.oidfilter) attribute is set to
employeenumber, every database record retrieved must have that column. In this case, it is
EmpNum as it maps to
Any other attributes in the mapping file that are not in the data retrieved by SQL are ignored—for example, the
After the profile thread processes all the change records from SQL, it updates the directory with correct values for these attributes:
Last Applied Change Number (
Last Execution Time
Last Successful Execution Time (