1529 - Oracle Requirements for Transactional/Mirroring Replications

Q.

I want to perform an incremental (mirroring) replication using Oracle as a source database. Are there any specific settings in Oracle or in Syniti Replicate that I need to consider?

A.

Yes. There are different conditions for incremental replication depending on the Oracle version that you are using.

Oracle 9 and above

There are no specific settings you need to make for Oracle versions 9 and higher. However, if you have not defined a primary key in your Oracle table, you will need to map the Oracle ROWID column to a target table column so that records can be uniquely identified. You then need to set the target table column as the primary key. See below for instructions on mapping the Oracle ROWID column.

Mapping the Oracle ROWID column (all target databases except Microsoft SQL Server)

To use the Oracle ROWID column to identify each record:

1. In the target table, create an additional column of type String.
2. If you have not yet created the replication, when you get to the Set Mapping Info screen of the Replication wizard or Multiple Replications wizard, choose to map the newly created column to an expression.
   If you have created the replication, in the Replication Properties dialog, open the Fields Mapping dialog and choose to map the newly created column to an expression.
3. In the Expression Generator, expand the tree to display the Values node. (If replicating to SQL Server, skip to the specific instructions for SQL Server below.)
4. Expand the Values node to display Log Field values.
5. Select the !RecordID value.
   The !RecordID value represents the Oracle Row ID.
6. Click OK in the Expression Generator.
   You have now set the value of the column that you created above to the Oracle Row ID.
   To set the new column as a primary key:
7. In the Metadata Explorer, select the target table.
8. From the right mouse button menu, choose Set Primary Key to open the Define Primary Key dialog.
9. Select the column name in the Fields column, then click the right-facing arrow to move the column name into the Keys column of the dialog.
10. Click OK in the Define Primary Key dialog to complete the operation.

Any time you reorganize a source table (to compress deleted rows, etc.), Syniti Replicate needs to perform a full refresh of all the target tables mapped using the Row ID.

Mapping the Oracle ROWID column with a Microsoft SQL Server Target Database

To use the Oracle ROWID column to identify each record:

1. In the target table, create an additional column of type Binary with length 30.
2. If you have not yet created the replication, when you get to the Set Mapping Info screen of the Replication wizard or Multiple Replications wizard, choose to map the newly created column to an expression.
   If you have created the replication, in the Replication Properties dialog, open the Fields Mapping dialog and choose to map the newly created column to an expression.
3. In the Expression Generator, expand the tree to display the Values node. 
4. Expand the Values node to display Log Field values.
5. Select the !RecordID value.
   The !RecordID value represents the Oracle Row ID.
6. Create the following expression with the !RecordID value:
   
   System.Text.Encoding.ASCII.GetBytes([!RecordID])
   
   
7. Click OK in the Expression Generator.
   You have now set the value of the column that you created above to the Oracle Row ID.
   
   To set the new column as a primary key:
8. In the Metadata Explorer, select the target table.
9. From the right mouse button menu, choose Set Primary Key to open the Define Primary Key dialog.
10. Select the column name in the Fields column, then click the right-facing arrow to move the column name into the Keys column of the dialog.
11. Click OK in the Define Primary Key dialog to complete the operation.

Any time you reorganize a source table (to compress deleted rows, etc.), Syniti Replicate needs to perform a full refresh of all the target tables mapped using the Row ID.