Oracle Streams Introduction

The streams contain following processes:

1. Capture
2. Instantiation (Not in image above)
3. Propagate
4. Apply

1) Capture process A Capture process can capture a majority of database transactions. The Capture process specifically captures DML and DDL. The Capture process uses both LogMiner and Advanced Queuing to accomplish it’s task (Note: Synchronous Capture uses internal triggers instead of LogMiner)

2) Instantiation process : composed of three steps

• Creating the object(s) at the destination Site
• Updating the Streams data dictionary with metadata
• Setting the Instantiation SCN for the object(s)

Once instantiation is complete, the instantiation SCN will be the same at both the source and destination site(s), indicating to Streams that it is from this SCN forward that changes should be captured, propagated, and applied for the destination.

Instantiation Levels include

• Table Level
• Schema Level
• Database (Global) Level
• Tablespace (this requires special steps)

Instantiation Methods

• Data Pump
• Transportable Tablespaces
• RMAN for entire database
• Manual method

3) Propagate process The Propagate process does the actual Propagation between the source and target queues.  Propagation has two configuration options, queue-to-queue or queue-to-dblink. In the queue-to-queue configuration, each Propagation has its own propagation job. It should be noted that queue-to-queue propagation is recommended for Streams in an RAC environment. The default configuration of queue-to-dblink has one shared propagation job.

4) Apply process It’s made up of multiple parts. Those parts are as follows:

• Reader server: Takes the LCRs and converts it into transactions, preserving transactional order, and dependencies.
• Coordinator process: Takes the transactions from reader server and sends them to Apply server. This process also monitors the Apply server to ensure that the transactions are applied in the correct order.
• Apply server: Applies the LCR or message to the handler, either an Apply handler or message handler. Apply server also deals with placing the LCR or message into the appropriate error queue if it cannot be applied.

Oracle Streams Restrictions

1. Capture Process Restrictions
2. Apply Process Restrictions

Simply query DBA_STREAMS_UNSUPPORTED view and you can find out the reason why a particular table could not be streamed.

1) Capture Process Restrictions

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
• Oracle-supplied types: Any types, URI types, spatial types, and media types

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

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

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.

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.

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.

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.

2.  Apply Process Restrictions

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)
• Oracle-supplied types: Any types, URI types, spatial types, and media 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:Oracle Support Master Note for Streams Setup Scripts (Doc ID 789445.1)

What is new at Oracle11g Streams?

Oracle Database 11g enhanced streams replication to provide a 30 to 50 percent performance improvement for SQL Apply and to support XML Type (CLOBs) and transparent data encryption.
New manageability improvements includes

1. Streams performance advisor
2. Topology views
3. Automatic Workload Repository (AWR) and ADDM support
4. Data comparison utility and synchronous capture
5. New Advanced Queuing (AQ) features include
6. Java Message Service (JMS) performance improvements
7. Direct streams AQ support in Java Database Connectivity (JDBC)
8. Scalable event notification

• Streams in 11g has much better monitoring and tracking than previous versions.

o      LCR tracking
o      Expanded streams specific error messages
o      Oracle Streams Performance Advisor
o      Better documentation and examples

• Distinguishes between implicit and explicit consumption (direct bearing on my work)
• Streams 11g switches from DBMS_JOB to DBMS_SCHEDULER for propagation

How does stream work uner RAC environment?

When you create the queue in an instance of the database, the instance assumes the ownership of the associated queue table. The capture and apply processes run in the instance that owns the queue table associated with their respective queues.  In RAC environments, it is possible to specify the ownership of the queue table. You can specify the primary instance and secondary instance for a given queue table.

When the primary instance becomes unavailable, the secondary instance assumes the ownership of the queue, and the Streams processes are restarted on the secondary instance automatically. When the primary instance becomes available, the queue ownership and the Streams processes switch back
to the primary instance.

You can use ALTER_QUEUE_TABLE procedure in the DBMS_AQADM package to define the primary and secondary instances for the queue table.

SQL> begin
dbms_aqadm.alter_queue_table (
queue_table => ‘CAP_Q_T’,
primary_instance => 2,
secondary_instance => 3);
end;

The view DBA_QUEUE_TABLES shows information about the queue table ownership and its current owner instance:

SQL> select queue_table,
owner_instance,
primary_instance,
secondary_instance
from dba_queue_tables
where owner = ‘STRMADMIN’;