Oracle 11g Streams Implementer's Guide: Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook

Let's take a moment to briefly run through the replication topologies and process flow, and identify the Oracle functional components that are used by Streams.

Topology configurations

Distributed topology configurations are as limited as rocks in a river. However, many are not conducive to an efficient and maintainable system. The number one consideration when choosing a distributed topology is data ownership and conflict resolution requirements as discussed in Chapter 2, Plot Your Course: Design Considerations. To keep your Streams from becoming wild, untamed rivers, and drowning the DBA's, keep master sites to a minimum, if at all possible, and data flows in a tightly controlled and synchronized manner. Oracle recommends no more than four masters involved in full-on all-site N-way replication, and the authors second that recommendation with gusto.

In the Preface, we briefly described single-source, multiple-source, hybrid, and heterogeneous configurations.

The following images provide a synopsis of succinct, controlled single-source and multiple-source configuration examples. Of course these are not the only viable configurations, but they will help you to start developing a feel of how to control your Streams.

Keep in mind that the relationship between source and target (secondary) databases assumes they share the same data at some level. Two databases that handle totally different data would not be considered a source or secondary site to each other.

Single source

In a single-source configuration there is only one database that is the source of data changes that are being Streamed to other database site(s). At the other site(s)/target(s), the data can be applied and/or forwarded to another database. If data changes are forwarded from a destination database, the configuration is called a directed network. There are two types of directed network forwarding configurations; Queue forwarding and Apply forwarding. Queue forwarding involves propagating the change to a target database site but not applying the change at the database. Rather, the destination only forwards the change to a subsequent site to be applied down the line. Apply forwarding will apply the change, and forward it to subsequent destinations via local Capture and Propagation. Destination sites configured as Queue or Apply forwarding sites are often referred to as intermediate sites. Another single source configuration consists of a copy of the actual redo logs being forwarded from the source database, to a "downstream" target database. The actual Capture process and queue are configured on the downstream database rather than on the source database. This configuration is called Downstream Capture which is explained in more detail later on this chapter and in Chapter 6, Get Fancy with Streams Advanced Configurations.

In a single source environment, steps should be taken to avoid changes being made at the secondary destination databases to avoid data divergence and change conflicts.

Some illustrated examples of single-source configurations with a brief explanation of where the Streams processes are located are shown as follows.

Single database

A single database configuration hosts both the Capture and Apply processes on the same database. This can negate the need for a Propagation process as the Apply process can be created to dequeue from the same buffered queue into which the Capture process enqueues. However, there may be circumstances where you wish to configure separate buffered capture queue and persistent apply queues. In this case you would also configure a Propagation process between the two queues. The Apply process can be assigned an apply handler that processes the LCRs in a specific manner. This type of configuration can be used to support client application access to captured LCR data and heterogeneous configurations.

Uni-directional

In single-source to single-target configuration the Capture and Propagate processes and the Capture queue are located at the Source database. The Apply process and queue resides on the Target.

Cascaded/directed network

In a directed network configuration, Capture and Propagation processes and Capture queue reside on the Source. For Queue forwarding, the forwarding site has a queue, but no Apply process. For Apply forwarding, the forwarding site is configured with an Apply process and queue as well as a local Capture process and queue. Tags (discussed in Chapter 6) are used to coordinate the local "recapture" of the applied changes. Appropriate Propagation processes are configured from the forwarding site Capture queue to the destination sites. The final destination site is configured with a regular Apply process and queue.

Hub-and-Spoke

In single-source Hub-and-Spoke configuration, data is Streamed from one source to multiple destinations (targets). This is often seen in "Headquarter to Branch (or Regional)" configuration. With this type of configuration, there is a Capture process and queue at the source as well as a Propagation process to each destination. An Apply process and queue are configured on each of the destinations.

Multiple source

In a multiple source Streams configuration, shared data can be changed at two or more sites. A situation can arise where there is conflict caused by DML/DDL originating from one or more databases acting on the exact same data at nearly the same time. To overcome this conflict situation, conflict resolution must be implemented to determine which data changes should be kept and which should be discarded.

Below are some illustrated examples of multiple-source configurations.

Bi-directional/N-way/Master-to-Master

