| snapshot refresh order, can we perform in order [message #360845] |
Mon, 24 November 2008 02:23  |
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 #361071 is a reply to message #361040] |
Tue, 25 November 2008 00:16  |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
