Home » Server Options » Replication » snapshot refresh order, can we perform in order (Oracle 10g, Windows Server 2003)
snapshot refresh order, can we perform in order [message #360845] Mon, 24 November 2008 02:23 Go to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member

Hello everybody,

I have implemented a basic snapshot replication. On primary site there is 1 schema holding all of my tables. I have create triggers for handling DML in all the tables that creates actual DML statements and puts those statements in a table called DML_INFO. DML_INFO table has a PK called DML_TRAN_ID. This PK is a sequence to generated statement ID in order. This table has a snapshot log table on itself.

In my destination site, i have created a SNAPHOT over the DML_INFO table's log table. When i perform snapshot refresh then all the statements come into the destination siet and i have further created a triggger over DML_INFO in the destination schema snapshot table (DML_INFO). This trigger automatically executes the statement in the destination schema, hence updating data in the corresponding schema tables in destination.

My data would be logically correct if statements are execute in order i.e w.r.t. DML_TRAN_ID ASC.
For initial setup i perform a complete snapshot refresh and lateron we perform fast refreshes to propagated latest changes to destination.

E.g. If entries in DML_INFO at source are generated as
DML_TRAN_ID STATEMENT
1 INSERT INTO A(ID,NAME) VALUES (1,'ABC');
2 UPDATE A SET ID=2 WHERE ID=1;
3 INSERT INTO A(ID,NAME) VALUES (1,'XYZ');

Now my data in the destination would be right if the statements execute in order i.e. 1,2,3; but sometimes i get a unique constraint error if the execution order is 1,3,2.

Basic snapshot replication does not ensure the order, as far as i know.

My Question is:
Is these something builtin available in snapshot log tables / snapshots to ensure that the replication using snapshots is performed in order i.e. whenever i perform a fast refresh it is perfdormed in order i.e. statements will execute in the order 1, then 2, and then 3.

Please help me

[Updated on: Mon, 24 November 2008 03:55]

Report message to a moderator

Re: snapshot refresh order, can we perform in order [message #361023 is a reply to message #360845] Mon, 24 November 2008 21:09 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Good Question.

>> i get a unique constraint error if the execution order is 1,3,2.


I hope it's basic replicaiton so all the mviews should be read only.

From mview site to try to create deferrable constraints.

Babu
Re: snapshot refresh order, can we perform in order [message #361032 is a reply to message #360845] Mon, 24 November 2008 22:17 Go to previous messageGo to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member

Thanks babu

I have already deferred all the FKs. I dont want the PKs to be deferrable.

But even if i convert all the PKs into deferrable; then Sometimes two unordered updates also put my database into abnormal situation. E.g.

DML_TRAN_ID STATEMENT
.
.
.
21 UPDATE LOAN SET PAYMENT_RECEIVED=2500 SET STATUS='PENDING' where loan_id=124;

22 UPDATE LOAN SET PAYMENT_RECEIVED=2500 SET STATUS='COMPELTED' where loan_id=124;
.
.
.

Now if statement exec order is 21,22; then things are right.
But if order is 22,21; then even after physically paying off everything, customer's status is yet marked as PENDING.

This situation comes in many areas if refresh order is not defined.
Re: snapshot refresh order, can we perform in order [message #361040 is a reply to message #361032] Mon, 24 November 2008 23:08 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


I request to you.

Please post your table script including constraints (from master site) & also post your mview script including constraints & indexes..

Babu
Re: snapshot refresh order, can we perform in order [message #361071 is a reply to message #361040] Tue, 25 November 2008 00:16 Go to previous messageGo to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member

there wont be any constraint / data abnormal situation only if the order can be maintained.
Scripts are very lengthy and contains hundreds of tables.

All i need is an ordered refresh policy to be maintained during each refresh.

I am forwarding u the snapshots related scrits as follows:

------------SOURCE SITE-------------
/*
NOTE: Source also has many transaction tables and having their own DML triggers caapturing DMLs into the following table. And this table is then replicated at destination and the statements in it as are executed at destination with:
EXECUTE IMMEDIATE DML_STATEMENT;
*/

CREATE TABLE DML_INFO
(
DML_TRAN_ID NUMBER(12),
DML_TYPE_FLAG CHAR(1 BYTE),
TABLE_NAME VARCHAR2(30 BYTE),
BRANCH_ID CHAR(3 BYTE) NOT NULL,
DML_DATTM DATE DEFAULT SYSDATE,
DML_STATEMENT VARCHAR2(4000 BYTE) NOT NULL
)
/
ALTER TABLE DML_INFO ADD PRIMARY KEY (DML_TRAN_ID) USING INDEX
/
CREATE MATERIALIZED VIEW LOG ON TEST.DML_INFO TABLESPACE TEST
/
------------DESTINATION-------------
/*
TESTLINK created that connects to source site schema.


*/

CREATE MATERIALIZED VIEW TEST_MV.DML_INFO
REFRESH FAST ON DEMAND WITH PRIMARY KEY AS
SELECT DML_TRAN_ID, DML_TYPE_FLAG, TABLE_NAME, BRANCH_ID, DML_DATTM, DML_STATEMENT FROM TEST.DML_INFO@TESTLINK;

-- this snapsht table has a trigger defined over this that executes the statements during the refresh process as:

CREATE OR REPLACE TRIGGER CAPT_DML_INFO
AFTER INSERT ON DML_INFO
FOR EACH ROW
DECLARE

V_DML_STATEMENT VARCHAR2(4000);
V_DML_TRAN_ID NUMBER(12);
V_BRANCH_ID CHAR(3);
V_BRANCH_NAME VARCHAR2(60);
V_DESCRIPTION VARCHAR2(500);
ecode VARCHAR2(10);
emesg VARCHAR2(400);

BEGIN

V_DML_STATEMENT := :NEW.DML_STATEMENT;
execute immediate V_DML_STATEMENT;

EXCEPTION

WHEN OTHERS THEN
V_DML_TRAN_ID := :NEW.DML_TRAN_ID;
V_BRANCH_ID := :NEW.BRANCH_ID;
SELECT DESCRIPTION INTO V_BRANCH_NAME FROM BRANCH WHERE BRANCH_ID=V_BRANCH_ID;
emesg := substr(SQLERRM,1,350);
V_DESCRIPTION := emesg;

--the save_line procedure puts the error description in a error logging table
SAVE_LINE(:NEW.DML_TRAN_ID,:NEW.BRANCH_ID, V_DESCRIPTION);

RAISE;

END;
/



Thats all how it works....

But there is no other issue except the execution order and there is nothing even tricky in the scripts. they are all straight and clear
Re: snapshot refresh order, can we perform in order [message #361092 is a reply to message #361071] Tue, 25 November 2008 00:41 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

Again I'm requestion to you.

>>I have already deferred all the FKs. I dont want the PKs to be deferrable.

1/ As per your comment; where is constraints script from mview site??

PS: I request to you use TOAD & post your mview script with all constraints.

2/ Please post your exact error message during data transfer..

PS: We are expecting like (ORA-12345; Unique constriants voilated in EMP) (only example purpose)

Babu
Re: snapshot refresh order, can we perform in order [message #361102 is a reply to message #360845] Tue, 25 November 2008 01:19 Go to previous messageGo to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member


there is no issue with the snapshot refresh process.
snapshot gets refreshed without any problem.

there are no constraints defined over the sansphot table, nor it gives me (ORA-12345; Unique constriants voilated for the sn

babu thanks for reminding the guidelines link.
i think i am unable to convey my request.

Can u please just tell me how can v ensure the refresh to be executed in a particular order in snapshot based replication? Also let me know if this is a limitation with SNAPSHOTS to ensure the refresh in order.

Thats all i want to know man.
Re: snapshot refresh order, can we perform in order [message #361111 is a reply to message #361102] Tue, 25 November 2008 01:41 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Thanks a lot.

ASAIK, for particular table records refresh randomly only happen. I think this is not happening by order.

I request to you; need follow below configuration in mview site.

1/ Create deferrable constraints (unique,foreign/refrence key's etc..)

2/ Try to avoid duplicate records in primary site.

3/ Please don't create unique index in mview site (which this column in primary key in master site)


Click Here

Babu
Re: snapshot refresh order, can we perform in order [message #361121 is a reply to message #361111] Tue, 25 November 2008 01:50 Go to previous messageGo to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member

I think your first line has an indication to my answer.
I THINK THIS IS A LIMITAION IN SNAPSHOTS, REFRESH ORDER IS NOT ENSURED, MAY BE A LATER RELEASE OR PATCH MAY FIX THIS ISSUE.

yes of course no duplication data exists on source site.

anyways i have implemented a procedure that executes the statements in order lateron after the snapshot refresh process without deferring the primary keys.

This way i execute all the statements in order and no unique constraints / data mismatch/ abnormal data issue are occurring now.

The problem was only with the logical sequence of execution of statements in my applicaiton. I have changed the execution order in a different way. But there is no issue with the refreshes now.

I run a job after each refresh that executes the procedure to execute all the currenlty available statements in DML_INFO table.


THANKS FOR SHARING BABU,
Re: snapshot refresh order, can we perform in order [message #361152 is a reply to message #361121] Tue, 25 November 2008 03:43 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Great.

One more question. Do u have trigger (CAPT_DML_INFO) in mview site??

Babu
Re: snapshot refresh order, can we perform in order [message #361332 is a reply to message #361152] Wed, 26 November 2008 00:04 Go to previous messageGo to next message
rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan
Member

I had that previously thats why i was having these issues because statements were executed immediately they arrived at mview site.

In the procedural logic i have dropped that trigger and statement execution logic has been conveyed in the procedure.
Re: snapshot refresh order, can we perform in order [message #362670 is a reply to message #361332] Wed, 03 December 2008 12:30 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Sorry for delay response.

From Mview site; If you have any trigger; I suggess pls use

DBMS_MVIEW.I_AM_A_REFRESH package;

Babu
Previous Topic: No data found error
Next Topic: What does "WITH SEQUENCE" clause in "CREATE SNAPSHOT LOG" statement do?
Goto Forum:
  


Current Time: Thu Mar 28 16:32:26 CDT 2024