Bi-directional, N-way, Master-to-Master are all names for essentially the same thing. This configuration allows data changes to be made at all master sites and replicated to all other master sites. As such, Capture, Propagation, and Apply processes and queues must be configured at each master site. For the Capture processes at each site, you can choose to configure a single Capture process and queue for all Propagation processes, or a separate Capture process and queue for each Propagation process.

Uni-directional Spokes-to-Hub

In this configuration, the SPOKES are the origination point of the data change and the HUB is the destination. Capture and Propagation processes and Capture queue are configured on each of the SPOKE sights. An Apply process and queue for each SPOKE site is configured on the HUB. Conflict resolution should be configured at the HUB to accommodate potential data change conflicts from multiple sources.

Bi-directional Spoke-to-Hub

This configuration is just an extension of uni-directional Spoke-to-Hub that allows the HUB to send its changes to each spoke. This means that at least one Capture process and queue must be configured on the HUB, and a Propagation process configured to each SPOKE. Note here that the HUB processes should be configured so that the HUB does not send the same change back to the SPOKE that originated it. This can be accomplished in a number of ways by using tags, and/or conditions (covered in Chapter 6). In addition, an Apply process and queue must be configured at each SPOKE to receive and process changes from the HUB, as well as the Capture process and queue and Propagation process to the HUB that we use in uni-directional Spoke-to-Hub.

Hybrid

A Hybrid configuration is simply a combination of single and/or multiple- source configurations. For instance, one leg of your topology could be a directed network, while another leg could be a master-to-master. The trick is all in where you put your Capture, Propagate, and Apply processes.

Heterogeneous

Heterogeneous configurations include a source or target database that is not an Oracle database. Oracle Heterogeneous Gateways can be used to allow an Oracle database to send and receive changes to and from these non-Oracle sources. The gateways provide a "translation" level that converts Oracle SQL syntax to and from non-Oracle SQL syntax. This allows the users to transparently accomplish equivalent SQL operations from an Oracle database to a non-Oracle database. Oracle 11gR2 delivers the following Gateways to non-Oracle database platforms:

• Adabas

• APPC

• DRDA

• IMS

• Informix

• ODBC

• SQL Server

• Sybase

• Teradata

• VSAM

In a heterogeneous environment, the Apply process and queue must still exist in an Oracle database and be configured to use a database link to the non-Oracle database. The source database may or may not be an Oracle database. It could be such that the Oracle database is merely an intermediate database that is a directed network where a client application enqueues LCR constructed from data at a non-Oracle database into the Apply queue. Regardless of how the LCR is enqueued in the Apply queue, the Apply process on the Oracle database uses Heterogeneous Services and Transparent Gateway to apply LCR changes directly to database objects in a non-Oracle database through the database link to the non-Oracle database. In other words, the Apply process unpacks the LCR and constructs the necessary DML statement on the Oracle side. It then executes the statement through the gateway database link, using Heterogeneous services to translate the SQL to the proper non-Oracle SQL syntax. Capture and Propagation are configured at the source database. If the Apply process for the non-Oracle database is configured on the source database, Propagation between the Capture and Apply would not be required. A remote Oracle destination database can also be configured to apply the change to a non-Oracle database.

As mentioned above, data can also be sent to an Oracle database from a non-Oracle source. This can be done with Advanced queuing and XSTREAMS or JMS. Again the Apply queue and process are on the Oracle database. The non-Oracle database interface must be configured to enqueue a message to the apply queue on the Oracle database. That message is then dequeued and processed by an Oracle advanced queue process.

Important: Apply processes for a non-Oracle database can only apply DML, not DDL.

Note

For more information on Streams Configurations, please reference the Oracle Streams Extended Examples manual.

Throughout our discussion on the Streams processes, we mention the Advanced Queues used by Streams to transport changes. These queues are either in-memory (buffered queues) or tables on disk (persistent queues). Oracle Streams uses both buffered queues and persistent queues. A buffered queue can only be an ANYDATA queue, while a persistent queue can be an ANYDATA or a TYPED queue.

ANYDATA and TYPED refer to the payload datatype of the message handled by the queue. An ANYDATA queue's payload is of the SYS.ANYDATA datatype. A TYPED queue has a specific datatype (such as Varchar2, CLOB, BLOB, Number). To determine the payload type of a queue, query the OBJECT_TYPE column of the DBA_QUEUE_TABLES view.

