This appendix describes Oracle Streams restrictions.
This appendix includes these topics:
This section describes restrictions for capture processes.
This section contains these topics:
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, REF
s, 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
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 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 for extended data type support. 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 extended 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. TheDBMS_STREAMS_ADM
package creates only simple rules.See Also:
"Listing the Database Objects That Are Not Compatible with Capture Processes"
"Simple Rule Conditions" for information about simple rules
Oracle Database SQL Language Reference for more information about data types
Oracle Database Utilities for more information about LogMiner restrictions for SecureFiles LOB columns
Oracle Database Upgrade Guide for information about database compatibility
This section describes changes that are not supported by capture processes.
This section contains these topics:
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
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 OLTP table 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 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:
Oracle Database Administrator's Guide for information about compressed tables
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:
"Considerations for Applying DDL Changes" for information about applying DDL changes with an apply process
Chapter 5, "How Rules Are Used in Oracle Streams" for more information about rule sets for Oracle Streams clients and for information about how messages satisfy rule sets
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 databasesIf 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:
TheUNRECOVERABLE
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 theNOLOGGING
and UNRECOVERABLE
keywords, FORCE
LOGGING
mode, and the logging_clause
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*LoaderColumns of the following data types cannot be part of a supplemental log group: LOB, LONG
, LONG
RAW
, user-defined types (including object types, REF
s, varrays, nested tables), and Oracle-supplied types (including Any
types, XML types, spatial types, and media types).
See Also:
Oracle Database SQL Language Reference for information about data types
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 database objects that use Oracle Label Security (OLS).
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 2 (11.2) 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.This section describes restrictions for synchronous captures.
This section contains these topics:
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"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, REF
s, varrays, and nested tables
Oracle-supplied types (including Any
types, XML types, spatial types, and media types)
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:
"Listing Database Objects and Columns Not Compatible with Synchronous Captures"
Chapter 5, "How Rules Are Used in Oracle Streams" for more information about rule sets for Oracle Streams clients and for information about how messages satisfy rule sets
Oracle Database SQL Language Reference for more information about data types
This section describes changes that are not supported by synchronous captures.
This section contains these topics:
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
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 OLTP table compression if the compatibility level of the database is set to 11.2.0.0.0 or higher.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 databasesAlthough 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.
This section describes restrictions for queues.
This section contains these topics:
See Also:
"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 ofROWID
data type cannot be wrapped in an ANYDATA
wrapper. This restriction does not apply to payloads of UROWID
data type.See Also:
Oracle Streams Advanced Queuing User's Guide for more information relating to ANYDATA
queues, such as wrapping payloads in an ANYDATA
wrapper, programmatic environments for enqueuing messages into and dequeuing messages from an ANYDATA
queue, propagation, and user-defined types
Oracle Database PL/SQL Packages and Types Reference for more information about the ANYDATA
type
Oracle Database SQL Language Reference for more information about data types
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:
Oracle Streams Advanced Queuing User's Guide for information about the DBMS_AQ
package
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.
This section describes restrictions for propagations.
This section contains these topics:
See Also:
"Message Propagation Between Queues"Connection qualifiers cannot be specified in the database links that are used by Oracle Streams 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.
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"This section describes restrictions for apply processes.
This section contains these topics:
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, REF
s, 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.
It is possible to configure Oracle Streams for extended data type support. 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 extended data type support for Oracle Streams.
See Also:
"Data Types Applied"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:
Oracle Streams Replication Administrator's Guide for information about LOB assembly
Oracle Database SQL Language Reference for more information about data types
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.
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.
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:
Oracle Database Concepts for more information about database structures
Oracle Database PL/SQL Packages and Types Reference for more information about the current_schema
attribute in DDL LCRs
Apply processes do not support database objects that use Oracle Label Security (OLS).
An apply process must be Oracle9i Database release 9.2.0.6 or later to process changes captured by an Oracle Database 11g Release 2 (11.2) 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) 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.This section describes restrictions for messaging clients.
This section contains these topics:
Messaging clients cannot dequeue buffered messages. However, the DBMS_AQ
package supports enqueue and dequeue of buffered messages.
This section describes restrictions for rules.
This section contains these topics:
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, REF
s, varrays, nested tables), and Oracle-supplied types (including Any
types, XML types, spatial types, and media types).
See Also:
Oracle Database SQL Language Reference for more information about data types
An action context cannot contain information of the following data types:
CLOB
NCLOB
BLOB
LONG
LONG
RAW
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"This section describes restrictions for rule-based transformations.
This section contains these topics:
Unsupported Data Types for Declarative Rule-Based Transformations
Unsupported Data Types for Custom Rule-Based Transformations
See Also:
Chapter 6, "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, REF
s, varrays, nested tables), and Oracle-supplied types (including Any
types, XML types, spatial types, and media types).
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"In an Oracle Streams replication configuration, the character set of a destination database must compatible to or a superset of the character set of its source database. Also, character repertoires of data contents must be supported by both source and destination database character sets to guarantee data integrity.
See Also:
Oracle Database Globalization Support Guide for more information about choosing a character set and character repertoires
Oracle Database Globalization Support Guide for more information about character set supersets