Posted by Chris Skorlinski Microsoft SQL Server Escalation Services
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)
Objects Created on the Oracle Publisher http://msdn.microsoft.com/en-us/library/ms152557(SQL.90).aspx
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.
PROCEDURE SnapshotBegin - Called just prior to the beginning of a snapshot PROCEDURE SnapshotEnd - Called after the snapshot process has concluded
PROCEDURE XactSetJob;
PROCEDURE InitPublisher PROCEDURE PublishTable PROCEDURE RefreshXactSetJob PROCEDURE AlterTableLog PROCEDURE ValidateRowFilter PROCEDURE UnPublishTable PROCEDURE SetSqlOriginator
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