select owner, queue_table, object_type from dba_queue_tables;

The Oracle memory segment used by buffered queues is part of the STREAMS_POOL in the SGA. The type of queue used by Streams depends on the type of LCR that is being stored. Captured and buffered LCRs are stored in buffered queues. Persistent LCRs are stored in persistent queues.

For more information on Streams Queues, review the Introduction to Message Staging and Propagation section of the Oracle Streams Concepts and Administration user's manual.

It is always helpful to understand the whole picture and the pieces that make up the picture. As such, we start with the image as follows:

We use the image above as a reference in this chapter to explain the following processes:

So, let us start covering each of the main processes and components and it's role in the Streams environment.

The Capture process uses both LogMiner and Advanced Queuing to accomplish it's task (Note: Synchronous Capture uses internal triggers instead of LogMiner). The Capture process uses a LogMiner process to examine the database redo log for changes. A Capture process references a set of user-defined rules that determines exactly what needs to be captured for the Stream. These Capture rules identify specific changes to be captured from the redo logs.

These changes are then formatted into Logical Change Records (LCRs) and placed (enqueued) into an advanced queue. In most cases, the queue is a buffered queue (more about LCRs and buffered and persistent queues a little later). This method of capture enqueueing is called "Implicit Capture" and is most often used in a Streams environment. The following image shows the process:

The other method of capturing involves user generation and enqueuing of a message directly into a buffered or persistent queue. This method is called "Explicit Capture" and is usually done by application software. These explicit messages can be either a user defined message or an LCR. For a more detailed explanation on Explicit Capture, refer to the Oracle Streams Concepts and Administration Guide.

A Capture process can capture a majority of database transactions. The Capture process specifically captures DML and DDL. The Streams Capture process can capture DML on columns of the following datatypes:

In turn, Capture process can capture the following DDL.

There are limitations with the Capture process. The following DDL commands are not captured.

If you take a careful look at the list above, you may notice that these commands are DDL that are instance specific. You want to avoid replicating them, so that you do not end up corrupting the target instance.

In addition, there are object specific DDLs that are not supported by Streams.

Looking at the lists above, one can start to think, "Is there a quick way to tell if my environment can be streamed?" Yes, Oracle Development did provide a quick way to find out. Simply query DBA_STREAMS_UNSUPPORTED view and you can find out the reason why a particular table could not be streamed. We suggest that you query this table as part of your planning a Streams environment.

SELECT * FROM DBA_STREAMS_UNSUPPORTED;

Pay particular attention to the REASON and AUTO_FILTERED column. The REASON column is self-explanatory. As for AUTO_FILTERED, if you see a YES value then Streams automatically filters out the object from being streamed.

Possible reasons include:

The Capture process is the first Streams specific related process. However, if you look again at the diagram you will see LogMiner is also in the picture. The Capture does not do everything by itself. The Capture process uses LogMiner to do all the "heavy lifting". The Capture process takes advantage of LogMiner's ability to mine the database redo logs.

In 9i, the LogMiner tablespace defaulted to the SYSTEM tablespace. As of 10g, it defaults to the SYSAUX tablespace. As there will be additional usage of LogMiner with a Streams environment, we recommend that you isolate the tables related to LogMiner in its own tablespace. This can be accomplished with the following scripts.

CREATE TABLESPACE LOGMNRTS DATAFILE '/u05/oracle/data/logmnrtbs.dbf'
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;
BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;

This can help eliminate possible fragmentation in the SYSTEM or SYSAUX tablespace where the LogMiner tables are created by default. Depending on your tablespace file to disk distribution, it can also help with performance. If your database has been upgraded from an earlier version, the LogMiner tablespace may well be set to the SYSTEM tablespace. If it is, you are strongly cautioned to use the above method to reset the LogMiner tablespace to a non-system tablespace.

To actually identify the Capture and LogMiner processes that are running on the source database, look for the background process on the host of CPnn for Capture and MSnn for LogMiner where nn is a combination of letters and numbers. Both of these processes may not be constantly running, so they should be monitored over time. Also, there may be multiple Capture and/or LogMiner processes running.

Downstream Capture

