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.
- Tables
- 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
- Indexes
Note
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.
Note
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.
Restrictions on Oracle database objects
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
RAW
data 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:
BFILE
BINARY_DOUBLE
BINARY_FLOAT
MLSLABEL
LONG
LONG RAW
- 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.
Note
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
INSERT
,UPDATE
, andDELETE
statements.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
INSERT
orUPDATE
statement.If JDBC LOB APIs (provided by
java.sql.Clob
orjava.sql.Blob
classes) 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_LOB
package, triggers won't activate, and streaming for LOB columns won't work.
Restrictions on SQL Server database pbjects
You can't replicate SQL Server tables that include the following data types:
GEOGRAPHY
GEOMETRY
SQL_VARIANT
Note
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:
BINARY
IMAGE
NTEXT
NVARCHAR(max)
TEXT
TIMESTAMP
VARBINARY
VARBINARY(max)
Restrictions on Postgres database objects
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:
BLOB
BYTEA
RAW
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:
JSONB
- Geometry data types
tsvector
,tsquery
,txid_snapshot
,pg_lsn
cidr
,inet
,mcaddress
,mcaddress8
,uuid
,money
ENUM
,ARRAY
, range data types (such asint4range
,tstzrange
,numrange
,daterange
)- 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 POINT
and POLYGON
.
You can't replicate Postgres tables to a SQL Server subscription database that include the following data types:
ABSTIME
ACLITEM
CHKPASS
- Geometric data types (such as
LINE
,PATH
,POLYGON
) CIRCLE
CUBE
JSON
JSONB
ROWID
tsvector
,tsquery
,txid_snapshot
,pg_lsn
SEG
Any ARRAY
data type (that is, defined asdata_type[]
)ENUM
, composite type, range data types (such asint4range
,tstzrange
,numrange
,daterange
),mcaddress8
- Any user-defined data type (that is, defined as
CREATE TYPE <type_name>
)
Restrictions on range data types
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: int4range
, int8range
, numrange
, tsrange
, tstzrange
, and daterange
.
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.
Limitations during re-snapshot for views
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.