Additional Configuration for RAC environment as Apply database

If the owning instance of a Streams target queue fails, the ownership of the queue is automatically transferred to a surviving instance. When this occurs, propagation from the source database will be unable to enqueue messages into the target queue UNTIL database link points to the current instance that owns the queue.

WORK-AROUND

Create a service for the queue at the target RAC sites. On failover start the service on the instance that owns the queue.

To implement the solution, please execute the followin steps:

• Define a new tns alias for the service at the RAC cluster database in the tnsnames.ora file, e.g.

     SRVL_STREAMS =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
         (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = SRVL_STREAMS)
         )
       )

• On the RAC cluster

     exec dbms_service.create_service('SRVL_STREAMS','SVRL_STREAMS');

• Identify the instance upon which to start the service initially. The OWNER_INSTANCE column identifies the correct instance.

     SELECT q.owner, q.name, t.queue_table, t.owner_instance
     FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
     WHERE t.object_type = 'SYS.ANYDATA' AND
     q.queue_table = t.queue_table AND
     q.owner = t.owner;

• Start the service on the owning node

     exec dbms_service.start_service('SRVL_STREAMS')

• Use GV$ACTIVE_SERVICES to confirm that the service is available.

• At the source database create the database link CMSR.CERN.CH to use the dbms_service alias SRVL_STREAMS when connecting.

With the above solution if a node goes down then you need to start the service on the new owning node for the Streams queue table. The following link describes how to automates the starting of the service:

• Streams Apply Service failover on RAC 10gR1