1. Administering Oracle Directory Integration Platform
  2. Synchronization Using Oracle Directory Integration Platform
  3. Synchronizing with Tables in Oracle Database

12 Synchronizing with Tables in Oracle Database

Know more about the DBReader configuration and dbexport configuration files to synchronize data stored in Oracle Database tables with the Oracle back-end directory and an Oracle back-end directory with an Oracle Database.

Note:

Note:

  • Multi-valued attribute synchronization from the database to the Oracle back-end directory is not supported.

    The Oracle Directory Integration Platform application does not support database delete operations. You can, however, create an outside trigger that will directly delete entries in the Oracle back-end directory whenever a row is deleted in the database. Information about how to create such a trigger is outside of the scope of this documentation.

    You can use either the Oracle Enterprise Manager user interface or the WLST command-line utility to configure Oracle Database import and export profiles.

    See Managing Synchronization Profiles Using Fusion Middleware Control, for help creating and managing profiles using the user interface Managing Synchronization Profiles Using manageSyncProfiles, for help creating and managing profiles using the WLST manageSyncProfiles command utility.

Topics:

Note:

Before reading this chapter, be sure to familiarize yourself with the introductory chapters about Oracle Directory Integration Platform—specifically:

12.1 Overview of the Additional Configuration Information File

Using DBReader configuration files, you can modify from the command line to synchronize data stored in Oracle Database tables with the Oracle back-end directory.

The synchronization can be either incremental—for example, one database table row at a time—or all the database tables at once.

Modify the dbexport configuration files, if you want to create from the command line an export profile that will synchronize an Oracle back-end directory with an Oracle Database.

12.1.1 Preparing the Additional Configuration Information File

To create an import profile that synchronizes data from Oracle Database into the Oracle back-end directory, open the sample file DBReader.cfg.master in the $ORACLE_HOME/ldap/odi/conf directory, and edit it to your specifications.

To create an export profile that synchronizes data from the Oracle back-end directory into the Oracle Database, open the sample file dbexport.cfg.master in the $ORACLE_HOME/ldap/odi/conf directory, and edit it to your specifications.

12.1.2 Understanding the Format of 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

12.1.3 Understanding the DBReader.cfg.master Configuration File

During synchronization from Oracle database to the Oracle back-end directory, the DBReader.cfg.master file governs the retrieval of data from the database. It provides the Oracle Directory Integration Platform with the following information:

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

The DBReader.cfg.master file looks like this: 

[DBQUERY]
SELECT: SELECT\
EMPNO EmpNum,\
ENAME,\
REPLACE(EMAIL,'@example.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]
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.

Also note the WHERE condition that is present in the SELECT statement. The WHERE condition picks up changes based on the Modified_Date. To copy modified user records to the Oracle back-end directory, update the WHERE clause to pick up the records. In this example, the Modified_Date is the key for incremental synchronization. Because it is a date, it must be presented in string format.

The 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 CHANGEKEYATTRS—for example, column1:column2—then the value in the orclodipcondirlastappliedchgnum 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 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,'@example.com',''), but it retrieves the expression value as UID.

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:

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

  2. The profile is updated.

Whenever the Oracle Directory Integration Platform executes the profile again, it uses the previously stored value.

12.1.4 Understanding the dbexport.cfg.master Configuration File

The dbexport.cfg.master file describes the structure of the Oracle database. It provides the Oracle Directory Integration Platform with the following information:

  • Primary Table. The database table that all of the other tables in the profile are connected to. Every dbexport.cfg.master file should have one primary table.

  • Primary Keys. Specifies the primary key(s) for the table(s) to which you are syncing. Primary keys should be defined for all table names that are involved in this configuration.

    If a primary key consists of multiple columns, list each column name separated by a comma. For example: id,name,dob.

  • Table Relations. Defines the relationships between the primary table and all of the other tables involved in the profile by specifying the connecting attribute(s). In the sample file, id is the connecting attribute.

    If needed, use a comma separated list to specify multiple attributes, for example: id,name.

