Home » Server Options » Replication » (materialized view) Replication Oracle - MS SQL Server
(materialized view) Replication Oracle - MS SQL Server [message #263575] Thu, 30 August 2007 07:51 Go to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
Hey guys!
We are searching for some possibilities to copy data between Oracle and MS SQL Server (in both directions)

We have found some possible solutions so far:

- Import/Export with XML
No solution for us, because of some restrictions Sad
- DB-Links
We will try that
- transactional replication

As for transactional replication...As far as i have read Oracle doesn't support that. We could use Oracle Streams but we don't want to pay for the license.
So i stumbled across replication (e.g. materialized view replication)
Do you guys think that this is a possible solution for our problem? Is it possible to do that with NON-Oracle-Databases? Any other ideas?

Any help would be appreciated
thanks in advance
panzertape

[Updated on: Thu, 30 August 2007 07:52]

Report message to a moderator

Re: (materialized view) Replication Oracle - MS SQL Server [message #264010 is a reply to message #263575] Fri, 31 August 2007 09:02 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
While I am waiting on an answer to my question I thought I would provide some feedback for yours.

I have some trigger based logic that I have implemented to go from one Sybase server to another. The same logic could be used between Oracle and MS SQL Server. Let me know and I will send you a high level description of the process.
Re: (materialized view) Replication Oracle - MS SQL Server [message #264017 is a reply to message #263575] Fri, 31 August 2007 09:16 Go to previous messageGo to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
Hey tomstone!
Thanks for your reply!
I am very interested on your solution! It would be great if you could send me the description of your logic!!
Do you use that for copying the entire DB or for some datasets?

thanks
panzertape
Re: (materialized view) Replication Oracle - MS SQL Server [message #264078 is a reply to message #263575] Fri, 31 August 2007 12:12 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Panzertape,

The logic only works with specified tables, so if you need to replicate the data for all tables you would have to specify all tables.

Here is the 30,000 foot view:

An insert/update/delete trigger in the source database on table source_t populates table source_replica_t that has the same columns, but also has a sequence_id column, and an transaction_flag column that will contain 'I', 'U', or 'D' (for insert, update, or delete).

The source_replica_t table is periodically unloaded to a file system (via bcp for MS SQL Server or SQL*Loader for Oracle).
This file transferred to the destination box through whatever mechanism you deem appropriate (scp - secure copy, ftp, ...etc.), or maybe you have an NFS mounted disk that both the source and destination box have access to.

Either way, the next step is to load the data into a dest_replica_t table in the destination database which has the same structure as source_replica_t.

You then have a SQL script that executes the transactions in the same order (using the sequence_id column) and populates a dest_t table that has the same structure as source_t.

If you have success up to this point, then you delete all the data in dest_replica_t, and then delete only the data in source_replica_t for the transactions that were just transferred. This way, during the transfer process the source database can continue to load new data into source_replica_t via the trigger.

Hope this helps.


Re: (materialized view) Replication Oracle - MS SQL Server [message #264445 is a reply to message #264078] Mon, 03 September 2007 06:32 Go to previous messageGo to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
tomstone, thank you very much for your description! I really appreciate that!

But unfortunately we can't use it.
The main problem is, that normally we don't have admin-rights on the DB-server. So it's not possible to create a file for dataset-exchange or establish a tcp-connection...

Do you have any idea how we could solve that?
thanks again!
panzertape
Re: (materialized view) Replication Oracle - MS SQL Server [message #264770 is a reply to message #263575] Tue, 04 September 2007 09:29 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Sorry. I do not have a technical solution for that.
Re: (materialized view) Replication Oracle - MS SQL Server [message #431248 is a reply to message #263575] Mon, 16 November 2009 05:33 Go to previous messageGo to next message
daspeac
Messages: 5
Registered: November 2009
Junior Member
Hi panzertape, the response seems to be too late, but maybe it may help others. if you are still interested, I recommend that you try the waiting for sql server to recover databases program, it is a good solution, if you experience any difficulties with dbx files. This application also converts database files. Hope it helps and not considered to be spam
Re: (materialized view) Replication Oracle - MS SQL Server [message #431254 is a reply to message #431248] Mon, 16 November 2009 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you posted it 3 times, you are at spam border line.

Regards
Michel
Re: (materialized view) Replication Oracle - MS SQL Server [message #431256 is a reply to message #431254] Mon, 16 November 2009 06:09 Go to previous message
daspeac
Messages: 5
Registered: November 2009
Junior Member
Michel, sorry, I'll be cool
Previous Topic: Oracle Materialized View | Deletion of Records, Oracle Materialized View | Deletion of Records (merg
Next Topic: Replication from 10.2.0.4 to 9.2.0.6
Goto Forum:
  


Current Time: Thu Mar 28 03:55:28 CDT 2024