Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Streams and RAC Tips

Oracle Tips by Burleson 

Streams and Real Application Cluster

In this chapter, the implementation of Streams in a typical Real Application Clusters (RAC) environment will be presented. With many businesses adopting RAC database technology for their data needs, the topic of Streams in a RAC environment will be of high importance. The differences in the Streams configuration in a RAC environment will be highlighted.  Information will be included on the extra things that need to be considered on account of the database environment being a RAC. 

Streams in a RAC Environment

 A RAC is a single database with multiple instances. Each instance in the RAC database environment has its own set of redo log files. Each instance thread produces its own set of archived log files; however, those redo log files and archived log files are accessible to all of the instances. Redo and archived log files reside on shared storage. Shared storage is configured either as an approved cluster file system or through Oracle’s Automatic Storage Management (ASM).

As shown in Figure 9.1, each of the RAC database instances consists of the following: Instance-1 has redo groups from 1 to 3; Instance-2 has redo groups 4 to 6; and Instance-3 has redo groups 7 to 9. The Streams Capture process extracts all the database changes recorded in all of the redo log files.

Figure 9.1 Typical RAC database environment


The Oracle10g Database release has brought in two important enhancements:


Streams in a RAC Environment

  • In order to improve the performance of Streams for RAC databases, a Capture process can now capture changes from the archived redo logs or from the online redo. This feature allows changes to be captured closer to the time they were executed, thereby reducing the capture latency. In other words, the moment database changes are effected and committed the transactions are available for the Capture process to extract.

  • When the owner instance for a queue table containing a queue used by a Capture process or Apply process fails, queue ownership is transferred automatically to another instance in the cluster. Then the Capture process or Apply process is restarted automatically, if it had been running. In previous releases, the Capture process or Apply process used to be ABORTED under these circumstances which would warrant a manual restart. This is an important improvement from the administrative point of view. Without the intervention of the DBA, the Streams process will be restarted.

Capture and Apply Processes in a RAC Instance

Since there are multiple instances in a RAC database, the database changes can occur at any instance. Such database changes are recorded in the respective instance’s redo logs and corresponding archive log files. A Capture process configured within any instance of the RAC database can scan and extract the transactional activity from the all the participating instance’s redo log files and convert them into LCR events. In this way, even though the Capture process is only running on one instance, it is aware of all the redo logs of all the RAC instances and does not miss any transactions.

Each Capture process is started on the owner instance for its SYS.AnyData queue, even if the start capture is executed on a different instance. The dba_queue_tables data dictionary view contains information about the owner instance for a queue table. Any parallel execution servers used by a single Capture process run on a single instance in a RAC environment.

Capture and Apply Processes in a RAC Instance

Whenever an instance in an Oracle RAC cluster fails, the instance is recovered immediately by another instance in the cluster. The following actions occur that affect the Streams environment:

  • Each queue owned by the failed instance is assigned to a new instance.

  • The Capture process is restarted automatically on the instance that now owns the queue. If the failed instance is brought back online later, the Capture process does not move back to the original instance. Even though the failed instance was running the Capture process at the time of failure, it is no longer the owner of the queue used by the Capture process. This is the normal behavior unless it is altered by the alter_queue_table procedure, which is explained in a later section.

  • All propagation jobs are automatically migrated to the new instance from the failed instance.

The SYS.AnyData queues can be configured on any instances within the RAC database. Only the owner instance may have a buffer for a queue, but different instances may have buffers for different queues. A buffered queue is System Global Area (SGA) memory associated with a SYS.AnyData queue that contains only captured events.

Figure 9.2 shows that instance DNYDBA2A has the SYS.AnyData and the associated buffers.

Figure 9.2 Streams in a RAC environment

Only the instance that has the queue, and therefore the buffers, can propagate and receive the LCR events. Each of the propagations that propagate captured events to a RAC destination database must use an instance-specific database link that refers to the owner instance of the destination queue. If the propagation connects to any other instance, the propagation will raise an error.

Capture and Apply Processes in a RAC Instance

That is the reason that when the Non-RAC instance, DNYTST10, receives or propagates the captured events, it needs to use the instance specific database link to DNYDBA2A.

Whenever the owner instance for a queue table containing a destination queue becomes unavailable, the queue ownership is transferred automatically to another instance in the cluster.

Since the queue is migrated to a new instance, the database link will need to be recreated. For the propagations that were configured with the failed instance as the destination, the database link should be dropped and then recreated using the same global name.  During recreation, the link will be pointed to the new instance that now owns the queue. Scripts that drop and re-create all necessary database links can be created and then run at the sites that are attempting to propagate to the failed instance. The propagation details do not need to be modified since the name of the database link is not being changed.

Next, the failover process with in the RAC environment will be illustrated with an example. Information on the effect of the failover process on the Streams related components will be presented.

As seen in Figure 9.2, the DNYTST10 database propagates and receives replication changes from the RAC database, DNYDBA20. The RAC database has two instances: DNYDBA2A and DNYDBA2B.

The DNYTST10 instance will have a database link to the RAC database named DNYDBA20, which corresponds to the database name. Assuming that the initial queue is on the DNYDBA2A instance, it will use the TNS entry that points to DNYDBA2A instance as shown below:

Use the following SQL queries to view the instance details and buffers details at the DNYDBA2A instance:

SQL> select * from v$active_instances ;

----------- --------------------------------------------
          1 nytms-a:DNYDBA2A
          2 nytms-b:DNYDBA2B

SQL> select instance_name, instance_number from v$instance ;

---------------- ---------------
DNYDBA2A                       1

                                          Primry    Secndry  Owner
QUEUE_TABLE         TYPE    OBJECT_TYPE    Insta      Insta  Insta
------------------- ------- ------------ -------- --------- ------
NY1_QUEUE_TABLE     OBJECT  SYS.ANYDATA       0          0      1


SQL> show user
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

After the DNYDBA2A is shutdown, the queue buffers move over to instance DNYDBA2B automatically. Now, connect to the DNYDBA2B instance and query the owner instance for the NY1_QUEUE_TABLE queue.

SQL> select instance_name, instance_number from v$instance ;

---------------- ---------------
DNYDBA2B                       2



The above text is an excerpt from:

Oracle Streams
High Speed Replication and Data Sharing

ISBN 0-9745993-5-2

by Madhu Tumma

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks