Share via

  • Article

Design Considerations and Limitations for Oracle Publishers

Publishing from an Oracle database is designed to work nearly identically to publishing from a Microsoft SQL Server database. However, you should be aware of the following limitations and issues:

  • The Oracle Gateway option provides improved performance over the Oracle Complete option; however, this option cannot be used to publish the same table in multiple transactional publications. A table can appear in at most one transactional publication and any number of snapshot publications. If you need to publish the same table in multiple transactional publications, choose the Oracle Complete option.

  • Replication supports publishing tables, indexes, and materialized views. Other objects are not replicated.

  • There are some small differences between the storage and processing of data in Oracle and SQL Server databases that affect replication.

  • There are a number of differences in how transactional replication features are supported when using an Oracle Publisher.

Support for Publishing Objects from Oracle

Replication supports replicating the following objects from Oracle databases:

  • Tables

  • Index-organized tables

  • Indexes

  • Materialized views (they are replicated as tables)

The following can be present on published tables but are not replicated:

  • Domain-based indexes

  • Function-based indexes

  • Defaults

  • Check constraints

  • Foreign keys

  • Storage options (tablespaces, clusters, etc.)

The following objects cannot be replicated:

  • Nested tables

  • Views

  • Packages, package bodies, procedures, and triggers

  • Queues

  • Sequences

  • Synonyms

For information about supported data types, see Data Type Mapping for Oracle Publishers.

Differences between Oracle and SQL Server

  • Oracle has different maximum size limits for some objects. Any objects created in the Oracle publication database should adhere to the maximum size limits for the corresponding objects in SQL Server. For information about limits in SQL Server, see Maximum Capacity Specifications for SQL Server.

  • By default Oracle object names are created in upper case. Ensure that you supply the names of Oracle objects in upper case when publishing them through a SQL Server Distributor if they are upper case on the Oracle database. Failure to specify the objects in the correct case may result in an error message indicating that the object cannot be found.

  • Oracle has a slightly different SQL dialect from SQL Server; row filters should be written in Oracle-compliant syntax.

Considerations for Large Objects

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.

Unique Indexes and Constraints

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.

Differences between Oracle Publishing and Standard Transactional Replication

  • An Oracle Publisher cannot have the same name as: its SQL Server Distributor; any of the SQL Server Publishers that use the Distributor; or any Subscribers that receive the publication. Publications serviced by the same Distributor must each have a unique name.

  • A table published in an Oracle publication cannot receive replicated data. Therefore, Oracle publishing does not support: publications with immediate updating or queued updating subscriptions; or topologies in which publication tables also act as subscription tables, such as peer-to-peer and bidirectional replication.

  • Primary key to foreign key relationships in the Oracle database are not replicated to Subscribers. However, the relationships are maintained in the data as changes are delivered.

  • Standard transactional publications support tables of up to 1000 columns. Oracle transactional publications support 995 columns (replication adds five columns to each published table).

  • Collate clauses are added to the CREATE TABLE statements to enable case sensitive comparisons, which is important for primary keys and unique constraints. This behavior is controlled with the schema option 0x1000, which is specified with the @schema_option parameter of sp_addarticle (Transact-SQL).

  • If you use stored procedures to configure or maintain an Oracle Publisher, do not put the procedures inside an explicit transaction. This is not supported over the linked server used to connect to the Oracle Publisher.

  • If you create a pull subscription to an Oracle publication with a wizard, you must use the New Subscription Wizard supplied with SQL Server 2005 and later versions. For previous versions of SQL Server, you can, however, use the stored procedure and SQL-DMO interfaces to setup pull subscriptions to Oracle publications.

  • If you use stored procedures to propagate changes to Subscribers (the default), be aware that the MCALL syntax is supported, but it has different behavior when the publication is from an Oracle Publisher. Typically MCALL provides a bitmap that shows which columns were updated at the Publisher. With an Oracle publication, the bitmap always shows that all columns were updated. For more information about using stored procedures, see Specifying How Changes Are Propagated for Transactional Articles.

Transactional Replication Feature Support

  • 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.

Replication Security Model

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

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