· Oracle Database 18c is the terminal release for support of the Oracle Streams feature. Oracle Streams will be desupported from Oracle Database 19 onwards. Oracle Streams was deprecated in Oracle Database 12c (). It does not support features introduced in Database version and later releases, including the multitenant architecture, the LONG VARCHAR data type, long . · Oracle Streams is a replication technology that enables real-time data integration across systems. The Streams feature was introduced in the Oracle database in version 9i. It allows you to track change data from the operational system(s) as they occur and then apply that in a staging database for further processing as part of an Extract/Transform/Load (ETL) questions: Vivek Sinha. Oracle Streams enables information sharing. Using Oracle Streams, each unit of shared information is called a message, and you can share these messages in a stream. The stream can propagate information within a database or from one database to another. The stream routes specified information to specified destinations.

Oracle assumes complete change management responsibility by applying implicit capture to automatically capture changes. Oracle Streams will capture and distribute database updates, events, and application messages.

It will automatically apply updates to destination databases, or pass events and messages to custom procedures and applications. The major difference between Oracle Streams and traditional Change Data Capture is that CDC captures inserts, updates, and deletes made to tables while on the other hand Streams goes even further by capturing event capture, transport transformation and consumption.

This log-based Stream CDC happens synchronously or asynchronously and therefore it does not impact database performance. Hevo loads the data onto the desired Data Warehouse, enriches the data, and transforms it into an analysis-ready form without writing a single line of code.

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different Business Intelligence BI tools as well.

Synchronous Capture uses triggers on the source database to capture change data. It has no latency because the change data is captured continuously and in real-time on the source database. The change tables are populated when DML operations on the source table are committed.

New change data arrives automatically as DML operations on the source tables are committed. Asynchronous capture records change data after the changes have been committed to the source database by using the database redo log files. With HotLog asynchronous capture, a process on the source database picks up new transactions from a redo log file.

There is a brief latency between the act of committing source table transactions and the arrival of change data. New change data arrives automatically, on a transaction-by-transaction basis from the current online redo log file. New change data arrives automatically, on a log-by-log basis, as log transport services make redo log files available. Redo transport services control the automated transfer of redo log files from the source database to the staging database.

Publishers define change sets in publisher-defined AutoLog change sources. The tables in your source database are modified to put triggers on, and the source database transactions will not commit until the changes are propagated over to the target table. It has the same performance implications as capture via user triggers.

Asynchronous Change Data Capture generates components of Oracle Streams to capture change data and to populate change sets. These components must not be reused or shared for any other purposes.

For example, the capture queue that Change Data Capture generates for a Distributed HotLog change source should not be used as the source queue for new user-created Streams propagations.

Messages in a staging area are consumed by the apply engine, where the changes they represent are applied to a database, or they are consumed by an application. The Oracle Streams apply engine works on the following:. If you are configuring Oracle Streams in an Oracle Real Application Clusters Oracle RAC environment, then the archive log files of all threads from all instances must be available to any instance running a capture process.

This requirement pertains to both local and downstream capture processes. Some initialization parameters are important for the configuration, operation, reliability, and performance of an Oracle Streams environment. Set these parameters appropriately for your Oracle Streams environment. Table describes the initialization parameters that are relevant to Oracle Streams.

This table specifies whether each parameter is modifiable. Specifies whether a database link is required to have the same name as the database to which it connects. To use Oracle Streams to share information between databases, set this parameter to true at each database that is participating in your Oracle Streams environment.

This parameter must be set at both the source database and the downstream database. Defines up to 31 log archive destinations, where n is 1 , 2 , 3 , To use downstream capture and copy the redo data to the downstream database using redo transport services, at least one log archive destination must be set at the site running the downstream capture process.

Specifies the availability state of the corresponding destination. Default: 5 MB to 32 MB depending on configuration. Specifies the amount of memory in bytes that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. If an Oracle Streams capture process is running on the database, then set this parameter properly so that the capture process reads redo log records from the redo log buffer rather than from the hard disk.

Range: 0 to the physical memory size available to Oracle Database. Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, plus external procedures and cartridges, each of which uses a separate process.