The Capture process usually resides on the Source database. This configuration is called Local Capture (and sometimes Upstream Capture). The Source database is defined as containing both the Capture process and the tables being captured. There is another Capture configuration that can be used called Downstream Capture. For now, we will just give a quick example of when and why a Downstream Capture would be configured.

The Capture process consumes resources (memory and CPU) from the host. This may not be optimal in a high-volume production environment (this is but one case where Downstream Capture comes into play). Downstream Capture allows the Capture process and queue to be moved to another staging node. That staging node is the "worker" that can afford the additional overhead of Capture. Downstream Capture uses standby archived log destinations (just like those used by Data Guard) defined at the source database to direct a copy of the redo to the staging node. The Capture process at the staging node then mines those redo copies and enqueues the necessary LCRs. Propagation processes on the staging node then send the LCRs to the appropriate destination database sites. We will cover Downstream Capture and other advanced configurations in more detail in Chapter 6.

We mention instantiation as part of this chapter to stress its importance. Instantiation refers to the creation of the replicated object at target databases, based on the source object. It also provides Streams with the information needed to determine what transactions to apply at the destination site(s). You can think of it as an agreement (starting point) that needs to be established between the source and destination before any Streaming can be accomplished. The main purpose of instantiation is to prepare the object structure and data at the destination site to receive changes from the source.

Instantiation is a process composed of three steps:

The database objects, which are either tables or other objects, need to exist on both the source and destination site. Table structures between a source and destination database can differ if there is a transformation or subsetting involved, though often the tables will have the same data and the same structure. If there is data that needs to be replicated in the table(s) then the data should be the same at the source and destination sites at the time of instantiation, unless there is some sort of transformation, subsetting, or other apply or error handler put in place to compensate for the data differences. This becomes apparent when an update or delete DML fails due to data NOT being at the destination site(s) or having different values in the replicated columns.

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. The following image demonstrates this concept as it shows the instantiated Inventory Table with the same instantiation SCN at both the Source and Destination site.

Instantiation Levels and Methods can be accomplished at different levels depending on your requirements. These instantiation levels include:

The possible combinations of Instantiation Levels that can be used with Instantiation Methods can become confusing. So, with a handful of different methods to instantiate tables; How does one decide which method to use? In general, you can use Data Pump to instantiate all (or some) of the tables at Schema Level. You can also decide to move all tables to a particular tablespace and use transportable tablespaces. For now, we will focus on two methods that we use most often due to its ease of use and flexibility.

Using Data Pump to instantiate tables and schemas is fairly straightforward. The Data Pump export utility EXPDP will use Oracle Flashback to ensure that the export is consistent and at the same time capture the instantiation data. For greater control use the FLASHBACK_SCN or FLASHBACK_TIME parameters. On the import side, use the DataPump import utility IMPDP. If it is a full database import, use the parameter STREAMS_CONFIGURATION=y (the default value) to direct IMPDP to include any Streams related metadata that may be contained in the export.

If the replicated structures and data are the same on both sites, we recommend that you use DataPump Export/Import to instantiate (this can be done via DBMS_STREAMS_ADM.MAINTAIN_* scripts).

If the replicated structures are the same, but the data different between sites, we recommend instantiating objects via DataPump with CONTENT=METADATA_ONLY, and manual calls to necessary DBMS_STREAMS_ADM.ADD_RULE and DBMS_CAPTURE_ADM subprograms.

If the replicated structures and data are different between sites, we recommend that you create and populate the objects at each site manually, then call the necessary DBMS_STREAMS_ADM.ADD_RULE and DBMS_CAPTURE_ADM subprograms manually. Make sure to configure transformation rules for the structural differences, and handlers for the data differences. One important thing to remember is that if Capture, Propagation and Apply processes and rules are added, or modified, you will need to re-instantiate the SCN between the source and destination. You can do this by following the manual method.

Now that we know about the Capture process, it is time to move to the 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. This allows multiple Propagations to be configured and scheduled to propagate at different times. 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. For queue-to-dblink configurations, having one shared propagation job may cause issues because making any propagation schedule change affects all the propagations that rely on the source queue and database link. This issue can be overcome by configuring different queues when using queue-to-dblink. For example, one queue would be set up QUEUE1 and be on its own SCHEDULE. Then a separate queue, QUEUE2 can be set up and have its own SCHEDULE. Propagation scheduler will be covered in a moment.

