Skip to Main Content
  • Questions

  • Oracle Streams from multiple source database to target database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, chirag.

Asked: January 25, 2017 - 1:35 am UTC

Last updated: February 01, 2017 - 8:07 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

This is my first question, Thanks for your help in advance.

We have two databases for OLTP having same schema with same tables (structure wise same but with different data in them).
we need to replicate these database schema tables (from both oltp databases) in reporting database but with the data containing from tables of both OLTP databases.

example : table A is in database X and table y is in database B (X and Y have same structure),
we need these two tables as table z with same structure as x and y in database C containing data of both table x and y.

We are targeting Streams replication for this thing, but i am not particularly sure if we can merge the two different data sets from two separate database tables in single reporting database table.

1) Could you please advise is streams would be a good option for this.
2) Can the merge be done within Streams itself or we will need separate ETL process for that.


Thanks.

and Connor said...

It *can* be done, but be aware that you will most probably have to deal with conflicts, ie, overlaps etc. This is *not* a trivial undertaking - in fact, we have a whole chapter in the Streams doc talking about it

http://docs.oracle.com/database/121/STREP/conflict.htm#STREP001

If you have a read in there, you can see that whilst we provide various means of resolving conflicts, there is an underlying theme of: try to avoid conflicts where possible

So it *can* be done...but I'm not sure embracing a conflict-based system is a good thing. If its just for reporting, perhaps consider bringing the changes into (for example) separate schemas, and using views to expose a consolidated data set etc.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the reply

A reader, February 01, 2017 - 10:13 am UTC

Thanks once again, it was of lot of help,

Our systems have table structure same, but the data is different hence there will be minimal chances of update conflict(atleast i hope so) after looking at the primary keys.

Just one followup, will there be any performance deficiency as compared to other licensed tools like golden gate or any considerable performance overhead?

Thanks

Connor McDonald
February 01, 2017 - 8:07 pm UTC

Streams works in a similar fashion to GG, ie, generate SQL from redo logs, so its going to similar levels of performance.

But run this:

select view_name
from dba_views
where view_name like upper('%STREAMS%')
and owner = 'SYS'

Using Streams is a fair bit more setup and management that using GG.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.