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:

  • SecureFile CLOB, NCLOB, and BLOB

  • 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

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.

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

  • ORDPLUGINS

  • ORDSYS

  • OUTLN

  • SI_INFORMTN_SCHEMA

  • SYS

  • SYSMAN

  • SYSTEM

  • WMSYS

  • XDB

Unsupported DML Changes for Capture Processes

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

  • A capture process cannot capture changes made to an index-organized table if the index-organized table contains any columns of the following data types:

    • ROWID

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

    • XMLType stored object relationally or as binary XML (XMLType stored as CLOB is supported.)

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

    If an index-organized table contains a column of one of these data types, then a capture process raises an error when a user makes a change to the index-organized table and the change satisfies the capture process rule sets.

    If an index-organized table does not contain a column of one of these data types, then a capture process can capture changes to it.

  • A capture process cannot capture DML changes made to temporary tables, object tables, or tables stored with segment compression enabled. A capture process raises an error if it attempts to capture such changes.

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

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.

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

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 if you want 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

LOB, LONG, LONG RAW, user-defined type, and Oracle-supplied type columns cannot be part of a supplemental log group.

See Also:

Oracle Streams Replication Administrator's Guide

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 Database release 9.2.0.6 or later for the changes it captures to be processed by an Oracle Database 11g Release 1 (11.1) apply process. The data type restrictions for the release of the capture process are enforced at the capture process database.

See Also:

The Oracle Streams documentation for an earlier Oracle Database release for information about capture 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)

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

  • ORDPLUGINS

  • ORDSYS

  • OUTLN

  • SI_INFORMTN_SCHEMA

  • SYS

  • SYSMAN

  • SYSTEM

  • WMSYS

  • XDB

Unsupported DML Changes for Synchronous Captures

A synchronous capture captures the results of DML changes that satisfy its rule set, except for the following types of DML changes:

  • Synchronous capture cannot capture changes made to an index-organized table if the index-organized table contains any 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)

    If an index-organized table contains a column of one of these data types, then synchronous capture raises an error when a user makes a change to the index-organized table and the change satisfies the synchronous capture rule set.

    If an index-organized table does not contain a column of one of these data types, then a synchronous capture can capture changes to it.

  • Synchronous capture cannot capture DML changes made to temporary tables or object tables. Synchronous capture raises an error if it attempts to capture such changes.

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

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.

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

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:

  • SecureFile CLOB, NCLOB, and BLOB

  • BFILE

  • ROWID

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

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

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.

See Also:

"Data Types Applied"

Unsupported Data Types for Apply Handlers

Do not modify LONG, LONG RAW, or nonassembled LOB column data in an LCR with DML handlers or error handlers. DML handlers and error handlers can modify LOB columns in row LCRs that have been constructed by LOB assembly.

See Also:

"Process Messages with Apply Handlers"

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 6, "How Rules Are Used in Oracle Streams" for more information about 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 Processes

An apply process must be Oracle9i Database release 9.2.0.6 or later to process changes captured by an Oracle Database 11g Release 1 (11.1) capture process. 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 Streams 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 LOB, LONG, LONG RAW, or user-defined type columns is not supported.

See Also:

"Subset Rules"

Rule-Based Transformation Restrictions

This section describes restrictions for rule-based transformations.

This section contains these topics:

Unsupported Data Types for Rule-Based Transformations

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

See Also:

"Custom Rule-Based Transformations"