The dbexport.cfg.master file looks like this: 

[INTERFACEDETAILS]
Writer: oracle.ldap.odip.gsi.DatabaseWriter
CheckAllEntries: null
SkipErrorToSyncNextChange: false
UpdateSearchCount: 100
SearchDeltaSize: 500
 
[SYNC-PARAMS]
PRIMARY_TABLE: table1
 
[PRIMARY-KEYS]
table1:id
table2:id
 
[TABLE-RELATIONS]
table1^table2:id

Note:

The SkipErrorToSyncNextChange parameter determines how the Oracle directory integration and provisioning server handles an error when processing a change during synchronization. By default, the SkipErrorToSyncNextChange parameter is assigned a value of false, which means that the Oracle directory integration and provisioning server will continue processing a change until the error is resolved. If you assign a value of true to the SkipErrorToSyncNextChange parameter, the Oracle directory integration and provisioning server will skip any changes that cause an error.

12.2 Updating the Configuration File

You must configure the DBReader.map.master and dbexport.map.master configuration files.

Topics:

12.2.1 The DBReader.map.master Configuration File

You can update the DBReader.map.master configuration file to configure mapping rules.

In a directory synchronization environment, a typical set of entries from one domain can be moved to another domain. Similarly, a set of attributes can be mapped to another set of attributes.

Mapping rules govern the conversion of attributes between a connected directory and the Oracle back-end directory. Each connector stores a set of these rules in the orclodipAttributeMappingRules attribute of its synchronization profile. The Oracle Directory Integration Platform uses these rules to map attributes as needed when exporting from the directory and interpreting data imported from a connected directory or file. When the Oracle Directory Integration Platform imports changes into the Oracle back-end directory, it converts the connected directory's change record into an LDAP change record following the mapping rules. Similarly, during export, the connector translates the Oracle back-end directory changes to the format understood by the connected directory.

12.2.2 The dbexport.map.master Configuration File

You can configure the dbexport.map.master configuration File to configure mapping rules.

In a directory synchronization environment, a typical set of entries from one domain can be moved to another domain. Similarly, a set of attributes can be mapped to another set of attributes.

Mapping rules govern the conversion of attributes between a connected directory and the Oracle back-end directory. Each connector stores a set of these rules in the orclodipAttributeMappingRules attribute of its synchronization profile. The Oracle Directory Integration Platform uses these rules to map attributes as needed when exporting from the directory and interpreting data imported from a connected directory or file. When the Oracle Directory Integration Platform imports changes into the Oracle back-end directory, it converts the connected directory's change record into an LDAP change record following the mapping rules. Similarly, during export, the connector translates the Oracle back-end directory changes to the format understood by the connected directory.

Open the sample file dbexport.map.master in the $ORACLE_HOME/ldap/odi/conf directory, and edit it to your specifications.

The mapping rule format for the dbexport.map.master configuration file specifies a destination table name and a destination column name instead of a destination objectclass and destination attribute.

For example, here is a sample map rule:

uid:1::inetorgperson:id::table2:

In this example, uid is the source attribute and inetorgperson is the source objectclass. Next, id is the destination column and table2 is the destination table name. You must include in the map file whichever column is designated as the primary key, and you must also specify that it is a required attribute. In the example mapping rule, the :1 following the uid source attribute indicates that uid is a required attribute.

12.3 Preparing the Directory Integration Profile

You can create a directory integration profile by using the Oracle Enterprise Manager Fusion Middleware Control user interface.

See Managing Synchronization Profiles Using 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 manageSyncProfiles command. For help using the manageSyncProfiles command, see Managing Synchronization Profiles Using manageSyncProfiles.

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 (orclodipAgentExeCommand) attribute.

  • Set the interface type (orclodipDataInterfaceType) attribute to DB.

12.4 Example: Synchronizing a Relational Database Table to the Back-end Directory

This section demonstrates how to synchronize a relational database table with the Oracle back-end directory.

Note:

