B Oracle Streams Restrictions

This appendix describes Oracle Streams restrictions.

This appendix includes these topics:

Capture Process Restrictions

This section describes restrictions for capture processes.

This section contains these topics:

Unsupported Data Types for Capture Processes

A capture process does not capture the results of DML changes to columns of the following data types:

  • BFILE

  • ROWID

  • User-defined types (including object types, REFs, varrays, and nested tables)

  • XMLType stored object relationally or as binary XML

  • The following Oracle-supplied types: Any types, URI types, spatial types, and media types

  • The extended data types for VARCHAR2 and NVARCHAR2 that are longer than 4000 bytes.

  • The extended data types for RAW that are longer than 2000 bytes.

These data type restrictions pertain to both ordinary (heap-organized) tables and index-organized tables.

Capture processes can capture changes to SecureFiles LOB columns only if the source database compatibility level is set to 11.2.0.0 or higher. Also, capture processes do not support capturing changes to SecureFiles LOB columns stored using Advanced LOB deduplication, capturing changes resulting from fragment-based operations on SecureFiles LOB columns, and capturing changes resulting from SecureFiles archive manager operations.

A capture process raises an error if it tries to create a row LCR for a DML change to a column of an unsupported data type. When a capture process raises an error, it writes the LCR that caused the error into its trace file, raises an ORA-26744 error, and becomes disabled. In this case, modify the rules used by the capture process to avoid the error, and restart the capture process.

It is possible to configure Oracle Streams to support additional data types. For instructions, go to the My Oracle Support (formerly OracleMetaLink) Web site using a Web browser:

http://support.oracle.com/

Database bulletin 556742.1 describes additional data type support for Oracle Streams.

Note:

You can add rules to a negative rule set for a capture process that instruct the capture process to discard changes to tables with columns of unsupported data types. However, if these rules are not simple rules, then a capture process might create a row LCR for the change and continue to process it. In this case, a change that includes an unsupported data type can cause the capture process to raise an error, even if the change does not satisfy the rule sets used by the capture process. The DBMS_STREAMS_ADM package creates only simple rules.

See Also:

Unsupported Changes for Capture Processes

This section describes changes that are not supported by capture processes.

This section contains these topics:

Unsupported Schemas for Capture Processes

A capture process never captures changes made to the following schemas:

  • CTXSYS

  • DBSNMP

  • DMSYS

  • DVSYS

  • EXFSYS

  • LBACSYS

  • MDDATA

  • MDSYS

  • OLAPSYS

  • ORDDATA

  • ORDPLUGINS

  • ORDSYS

  • OUTLN

  • SI_INFORMTN_SCHEMA

  • SYS

  • SYSMAN

  • SYSTEM

  • WMSYS

  • XDB

Unsupported Table Types for Capture Processes

A capture process cannot capture DML changes made to temporary tables or object tables.

Note:

  • A capture process can capture changes to tables compressed with basic table compression and Advanced Row Compression only if the compatibility level at both the source database and the capture database is set to 11.2.0.0.0 or higher.

  • Starting with Oracle Database 11g Release 2 (11.2.0.2), a capture process can capture changes to tables compressed with hybrid columnar compression if all of the following conditions are met: both the source database and the capture database must be running at least Oracle Database 11g Release 2 (11.2.0.2), and the compatibility level at both the source database and the capture database is set to 11.2.0.0.0 or higher.

See Also:

Unsupported DDL Changes for Capture Processes

A capture process captures the DDL changes that satisfy its rule sets, except for the following types of DDL changes:

  • ALTER DATABASE

  • CREATE CONTROLFILE

  • CREATE DATABASE

  • CREATE PFILE

  • CREATE SPFILE

A capture process can capture DDL statements, but not the results of DDL statements, unless the DDL statement is a CREATE TABLE AS SELECT statement. For example, when a capture process captures an ANALYZE statement, it does not capture the statistics generated by the ANALYZE statement. However, when a capture process captures a CREATE TABLE AS SELECT statement, it captures the statement itself and all of the rows selected (as INSERT row LCRs).

Some types of DDL changes that are captured by a capture process cannot be applied by an apply process. If an apply process receives a DDL LCR that specifies an operation that cannot be applied, then the apply process ignores the DDL LCR and records information about it in the trace file for the apply process.

