Large object (LOB) data is not stored in the article log table; updates to LOB data are always retrieved directly from the published table. Updates are replicated in transactional publications only if the operation affecting the LOB fires the replication trigger on the replicated table. Oracle triggers fire when rows containing LOBs are inserted or deleted; however updates to LOB columns do not fire triggers. An update to a LOB column will be replicated immediately only if a non-LOB column of the same row is also updated in the same Oracle transaction. If not, the LOB column will be refreshed at the Subscriber when the next update to a non-LOB column in the same row occurs. Ensure that this behavior is acceptable for your application.

To replicate updates to LOB columns in transactional publications, consider one of the following strategies when writing the application:

• Delete and reinsert the row(s) within a transaction instead of updating the row: specify the new LOB when re-inserting the row. Because delete and insert both fire triggers, the row will be replicated.
  
• Include a non-LOB column in the row update in addition to the LOB column, or update a non-LOB column of the row as part of the same Oracle transaction. In both cases, the update of the non-LOB column ensures that the trigger fires.
  

For more information about LOBs, see Data Type Mapping for Oracle Publishers.

For both snapshot and transactional replication, columns contained in unique indexes and constraints (including primary key constraints) must adhere to certain restrictions. If they do not adhere to these restrictions, the constraint or index is not replicated.

• The maximum number of columns allowed in an index on SQL Server is 16.
  
• All columns included in unique constraints must have supported data types. For more information about data types, see Data Type Mapping for Oracle Publishers.
  
• All columns included in unique constraints must be published (they cannot be filtered).
  
• Columns involved in unique constraints or indexes should not be null.
  

Also consider the following issues:

• Oracle and SQL Server treat NULL differently: Oracle permits multiple rows with NULL values for columns that allow NULL and are included in unique constraints or indexes. SQL Server enforces uniqueness by only permitting a single row with a NULL value for the same column. You cannot publish a unique constraint or index that allows NULL because a constraint violation would occur on the Subscriber if the published table contains multiple rows with NULL values for any of the columns included in the index or constraint.
  
• When testing for uniqueness, trailing blanks in a field are ignored by SQL Server but not by Oracle.
  

As in SQL Server transactional replication, tables in Oracle transactional publications require a primary key; the primary key must be unique based on the rules specified above. If the primary key does not adhere to the rules outlined in the previous bullets, the table cannot be published for transactional replication.

• Oracle publications do not support all of the schema options that SQL Server publications support. For more information on schema options, see sp_addarticle (Transact-SQL).
  
• Subscribers to Oracle publications cannot use immediate updating or queued updating subscriptions, or be nodes in a peer-to-peer or bidirectional topology.
  
• Subscribers to Oracle publications cannot be automatically initialized from a backup.
  
• SQL Server supports two types of validation: binary and rowcount. Oracle Publishers support rowcount validation. For more information, see Validating Replicated Data.
  
• SQL Server offers two snapshot formats: native bcp-mode and character-mode. Oracle Publishers support character mode snapshots.
  
• Schema changes to published Oracle tables are not supported. To make schema changes, first drop the publication, make the changes, and then re-create the publication and any subscriptions.
  

  Note:

  If the schema changes and the subsequent drop and re-creation of the publication and subscriptions are performed at a time when no activity is occurring on the published tables, you can specify the option 'replication support only' for the subscriptions. This allows them to be synchronized without having to copy a snapshot to each Subscriber. For more information, see Initializing a Transactional Subscription Without a Snapshot.

The security model for Oracle publishing is the same as the security model for standard transactional replication, with the following exceptions:

• The account under which the Snapshot Agent and Log Reader Agent make connections from the Distributor to the Publisher is specified through one of the following methods:
  
  • The @security_mode parameter of sp_adddistpublisher (Transact-SQL) (you also specify values for @login and @password if Oracle Authentication is used)
    
  • In the Connect to Server dialog box in SQL Server Management Studio, which you use when you configure the Oracle Publisher at the SQL Server Distributor.
    
  In standard transactional replication, the account is specified with sp_addpublication_snapshot (Transact-SQL) and sp_addlogreader_agent (Transact-SQL).
  
• The account under which the Snapshot Agent and Log Reader Agent make connections cannot be changed with sp_changedistpublisher (Transact-SQL) or through a property sheet, but the password can be changed.
  
• If you specify a value of 1 (Windows Integrated Authentication) for the @security_mode parameter of sp_adddistpublisher (Transact-SQL):
  
  • The process account and password used for both the Snapshot Agent and Log Reader Agent (the @job_login and @job_password parameters of sp_addpublication_snapshot (Transact-SQL) and sp_addlogreader_agent (Transact-SQL)) must be the same as the account and password used to connect to the Oracle Publisher.
    
  • You cannot change the @job_login parameter through sp_changepublication_snapshot (Transact-SQL) or sp_changelogreader_agent (Transact-SQL), but the password can be changed.
    

For more information about replication security, see Security and Protection (Replication).