Propagation can be configured to propagate to different targets. With the use of RULES one can set up multiple Propagate processes, referencing one buffer queue on the Source. Each Propagation process will process from the same source queue. The source queue will only delete the LCR once it is consumed by each Propagation process registered as a consumer of the queue.

At this time, we need to mention Secure Queue. A secure queue can be used only by the owner of that secure queue. Think of it this way, the owner of the queue runs and controls it. No other users or processes may use a secure queue unless the owner allow it by granting explicit privileges to the user. For the purpose of this book, all queues will be secure queues. This is compared to an unsecure queue which any session or process is allowed to use.

So, how does Propagate know when to do its job? In Oracle 11g, Scheduler controls when the Propagation takes place (in previous versions, it was accomplished via regular database jobs). Later, when we work through our example Streams configurations, we will accept the default propagation schedule.

"What" gets propagated "Where" is controlled via Propagation rules is just like "What" is captured by the Capture process that is controlled via Capture rules. The creation of these rules is very similar to Capture process rules, so we won't go into the same level of detail again. Propagation process and rules can be created using either the DBMS_STREAMS_ADM.ADD_*_PROPAGATION_RULE for the replication level desired, or via the DBMS_PROPAGATION_ADM.CREATE_PROPAGATION procedure.

The main thing to understand is that Propagation needs to know what queue from which it must dequeue captured LCRs, what database link to use to send the changes and the destination database name, as well as the remote queue at the destination database in which to enqueue the LCR. As with Capture rules, you can control what the Propagation process sends based on the same type of parameters and rule conditions used in the Capture process.

It should be noted that even though you create a Propagation process using either the DBMS_STREAMS_ADM or DBMS_PROPAGATION_ADM procedures, you use the DBMS_AQADM Propagation Subprograms to schedule/unschedule, alter, enable, and disable propagation. You use the DBMS_PROPAGATION_ADM subprograms to create drop, start, and stop propagation jobs. This is because the Propagation process is separate from the propagation job. The Propagation process can remain running while the propagation job is disabled. This separation allows Streams to support queue-to-queue Propagation as well as queue-to-dblink propagation. Understanding the separation of the two, aids in understanding what procedure to use to control which piece of Propagation.

You can see this separation of duties by looking at the background processes for Propagation. The Propagation processes (similar to the Capture processes) are designated by Pnnn, where as the propagation jobs are separate job processes designated by Jnnn.

We are over the hump and it's all downhill from here. From our previous image on Queues, we notice that we are now on the Target side of the Streams environment. On this side, we have a buffered queue and the Apply process. The queue on this side will be a secure queue that is the same kind as that on the Source. This secure queue (on the Target side) contains the LCRs sent over by the Propagation process.

At this point, the Apply process comes into the picture. The Apply process takes LCRs (or messages) from the secure queue and applies them to the Target database object, or hands it off to an Apply handler. An Apply handler is a user defined procedure that processes the LCR change. The user defined procedure takes a single LCR (or messages) as input. As with the Capture and Propagation, the Apply process uses rules to determine what LCR's to Apply.

The Apply process is made up of multiple parts. Those parts are as follows:

Keep in mind that there can be multiple Apply processes. The Apply reader and Apply server processes show up as background process on the host as ASnn. In addition, there can also be multiple Coordinator Processes (from above). The Apply coordinator background processes names appear as APnn. In both cases, nn is a number and letter combination (0 9 and a z).

The Apply process itself is pretty straightforward; dequeue the LCR, evaluate the LCR against the Apply rules, if the overall evaluation is true, apply it (if it evaluates to FALSE ignore it), if the Apply fails, put the LCR in the Apply error queue. Where things can get complicated is at conflict detection, resolution, transformations, and user defined Apply handlers. To really get your head around conflict detection and resolution, you need to understand the LCR structure. Therefore, we save this discussion for the LCR section. Additional understanding of conflict resolution, transformations, and Apply handlers requires a strong understanding of Rule structures. Thus, we save these discussions for Chapter 5, N-Way Replication, for configuring conflict resolution and Chapter 6 for rule-based transformations.

