Thursday, August 27, 2009

Tracking Streams Changes with V$STREAMS_MESSAGE_TRACKING

V$STREAMS_MESSAGE_TRACKING is one of the best new features of Oracle Streams 11g. For a full description and an example, check the "Tracking LCRs Through a Stream" section of the documentation. In this post, we'll be creating a procedure after setting a 1-way Streams replication and the message tracking. You'll see how easy to use it is. To shorten this post, I won't be describing how to configure the replication. The 2 scripts attached, should help you to quickly get to it:Connect to the source database (i.e. BLACK), set the label and create a procedure in the DEMO schema:
. oraenv
BLACK

sqlplus / as sysdba

set serveroutput on

declare
tracking_label VARCHAR2(15);
begin
dbms_streams_adm.set_message_tracking(
tracking_label => 'ARKZOYD',
actions => DBMS_STREAMS_ADM.ACTION_MEMORY);
END;
/

create or replace procedure demo.hizoyd is
begin
null;
end;
/
Check the view to see how the message has been managed by Streams:
set lines 120
col component_name format a19
col component_type format a18
col action format a23
col object_owner format a14
col object_name format a14
col command_type format a16
select component_name,
component_type,
action,
object_owner,
object_name,
command_type
from V$STREAMS_MESSAGE_TRACKING
where tracking_label='ARKZOYD'
order by timestamp;
Here is the result:
COMPONENT_NAME     COMPONENT_TYPE      ACTION                     OBJECT_OWNER   OBJECT_NAME      COMMAND_TYPE
----------------- ----------------- ----------------------- ------------- -------------- ----------------
STREAMS_CAPTURE CAPTURE Created Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_CAPTURE CAPTURE Rule evaluation Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_CAPTURE CAPTURE Enqueue Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_CAPTURE CAPTURE Created COMMIT
STREAMS_CAPTURE CAPTURE Enqueue COMMIT
STREAMS_PROPAGATION PROPAGATION SENDER Dequeued Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION SENDER Propagation Sender sent Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION SENDER Dequeued COMMIT
STREAMS_PROPAGATION PROPAGATION SENDER Propagation Sender sent COMMIT
The tracking is propagated to the destination database (i.e. WHITE) like you can see below :
exit

. oraenv
WHITE

sqlplus / as sysdba

set pages 20
set lines 120
col component_name format a19
col component_type format a20
col action format a28
col object_owner format a14
col object_name format a14
col command_type format a16
select component_name,
component_type,
action,
object_owner,
object_name,
command_type
from V$STREAMS_MESSAGE_TRACKING
where tracking_label='ARKZOYD'
order by timestamp;
Here is the result:
COMPONENT_NAME      COMPONENT_TYPE       ACTION                       OBJECT_OWNER    OBJECT_NAME    COMMAND_TYPE
----------------- ------------------ ------------------------- ------------- -------------- ----------------
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue COMMIT
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation COMMIT
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation COMMIT
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue COMMIT
STREAMS_APPLY APPLY READER Dequeued Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_APPLY APPLY READER Dequeued COMMIT
STREAMS_APPLY APPLY SERVER Apply executed Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_APPLY APPLY SERVER Commit COMMIT
And you can check the procedure has been replicated to the WHITE database:
select text    
from dba_source
where owner='DEMO'
and name='HIZOYD'
order by line;

TEXT
-------------------
procedure hizoyd is
begin
null;
end;

No comments:

Post a Comment

Post a Comment