Home » Server Options » Streams & AQ » Streams Synchronization (Oracle 10.2.0.2)
Streams Synchronization [message #279870] Sun, 11 November 2007 04:57 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
In my Oracle Streams Configuration somehow two database in unsynchronized. Like in one table column value is different from other table of the same column. In my database there are hundreds of columns. I don't want to synchronize table by table and column by column. Is there any conflict handler so that it automatically handled any conflict making one database tables' column as a base database?

Any suggestion is welcome.

Report message to a moderator

Re: Streams Synchronization [message #279923 is a reply to message #279870] Sun, 11 November 2007 21:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
More precise I am,

I have two database named d1 and d2. I have configured two-way replication between these two.

There is a table tab1( a number primary key, b number, c number);

In tabs one record is (1,5,7);

Now in d1 I invoke update tab1 set b=6 where c=7;

And in d2 I invoke update tab1 set c=10 where b=5;

Now I commit on d1 . Then the data of tab1 will mismatch in both database. Now I want to synchronize both database in such a way so that base on one table data will overwrite to another.

In my database there are hundreds of table. Is there any way so that one database tables record will act as a base one and based on that other database record will be reflected.



Give me suggestion.

Report message to a moderator

Re: Streams Synchronization [message #279924 is a reply to message #279870] Sun, 11 November 2007 21:59 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
A demonstration of mismatch data.

SQL> select * from test;

         A
----------
         1
         2

SQL> update test set a=3 where a=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select * from test;

         A
----------
         3
         2

SQL> !hostname
Saturn



In another host,
SQL> select * from test;

         A
----------
         1
         2

SQL> update test set a=5 where a=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select * from test;

         A
----------
         5
         2

SQL> !hostname
saturn


Now I want both database will be synchronized base on 1st database. The is in both database when I query

select * from test
It will return (3,2).......


Anyone please...

Report message to a moderator

Re: Streams Synchronization [message #279933 is a reply to message #279870] Mon, 12 November 2007 00:05 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Michel or any one can you suggest me how I can synchronize two database so that both database will contain same data..

Please sound.

Report message to a moderator

Re: Streams Synchronization [message #279939 is a reply to message #279933] Mon, 12 November 2007 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can set a conflict handler. See

Streams Concepts and Administration
Chapter 1 Introduction to Streams
Select Overview of Automatic Conflict Detection and Resolution

Streams Replication Administrator's Guide
Chapter 3 Streams Conflict Resolution

Regards
Michel

Report message to a moderator

Re: Streams Synchronization [message #279941 is a reply to message #279870] Mon, 12 November 2007 00:49 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Michel , I am aware of this. My problem is not like this. A record is different in both database. That is a record exists in one database in not found on another. How I can resolve this?

Report message to a moderator

Re: Streams Synchronization [message #279950 is a reply to message #279870] Mon, 12 November 2007 01:04 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
That talk about conflict detection and conflict resolution. But I am talking about synchronization.

Report message to a moderator

Re: Streams Synchronization [message #279961 is a reply to message #279870] Mon, 12 November 2007 01:42 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Synchronization can be done by data pump or by any other method like we do at first while configuration stream replication. But I want to know is there any way in stream so that automatically it can be synchronized based on one database data.

Report message to a moderator

Re: Streams Synchronization [message #284391 is a reply to message #279961] Thu, 29 November 2007 09:02 Go to previous messageGo to next message
sriramkalyan
Messages: 7
Registered: November 2007
Location: Silver spring MD, USA
Junior Member
Yes .. i am also looking for that.

Shareplex has tool compare/repair to do that ...
Looks like Streams does have it .. may be datapump has that option.

Report message to a moderator

Re: Streams Synchronization [message #284861 is a reply to message #279870] Sat, 01 December 2007 23:07 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Exactly. DataPump, RMAN Duplicate, export/import, transport tablespaces anything can be done. But they talk about whole database. I got nothing like shareplex compare/repair tool to do synchronization in streams.

Report message to a moderator

Re: Streams Synchronization [message #285115 is a reply to message #284861] Mon, 03 December 2007 10:12 Go to previous messageGo to next message
sriramkalyan
Messages: 7
Registered: November 2007
Location: Silver spring MD, USA
Junior Member

Streams can be used to do synchronication by starting the capture and setting firstscn, startscn to back scns. Capture will go back to archive logs and starts propagating from old scns.

Or you can use Logminer to sync up ..if you know from which scn you want the data to be copied.

Report message to a moderator

Re: Streams Synchronization [message #285206 is a reply to message #285115] Mon, 03 December 2007 22:48 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Quote:

Streams can be used to do synchronication by starting the capture and setting firstscn, startscn to back scns. Capture will go back to archive logs and starts propagating from old scns.


That's true. But it does not talk like synchronization. Only committed transaction after the scn will be applied.

And another issue is in capture process how I can specify first scn if I don't use create_capture. I use add_schema_rules and it can be specified there.

Report message to a moderator

Re: Streams Synchronization [message #285442 is a reply to message #285206] Tue, 04 December 2007 10:34 Go to previous messageGo to next message
sriramkalyan
Messages: 7
Registered: November 2007
Location: Silver spring MD, USA
Junior Member

You can determine the current first SCN, applied SCN, and required checkpoint SCN for each capture process in a database using the following query:

SELECT CAPTURE_NAME, FIRST_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN
FROM DBA_CAPTURE;


For example, the following procedure sets the first SCN for a capture process named strm01_capture to 351232.

BEGIN
DBMS_CAPTURE_ADM.ALTER_CAPTURE(
capture_name => 'strm01_capture',
first_scn => 351232);
END;
/

The specified start SCN must be greater than or equal to the first SCN for the capture process. You can determine the first SCN for each capture process in a database using the following query:

SELECT CAPTURE_NAME, FIRST_SCN FROM DBA_CAPTURE;

Also, when you reset a start SCN for a capture process, make sure the required redo log files are available to the capture process.

For example, the following procedure sets the start SCN for a capture process named strm01_capture to 750338.

BEGIN
DBMS_CAPTURE_ADM.ALTER_CAPTURE(
capture_name => 'strm01_capture',
start_scn => 750338);
END;
/

Report message to a moderator

Re: Streams Synchronization [message #285443 is a reply to message #279870] Tue, 04 December 2007 10:45 Go to previous messageGo to next message
sriramkalyan
Messages: 7
Registered: November 2007
Location: Silver spring MD, USA
Junior Member

Arju,

You have to write a script using Logmnr to do similar thing as compair/repair does ..

Using Logmnr get all SQL transactions after certain scn or Date and compare the data with target and apply the missing SQL transactions at target.

Report message to a moderator

Re: Streams Synchronization [message #285492 is a reply to message #279870] Tue, 04 December 2007 21:57 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Kalyan, I know this. It does not help about synchronization. Suppose you think about the example that I have given prior in this thread. Two database data is now different. How I can repair/match other database data based on one database.


I think with streams it is not possible.

Report message to a moderator

Previous Topic: Remove apply rule
Next Topic: DBMS_AQ.dequeue_array always returns 0 messages
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 12 05:55:02 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.