Restrictions on replicated database objects v7
When you create a subscription in a single-master replication system, the table definitions and most database objects and attributes associated with the publication tables are created in the subscription database by the subscription server.
If you choose, the same process can automatically occur when a primary node is added to a multi-master replication system. You can create the table definitions and most database objects and attributes associated with the publication tables in the newly added primary node by the publication server.
The following is a list of database objects and table attributes that are replicated from the publication in either a single-master or multi-master replication system.
- Views (for snapshot-only publications) created as tables in the subscription database
- Materialized Views (for Oracle snapshot-only publications) created as tables in the subscription database
- Primary keys
- Not null constraints
- Unique constraints
- Check constraints
Foreign key constraints aren't replicated by the publication or subscription server in a single-master replication system. However, in a multi-master replication system, foreign key constraints are replicated from the primary definition node to other primary nodes.
Sequences (database objects created by the
CREATE SEQUENCE statement) aren't replicated from the publication database to the subscription databases in a single-master replication system. Sequences also aren't replicated from the primary definition node to other primary nodes in a multi-master replication system.
Replication Server does have some restrictions on the types of tables it can replicate.
The following are the restrictions on Oracle database objects:
You can replicate certain types of Oracle partitioned tables. See Replicating Oracle partitioned tables for details.
You can't replicate Oracle global temporary tables.
You can use Oracle tables with the
RAWdata type in snapshot-only publications but not in synchronization replications.
You can include Oracle materialised views in snapshot-only publications but not in synchronization replications.
You can't replicate Oracle tables that include the following data types:
- Replication Server 7.0 provides support for LOB column streaming from Oracle to PostgreSQL/EDB Postgres Advanced Server. However, you must create LOB columns to allow NULLs on the source Oracle and target PostreSQL/EDB Postgres Advanced Server database server. The previous version of Replication Server didn't stream the LOB columns. Rows of tables containing LOB columns were replicated during streaming, but LOB columns contained NULL. This limitation was removed.
If you upgrade an existing Replication Server 6.x deployment to 7.0 and the published tables contain LOB columns, you must replublish all the tables with LOB columns to avoid data inconsistency.
Oracle provides different methods to insert and update LOB columns. Replication Server supports LOB streaming only if LOB columns are inserted, updated, and deleted using
If you use the command line SQL*Plus tool, the maximum LOB size that you can insert or update is 4K. You can use Java program to insert large values, but you must use an
If JDBC LOB APIs (provided by
java.sql.Blobclasses) are used, triggers won't activate, and hence streaming for LOB columns won't work.
Similarly, if you use functions and procedures provided by the
DBMS_LOBpackage, triggers won't activate, and streaming for LOB columns won't work.
You can't replicate SQL Server tables that include the following data types:
See Replicating the SQL Server SQL_VARIANT data type for a method to replicate tables containing the
SQL_VARIANT data type under certain conditions.
You can use SQL Server tables with the following data types in snapshot-only publications but not in synchronization replications:
For replicating Postgres partitioned tables, see Replicating Postgres partitioned tables for details. You can't replicate Postgres tables with the following data types in a column that's part of the identity columns:
This restriction applies to primary key or unique columns. See Design considerations.
You can't replicate PostgreSQL or EDB Postgres Advanced Server database tables to the Oracle database that include the following data type:
- Geometry data types
ARRAY, range data types (such as
- Any user-defined data type (that is, defined as
CREATE TYPE type_name)
You can't replicate Postgres tables that include
OID-based large objects. For information on
OID-based large objects, see
pg_largeobject in the PostgreSQL core documentation.
You can't replicate Postgres tables to an Oracle subscription database that include any geometric data types such as
You can't replicate Postgres tables to a SQL Server subscription database that include the following data types:
- Geometric data types (such as
Any ARRAYdata type (that is, defined as
ENUM, composite type, range data types (such as
- Any user-defined data type (that is, defined as
CREATE TYPE <type_name>)
Postgres data types called range types were first supported in PostgreSQL version 9.2 and EDB Postgres Advanced Server version 9.2. Built-in range types refer to the following built-in data types:
You can include Postgres tables containing the built-in range types in the publication of a single-master or multi-master replication system. However, this results in the following restrictions on the subscription databases of a single-master replication system or the additional primary nodes of a multi-master replication system:
- If a publication table of a single-master replication system contains any built-in range types, then a you can add a database as a subscription database only if the database server of the intended subscription database is Postgres version 9.2 or later.
- If a publication table of the primary definition node in a multi-master replication system contains any built-in range types, then you can add a database as a primary node only if the database server of this intended primary node is Postgres version 9.2 or later.
Custom range types constructed with the
CREATE TYPE AS RANGE command aren't supported in Replication Server.
As part of the re-snapshot process, Replication Server drops constraints from the tables included in the snapshot using the
CASCADE option. As a result, any objects, such as views in certain use cases, are also dropped. The following example illustrates a situation with a view that contains a
GROUP BY clause that depends on the constraints in the underlying table where if the
CASCADE option were not used, the constraints could not be dropped for the snapshot operation.
Since Replication Server drops the constraints using the
CASCADE option, the above error would not occur; however, the result of using this option means that the view will also be dropped.