When a capture process captures a DDL change that specifies time stamps or system change number (SCN) values in its syntax, configure a DDL handler for any apply processes that will dequeue the change. The DDL handler must process time stamp or SCN values properly. For example, a capture process captures FLASHBACK TABLE statements when its rule sets instruct it to capture DDL changes to the specified table. FLASHBACK TABLE statements include time stamps or SCN values in its syntax.

See Also:

Changes Ignored by a Capture Process

A capture process ignores the following types of changes:

  • The session control statements ALTER SESSION and SET ROLE.

  • The system control statement ALTER SYSTEM.

  • CALL, EXPLAIN PLAN, and LOCK TABLE statements.

  • GRANT statements on views.

  • Changes made to a table or schema by online redefinition using the DBMS_REDEFINITION package. Online table redefinition is supported on a table for which a capture process captures changes, but the logical structure of the table before online redefinition must be the same as the logical structure after online redefinition.

  • Changes to sequence values. For example, if a user references a NEXTVAL or sets the sequence, then a capture process does not capture changes resulting from these operations. Also, if you share a sequence at multiple databases, then sequence values used for individual rows at these databases might vary.

  • Invocations of PL/SQL procedures, which means that a call to a PL/SQL procedure is not captured. However, if a call to a PL/SQL procedure causes changes to database objects, then these changes can be captured by a capture process if the changes satisfy the capture process rule sets.

Note:

  • If an Oracle-supplied package related to XML makes changes to database objects, then these changes are not captured by capture processes. See Oracle Database PL/SQL Packages and Types Reference for information about packages related to XML.

  • If an Oracle-supplied package related to Oracle Text makes changes to database objects, then these changes are not captured by capture processes. See Oracle Text Reference for information about packages related to Oracle Text.

See Also:

Oracle Streams Replication Administrator's Guide for information about strategies to avoid having the same sequence-generated value for two different rows at different databases

NOLOGGING and UNRECOVERABLE Keywords for SQL Operations

If you use the NOLOGGING or UNRECOVERABLE keyword for a SQL operation, then the changes resulting from the SQL operation cannot be captured by a capture process. Therefore, do not use these keywords to capture the changes that result from a SQL operation.

If the object for which you are specifying the logging attributes resides in a database or tablespace in FORCE LOGGING mode, then Oracle Database ignores any NOLOGGING or UNRECOVERABLE setting until the database or tablespace is taken out of FORCE LOGGING mode. You can determine the current logging mode for a database by querying the FORCE_LOGGING column in the V$DATABASE dynamic performance view. You can determine the current logging mode for a tablespace by querying the FORCE_LOGGING column in the DBA_TABLESPACES static data dictionary view.

Note:

The UNRECOVERABLE keyword is deprecated and has been replaced with the NOLOGGING keyword in the logging_clause. Although UNRECOVERABLE is supported for backward compatibility, Oracle strongly recommends that you use the NOLOGGING keyword, when appropriate.

See Also:

Oracle Database SQL Language Reference for more information about the NOLOGGING and UNRECOVERABLE keywords, FORCE LOGGING mode, and the logging_clause

UNRECOVERABLE Clause for Direct Path Loads

If you use the UNRECOVERABLE clause in the SQL*Loader control file for a direct path load, then a capture process cannot capture the changes resulting from the direct path load. Therefore, if the changes resulting from a direct path load should be captured by a capture process, then do not use the UNRECOVERABLE clause.

If you perform a direct path load without logging changes at a source database, but you do not perform a similar direct path load at the destination databases of the source database, then apply errors can result at these destination databases when changes are made to the loaded objects at the source database. In this case, a capture process at the source database can capture changes to these objects, and one or more propagations can propagate the changes to the destination databases. When an apply process tries to apply these changes, errors result unless both the changed object and the changed rows in the object exist on the destination database.

Therefore, if you use the UNRECOVERABLE clause for a direct path load and a capture process is configured to capture changes to the loaded objects, then ensure that any destination databases contain the loaded objects and the loaded data to avoid apply errors. One way to ensure that these objects exist at the destination databases is to perform a direct path load at each of these destination databases that is similar to the direct path load performed at the source database.

