How Oracle Publishing Works
With Microsoft SQL Server, you can include Oracle Publishers in your replication topology, starting with Oracle version 9i and SQL Server 2005. Publishing servers can be deployed on any Oracle supported hardware and operating system. The feature is built on the well-established foundation of SQL Server snapshot replication and transactional replication, providing similar performance and usability.
Snapshot Replication for Oracle
Oracle snapshot publications are implemented in a manner similar to SQL Server snapshot publications. When the Snapshot Agent runs for an Oracle publication, it connects to the Oracle Publisher and processes each table in the publication. When processing each table, the agent retrieves the table rows and creates schema scripts, which are then stored on the publication's snapshot share. The entire set of data is created each time the Snapshot Agent runs, so change tracking triggers are not added to the Oracle tables as they are with transactional replication. Snapshot replication provides a convenient way to migrate data with minimal impact on the publishing system.
For more information about snapshot replication, see How Snapshot Replication Works.
Transactional Replication for Oracle
Oracle transactional publications are implemented using the transactional publishing architecture of SQL Server; however, changes are tracked using a combination of database triggers on the Oracle database and the Log Reader Agent. Subscribers to an Oracle transactional publication are automatically initialized using snapshot replication; subsequent changes are tracked and delivered to Subscribers as they occur via the Log Reader Agent. For more information about change tracking for Oracle Publishers, see Transactional Replication Workflow for Oracle Publishers.
When an Oracle publication is created, triggers and tracking tables are created for each published table within the Oracle database. When data changes are made to the published tables, the database triggers on the tables fire and insert information into the replication tracking tables for each modified row. The Log Reader Agent on the SQL Server Distributor then moves the data change information from the tracking tables to the distribution database on the Distributor. Finally, as in standard transactional replication, the Distribution Agent moves changes from the Distributor to the Subscribers.
For more information about transactional replication, see How Transactional Replication Works.