---
title: Configure the database for synchronization
description: Configuring the database for synchronization includes defining:
component: pingdirectory
version: 11.0
page_id: pingdirectory:pingdatasync_server_administration_guide:pd_sync_config_database_for_sync
canonical_url: https://docs.pingidentity.com/pingdirectory/11.0/pingdatasync_server_administration_guide/pd_sync_config_database_for_sync.html
revdate: September 13, 2023
section_ids:
  about-this-task: About this task
  steps: Steps
---

# Configure the database for synchronization

## About this task

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.

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

## Steps

1. Create an Oracle login (`SyncUser`) for PingDataSync, 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.