Managing Synchronization Between Oracle Internet Directory and a Relational Database

This section describes the tasks you perform to synchronize an Oracle Internet Directory with a relational database. It also provides an example of creating an integration profile with the appropriate details.

This section contains these topics:

• Task 1: Prepare the Additional Configuration Information File

• Task 2: Prepare the Mapping File

• Task 3: Prepare the Directory Integration Profile

• Example: Synchronizing a Relational Database Table to Oracle Internet Directory

Task 1: Prepare the Additional Configuration Information File

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 and provisioning server with the following information:

• The SELECT statement to execute

• Either the attribute(s) or the database column(s) 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/samples/ 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\

FROM\

EMPLOYEE\

WHERE\

LAST_UPDATE_DATE>TO_DATE (:Modified_Date,'YYYYMMDDHH24MISS')\

ORDER BY\
LAST_UPDATE_DATE

[SYNC-PARAMS]
CHANGEKEY: Modified_Date

Note that the entire SELECT statement is put as a value in the parameter SELECT 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.

The CHANGEKEY parameter value is the name of the column(s) to be used while doing incremental synchronization. The value(s) of these column(s) is always stored in the orclOdipLastAppliedChgNum attribute of the profile. Every time the SELECT statement is executed, the current value(s) 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 CHANGEKEY--for example, column1:column2--then the value in the orclOdipLastAppliedChgNum attribute of the profile is stored as value1~value2 and so on, with value1 corresponding to column1 and value2 to column2.

Column names are retrieved into the Oracle Directory Integration and Provisioning 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 UID.

In this example, the Modified_Date is the key for incremental synchronization. Because it is a date, it must be represented in a string format.

When the profile is created, the orclOdipLastAppliedChgNum 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 orclOdipLastAppliedChgNum 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:

1. The orclOdipLastAppliedChgNum attribute value is set to the Modified_Date from the last row retrieved.

2. The profile is updated.

Whenever the Oracle Directory Integration and Provisioning platform executes the profile again, it uses the previously stored value.

Task 2: Prepare the Mapping File

To configure the mapping rules, follow the instructions in "Mapping Rules and Formats".

Task 3: Prepare the Directory Integration Profile

You can create the directory integration profile by using either Oracle Directory Manager or the Directory Integration and Provisioning Assistant. If you use Oracle Directory Manager, then you must upload the additional configuration information file and the mapping file by using either the Directory Integration and Provisioning Assistant or the script ldapUploadAgentFile.sh

To configure the directory integration profile, follow the general instructions in "Registration of Connectors into the Oracle Directory Integration and Provisioning Platform", but with these specific instructions in mind:

• Do not set a value for the Agent Execution Command (orclodipAgentExeCommand) attribute.

• Set the Interface Type (orclodipDataInterfaceType) attribute to DB.

  See Also: "The Directory Integration and Provisioning Assistant" "The ldapUploadAgentFile.sh Tool Syntax"for instructions on using the ldapUploadAgentFile.sh script

Example: Synchronizing a Relational Database Table to Oracle Internet Directory

In this example, the following relational database table containing employee data is synchronized with Oracle Internet Directory.

Table 38-1  Employee Table

EMPNO	ENAME	LAST_UPDATE_DATE	EMAIL	TELEPHONE
98357	JOHN DOE	2-JAN-2000	[e-mail]	435-324-3455
98360	ROGER BECK	3-JUL-2001	[e-mail]	435-324-3600
98365	JIMMY WONG	4-MAR-2001	[e-mail]	435-324-2390
98370	GEORGE MICHAEL	6-FEB-2002	[e-mail]	435-324-9232

You can find a sample profile for this example in the directory ORACLE_HOME/ldap/odi/samples. Also present there are the sample configuration and mapping files. In this example:

• The name of the table is Employee

• The Profile Name is TESTDBIMPORT.

• 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 Table B-20.

• This table is present in the testsync/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 machine.acme.com:1526:iasdb.

• Appropriate read/write permissions have been 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.

Task 1: Configure the Additional Configuration Information File

This example uses the same Additional Configuration Information file described earlier in "Task 1: Prepare the Additional Configuration Information File".

Task 2: Configure the Mapping 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 % is a placeholder for the actual value of uid. The uid must be present in the mapping rules so that it has a value after the mapping. Otherwise the DN construction fails.

• Both the cn and sn attributes are to have the same value as ename.

• The uid element must have the value of the EMail prefix, which is the element of the e-mail address prior to the '@' character.

• empnum becomes employeenumber in the directory entry.

• telephone becomes telephone number in the directory entry.

Task 3: Configure the Directory Integration Profile