If you load objects into a database or tablespace that is in FORCE LOGGING mode, then Oracle Database ignores any UNRECOVERABLE clause during a direct path load, and the loaded changes are logged. You can determine the current logging mode for a database by querying the FORCE_LOGGING column in the V$DATABASE dynamic performance view. You can determine the current logging mode for a tablespace by querying the FORCE_LOGGING column in the DBA_TABLESPACES static data dictionary view.

See Also:

Oracle Database Utilities for information about direct path loads and SQL*Loader

Supplemental Logging Data Type Restrictions

Columns of the following data types cannot be part of a supplemental log group: LOB, LONG, LONG RAW, user-defined types (including object types, REFs, varrays, nested tables), and Oracle-supplied types (including Any types, XML types, spatial types, and media types).

See Also:

Operational Requirements for Downstream Capture

The following are operational requirements for using downstream capture:

  • The source database must be running at least Oracle Database 10g and the downstream capture database must be running the same release of Oracle Database as the source database or later.

  • The downstream database must be running Oracle Database 10g Release 2 or later to configure real-time downstream capture. In this case, the source database must be running Oracle Database 10g Release 1 or later.

  • The operating system on the source and downstream capture sites must be the same, but the operating system release does not need to be the same. In addition, the downstream sites can use a different directory structure than the source site.

  • The hardware architecture on the source and downstream capture sites must be the same. For example, a downstream capture configuration with a source database on a 32-bit Sun system must have a downstream database that is configured on a 32-bit Sun system. Other hardware elements, such as the number of CPUs, memory size, and storage configuration, can be different between the source and downstream sites.

See Also:

"Local Capture and Downstream Capture"

Capture Processes Do Not Support Oracle Label Security

Capture processes do not support database objects that use Oracle Label Security (OLS).

See Also:

Oracle Label Security Administrator's Guide

Capture Process Interoperability with Oracle Streams Apply Processes

A capture process must be Oracle9i Release 2 (9.2.0.6) or later for the changes it captures to be processed by an Oracle Database 11g Release 2 (11.2) or later apply process. The data type restrictions for the release of the capture process are enforced at the source database for the capture process.

See Also:

The Oracle Streams documentation for an earlier Oracle Database release for information about capture process data type restrictions and apply process data type restrictions for that release.

Synchronous Capture Restrictions

This section describes restrictions for synchronous captures.

This section contains these topics:

Synchronous Captures Only Use Table Rules

Synchronous captures only use table rules that were created by a procedure in the DBMS_STREAMS_ADM package. Synchronous captures ignore schema rules, global rules, and rules created by a procedure in the DBMS_RULE_ADM package.

See Also:

"Synchronous Capture Rules"

Unsupported Data Types for Synchronous Captures

