Configuring the database for synchronization includes defining:
  • A database SyncUser account
  • The change tracking mechanism
  • The database triggers (one per table) for the application

The following procedure uses the example setup script in /config/jdbc/samples/oracle- db/OracleSyncSetup.sql. Items in brackets are user-named labels.

Note:

Database change tracking necessary if synchronizing FROM the database. If synchronizing TO a database, configure the Sync User account and the correct privileges.

  1. Create an Oracle login (SyncUser) for PingDataSync Server, so that it can access the database server. Grant sufficient privileges to the SyncUser for any tables to be synchronized, and change the default password.
    CREATE USER SyncUser IDENTIFIED BY password
    DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; 
    GRANT "RESOURCE" TO SyncUser;
    GRANT "CONNECT" TO SyncUser;
  2. Create change log tables on the database as follows:
    CREATE TABLE ubid_changelog (
      --This is the unique number for the change change_number Number NOT NULL
    PRIMARY KEY,
      --This is the type of change (insert, update, delete). NOTE: This should
    represent
      --the actual type of change that needs to happen on the destination(for
    example a
      --database delete might translate to a LDAPmodify, etc.)
        change_type VARCHAR2(10) NOT NULL,
      --This is the name of the table that was changed table_name VARCHAR(50)
    NOT NULL,
      --This is the unique identifier for the row that was changed. It is up
    to
      --the trigger code to construct this, but it should follow a DN-like
    format
      --(e.g. accountID={accountID}) where at least the primary key(s) are
      --present. If multiple primary keys are required, they should be
    delimited
      --with a unique string, such as '%%' (e.g. accountID={accountID}%%
      --groupID={groupID})
        identifier VARCHAR2(100) NOT NULL,
      --This is the database entry type. The allowable values for this must be
      --set on the JDBC Sync Source configuration within the Synchronization
      --Server.
        entry_type VARCHAR2(50) NOT NULL,
      --This is a comma-separated list of columns from the source table that
    were updated as part of
      --this change.
        changed_columns VARCHAR2(1000) NULL,
      --This is the name of the database user who made the change
        modifiers_name VARCHAR2(50) NOT NULL,
      --This is the timestamp of the change
        change_time TIMESTAMP(3) NOT NULL, CONSTRAINT chk_change_type
        CHECK (change_type IN ('insert','update','delete'))) ORGANIZATION
    INDEX;
  3. Create an Oracle function to get the SyncUser name. This is a convenience function for the triggers.
    CREATE OR REPLACE FUNCTION get_sync_user RETURN VARCHAR2
    IS
    BEGIN
      RETURN 'SyncUser';
    END get_sync_user;
  4. Create an Oracle sequence object for the change-number column in the change log table.
    CREATE SEQUENCE ubid_changelog_seq MINVALUE 1 START WITH 1
    NOMAXVALUE INCREMENT BY 1 CACHE 100 NOCYCLE;
  5. Create a database trigger for each table that will participate in synchronization. An example, located in /config/jdbc/samples/oracle-db/OracleSyncSetup.sql, shows a trigger for the Accounts table that tracks all changed columns after any INSERT, UPDATE, and DELETE operation. The code generates a list of changed items and then inserts them into the change log table.