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

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Creating Streams Queues and Queue tables

Oracle Tips by Burleson 

Oracle Streams
Chapter 4 - Capture and Propagate Configuration

Creating Queues and Queue tables

Steams data flow is totally integrated with the queues. Both the source database and destination database need to have queues to manage events. Events are stored or staged in a queue. These events may be captured events or user-enqueued events. The Capture process enqueues events into a SYS.AnyData queue. A SYS.AnyData queue can stage events of different types.  The queue and its associated queue table can be created by using the procedure set_up_queue of the dbms_streams_adm package.

Figure 4.3 Procedure for creating the queue

As shown in Figure 4.3, the queue name and queue table name must be supplied as arguments. In order to specify the storage characteristic features such as the tablespace, the storage_clause argument can be used.

The following SQL statement can be used to view the queues:

set linesize 132

COLUMN OWNER                   HEADING 'Owner'    FORMAT A14
COLUMN NAME                    HEADING 'Queue Name' FORMAT A30
COLUMN QUEUE_TABLE             HEADING 'Queue Table' FORMAT A25
COLUMN USER_COMMENT            HEADING 'Comment' FORMAT A25

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT
 FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
 SEE CODE DEPOT FOR FULL SCRIPT
 q.QUEUE_TABLE = t.QUEUE_TABLE AND
 q.OWNER = t.OWNER
/

A buffered queue consists of following two storage areas:

  • System Global Area (SGA) memory associated with a SYS.AnyData queue that contains only captured events
     

  • Part of a queue table for a SYS.AnyData queue that stores captured events that have spilled from memory

A buffered queue helps to optimize the capture of events by buffering them in the SGA instead of always storing them in a queue table. This buffering of captured events occurs at both the source database and the destination database. Captured events are always stored in a buffered queue, but user-enqueued LCR events and user-enqueued non-LCR events are always stored in queue tables rather than in buffered queues.

To determine whether there are captured events in a buffered queue, the v$buffered_queues and v$buffered_subscribers dynamic performance views can be queried. The latter view shows the Streams clients who subscribe to this queue to receive the messages. Sometimes it is possible that more than one destination database will receive the captured events. In other words, there exists more than one subscriber to the queues messages.

 

To determine whether there are user-enqueued events in a queue, the queue table for the queue can be queried. The following SQL statements show the messages in the buffered queues:

show_buffered_queues.sql
 

COLUMN QUEUE_SCHEMA             HEADING 'Queue Owner' FORMAT A15
COLUMN QUEUE_NAME             HEADING 'Queue Name' FORMAT A15
COLUMN MEM_MSG                HEADING 'LCRs in Memory' FORMAT 99999999
COLUMN SPILL_MSGS             HEADING 'Spilled LCRs' FORMAT 99999999
COLUMN NUM_MSGS             HEADING 'Total Captured LCRs|in Buffered Queue'
FORMAT 99999999

SELECT QUEUE_SCHEMA,
       QUEUE_NAME,
      
(NUM_MSGS - SPILL_MSGS) MEM_MSG,
       SPILL_MSGS, NUM_MSGS
 
FROM V$BUFFERED_QUEUES
/

 

The CNUM_MSGS column shows the cumulative total number of messages enqueued into the buffered queue since the buffered queue was created. This information is useful for monitoring the Capture process activity and to examine the load patterns etc.

Configuration Flow

In general, a simple Streams configuration of Capture, Propagation and Apply involves the creation of certain entities, as shown below:

At the Source database:

  • A SYS.AnyData queue
     

  • Supplemental Logging specifications to the objects
     

  • Capture process
     

  • Propagation process and schedule
     

  • Rule sets for the Capture process and the propagation
     

  • Each shared object prepared for instantiation

At the Destination Database:

  • A SYS.AnyData queue
     

  • Instantiation SCN set for each shared object
     

  • An Apply process for the source database
     

  • Rule sets for the Apply process

  • Next, the configuration details on how to create the Capture, Propagation and Apply Process and how to manage them will be presented. As the process details are introduced, a simple example will also be provided.  The example will cover scenarios where the goal is to replicate data:
  • from a set of tables
     

  • from a subset of the table
     

  • from a schema

In each scenario, different procedures will be used to create the necessary client processes such as Capture, Propagate and Apply.

The next section examines the architecture and components for a typical capture process.

 


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:

www.oracle-script.com

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 -2016 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