sql>exec DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY (
trig_owner => '<schema>',
trig_name => '<trigger_name>',
fire_once => FALSE);

You've seen these movies and heard the famous songs where, when certain planets align in certain ways at certain times, special powerful things automatically happen. As of 11g, Streams has such a cosmic event potential. And when this cosmic event occurs, its called Combined Capture and Apply. Seriously, it really is like a cosmic event in the galaxy of optimization. When Oracle Streams is configured a particular way between two sites, the Capture process acts as the propagator, using its associated Propagation process rule set, and transmits the eligible LCR's directly to the Apply process at the destination via database link. This functionality automatically detects if the optimal configuration is in place and "flips the switch" to enable Combined Capture and Apply. The only way to control whether or not Combined Capture and Apply is enabled, is to change the configuration of your Streams so that one of the configuration "rules" is violated.

The configurations that cultivate this cosmic event are a little different depending on where the Apply process resides.

If the Apply process resides in a different database than the Capture process, the configuration is required:

One behavior to point out here is that if the Apply process is unavailable at the destination database, the Capture process will hang in the INITIALIZING state at startup until the Apply process becomes available. Once the Apply process is enabled, the Capture process immediately transitions to CAPTURING CHANGES.

If the Apply process resides in the same database as the Capture process, the configuration is required:

As the user has no control over the enablement of Combined Capture and Apply (CCA) beyond setting up the Streams configuration, it may not be immediately obvious when Combined Capture and Apply is enabled. You can determine if it is enabled by checking the V$STREAMS_CAPTURE and V$STREAMS_APPLY_READER views.

In V$STREAMS_CAPTURE, the APPLY_NAME will have the name of the Apply process and the OPTIMIZATION will be greater than 0 (zero, zed) if CCA is enabled.

select capture_name, apply_name, optimization from V$STREAMS_CAPTURE;
CAPTURE_NAME APPLY_NAME OPTIMIZATION
------------------ ----------------------- ------------
HR_CAPTURE HR_APPLY 2

The PROXY_SID is not NULL in V$STREAMS_APPLY_READER.

select apply_name, proxy_sid from V$STREAMS_APPLY_READER;
APPLY_NAME PROXY_SID
------------------------------ ----------
HR_APPLY 132

You will also see a similar entry in the alert log:

Propagation Sender (CCA) HR_PROPAGATION for Streams Capture HR_CAPTURE and Apply HR_APPLY [on destination STRM2] with pid=28, OS id=6096 started.

When Streams is in Combined Capture and Apply mode, you will not see information concerning the Propagation in the DBA_QUEUE_SCHEDULES view. In this case, you will need to query the V$PROPAGATION_SENDER and V$PROPAGATION_RECEIVER views.

All of the Streams processes use SCNs to keep track of what change transactions they have processed and they share this information to coordinate who gets what, who still needs what, and what can be ignored (because it has already processed). This is why coordinating the Instigation SCN at the start is so important.

Capture and Apply object instantiation are not the only components of Streams that rely on SCN synchronization. The Capture process must also coordinate it's SCNs with the LogMiner process and available archived logs to ensure data integrity over time. This is done via FIRST_SCN, START_SCN and REQUIRED_CHECKPOINT_SCN.