In an Oracle Streams environment, ensure that this parameter is set to the default value of 4 or higher. Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Ensure that the value of this parameter allows for all background processes, such as locks and slave processes. In Oracle Streams, capture processes, apply processes, XStream inbound servers, and XStream outbound servers use background processes.

Propagations use background processes in combined capture and apply configurations. Propagations use Oracle Scheduler slave processes in configurations that do not use combined capture and apply. Range: 1 to 2 To run one or more capture processes, apply processes, XStream outbound servers, or XStream inbound servers in a database, you might need to increase the size of this parameter.

Each background process in a database requires a session. Range: 64 MB to operating system-dependent. If this parameter is set to a nonzero value, then the size of the Oracle Streams pool is managed by Automatic Shared Memory Management. If the parameter is specified, then the user-specified value indicates a minimum value for the shared memory pool.

Specifies in bytes the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. Specifies in bytes the size of the Oracle Streams pool. The Oracle Streams pool contains buffered queue messages. In addition, the Oracle Streams pool is used for internal communications during parallel capture and apply. This parameter is modifiable.

If this parameter is reduced to zero when an instance is running, then Oracle Streams processes and jobs might not run. Ensure that there is enough memory to accommodate the Oracle Streams components. The following are the minimum requirements:. The buffered queue is where the buffered messages are stored. For example, if parallelism is set to 3 for a capture process, then at least 45 MB is required for the capture process.

If a database has two buffered queues, then at least 20 MB is required for the buffered queues. If parallelism is set to 4 for an apply process, then at least 4 MB is required for the apply process. To collect elapsed time statistics in the dynamic performance views related to Oracle Streams, set this parameter to true. For a database running one or more capture processes, ensure that this parameter is set to specify an adequate undo retention period. If you run one or more capture processes and you are unsure about the proper setting, then try setting this parameter to at least If you encounter "snapshot too old" errors, then increase the setting for this parameter until these errors cease.

Oracle Database Reference for more information about these initialization parameters. Oracle Database XStream Guide. The Oracle Streams pool stores buffered queue messages in memory, and it provides memory for capture processes, apply processes, XStream outbound servers, and XStream inbound servers. The Oracle Streams pool always stores LCRs captured by a capture process, and it stores LCRs and messages that are enqueued into a buffered queue by applications. The Oracle Streams pool is initialized the first time any one of the following actions occurs in a database:.

Oracle Streams components manipulate messages in a buffered queue. These components include capture processes, propagations, apply processes, XStream outbound servers, and XStream inbound servers. Also, Data Pump export and import operations initialize the Oracle Streams pool because these operations use buffered queues. The Oracle Streams pool is used to optimize dequeue operations from persistent queues.

The Oracle Streams pool is not used to optimize dequeue operations from persistent queues in an Oracle RAC configuration. Oracle Streams Concepts and Administration. When you use Automatic Memory Management, you can still set the following initialization parameters:. Oracle Database Administrator's Guide. Oracle Database Reference. The Automatic Shared Memory Management feature automatically manages the size of the Oracle Streams pool when the following conditions are met:. You can set a minimum size if your environment needs a minimum amount of memory in the Oracle Streams pool to function properly.

For example, consider the following configuration in a database before Oracle Streams is used for the first time:. Given this configuration, the amount of memory allocated after Oracle Streams is used for the first time is the following:.

When you use a capture process to capture changes, supplemental logging must be specified for certain columns at a source database for changes to the columns to be applied successfully at a destination database.

Supplemental logging places additional information in the redo log for these columns. A capture process captures this additional information and places it in logical change records LCRs , and an apply process might need this additional information to apply changes properly. There are two types of supplemental logging: database supplemental logging and table supplemental logging. Database supplemental logging specifies supplemental logging for an entire database, while table supplemental logging enables you to specify log groups for supplemental logging of a particular table.

If you use table supplemental logging, then you can choose between two types of log groups: unconditional log groups and conditional log groups. Unconditional log groups log the before images of specified columns when the table is changed, regardless of whether the change affected any of the specified columns.

Unconditional log groups are sometimes referred to as "always log groups. Supplementing logging at the database level, unconditional log groups at the table level, and conditional log groups at the table level determine which old values are logged for a change. If you plan to use one or more apply processes to apply LCRs captured by a capture process, then you must enable supplemental logging at the source database for the following types of columns in tables at the destination database :.

