An oracle stream is an information-sharing technology, which allows the propagation and management of data, event, and transactions within oracle databases or between oracle and non-oracle databases. Oracle streams is flexible in the sense that it allows user-intervention- users can specify what information goes into the stream, the route of flow, what happens to events in the stream and how the stream terminates. It is used to capture events such as DML (insert, update and delete) and DDL (alter, drop, rename) operations. The result is a new feature that provides greater functionality and flexibility than traditional solutions for capturing and managing events, and sharing the events with other databases and applications.
Prerequisites
GLOBAL_NAMES: This parameter must be set to true at each database that is participating in your Streams environment.
JOB_QUEUE_PROCESSES: This parameter must be set to at least 2 at each database that is propagating events in your Streams environment. It should be set to the same value as the maximum number of jobs that can run simultaneously plus one
COMPATIBLE: This parameter must be set to 10.1.0 or higher at each database that is participating in your Streams environment.
STREAMS_POOL_SIZE: Optionally set this parameter to an appropriate value for each database in the environment. This parameter specifies the size of the Streams pool. The Streams pool contains captured events. In addition, the Streams pool is used for internal communications during parallel capture and apply. If STREAMS_POOL_SIZE is set to zero (the default), then Streams uses the shared pool. In this case, you may need to increase the size of the shared pool at each database.
Stream – Overview
Oracle Streams provides a set of elements that allow users to control what information is put into a stream, how the stream flows or is routed from node to node, what happens to events in the stream as they flow into each node, and how the stream terminates. By specifying the configuration of the elements acting on the stream, a user can address specific requirements.
The architecture of Oracle Streams is very flexible. As shown in the above diagram, Streams contains three basic elements. Capture
Staging
Consumption (Apply)
Capture
The capture process is responsible for the identification of data to capture of events (all database changes, and application generated messages) into the staging area. These events are captured in two ways. With implicit capture, the server captures DML and DDL events at a source database. Explicit capture allows applications to explicitly generate events and place them in the staging area. It can be done either implicit capture in which the server captures DML and DDL events at a source database using oracle's default rules or explicit capture, in which a customized configuration is used to capture data using procedures.
Implicit Capture:
The capture process retrieves change data extracted from the redo log, either by hot mining the online redo log or by mining archived log files with the help of implicit capture. After retrieving the data, the capture process formats it into a Logical Change Record (LCR) and places it in a staging area for further processing. The capture process can intelligently filter LCRs based upon defined rules. Thus, only changes to desired objects are captured.
Explicit Capture:
User applications can explicitly enqueue user messages representing events into the staging area. These messages can be formatted as LCRs, which will allow them to be consumed by the apply engine, or they can be formatted for consumption by another user application.
Staging
Once captured, events are placed in a staging area. The staging area is a queue and acts as a temporary repository for logical change records (LCR) until they are subscribed to. The subscriber (a user application or another staging area or default apply process) has control over the contents of the staging area. Therefore, the subscriber can decide which records are propagated or consumed from the queue. For events propagation from a queue to take place, a user must be the owner of the queue and appropriate privileges are needed, not only on the source queue but also on the target queue. Moreover, a particular target queue can accept events from more than one source queues
Propagation
If the subscriber is another staging area, the event is propagated to the other staging area, either within the same database or in a remote database, as appropriate. To simplify network routing and reduce WAN traffic, events need not be sent to all databases and applications. Rather, they can be directed through staging areas on one or more systems until they reach the subscribing system. For example, an event may propagate via a hub database that does not actually apply the event. A single staging area can stage events from multiple databases, simplifying setup and configuration.
Consumption
The apply process is responsible for applying the changes to the target database. This is possible in two ways namely- Default consumption (implicit) and customized consumption (explicit). In default consumption, the apply engine is used to apply the changes to the database directly. if a conflict occurs, the apply engine resolves it by invoking resolution (data transmission) routines.
In customized consumption, the logical change records are passed as arguments to a user-defined function for processing. If the customized procedure process DML LCRs, DDL LCRs and enqueued messages, they are called DML handlers, DDL handlers and message handlers respectively.
In customized consumption, the logical change records are passed as arguments to a user-defined function for processing. If the customized procedure process DML LCRs, DDL LCRs and enqueued messages, they are called DML handlers, DDL handlers and message handlers respectively.
Default Apply
The default apply engine applies DML changes and DDL changes represented by implicitly or explicitly captured LCRs. The default apply engine will detect conflicts where the destination row has been changed and does not contain the expected values. If a conflict is detected, then a resolution routine may be invoked.
User-Defined Function Apply
The apply engine can pass the LCR or a user message to a user-defined function. This provides the greatest amount of flexibility in processing an event. A typical application of a user-defined function would be to reformat the data represented by the LCR before applying it to a local table, for example, field format, object name and column name mapping transformations. A user-defined function could also be used to perform column sub setting, or to update other objects that may not be present in the source database.
Explicit Dequeue
User applications can explicitly dequeue LCRs or user messages from the receiving staging area. This allows a user application to efficiently access the data in a Streams' staging area. Streams can send notifications to registered PL/SQL or OCI functions, giving the applications an alternative to polling for new messages. Of course, applications can still poll, or even wait, for new subscribed messages in the staging area to become available.
Rules
Streams lets users control which information to share and where to send it by specifying rules. At the highest level, users can indicate if they want to capture, propagate or apply changes at the table, schema, or global (database) level. For more complex requirements, for example, to apply only a particular subset of data at a given location, users can specify a rule condition similar to the condition in the WHERE clause of a SQL query. If necessary, related rules can be grouped into rule sets.
Transformations
A transformation is a change in the form of an object participating in capture and apply or a change in the data it holds. Transformations can include changing the data type representation of a particular column in a table at a particular site, adding a column to a table at one site only, or including a subset of the data in a table at a particular site.
A transformation can be specified during enqueue, to transform the message to the correct type before inserting it into the staging area. It can also be specified for propagation, which may be useful for subsetting data before it is sent to a remote site. Finally, it can be specified at dequeue or local apply, which can be useful for formatting a message in a manner appropriate for a specific destination.Deployment for Specific Markets
Oracle Streams satisfies customer's information sharing requirements in a variety of markets. For example, customers can use Oracle Streams to create Event Notification, Replication, and Data Warehouse Loading solutions. Oracle also provides features built on Streams that extend its capabilities for tasks. Advanced Queuing is built on Oracle Streams and provides robust message queuing functionality integrated with the Oracle9i Database. Of course, all customers can utilize the full power of Oracle Streams, and create configurations that seemingly span multiple markets, enabling new classes of applications. In addition, all deployments and their associated meta-data are compatible. For example, a replication installation can easily be extended to load a data warehouse or enable bi-directional replication--a complete reconfiguration is not required.
Oracle Streams--Single, Unified Solution
Oracle Streams satisfies the most demanding information sharing requirements using a common infrastructure. Complex distributed environments benefit from a single solution that satisfies their information sharing requirements. As an organization grows, developers and administrators can be confident that Oracle Streams has the flexibility to meet their changing requirements.
Uses of Streams
The following sections briefly describe some of the reasons for using Streams. In some cases, Streams components provide infrastructure for various features of Oracle.
Message Queuing
Oracle Streams Advanced Queuing (AQ) enables user applications to enqueue messages into a queue, propagate messages to subscribing queues, notify user applications that messages are ready for consumption, and dequeue messages at the destination. A queue may be configured to stage messages of a particular type only, or a queue may be configured as a SYS.AnyData queue. Messages of almost any type can be wrapped in a SYS. Any Data wrapper and staged in SYS.AnyData queues. AQ supports all the standard features of message queuing systems, including multiconsumer queues, publish and subscribe, content-based routing, Internet propagation, transformations, and gateways to other messaging subsystems.
You can create a queue at a database, and applications can enqueue messages into the queue explicitly. Subscribing applications or messaging clients can dequeue messages directly from this queue. If an application is remote, then a queue may be created in a remote database that subscribes to messages published in the source queue. The destination application can dequeue messages from the remote queue. Alternatively, the destination application can dequeue messages directly from the source queue using a variety of standard protocols.
Data Replication
Streams can capture DML and DDL changes made to database objects and replicate those changes to one or more other databases. A Streams capture process captures changes made to source database objects and formats them into LCRs, which can be propagated to destination databases and then applied by Streams apply processes.
The destination databases can allow DML and DDL changes to the same database objects, and these changes may or may not be propagated to the other databases in the environment. In other words, you can configure a Streams environment with one database that propagates changes, or you can configure an environment where changes are propagated between databases bidirectionally. Also, the tables for which data is shared do not need to be identical copies at all databases. Both the structure and the contents of these tables can differ at different databases, and the information in these tables can be shared between these databases.
Event Management and Notification
Business events are valuable communications between applications or organizations. An application may enqueue events into a queue explicitly, or a Streams capture process may capture a database event. These captured events may be DML or DDL changes. Propagations may propagate events in a stream through multiple queues. Finally, a user application may dequeue events explicitly, or a Streams apply process may dequeue events implicitly. An apply process may re-enqueue these events explicitly into the same queue or a different queue if necessary.
You can configure queues to retain explicitly-enqueued messages after consumption for a specified period of time. This capability enables you to use Advanced Queuing (AQ) as a business event management system. AQ stores all messages in the database in a transactional manner, where they can be automatically audited and tracked. You can use this audit trail to extract intelligence about the business operations.
Capture processes, propagations, apply processes, and messaging clients perform actions based on rules. You specify which events are captured, propagated, applied, and dequeued using rules, and a built-in rules engine evaluates events based on these rules. The ability to capture events and propagate them to relevant consumers based on rules means that you can use Streams for event notification. Events staged in a queue may be dequeued explicitly by a messaging client or an application, and then actions can be taken based on these events, which may include an email notification, or passing the message to a wireless gateway for transmission to a cell phone or pager.
Data Warehouse Loading
Data warehouse loading is a special case of data replication. Some of the most critical tasks in creating and maintaining a data warehouse include refreshing existing data, and adding new data from the operational databases. Streams components can capture changes made to a production system and send those changes to a staging database or directly to a data warehouse or operational data store. Streams capture of redo log information avoids unnecessary overhead on the production systems. Support for data transformations and user-defined apply procedures enables the necessary flexibility to reformat data or update warehouse-specific data fields as data is loaded. In addition, Change Data Capture uses some of the components of Streams to identify data that has changed so that this data can be loaded into a data warehouse.
Data Protection
One solution for data protection is to create a local or remote copy of a production database. In the event of human error or a catastrophe, the copy can be used to resume processing. You can use Streams to configure flexible high availability environments. In addition, you can use Oracle Data Guard, a data protection feature that uses some of the same infrastructure as Streams, to create and maintain a logical standby database, which is a logically equivalent standby copy of a production database. As in the case of Streams replication, a capture process captures changes in the redo log and formats these changes into LCRs. These LCRs are applied at the standby databases.
The standby databases are fully open for read/write and may include specialized indexes or other database objects. Therefore, these standby databases can be queried as updates are applied, making Oracle Data Guard a good solution for off loading queries from a production database.
It is important to move the updates to the remote site as soon as possible with a logical standby database. Doing so ensures that, in the event of a failure, lost transactions are minimal. By directly and synchronously writing the redo logs at the remote database, you can achieve no data loss in the event of a disaster. At the standby system, the changes are captured and directly applied to the standby database with an apply process.
Overview of Oracle to Non-Oracle Data Sharing
If an Oracle database is the source and a non-Oracle database is the destination, then the non-Oracle database destination lacks the following Streams mechanisms:
A queue to receive events
An apply process to dequeue and apply events
To share DML changes from an Oracle source database with a non-Oracle destination database, the Oracle database functions as a proxy and carries out some of the steps that would normally be done at the destination database. That is, the events intended for the non-Oracle destination database are dequeued in the Oracle database itself, and an apply process at the Oracle database uses Heterogeneous Services to apply the events to the non-Oracle database across a network connection through a gateway. Figure shows an Oracle databases sharing data with a non-Oracle database.
Overview of Non-Oracle to Oracle Data Sharing
To capture and propagate changes from a non-Oracle database to an Oracle database, a custom application is required. This application gets the changes made to the non-Oracle database by reading from transaction logs, using triggers, or some other method. The application must assemble and order the transactions and must convert each change into an LCR. Next, the application must enqueue the LCRs into a queue in an Oracle database by using the PL/SQL interface, where they can be processed by an apply process. Figure shows a non-Oracle databases sharing data with an Oracle database.
Streams Configurations Single as well multiple databases
Below pictures show how Streams might be configured to share information within a single database or share information between two different databases.
Streams Configuration in a Single Database
Streams Configuration Sharing Information Between Databases
Administration Tools for a Streams Environment
Several tools are available for configuring, administering, and monitoring your Streams environment. Oracle-supplied PL/SQL packages are the primary configuration and management tool, while the Streams tool in the Oracle Enterprise Manager Console provides some configuration, administration, and monitoring capabilities to help you manage your environment. Additionally, Streams data dictionary views keep you informed about your Streams environment.
Oracle-Supplied PL/SQL Packages
The following Oracle-supplied PL/SQL packages contain procedures and functions for configuring and managing a Streams environment.
DBMS_STREAMS_ADM Package
The DBMS_STREAMS_ADM package provides an administrative interface for adding and removing simple rules for capture processes, propagations, and apply processes at the table, schema, and database level. This package also enables you to add rules that control which events a propagation propagates and which events a messaging client dequeues. This package also contains procedures for creating queues and for managing Streams metadata, such as data dictionary information. This package also contains procedures that enable you to configure and maintain a Streams replication environment for specific tablespaces. This package is provided as an easy way to complete common tasks in a Streams environment. You can use other packages, such as the DBMS_CAPTURE_ADM, DBMS_PROPAGATION_ADM, DBMS_APPLY_ADM, DBMS_RULE_ADM, and DBMS_AQADM packages, to complete these same tasks, as well as tasks that require additional customization.
DBMS_CAPTURE_ADM Package
The DBMS_CAPTURE_ADM package provides an administrative interface for starting, stopping, and configuring a capture process. This package also provides administrative procedures that prepare database objects at the source database for instantiation at a destination database.
DBMS_PROPAGATION_ADM Package
The DBMS_PROPAGATION_ADM package provides an administrative interface for configuring propagation from a source queue to a destination queue.
DBMS_APPLY_ADM Package
The DBMS_APPLY_ADM package provides an administrative interface for starting, stopping, and configuring an apply process. This package includes procedures that enable you to configure apply handlers, set enqueue destinations for events, and specify execution directives for events. This package also provides administrative procedures that set the instantiation SCN for objects at a destination database. This package also includes subprograms for configuring conflict detection and resolution and for managing apply errors.
DBMS_STREAMS_MESSAGING Package
The DBMS_STREAMS_MESSAGING package provides interfaces to enqueue messages into and dequeue messages from a SYS.AnyData queue.
DBMS_RULE_ADM Package
The DBMS_RULE_ADM package provides an administrative interface for creating and managing rules, rule sets, and rule evaluation contexts. This package also contains subprograms for managing privileges related to rules.
DBMS_RULE Package
The DBMS_RULE package contains the EVALUATE procedure, which evaluates a rule set. The goal of this procedure is to produce the list of satisfied rules, based on the data. This package also contains subprograms that enable you to use iterators during rule evaluation. Instead of returning all rules that evaluate to TRUE or MAYBE for an evaluation, iterators can return one rule at a time.
DBMS_STREAMS Package
The DBMS_STREAMS package provides interfaces to convert SYS.AnyData objects into LCR objects, to return information about Streams attributes and Streams clients, and to annotate redo entries generated by a session with a tag. This tag may affect the behavior of a capture process, a propagation job, an apply process, or a messaging client whose rules include specifications for these tags in redo entries or LCRs.
DBMS_STREAMS_AUTH Package
The DBMS_STREAMS_AUTH package provides interfaces for granting privileges to Streams administrators and revoking privileges from Streams administrators.
DBMS_STREAMS_TABLESPACE_ADM
The DBMS_STREAMS_TABLESPACE_ADM package provides administrative procedures for copying tablespaces between databases and moving tablespaces from one database to another. This package uses transportable tablespaces, Data Pump, and the DBMS_FILE_TRANSFER package.
Streams Data Dictionary Views
Every database in a Streams environment has Streams data dictionary views. These views maintain administrative information about local rules, objects, capture processes, propagations, apply processes, and messaging clients. You can use these views to monitor your Streams environment.
Streams Tool in the Oracle Enterprise Manager Console
To help configure, administer, and monitor Streams environments, Oracle provides a Streams tool in the Oracle Enterprise Manager Console. You also can use the Streams tool to generate Streams configuration scripts, which you can then modify and run to configure your Streams environment. The Streams tool online help is the primary documentation source for this tool. Figures shows the Topology tab in the Streams tool.
Handy queries for apply administration of stream.
Information about apply process
select * from dba_apply;
SELECT APPLY_NAME,QUEUE_NAME,RULE_SET_NAME,DECODE(APPLY_CAPTURED,'YES', 'Captured','NO', 'User-Enqueued') APPLY_CAPTURED,STATUS
FROM DBA_APPLY;
Find out apply process status
select apply_name,status
from dba_apply;
exec dbms_apply_adm.start_apply(apply_name=>'&apply_name');
exec dbms_apply_adm.stop_apply(apply_name=>'&apply_name');
Apply parameter
SELECT PARAMETER,VALUE,SET_BY_USER
FROM DBA_APPLY_PARAMETERS
WHERE APPLY_NAME = '&apply_parameter';
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(apply_name => '&apply_parameter',parameter => 'commit_serialization',value => 'none');
END;
/
Determine any instantiation
Instantiation SCN is SCN that apply process discard any SCN lower than or equal and commit any SCN higher than that.
SELECT SOURCE_DATABASE,SOURCE_OBJECT_OWNER,SOURCE_OBJECT_NAME,
INSTANTIATION_SCN
FROM DBA_APPLY_INSTANTIATED_OBJECTS;
Find out about any handler -- Any DML handler
SELECT OBJECT_OWNER,OBJECT_NAME,OPERATION_NAME,USER_PROCEDURE,
DECODE(ERROR_HANDLER,'Y', 'Error','N', 'DML') ERROR_HANDLER
FROM DBA_APPLY_DML_HANDLERS
WHERE APPLY_DATABASE_LINK IS NULLORDER BY OBJECT_OWNER, OBJECT_NAME, ERROR_HANDLER;
Any DDL/Message handler
SELECT APPLY_NAME, DDL_HANDLER, MESSAGE_HANDLER FROM DBA_APPLY;
Using key substitution for any table
Key substitution is useful when there is no PK for distinguishing rows.
SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, APPLY_DATABASE_LINK
FROM DBA_APPLY_KEY_COLUMNS
ORDER BY APPLY_DATABASE_LINK, OBJECT_OWNER, OBJECT_NAME;
Update conflict handler
SELECT OBJECT_OWNER,OBJECT_NAME,METHOD_NAME,RESOLUTION_COLUMN,
COLUMN_NAME
FROM DBA_APPLY_CONFLICT_COLUMNS
ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;
Reader servers
In parallel servers, reader server dequeues data and translates them to transactions, It also checks dependencies, Delivers all data to coordinator
SELECT DECODE(ap.APPLY_CAPTURED,'YES','Captured LCRS','NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED,SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,r.STATE,r.TOTAL_MESSAGES_DEQUEUED
FROM V$STREAMS_APPLY_READER r, V$SESSION s, DBA_APPLY ap
WHERE r.APPLY_NAME = '&apply_name' AND r.SID = s.SID AND r.SERIAL# = s.SERIAL# AND r.APPLY_NAME = ap.APPLY_NAME;
Check out latency
-- Capture to dequeue latency
-- Latency : =(Dequeue_time in dest database - event creation in source DB)
-- Creation: = Time when redo log generated in source database / time user-message enqueued
-- Message number = MSG number which was dequeued.
SELECT (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,DEQUEUED_MESSAGE_NUMBERFROM V$STREAMS_APPLY_READERWHERE APPLY_NAME = '&apply_name';
Capture to apply latency
SELECT (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') "Event Creation",TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",HWM_MESSAGE_NUMBER "Applied Message Number"
FROM V$STREAMS_APPLY_COORDINATOR
WHERE APPLY_NAME = '&apply_name';
OR
SELECT (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')"Event Creation",TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",APPLIED_MESSAGE_NUMBER "Applied Message Number"
FROM DBA_APPLY_PROGRESSWHERE APPLY_NAME = '&apply_name';
Apply coordinator
select * from v$streams_apply_coordinator;
Effectiveness of parallelism in apply process.
SELECT COUNT(SERVER_ID) "Effective Parallelism"
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'STRMADMIN_ORA9IPR_US_ORAC' ANDTOTAL_MESSAGES_APPLIED > 0;
How many message applied by each apply process
SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'STRMADMIN_ORA9IPR_US_ORAC'ORDER BY SERVER_ID;
Find apply error -- Find out apply error reason, two options : reexecute transaction, delete transaction.
select * from apply_error;
Find out progpagation rule set
SELECT RULE_SET_OWNER, RULE_SET_NAME FROM DBA_apply WHERE apply_NAME = '&apply_name';
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
TABLE_OWNER'.'TABLE_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_TABLE_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
SCHEMA_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
null,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name);
Which DML/DDL rules the capture process is capturing
select * from "DBA_STREAMS_TABLE_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name and apply_name='&apply_name');
Which schame rules the capture process is capturing
select * from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name and apply_name='&apply_name');
Which global rules the capture process is capturing
select * from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name and apply_name='&apply_name');