Directory Integration Platform database profiles do not support delete operations. You can, however, create a separate trigger outside of DIP that will directly delete entries in the Oracle back-end directory whenever a row is deleted in the database. Information about how to create such a trigger is outside of the scope of this documentation.

This section contains these topics:

12.4.1 About the Example Scenario

This example shows how the following relational database table containing employee data is synchronized with the Oracle back-end directory.

EMPNO ENAME LAST_UPDATE_DATE EMAIL TELEPHONE

98357

JOHN DOE

2-JAN-2000

JOHN.DOE@EXAMPLE.COM

435-324-3455

98360

ROGER BECK

3-JUL-2001

ROGER.BECK@EXAMPLE.COM

435-324-3600

98365

JIMMY WONG

4-MAR-2001

JIMMY.WONG@EXAMPLE.COM

435-324-2390

98370

GEORGE TWINSLEY

6-FEB-2002

GEORGE.TWINSLEY@EXAMPLE.COM

435-324-9232

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 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 the attributes reference chapter of the Oracle Identity Management User Reference.

  • This table is present in the testsync/testsyncpwd schema in a database. The database is located on the host machine.example.com, the database listener port is 1526, and the serviceName is ias.example.com. The database URL is machine.example.com:1526:ias.example.com.

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

12.4.2 Configure the Additional Configuration Information File

This example uses the same Additional Configuration Information file described earlier in "Overview of the Additional Configuration Information File".

12.4.3 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 percent sign (%) 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 need 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 at sign (@) character.

  • The empnum attribute becomes employeenumber in the directory entry.

  • The telephone attributes becomes telephone number in the directory entry.

12.4.4 Configure the Directory Integration Profile

The directory integration profile for this example contains the attribute values as described in Table 12-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 12-1 Directory Integration Profile for TESTDBIMPORT

Attribute Value

Profile Name (odip.profile.name)

TESTDBIMPORT

Synchronization Mode (odip.profile.syncmode)

IMPORT

Profile Status (odip.profile.status)

ENABLE

Agent Execution Command (odip.profile.agentexecommand)

null

Advanced Configuration Information (odip.profile.configfile)

Maintains configuration details which are not individually maintained in LDAP attributes.

Connected Directory Account (odip.profile.condiraccount)

testdbsync

Connected Directory Account Password (odip.profile.condirpassword)

testdbsyncpwd

Connected Directory URL (odip.profile.condirurl)

machine.example.com:1526:iasdb.example.com

Where iasdb.example.com is the database service name.

Interface Type (odip.profile.interface)

DB

Mapping File (odip.profile.mapfile)

Attribute for storing mapping rules.

OID Matching Filter (odip.profile.oidfilter)

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 Platform 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 (odip.profile.lastchgnum)

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.

12.4.5 Uploading the Additional Configuration Information and Mapping Files

Use the update operation of the manageSyncProfiles command to update the additional configuration information and mapping files.

Run the manageSyncProfiles command, as follows: 

manageSyncProfiles update -h HOST -p PORT -D WLS_USER -pf PROFILE_NAME -file FILE_NAME

12.4.6 Understanding Synchronization Process for Relational Database Table with the Oracle Back-End Directory

This section explains the synchronization process for relational database table with the Oracle back-end directory.

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

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

  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 (odip.profile.oidfilter) attribute, the Oracle Directory Integration Platform determines whether a matching entry exists in the Oracle back-end 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.

Note:

The OID Matching Filter (odip.profile.oidfilter) attribute supports Oracle Unified Directory and Oracle Directory Server Enterprise Edition, as well as Oracle Internet Directory.

12.4.7 Observations About the Synchronizing a Relational Database Table to the Back-End Directory Example

This topic describes the form of the row after it is retrieved from the database.

EmpNum: 98357
EName: JOHN DOE
UID: JOHN.DOE
EMAIL: JOHN.DOE@EXAMPLE.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@EXAMPLE.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 employeenumber.

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

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 (odip.profile.lastchgnum)

  • Last Execution Time (orclOdipLastExecutionTime)

  • Last Successful Execution Time (orclOdipLastSuccessfulExecutionTime)