Initial Data Migration

This example copies the data that exists in a SQL Server database to an Oracle database (the Initial Load) and then keeps the two databases in sync. All the tables of the schema HumanResources are replicated, and the tables that do not exist in Oracle are created. The steps are the ones of Basic Replication Configuration. The data can also be synchronized from a backup of the Source instead of the Initial Load: see Database Sync from Backup, which compares the two ways.

What you need

• The same as in Basic Replication Configuration: Repstance is running, the Source Database is testDB on db-src.example.com, the Target Database is the Oracle service ORA1 on ora-tgt.example.com, and the logins have the privileges that are described in Database Privileges.
• An Oracle user HumanResources in the Target Database. Repstance creates the tables but not the user. It writes the names without quotes, so Oracle stores them in upper case: the user is HUMANRESOURCES. The login of Repstance has to be allowed to create tables in that schema (see Database Privileges).

Prepare the Source and the Target Databases

Prepare the SQL Server database as a Source. housekeeping=1 installs a Repstance job that deletes the changes from the CDC tables after the Capture Process has read them (see SQL Server as Source):

Prepare the Oracle database as a Target. connectiontype=ezconnect says that the connection is made with the server, the port and the service name (see Oracle as Target):

Create the Capture Process with the Initial Load

The Capture Process sql2ora has the number 4. dmlinclude and ddlinclude select the tables whose changes are replicated. loadinclude=HumanResources.%:C selects the tables of the Initial Load: all the tables of the schema HumanResources, and :C creates a table in the Target Database when it does not exist there. The other options clean a table before the load (T, D), recreate it (R) or merge the rows into it (M), see Initial Load. A table has to be selected by dmlinclude too, otherwise it is not loaded.

The Initial Load runs when the Capture Process starts. autostart=1 starts the process as soon as it is created, and again every time the Repstance server starts. When the load is finished, Repstance removes loadinclude from the settings of the process (see Initial Load).

Create the Apply Process

The Source and the Target are different databases, so the DDL statements of the Source cannot be replayed as they are. With ddlprocessing=map Repstance rebuilds them for the Target. The default, native, replays the text of the Source statement (see Oracle Apply Parameters):

Check the load

Both processes start by themselves. The command status shows the Initial Load (see Status of any Capture or Apply Processes): while the tables are loaded, the Capture Process shows the lines Loading the Data, Average Load Speed (ops) and Loaded Objects, the number of the tables and of the rows that have been loaded. When the load is finished, the Apply Process keeps the Target in sync with the Source.

To check that the tables are the same in both databases, compare them: DATA COMPARISON. The command report shows what each process has done for every table (see Report Process).