Edit

Design considerations and limitations for Oracle publishers

Applies to: SQL Server

Publishing from an Oracle database works almost the same as publishing from a Microsoft SQL Server database. However, be aware of the following limitations and issues:

  • The Oracle Gateway option provides better performance than the Oracle Complete option. However, you can't use the Oracle Gateway option to publish the same table in multiple transactional publications. A table can appear in only one transactional publication but can appear in 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. It doesn't replicate other objects.

  • Some small differences between the storage and processing of data in Oracle and SQL Server databases affect replication.

  • There are differences in how transactional replication features are supported when you use 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 (replicated as tables)

The following objects can be present on published tables but aren't replicated:

  • Domain-based indexes

  • Function-based indexes

  • Defaults

  • Check constraints

  • Foreign keys

  • Storage options (tablespaces, clusters, and so on)

The following objects can't 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 you create 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 creates object names in uppercase. Ensure that you supply the names of Oracle objects in uppercase when publishing them through a SQL Server Distributor if they are uppercase on the Oracle database. Failure to specify the objects in the correct case might result in an error message indicating that the object can't be found.

  • Oracle uses a slightly different SQL dialect from SQL Server; write row filters in Oracle-compliant syntax.

Considerations for large objects

The article log table doesn't store large object (LOB) data. 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 don't fire triggers. An update to a LOB column is 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 is 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 rows within a transaction instead of updating the row: specify the new LOB when reinserting the row. Because delete and insert both fire triggers, the row is 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 in unique indexes and constraints (including primary key constraints) must follow certain restrictions. If they don't follow these restrictions, the constraint or index isn't replicated.

  • You can include up to 16 columns in an index on SQL Server.

  • All columns in unique constraints must have supported data types. For more information about data types, see Data Type Mapping for Oracle Publishers.

  • You must publish all columns in unique constraints (you can't filter them).

  • Columns in unique constraints or indexes shouldn't 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 can't 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, SQL Server ignores trailing blanks in a field but Oracle doesn't.

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 earlier. If the primary key doesn't follow these rules, you can't publish the table 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 (9.x) 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 Specify How Changes Are Propagated for Transactional Articles.

Transactional replication feature support

  • Oracle publications don't support all of the schema options that SQL Server publications support. For more information about schema options, see sp_addarticle (Transact-SQL).

  • Subscribers to Oracle publications can't use immediate updating or queued updating subscriptions, or be nodes in a peer-to-peer or bidirectional topology.

  • Subscribers to Oracle publications can't 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 Validate 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 aren't supported. To make schema changes, first drop the publication, make the changes, and then re-create the publication and any subscriptions.

    Note

    If you make schema changes and drop and re-create the publication and subscriptions when no activity is occurring on the published tables, you can specify the option replication support only for the subscriptions. This option synchronizes them without copying a snapshot to each Subscriber. For more information, see Initialize a Transactional Subscription Without a Snapshot.

Replication security model

The replication 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 View and modify replication security settings.

Related content

  • Last updated on