The Capture process relies on a valid LogMiner Data Dictionary to access database object structure for redo capture to build LCRs. This LogMiner Data Dictionary is separate from the Database Data Dictionary, but is a "picture" of the Database Data Dictionary at the time the Capture process is created. Each Capture process either builds a new LogMiner Data Dictionary or accesses an existing one when it first starts. To build a LogMiner Data Dictionary, the Capture process must have access to the "picture" of the Database Data Dictionary from the redo logs at the time of the SCN from which it must first start capturing. This picture of the Database Data Dictionary is created in the redo logs by running the DBMS_CAPTURE_ADM.BUILD procedure. This procedure must be one at least once in the database before a Capture process can be created. The BUILD creates a picture of the Database Data Dictionary in the current redo log and records an entry in the V$ARCHVIED_LOG view indicating that the redo log contains a Data Dictionary information (DICTIONARY_BEGIN='YES') as of the SCN (FIRST_CHANGE#) at the time of the BUILD. The FIRST_SCN of the Capture process must correspond to a FIRST_CHANGE# for a BUILD. For the Capture process to start for the first time, the redo log for that FIRST_CHANGE# must be available to the database instance. The BUILD procedure can be run multiple times, and different Capture processes can use any one of these builds when it is created by specifying one of the FIRST_CHANGE# values for a build for the Capture process FIRST_SCN parameter (as long as the necessary redo logs are available to the instance). The Capture process will access the redo log containing the Dictionary information, and build its LogMiner Data Dictionary if needed. You can find eligible FIRST_SCN values by querying V$ARCHIVED_LOGS for FIRST_CHANGE# values generated by a build.

select distinct NAME, FIRST_CHANGE# from V$ARCHIVED_LOG where DICTIONARY_BEGIN = 'YES';

The NAME column has the name of the redo log(s) in which the BUILD resides. All redo logs from this redo log forward, must be available for the Capture process to first start.

If you specify a FIRST_SCN for a new Capture process from a BUILD for which another Capture process has already built a LogMiner Data Dictionary, the new Capture process will use the existing LogMiner Data Dictionary.

If you do not specify a FIRST_SCN (default is NULL) when creating a Capture process, the creation will call DBMS_CAPTURE_ADM.BUILD procedure, and set the FIRST_SCN for the Capture process to the FIRST_CHANGE# generated by the build. If you create a Capture process using one of the procedures in the DBMS_STREAMS_ADM package, the FIRST_SCN parameter is automatically set to NULL, forcing the capture creation to do a BUILD.

Knowing how LCR moves from source to target is only part of the story. What an LCR contains is also important. Let's start by going over what we know about database transactions. Every transaction in a database is assigned a unique transaction ID. The transaction itself can be composed of one or more DML or DDL instructions.

Each one of these instructions is associated to its parent transaction via this ID. When we attempt to commit a transaction, all the instructions in the transaction must be successfully completed or the whole transaction fails/rolls back. This means that all the DML/DDL instructions within that transaction do not get applied to the database. Remember this. It will be important when you have to troubleshoot situations where a user demands "Where did my data go?"

As mentioned above, an LCR is a logical change record that is created by the Capture process. The content of the LCR is the actual steps the database took to accomplish the change instruction(s) of a transaction. These steps are stored in a special, ordered format that is then parsed by the Apply process to rebuild the SQL to duplicate the original transaction. We know that a transaction can have multiple instructions, thus, an LCR can include multiple steps. Each one of these steps is a message. When you look at the LCR metadata (where available; usually in error queues), you will see that each LCR has a message count, and that each message has a sequential ID.

The message itself is composed of metadata from which the Apply process builds the SQL to accomplish the instruction. This information includes (but is not limited to) the following:

There is additional information stored in LCRs. If you wish to familiarize yourself with the content of LCRs you can review the Types of Information Captured with Oracle Streams section in the Oracle Streams Concepts and Administration user's manual, and SYS.LCR$_ROW_RECORD and LCR$_DDL_RECORD type definitions found in the Oracle PL/SQL Packages and Types Reference manual. These types are visible to the user for use in explicit capture and are used by implicit capture as well.

At this point, we want to remind you that Streams interacts with the Oracle database architecture in many different ways. Interaction and changes to the SGA and SPFILE should be done prior to configuration of Streams. If subsequent changes are needed, make sure to review those changes. The isolation of tables related to LogMiner (already mentioned above) is also part of the pre-work that should be done as part of the Streams configurations. The location of the redo logs and archive logs and the retrieval speed from disk or disk cache should also be considered. The retention time of archived logs must be coordinated with Capture process SCN requirements.

The use of Automatic Memory Management (AMM) or Dynamic SGA is suggested when configuring Streams to ease administration. One of the parameters that will need to be configured is STREAMS_POOL_SIZE. STREAMS_POOL_SIZE controls the size of the Streams Pool in the SGA memory. A properly sized STREAMS_POOL_SIZE allows for proper performance of the Capture and Apply processes. Streams Pool also stores LCRs (or messages) in buffered queues. If the Streams Pool is undersized, you can see issues with "Spilled transactions" for the Apply. This means that the Apply process ran out of Streams Pool and had to write the LCRs to disk until they could be loaded back to memory. This is where an undersized Streams Pool can have a significant impact on Streams performance. If this happens, you will see error messages in the alert log, as well as entries in the V$BUFFERED_QUEUES.

If you see entries in the DBA_APPLY_SPILL_TXN view, this is a result of transaction size or age exceeding the Apply process txn_lcr_spill_threshold and txn_age_spill_threshold parameter values respectively.

We will go into details about configuration of the database in Chapter 3. For now just be aware that the database needs to be configured specifically for a Streams environment.

We have briefly mentioned some performance-related concerns when setting up a Streams environment. Having robust hardware and a fast, stable network greatly affects overall Streams performance. Configuration of the database will also have an impact on performance. This chapter was more about understanding the key components of Streams. In Chapter 7, Document What You Have and How it is Working, we will go into detail about Streams performance.

The current ethical climate of computing unfortunately mandates the need to identify who made what changes to what data, when and from where. Corporations must now comply with stringent data change auditing mandates associated with such regulations as SOX (Sarbanes Oxley Act), FISMA (Federal Information Security Management Act); to name a couple. Prior to Oracle 11gR2, the Capture and Propagation of data change audit information had to be manually included in Streamed environments. Oracle 11gR2 introduces the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure that allows the DBA to quickly configure a separate change audit table for a Streamed table, as well as to propagate the change audit data from the source site to all target destination sites. This procedure can also be used to create one-way replication of a table along with the change capture from a source to a destination database. Change tables can be implemented for local or downstream capture, and local or remote apply configurations.

The DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure creates all the components necessary to capture, send, and record data change information to the change table.

The DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure is run at the capture site and accomplishes the following:

The following figure shows configuration with local capture, remote Apply with Source table replication.

Before configuring change tables, you want to make decisions as to:

With the acquisition of GoldenGate, Oracle 11gR2 incorporates GoldenGate XSTREAMS technology providing client applications with the ability to insert and extract LCR datatypes directly into an implicit or explicit Stream.

The following lists views that supply helpful information for XSTREAMS:

XTREAMS is built on Oracle Streams infrastructure and thus can take advantage of Oracle Streams' functionality and flexibility.

The Oracle database is configured to support XSTREAMS using the DBMS_XSTREAMS_ADM package subprograms. Specialized server processes are configured to handle inbound and outbound traffic to standard Oracle Streams queues. Outbound server processes can be created to dequeue from an existing Streams queue, or its own queue. Inbound server processes are created with their own Streams queue which can then be configured for use via normal Streams apply rule creation. These server processes are accessed via the OCI or Java API function interfaces.

XSTREAMS provides the ability to share information across a heterogeneous environment with excellent flexibility, usability, and performance. This functionality can be used as an alternative to replicating with Oracle Heterogeneous Gateways, and Java Messaging Service(JMS) clients.

This is the foundation chapter for the rest of this book. We went over the basics of what Streams can do and the background processes that make up a Streams environment. Those background processes being Capture, Propagate, and Apply. Knowing each of these processes well is crucial to implementing a robust production level Streams environment. Not knowing these background processes can cause you difficulties when setting up Streams and future problems. Instantiation was covered in this chapter to point out not only what it is but to also stress its importance in the Streams environment and its role in SCN synchronization.

Streams is also dependent on the hardware and network that it runs on. Having properly sized hardware is within a DBA's influence. The network is often beyond the DBA's control. As the network is so crucial to Streams, we included discussion on the impacts of network speed and stability on Streams performance.

We also went into what an LCR is and its function. An LCR contains the DMLs and/or DDLs that are sent from Source to Target. Not all DMLs and DDLs are supported by Streams and we showed how to quickly identify what objects are supported and those that are not (query DBA_STREAMS_UNSUPPORTED).

We began to go into the Oracle Architecture (and components needed to support Streams) and Streams Performance. Chapter 7 will cover capturing Streams performance statics because it deserves its own special place. We will go into detail about how to configure the database in Chapter 3 to support optimal performance. Finally, we focus on two new Oracle 11gR2 features: Streams Change tables and XSTREAMS.

Now that we have reviewed the components of Oracle Streams Replication, let us move on to Chapter 2 where we will explore the many, many things to consider and options available when designing your Streamed environment.