Streams and Oracle RAC

Oracle Real Application Cluster ( RAC) is one of the components used in Oracle Maximum Available Architecture (MAA). As such, we are seeing more Oracle RAC configurations being deployed more often to support High Availability requirements. Oracle Streams does work in an Oracle RAC configuration.

Pre-planning must be done in order to fully utilize both Oracle Streams and Oracle RAC. There must be careful considerations related to the design, and implementation of: Capture, Queues, Propagation, and Apply processes. Oracle RAC must be configured so that all archive logs can be accessed by Capture process. As Capture process will follow the Queue if an instance goes down, the configuration of Capture and Queues should be planned accordingly.

The use of the procedure DBMS_AQADM.ALTER_QUEUE_TABLE is suggested to set up primary_instance and secondary_instance. This will help when determining where the Queue migrates, should an instance fail in a RAC environment. To determine the instance number, use: 

SELECT INST_ID, INSTANCE_NAME, HOST_NAME
FROM GV$INSTANCE;
DBMS_AQADM.ALTER_QUEUE_TABLE (
queue_table IN VARCHAR2,
comment IN VARCHAR2 DEFAULT NULL,
primary_instance IN BINARY_INTEGER DEFAULT NULL,
secondary_instance IN BINARY_INTEGER DEFAULT NULL);

Parameter

Description

queue_table

Name of a queue table to be created.

comment

Modifies the user-specified description of the queue table. This user comment is added to the queue catalog. The default value is NULL which means that the value will not be changed.

primary_instance

This is the primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table will be done in this instance. The default value is NULL, which means that the current value will not be changed.

secondary_instance

The queue table fails over to the secondary instance if the primary instance is not available. The default value is NULL, which means that the current value will not be changed.

Propagation needs to be configured to connect to the queue of the destination instance using the proper database link. The database link should refer to the tnsnames.ora entry that contain the Virtual IPs (VIPs).

For instance, the entry below in tnsnames.ora is as follows:

db02.mycompany.com=
(description=
(address=(protocol=tcp)(host=node2-vip)(port=1521))
(address=(protocol=tcp)(host=node4-vip)(port=1521))
(connect_data=
(service_name=db02.mycompany.com)))

Then, building the database link:

create database link 'db02.mycompany.com' connect to <username> identified by <password> using 'db02.mycompany.com';

For the Propagation, focus on the parameter destination_queue_name when adding the Propagation rule. The destination_queue_name should point to the queue name and database link mentioned previously.

The Apply process also follows the Queues, so careful creation of the related Queue is a must. Once the Queue is created, adjustments using DBMS_AQADM.ALTER_QUEUE_TABLE should be done.

Pulling it all together, we have the configuration as seen in the following figure:

Streams and Oracle RAC

The following steps explain the configuration:

  1. Database DB01 with Instance 1 and Instance 3.
  2. Database DB02 with Instance 2 and Instance 4.
  3. Capture, Queue, and Propagation is on Instance 1. Streaming to Queue, and Apply Instance 2.
  4. Use of DBMS_AQADM.ALTER_QUEUE_TABLE to configure Capture, Queue, and Propagation to failover from Instance 1 to Instance 3. Queue and Apply to failover from Instance 4 to Instance 4.
  5. Configuration of Propagation to use database link db02.mycompany.com which has the alias in tnsnames.ora as shown previously.

Oracle Support also provides the following as background to setting up Streams and RAC:

  • In an RAC configuration, all Streams processes run from a single "owning" instance. The owning instance is identified in the DBA_QUEUE_TABLES view in the column OWNER_INSTANCE. If the instance that "owns" the queue goes down, ownership is switched to one of the surviving instances. All Streams processes automatically migrate and restart at the new "owning" instance.
  • Instance ownership can be explicitly set for individual queue tables. Use the DBMS_AQADM.ALTER_QUEUE_TABLE procedure to specify the primary_instance and secondary_instance ownership for a particular queue table and its associated queues. The Streams processes will automatically start on the owning instance of the queue. If both the primary and secondary instance for a queue table containing a destination queue become unavailable, then queue ownership is transferred automatically to another instance in the cluster. In this case, if the primary or secondary instance becomes available again, then ownership is transferred back to one of them accordingly.
  • When a queue is created, it also creates a service for the queue, which follows the queue. The NAME column in the DBA_SERVICES data dictionary view contains the service name for a queue. You can also determine the service_name for a particular queue from the NETWORK_NAME column of DBA_QUEUES. Use GV$ACTIVE_SERVICES to confirm that the queue service is available.
  • A queue-to-queue propagation always has its own exclusive propagation job to propagate messages from the source queue to the destination queue. Because each propagation job has its own propagation schedule, the propagation schedule of each queue-to-queue propagation can be managed separately. Even when multiple queue-to-queue propagations use the same database link, you can enable, disable, or set the propagation schedule for each queue-to-queue propagation separately.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset