Home » Server Options » Replication » Suggestions sought on replication (Oracle EE 11.2)
Suggestions sought on replication [message #588846] Fri, 28 June 2013 17:16 Go to next message
Jeremy.Russell
Messages: 14
Registered: September 2011
Location: Charleston, SC
Junior Member
I have a requirement from my user community to research the optimum mechanism for refreshing a disconnected, remote database from a production database. Until now, we have simply data pump exported the required data with data pump (45GB), zipped the dump files (16GB), ftp'd them to the target server, then unzipped and imported into the target database. For security reasons, we are unable to set up database links from source to target. The nightly volumes are becoming too great for this pseudo complete refresh, therefore we are seeking alternatives.

I have researched the following:

1) transportable tablespaces - not appropriate due to physical file size to transfer
2) user written triggers to capture deltas - impractical due to rapidly changing structures
3) materialized views, via exporting MLOG$_tables and refreshing

Should I be able export/import mview logs and refresh from those? When I try, the dbms_mview.refresh appears to be successful (no error is returned) but the log is emptied and the target table remains unchanged.

Does anyone have other suggestions for me to investigate, remembering that direct server->server comms aren't possible.

Thanks in advance
Re: Suggestions sought on replication [message #588861 is a reply to message #588846] Sat, 29 June 2013 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) You can zip the files
2) Triggers are unavailable as you can't use db links
3) MViews are unavailable as you can't use db links

Quote:
Should I be able export/import mview logs and refresh from those?


You can follow this topic (but it depends on OP's will to work).

Quote:
When I try, the dbms_mview.refresh appears to be successful (no error is returned) but the log is emptied and the target table remains unchanged.

Quote:
remembering that direct server->server comms aren't possible.


How did set up mviews with no link between the 2 servers?

Regards
Michel
Re: Suggestions sought on replication [message #588868 is a reply to message #588846] Sat, 29 June 2013 02:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Could you do something with the Change Data Capture facility, using exp/imp to get the change data sets across?
http://docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm#i1025409
CDC is horribly complicated, even within one database.
Re: Suggestions sought on replication [message #588880 is a reply to message #588868] Sat, 29 June 2013 04:57 Go to previous messageGo to next message
Jeremy.Russell
Messages: 14
Registered: September 2011
Location: Charleston, SC
Junior Member
@Michel: I am experimenting with SCOTT.EMP and SCOTT.DEPT for now - I created those tables separately on each server, then added mviews and mview logs for each. On the source server, I performed updates to DEPT, then exported, ftp'd and imported the mview log files. The mview log content was queryable on the target server - a dbms_mview.refresh ran without error but didn't modify the target master table. The log was created with values - but I am wondering whether the CHANGE_VECTOR$$ and XID$$ columns are specific to the database itself?

@John: thanks, I will try experimenting on Monday but have the feeling the mode that looks appropriate - Asynchronous Distributed HotLog - may also need inter db connectivity. But it's not yet 6:00am, im not really awake and I can't try until next week. I'll be back ...

[Updated on: Sat, 29 June 2013 05:22]

Report message to a moderator

Re: Suggestions sought on replication [message #588882 is a reply to message #588880] Sat, 29 June 2013 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am wondering whether the CHANGE_VECTOR$$ and XID$$ columns are specific to the database itself?


Of course.

Regards
Michel
Re: Suggestions sought on replication [message #589046 is a reply to message #588882] Mon, 01 July 2013 12:04 Go to previous message
Jeremy.Russell
Messages: 14
Registered: September 2011
Location: Charleston, SC
Junior Member
Then I guess it's back to the drawing board and I'll need to figure out a different mechanism.

Thanks, Michel and John!
Previous Topic: Oracle instance crash
Next Topic: Oracle Replicate Database and failover or switchover
Goto Forum:
  


Current Time: Fri Mar 29 08:23:49 CDT 2024