Oracle to SQL Server Replication

Content provided with help of Jonathan Clark, Microsoft SQL Server Escalation Services

Oracle to SQL Server replication is used to support customers migrating Oracle legacy application to Microsoft SQL Server.  The data changes in Oracle can be replicated near real-time to a SQL Server database. Here is a little background information on the Oracle to SQL Server replication feature available with SQL Server 2005 and SQL Server 2008(R2).

When configured, triggers are added to the Oracle published tables to write data changes into tracking tables HREPL_ArticleNlog_V on Oracle.  The SQL Server LogReader connects to Oracle and reads list of pending data changes from these tracking tables.  It writes transactions into the Distribution database.  Once written into the Distribution database, the data is deleted from the Oracle HREPL_ArticleNlog_V tracking table.  Standard SQL Server Distribution Agents move the data to Subscribers as they do all published data.

Objects Created on the Oracle Publisher
http://msdn.microsoft.com/en-us/library/ms152557(SQL.90).aspx

Only two package procedures are called by the LogReader  — one at the beginning of a polling interval to create a new Xactset and one at the end of the polling interval to cleanup entries in the log tables and HREPL_Poll table.

PROCEDURE PollBegin – Called to mark log table entries as part of the next Xactset.
PROCEDURE PollEnd – Called to cleanup log table entries after they have been committed to MSrepl_commands

HREPL_ArticleNlog_V – Change tracking table used to store information as changes are made to the published table. A change tracking table is created for each published table.

The following procedures are called by the snapshot agent:

PROCEDURE SnapshotBegin – Called just prior to the beginning of a snapshot
PROCEDURE SnapshotEnd – Called after the snapshot process has concluded

The following procedure is used by the Oracle database job to create Xactsets:

PROCEDURE XactSetJob;

The following procedures are used for configuration:

PROCEDURE InitPublisher
PROCEDURE PublishTable
PROCEDURE RefreshXactSetJob
PROCEDURE AlterTableLog
PROCEDURE ValidateRowFilter
PROCEDURE UnPublishTable
PROCEDURE SetSqlOriginator

The following procedures are used to handle asynchronous requests generated by SQL Server stored procedures:

PROCEDURE Trace — Insert tracer token event in HREPL_Event
PROCEDURE RowCnt — Insert row count event in HREP_Event
PROCEDURE MarkSubscription — Insert subscription marker event in – HREPL_Event
Show Changes (Fields)

Like SQL Server to SQL Serve publishing, large single BATCH updates prove challenging for near real-time data replication.  We recommend the following when doing batch updates with Oracle to MS SQL Replication

Review Oracle batch processing or data loading operations to see if they can be updated in smaller batches. This will reduce the number of rows retrieved and deleted in one batch from the Oracle tracking tables.

Modify the batch processing to include updating the statistics in the Oracle tracking tables.

DBMS_STATS
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm

Managing Optimizer Statistics
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#PFGRF003