Synchronous capture does not capture the results of DML changes to columns of the following data types:

  • LONG

  • LONG RAW

  • CLOB

  • NCLOB

  • BLOB

  • BFILE

  • ROWID

  • User-defined types (including object types, REFs, varrays, and nested tables

  • Oracle-supplied types (including Any types, XML types, spatial types, and media types)

  • The extended data types for VARCHAR2 and NVARCHAR2 that are longer than 4000 bytes.

  • The extended data types for RAW that are longer than 2000 bytes.

These data type restrictions pertain to both ordinary (heap-organized) tables and index-organized tables.

Synchronous capture raises an error if it tries to create a row LCR for a DML change to a table containing a column of an unsupported data type. Synchronous capture returns an ORA-25341 error to the user, and the DML change is not made. In this case, modify the rules used by synchronous capture to avoid the error.

Note:

  • The rules in the positive rule set determine the types of changes captured by synchronous capture. To avoid errors, ensure that these rules do not instruct synchronous capture to capture changes to tables with unsupported data types.

  • It might be possible to configure a synchronous capture to capture changes to tables with unsupported columns. To do so, specify DELETE_COLUMN declarative rule-based transformations on the relevant synchronous capture rules to remove the unsupported columns.

See Also:

Unsupported Changes for Synchronous Captures

This section describes changes that are not supported by synchronous captures.

This section contains these topics:

Unsupported Schemas for Synchronous Captures

A synchronous capture never captures changes made to the following schemas:

  • CTXSYS

  • DBSNMP

  • DMSYS

  • DVSYS

  • EXFSYS

  • LBACSYS

  • MDDATA

  • MDSYS

  • OLAPSYS

  • ORDDATA

  • ORDPLUGINS

  • ORDSYS

  • OUTLN

  • SI_INFORMTN_SCHEMA

  • SYS

  • SYSMAN

  • SYSTEM

  • WMSYS

  • XDB

Unsupported Table Types for Synchronous Captures

A synchronous capture cannot capture DML changes made to temporary tables, object tables, or tables compressed with hybrid columnar compression.

Note:

A synchronous capture can capture changes to tables compressed with basic table compression or Advanced Row Compression if the compatibility level of the database is set to 11.2.0.0.0 or higher.

See Also:

Changes Ignored by Synchronous Capture

The following types of changes are ignored by synchronous capture:

  • DDL changes.

  • The session control statements ALTER SESSION and SET ROLE.

  • The system control statement ALTER SYSTEM.

  • Synchronous capture ignores CALL, EXPLAIN PLAN, or LOCK TABLE statements.

  • Changes made by direct path loads.

  • Changes made to a table or schema by online redefinition using the DBMS_REDEFINITION package. Online table redefinition is supported on a table for which synchronous capture captures changes, but the logical structure of the table before online redefinition must be the same as the logical structure after online redefinition.

  • Changes to actual sequence values. For example, if a user references a NEXTVAL or sets the sequence, then synchronous capture does not capture changes resulting from these operations. Also, if you share a sequence at multiple databases, then sequence values used for individual rows at these databases might vary.

  • Invocations of PL/SQL procedures, which means that a call to a PL/SQL procedure is not captured. However, if a call to a PL/SQL procedure causes changes to database objects, then these changes can be captured by synchronous capture if the changes satisfy the synchronous capture rule set.

Note:

  • If an Oracle-supplied package related to XML makes changes to database objects, then these changes are not captured by synchronous captures. See Oracle Database PL/SQL Packages and Types Reference for information about packages related to XML.

  • If an Oracle-supplied package related to Oracle Text makes changes to database objects, then these changes are not captured by synchronous captures. See Oracle Text Reference for information about packages related to Oracle Text.

See Also:

Oracle Streams Replication Administrator's Guide for information about strategies to avoid having the same sequence-generated value for two different rows at different databases

Synchronous Capture Rules and the DBMS_STREAMS_ADM Package

Although you can create a rule set for a synchronous capture using the DBMS_RULE_ADM package, only rules created using the DBMS_STREAMS_ADM package determine the behavior of a synchronous capture. A synchronous capture ignores rules created by the DBMS_RULE_ADM package.

Synchronous Captures Do Not Support Oracle Label Security

Synchronous captures do not support database objects that use Oracle Label Security (OLS).

See Also:

Oracle Label Security Administrator's Guide

Queue Restrictions

This section describes restrictions for queues.

This section contains these topics:

See Also:

"Queues"

Explicit Enqueue Restrictions for ANYDATA Queues

You cannot explicitly enqueue ANYDATA payloads that contain payloads of the following types into an ANYDATA queue:

  • CLOB

  • NCLOB

  • BLOB

  • Object types with LOB attributes

  • Object types that use type evolution or type inheritance

Note:

Payloads of ROWID data type cannot be wrapped in an ANYDATA wrapper. This restriction does not apply to payloads of UROWID data type.

See Also:

Restrictions for Buffered Messaging

To use buffered messaging, the compatibility level of the Oracle database must be 10.2.0 or higher.

The DBMS_STREAMS_MESSAGING package cannot be used to enqueue messages into or dequeue messages from a buffered queue. However, the DBMS_AQ package supports enqueue and dequeue of buffered messages.

See Also:

Triggers and Queue Tables

Using triggers on queue tables is not recommended because it can have a negative impact on performance. Also, triggers are not supported on index-organized queue tables.

Propagation Restrictions

This section describes restrictions for propagations.

This section contains these topics:

See Also:

"Message Propagation Between Queues"

Connection Qualifiers and Propagations

Connection qualifiers cannot be specified in the database links that are used by Oracle Streams propagations.

Character Set Restrictions for Propagations

Propagations can propagate ANYDATA messages that encapsulate payloads of object types, varrays, or nested tables between databases only if the databases use the same character set.

Propagations can propagate logical change records (LCRs) between databases of the same character set or different character sets.

Compatibility Requirements for Queue-To-Queue Propagations

To use queue-to-queue propagation, the compatibility level must be 10.2.0 or higher for each database that contains a queue involved in the propagation.

See Also:

"Queue-to-Queue Propagations"

Apply Process Restrictions

This section describes restrictions for apply processes.

This section contains these topics:

Unsupported Data Types for Apply Processes

An apply process does not apply row LCRs containing the results of DML changes in columns of the following data types:

  • BFILE

  • ROWID

  • User-defined types (including object types, REFs, varrays, and nested tables)

  • XMLType stored object relationally or as binary XML

  • The following Oracle-supplied types: Any types, URI types, spatial types, and media types

  • The extended data types for VARCHAR2 and NVARCHAR2 that are longer than 4000 bytes.

  • The extended data types for RAW that are longer than 2000 bytes.

An apply process raises an error if it attempts to apply a row LCR that contains information about a column of an unsupported data type. In addition, an apply process cannot apply DML changes to temporary tables or object tables. An apply process raises an error if it attempts to apply such changes. When an apply process raises an error for an LCR, it moves the transaction that includes the LCR into the error queue.

These data type restrictions pertain to both ordinary (heap-organized) tables and index-organized tables.

It is possible to configure Oracle Streams to support additional data types. For instructions, go to the My Oracle Support (formerly OracleMetaLink) Web site using a Web browser:

http://support.oracle.com/

Database bulletin 556742.1 describes additional data type support for Oracle Streams.

See Also:

"Data Types Applied"

Unsupported Data Types for Apply Handlers

Statement DML handlers cannot process LONG, LONG RAW, or nonassembled LOB column data in row LCRs. However, statement DML handlers can process LOB column data in row LCRs that have been constructed by LOB assembly. LOB assembly is enabled by default for statement DML handlers.

Procedure DML handlers and error handlers cannot process LONG or LONG RAW column data in row LCRs. However, procedure DML handlers and error handlers can process both nonassembled and assembled LOB column data in row LCRs, but these handlers cannot modify nonassembled LOB column data.

See Also:

Types of DDL Changes Ignored by an Apply Process

The following types of DDL changes are not supported by an apply process. These types of DDL changes are not applied:

  • ALTER MATERIALIZED VIEW

  • ALTER MATERIALIZED VIEW LOG

  • CREATE DATABASE LINK

  • CREATE SCHEMA AUTHORIZATION

  • CREATE MATERIALIZED VIEW

  • CREATE MATERIALIZED VIEW LOG

  • DROP DATABASE LINK

  • DROP MATERIALIZED VIEW

  • DROP MATERIALIZED VIEW LOG

  • FLASHBACK DATABASE

  • RENAME

If an apply process receives a DDL LCR that specifies an operation that cannot be applied, then the apply process ignores the DDL LCR and records the following message in the apply process trace file, followed by the DDL text that was ignored:

Apply process ignored the following DDL:

An apply process applies all other types of DDL changes if the DDL LCRs containing the changes should be applied according to the apply process rule sets.

Note:

  • An apply process applies ALTER object_type object_name RENAME changes, such as ALTER TABLE jobs RENAME. Therefore, if you want DDL changes that rename objects to be applied, then use ALTER object_type object_name RENAME statements instead of RENAME statements. After changing the name of a database object, new rules that specify the new database object name might be needed to replicate changes to the database object.

  • The name "materialized view" is synonymous with the name "snapshot". Snapshot equivalents of the statements on materialized views are ignored by an apply process.

See Also:

Chapter 5, "How Rules Are Used in Oracle Streams"

Database Structures in an Oracle Streams Environment

For captured DDL changes to be applied properly at a destination database, either the destination database must have the same database structures as the source database, or the nonidentical database structural information must not be specified in the DDL statement. Database structures include data files, tablespaces, rollback segments, and other physical and logical structures that support database objects.

For example, for captured DDL changes to tables to be applied properly at a destination database, the following conditions must be met:

  • The same storage parameters must be specified in the CREATE TABLE statement at the source database and destination database.

  • If a DDL statement refers to specific tablespaces or rollback segments, then the tablespaces or rollback segments must have the same names and compatible specifications at the source database and destination database.

    However, if the tablespaces and rollback segments are not specified in the DDL statement, then the default tablespaces and rollback segments are used. In this case, the tablespaces and rollback segments can differ at the source database and destination database.

  • The same partitioning specifications must be used at the source database and destination database.

Current Schema User Must Exist at Destination Database

For a DDL LCR to be applied at a destination database successfully, the user specified as the current_schema in the DDL LCR must exist at the destination database. The current schema is the schema that is used if no schema is specified for an object in the DDL text.

See Also:

Apply Processes Do Not Support Oracle Label Security

Apply processes do not support database objects that use Oracle Label Security (OLS).

See Also:

Oracle Label Security Administrator's Guide

Apply Process Interoperability with Oracle Streams Capture Components

An apply process must be Oracle9i Release 2 (9.2.0.6) or later to process changes captured by an Oracle Database 11g Release 2 (11.2) or later capture process. The data type restrictions for the release of the apply process are enforced at the apply process database.

An apply process must be Oracle Database 11g Release 1 (11.1) or later to process changes captured by an Oracle Database 11g Release 2 (11.2) or later synchronous capture. The data type restrictions for the release of the apply process are enforced at the apply process database.

See Also:

The Oracle Streams documentation for an earlier Oracle Database release for information about apply process data type restrictions for that release.

Messaging Client Restrictions

This section describes restrictions for messaging clients.

This section contains these topics:

See Also:

"Explicit Consumption with a Messaging Client"

Messaging Clients and Buffered Messages

Messaging clients cannot dequeue buffered messages. However, the DBMS_AQ package supports enqueue and dequeue of buffered messages.

See Also:

Oracle Database Advanced Queuing User's Guide for information about the DBMS_AQ package

Rule Restrictions

This section describes restrictions for rules.

This section contains these topics:

Restrictions for Subset Rules

The following restrictions apply to subset rules:

  • A table with the table name referenced in the subset rule must exist in the same database as the subset rule, and this table must be in the same schema referenced for the table in the subset rule.

  • If the subset rule is in the positive rule set for a capture process or a synchronous capture, then the table must contain the columns specified in the subset condition, and the data type of each of these columns must match the data type of the corresponding column at the source database.

  • If the subset rule is in the positive rule set for a propagation or apply process, then the table must contain the columns specified in the subset condition, and the data type of each column must match the data type of the corresponding column in row LCRs that evaluate to TRUE for the subset rule.

  • Creating subset rules for tables that have one or more columns of the following data types is not supported: LOB, LONG, LONG RAW, user-defined types (including object types, REFs, varrays, nested tables), and Oracle-supplied types (including Any types, XML types, spatial types, and media types).

See Also:

Restrictions for Action Contexts

An action context cannot contain information of the following data types:

  • CLOB

  • NCLOB

  • BLOB

  • LONG

  • LONG RAW

  • The extended data types for VARCHAR2 and NVARCHAR2 that are longer than 4000 bytes.

  • The extended data types for RAW that are longer than 2000 bytes.

In addition, an action context cannot contain object types with attributes of these data types, or object types that use type evolution or type inheritance.

See Also:

"Oracle Streams and Action Contexts"

Restrictions on Data Type

The following data type restrictions apply:

  • Columns of extended data type VARCHAR2(32K) cannot be referenced in rules.

Rule-Based Transformation Restrictions

This section describes restrictions for rule-based transformations.

This section contains these topics:

See Also:

Chapter 6, "Rule-Based Transformations"

Unsupported Data Types for Declarative Rule-Based Transformations

Except for add column transformations, declarative rule-based transformations that operate on columns support the same data types that are supported by Oracle Streams capture processes.

Add column transformations cannot add columns of the following data types: BLOB, CLOB, NCLOB, BFILE, LONG, LONG RAW, ROWID, user-defined types (including object types, REFs, varrays, nested tables), Oracle-supplied types (including Any types, XML types, spatial types, and media types) and extended data types for VARCHAR2, NVARCHAR2, or RAW.

See Also:

Unsupported Data Types for Custom Rule-Based Transformations

Do not modify LONG, LONG RAW, nonassembled LOB column data, or XMLType data in a custom rule-based transformation function.

See Also:

"Custom Rule-Based Transformations"

Oracle Multitenant Option Restrictions for Oracle Streams

Oracle Streams cannot be used with multitenant container databases (CDBs) or pluggable databases (PDBs).

See Also:

Oracle Database Administrator's Guide