Any columns at the source database that are used in a primary key in tables for which changes are applied at a destination database must be unconditionally logged in a log group or by database supplemental logging of primary key columns. If the parallelism of any apply process that will apply the changes is greater than 1, then any unique constraint column at a destination database that comes from multiple columns at the source database must be conditionally logged.

Supplemental logging does not need to be specified if a unique constraint column comes from a single column at the source database.

If the parallelism of any apply process that will apply the changes is greater than 1, then any foreign key column at a destination database that comes from multiple columns at the source database must be conditionally logged.

Supplemental logging does not need to be specified if the foreign key column comes from a single column at the source database. If the parallelism of any apply process that will apply the changes is greater than 1, then any bitmap index column at a destination database that comes from multiple columns at the source database must be conditionally logged.

Supplemental logging does not need to be specified if the bitmap index column comes from a single column at the source database. Any columns at the source database that are used as substitute key columns for an apply process at a destination database must be unconditionally logged. The columns specified in a column list for conflict resolution during apply must be conditionally logged if multiple columns at the source database are used in the column list at the destination database.

Any columns at the source database that are used by a statement DML handler, change handler, procedure DML handler, or error handler at a destination database must be unconditionally logged. Any columns at the source database that are used by a rule or a rule-based transformation must be unconditionally logged. Any columns at the source database that are specified in a value dependency virtual dependency definition at a destination database must be unconditionally logged.

If you specify row subsetting for a table at a destination database, then any columns at the source database that are in the destination table or columns at the source database that are in the subset condition must be unconditionally logged. If you do not use supplemental logging for these types of columns at a source database, then changes involving these columns might not apply properly at a destination database.

For example, the following statement adds the primary key column of the hr. For example, the following statement adds all of the columns in the hr. These log groups can include key columns, if necessary.

The UNIQUE option creates a conditional log group that includes the unique key column s and bitmap index column s in the table. One conditional log group includes the unique key columns and bitmap index columns for the table, and the other conditional log group includes the foreign key columns for the table. Both log groups have a system-generated name. You have the option of specifying supplemental logging for all primary key, unique key, bitmap index, and foreign key columns in a source database.

You might choose this option if you configure a capture process to capture changes to an entire database. To specify supplemental logging for all primary key, unique key, bitmap index, and foreign key columns in a source database, issue the following SQL statement:. If your primary key, unique key, bitmap index, and foreign key columns are the same at all source and destination databases, then running this command at the source database provides the supplemental logging needed for primary key, unique key, bitmap index, and foreign key columns at all destination databases.

When you specify the UNIQUE option, any columns in a row's unique key and bitmap index are placed in the redo log file if any column belonging to the unique key or bitmap index is modified conditional logging. You can omit one or more of these options. Supplemental logging statements are cumulative. To drop database supplemental logging for all primary key, unique key, bitmap index, and foreign key columns, issue the following SQL statement:.

If you decided to use a local capture process at the source database, then log file transfer is not required. However, if you decided to use downstream capture that uses redo transport services to transfer archived redo log files to the downstream database automatically, then configure log file transfer from the source database to the capture database before configuring the replication environment.

You must complete the steps in this section if you plan to configure downstream capture using either of the following methods:. Complete the following steps to prepare the source database to transfer its redo log files to the capture database, and to prepare the capture database to accept these redo log files:.

Redo transport sessions are authenticated using either the Secure Sockets Layer SSL protocol or a remote login password file.

If the source database has a remote login password file, then copy it to the appropriate directory on the downstream capture database system. The password file must be the same at the source database and the downstream capture database. At the source database, set the following initialization parameters to configure redo transport services to transmit redo data from the source database to the downstream database:.

Set the following attributes of this parameter in the following way:. The advantage of specifying ASYNC is that it results in little or no effect on the performance of the source database. ASYNC is recommended to avoid affecting source database performance if the downstream database or network is performing poorly. TEMPLATE - If you are configuring an archived-log downstream capture process, then specify a directory and format template for archived redo logs at the downstream database.

