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.
