Home » Server Options » Streams & AQ » Message filtering in propagation process (stream replication environment) (database 64-bit Windows, ver. 10.2.0.4.0 + 32-bit Windows, ver. 10.2.0.1.0, Windows 2008 server + Windows XP)
Message filtering in propagation process (stream replication environment) [message #356068] Wed, 29 October 2008 05:14 Go to next message
dariusz
Messages: 1
Registered: October 2008
Junior Member
Hi!

We have fine configured stream replication in star topology:
ORCL2 <=> ORCL1 <=> ORCL3
Where the ORCL1 is "headquarters" and there is no message flow between ORCL2 and ORCL3.

For some reason we want to filter messages in propagation processes, e.g. DML captured on ORCL1 should be replicated only to ORCL2 or only to ORCL3. There is one propagation process for each "satellite" database.
To solve this problem I have written function:
FUNCTION Replicate_Lcr (
p_lcr IN SYS.lcr$_row_record)
RETURN VARCHAR2 IS
which will be making a decision whether to pass the message (return 'Y') or not (return 'N').
But there is problem: rule is evaluated and function is executed (there is insert into 'stream_log_lcr' table) but value of the expression seems to be 'FALSE' and message (LCR) is not beeing sent to ORCL2 (or to ORCL3).
When I remove function 'Replicate_Lcr' from propagation rule condition then every message captured by capture process on ORCL1 reaches destination database (ORCL2 or ORCL3).

The second observation is that, if I run the same code on ORCL2 or ORCL3 then everything seems to be OK: there is insert into 'stream_log_lcr' table and DML captured on ORCL2 (or ORCL3) appears in ORCL1 ("headquarters").


I suppose that this could be problem with other version of database on "headquarters" (ORCL1) or configuration issues.
I will appreciate every suggestion.

Databases:
ORCL1: 64-bit Windows, ver. 10.2.0.4.0, Windows 2008 server
ORCL2: 32-bit Windows, ver. 10.2.0.1.0, Windows XP
ORCL3: 32-bit Windows, ver. 10.2.0.1.0, Windows XP


SQL code run on ORCL1:

CREATE TABLE stream_log_lcr (
data DATE NOT NULL,
msg SYS.lcr$_row_record NOT NULL
)
/

-- simplified, always return 'Y'
CREATE OR REPLACE FUNCTION Replicate_Lcr (
p_lcr IN SYS.lcr$_row_record)
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
--------------------------------------------------------------------------------
BEGIN
IF p_lcr IS NOT NULL
THEN
INSERT INTO stream_log_lcr
VALUES (SYSDATE,
p_lcr);

COMMIT;
END IF;

RETURN 'Y';
END;
/

-- create propagation process with above function in rule condition
BEGIN
DBMS_STREAMS_ADM.add_schema_propagation_rules
(schema_name => 'data_schema',
streams_name => 'primary_to_secondary2',
source_queue_name => 'strmadmin.capture_primary',
destination_queue_name => 'strmadmin.from_primary@ORCL2',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'ORCL1',
and_condition => ' strmadmin.Replicate_Lcr(:dml) = ''Y'' ',
inclusion_rule => TRUE,
queue_to_queue => TRUE);
END;
/

-- check if function 'Replicate_Lcr' was evoked:
SELECT * FROM stream_log_lcr ORDER BY data;
Re: Message filtering in propagation process (stream replication environment) [message #456736 is a reply to message #356068] Wed, 19 May 2010 06:49 Go to previous message
dboyr
Messages: 1
Registered: May 2010
Junior Member
I think you could add a propagation/capture for each database. In this way you can add the filter condition at dml_condition at capture.
I am having troubles with and_condition in propagation myself.

It seems that if I have a dml_condition in place at capture level, everything works like it should. I mean if the condition is achieved then the row is inserted in the destination db. If the condition will no longer be achieved then the row at destination is deleted.
When I put the filter condition at propagation level at and_condition, the row at destination is no longer deleted and is not updated (obviously - because the filter condition is not achieved). So when the filter condition is achieved again on source db, then when it tries to update the data at destination db, of course the data is not synchronized with the source and I get an error. The Oracle Stream setup you can see it here: www.eratelecom.ro/consultanta-oracle/oracle-forum/55-oracle-streams-one-step-setup-or-not .

Any ideas anybody?

[Updated on: Wed, 19 May 2010 06:52]

Report message to a moderator

Previous Topic: How to keep Advanced Queuing clean (remove dead subscribers)?
Next Topic: Error in Apply process
Goto Forum:
  


Current Time: Fri Mar 29 10:59:01 CDT 2024