See "Decide Whether to Configure Local or Downstream Capture for the Source Database" for information about the differences between real-time and archived-log downstream capture. If you reset any initialization parameters while the instance was running at a database in Step 3 or Step 4 , then you might want to reset them in the initialization parameter file as well, so that the new values are retained when the database is restarted.

If you did not reset the initialization parameters while the instance was running, but instead reset them in the initialization parameter file in Step 3 or Step 4 , then restart the database. The source database must be open when it sends redo log files to the downstream database, because the global name of the source database is sent to the downstream database only if the source database is open. Configure an archived-log downstream capture process. In this case, see the instructions in the following sections:.

Add standby redo logs files at the downstream database for a real-time downstream capture process. The example in this section adds standby redo logs at a downstream database.

Standby redo logs are required to configure a real-time downstream capture process. In the example, the source database is dbs1. The steps in this section are required only if you are configuring real-time downstream capture. If you are configuring archived-log downstream capture, then do not complete the steps in this section. At the downstream database, set the following initialization parameters to configure archiving of the redo data generated locally:.

This location is the local destination for archived redo log files written from the standby redo logs. Log files from a remote source database should be kept separate from local database log files. A real-time downstream capture configuration should keep archived standby redo log files separate from archived online redo log files generated by the downstream database.

At the downstream database, set the following initialization parameters to configure the downstream database to receive redo data from the source database and write the redo data to the standby redo log at the downstream database:. If you reset any initialization parameters while an instance was running at a database in Step 2 or 3 , then you might want to reset them in the relevant initialization parameter file as well, so that the new values are retained when the database is restarted.

If you did not reset the initialization parameters while an instance was running, but instead reset them in the initialization parameter file in Step 2 or 3 , then restart the database. The source database must be open when it sends redo data to the downstream database, because the global name of the source database is sent to the downstream database only if the source database is open.

Determine the log file size used on the source database. The standby log file size must exactly match or be larger than the source database log file size. For example, if the source database log file size is MB, then the standby log file size must be MB or larger. Determine the number of standby log file groups required on the downstream database. The number of standby log file groups must be at least one more than the number of online log file groups on the source database.

For example, if the source database has two online log file groups, then the downstream database must have at least three standby log file groups. For example, assume that the source database has two online redo log file groups and is using a log file size of MB. In this case, use the following statements to create the appropriate standby log file groups:.

You might need to switch the log file at the source database to see files in the directory. When these steps are complete, you are ready to configure a real-time downstream capture process.

See the instructions in the following sections:. This document assumes that you understand the concepts described in Oracle Streams Concepts and Administration. Overview of Oracle Streams Replication Replication is the process of sharing database objects and data at multiple databases.

When you use Oracle Streams, replication of a data manipulation language DML or data definition language DDL change typically includes three steps: A capture process, a synchronous capture, or an application creates one or more logical change records LCRs and enqueues them. Common Reasons to Use Oracle Streams Replication The following are some of the most common reasons for using Oracle Streams replication: Availability: Replication provides fast, local access to shared data because it balances activity over multiple sites.

Rules in an Oracle Streams Replication Environment A rule is a database object that enables a client to perform an action when an event occurs and a condition is satisfied. Each of the following components is a client of the rules engine: Capture process Synchronous capture Propagation Apply process You control the behavior of each of these Oracle Streams clients using rules.

Specifically, you control the information flow in an Oracle Streams replication environment in the following ways: Specify the changes that a capture process captures from the redo log or discards.

You can specify these system-created rules at the following levels: Table level - Contains a rule condition that evaluates to TRUE for changes made to a particular table Schema level - Contains a rule condition that evaluates to TRUE for changes made to a particular schema and the database objects in the schema Global level - Contains a rule condition that evaluates to TRUE for all changes made to a database In addition, a single system-created rule can evaluate to TRUE for DML changes or for DDL changes, but not both.

Note: Synchronous captures only use table rules. Synchronous captures ignore schema and global rules. Both the capture and apply processes can use table, schema and database level rules to determine their actions. In order to begin the following parameters should be set in the spfiles of participating databases. Next we create a stream administrator, a stream queue table and a database link on the source database. This process must be repeated on the destination database DBA2.