Transactional Replication Workflow for Oracle Publishers
Transactional replication for Oracle Publishers is built on the Microsoft SQL Server transactional replication publishing architecture, but the process of tracking changes at the Publisher and delivering them to the Distributor is significantly different from standard transactional replication. Despite the differences, transactions that occur in the Oracle database are applied at the Subscriber in consistent transactional groups. Tables with foreign key references between them maintain their data relationships as changes are delivered (foreign keys are not copied to the Subscriber).
Note
Large Objects (LOBs) are tracked differently from other data types. For more information, see the section "Considerations for Large Objects" in the topic Design Considerations and Limitations for Oracle Publishers.
The following workflow highlights the key steps involved in tracking and delivering changes from the Oracle Publisher.
Tracking changes
A user or application performs inserts, updates, or deletes on one or more Oracle tables published for replication.
A row-level trigger installed by replication on each published Oracle table fires for each row modified, storing information about the change in the table's associated article log table.
When the row-level trigger fires, a number is retrieved from the HREPL_seq sequence and assigned to the log table row that describes the DML operation. This ensures replication will apply change commands in the correct order at the Subscriber.
If a primary key update occurred, a statement level trigger installed on the table also fires, allowing multiple primary key updates occurring in the same statement to be associated with each other. The statement identifier is drawn from the HREPL_Stmt sequence. This is used for proper handling of primary key updates at the Subscriber.
For each row inserted into or deleted from the Oracle table that is published, one row is inserted into the associated article log table. For each row updated in the Oracle table, one row (the after image) or two rows (the before image and after image) are inserted into the log table, depending on whether replication requires information about the prior state of the row.
Delivering changes to the Distributor
The Log Reader Agent identifies the set of transactionally-consistent changes from the article logs that have not already been associated with a polling interval and temporarily stores the row IDs of the log table entries in the HREPL_Poll table. The identifier drawn from the sequence HREPL_Pollid is used to mark each change entry as a member of the transactionally-consistent set, as well as to provide a processing order for the set relative to other sets.
When processing changes for a published table, rows are retrieved from the log table; the current poll ID from HREPL_Pollid is used to identify those rows to process.
The batch of changes from the log table is committed as a single transaction in the distribution database and stored in the MSrepl_commands and MSrepl_transactions tables.
The changes are then read from the distribution database and delivered to the Subscribers by the Distribution Agent, as they are with standard transactional replication.