The directory integration profile for this example contains the attribute values as described in Table 38-2. A sample integration profile with these values populated and the corresponding mapping and configuration files are available in $ORACLE_HOME/ldap/odi/samples directory. You can create the profile by running the Directory Integration and Provisioning Assistant in the createprofile mode and specifying the file as the argument. Alternatively, you can create the profile by using Oracle Directory Manager.

See Also: "The Directory Integration and Provisioning Assistant" "Registering a Profile by Using Oracle Directory Manager" for instructions on creating a profile by using Oracle Directory Manager

Table 38-2  Directory Integration Profile for TESTDBIMPORT

Attribute	Value
Profile Name (orclOdipAgentName)	TESTDBIMPORT
Synchronization Mode (orclOdipSynchronizationMode)	IMPORT
Professoriats (orclOdipAgentControl)	ENABLE
Agent Execution Command (orclodipAgentExeCommand)	null
Additional Config Info (orclOdipAgentConfigInfo)	As shown in the preceding file. Needs to be uploaded
Connected Directory Account (orclOdipConDirAccessAccount)	testdbsync
Connected Directory Account Password (orclOdipConDirAccessPassword)	testdbsyncpwd
Connected Directory URL (orclOdipConDirURL)	machine.acme.com:1526:iasdb
Interface Type (orclodipDataInterfaceType)	DB
Mapping File:	To be uploaded from a file
OID Matching Filter (orclOdipOIDMatchingFilter)	employeenumber This means that employeenumber is used to search the directory while looking for a match. If a match is found, then the directory entry is modified. Otherwise, a new entry is created. This is necessary to ensure that the orclOdipOIDMatchingFilter attribute is unique in the database also. Once a database row is retrieved, the Oracle directory integration and provisioning server searches the directory for that employeenumber in the domain dc=testdbsync,dc=com according to the domain rules. If it gets a match, it updates that entry with the latest values of the columns in the row retrieved. If it does not get a match, it creates a new entry in the directory with all the attributes from the column values.
Last Applied Change Number (orclodipConDirLastAppliedChgNum)	20000101000000 This means that the first time the profile executes, it retrieves and synchronizes all four rows. Subsequently, it retrieves rows only when the LAST_UPDATE_DATE column in the table is updated to the time last modified.

Task 4: Upload the Additional Configuration Information File

If you used Oracle Directory Manager to create the profile, then enter this command:

ORACLE_HOME/ldap/odi/admin/ldapuploadagentfile.sh -name "TESTDBIMPORT" -config 1 
\
-bindpass password -binddn "cn=orcladmin" -attrtype "ATTR" \ 

-filename full_path_name_of_the_file

Task 5: Upload the Mapping File

If you used Oracle Directory Manager to create the profile, then enter this command:

ORACLE_HOME/ldap/odi/admin/ldapuploadagentfile.sh -name "TESTDBIMPORT" -config 1 
\
-bindpass password -binddn "cn=orcladmin" -attrtype "MAP" \ 

-filename full_path_name_of_the_file

The Synchronization Process

In this example, the sequence of steps in the synchronization process is:

1. The Oracle directory integration and provisioning server starts a new profile thread for the TESTDBIMPORT profile every time the value specified in the scheduling interval (orclOdipSchedulingInterval) attribute expires.

2. The profile thread reads the additional configuration information to get the SQL to execute, and then runs the SQL.

3. For every row retrieved from the database, the mapping rules are applied to the record and LDAP attributes are created.

4. Depending on the OID Matching Filter (orclOdipOIDMatchingFilter) attribute, the directory integration and provisioning server determines whether a matching entry exists in Oracle Internet Directory or not. If it exists, then it is updated. If not, then a new entry is created. After the directory operation, the last applied change number (orclodipConDirLastAppliedChgNum) attribute is updated.

Observations on the Example

When a row is retrieved from the database, it is in the following form:

EmpNum: 98357
EName: JOHN DOE
UID: JOHN.DOE
EMAIL: [e-mail]
TELEPHONE: [phone]
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: [e-mail]
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 (orclOdipOIDMatchingFilter) attribute is set to employeenumber, every database record retrieved must have that column. In this case, it is EmpNum as it maps to employeenumber.

Any other attributes in the mapping file that are not in the data retrieved by the SQL are ignored--for example, the attribute birthday.

After the profile thread processes all the change records from the SQL, it updates the directory with correct values for these attributes:

• Last Applied Change Number (orclodipConDirLastAppliedChgNum)

• Last Execution Time (orclOdipLastExecutionTime)

• Last Successful Execution Time (orclOdipLastSuccessfulExecutionTime)