Home » RDBMS Server » Server Administration » Need help & advice on incremental data movement.
Need help & advice on incremental data movement. [message #253335] Mon, 23 July 2007 10:10 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
I’m currently working on two Oracle 10g Enterprise servers. The problem involves moving a subset of from one database DB1 to another database DB2 and keeping DB2 in sync with DB1. The size of the tables in both databases can reach millions of rows. Since DB2 is only a subset of DB1 it’s a little smaller but still has a lot of data.

Requirements:
DB1 and DB2 are not on the same network. An extract file will have to be extracted from DB1 and imported into DB2.

The extracted file 'MUST' be in XML format.

Incremental changes to DB1 (insert, update, delete) needs to be carried over to DB2.


The approach I’ve taken thus far:
I decided to use the flashback feature on Oracle 10g to check for changes that have occurred on the DB1 tables and capture them for an extract. To do the data extraction, I’ve written a java application to query DB1 and select all the insert, update and delete commands and have them write out to an xml file. I’m also using java to read the xml file and import that back into DB1.

The problem with this approach is that the flashback feature takes up a lot of space and had a limited range in how far back in time you wish to enable it for.


Are there any other options that people can suggest which might be more efficient? I looked at datapump but that won’t extract to an xml file.
Re: Need help & advice on incremental data movement. [message #253338 is a reply to message #253335] Mon, 23 July 2007 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Change Data Capture might be worth investigating
Re: Need help & advice on incremental data movement. [message #253340 is a reply to message #253335] Mon, 23 July 2007 10:13 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Replication I guess .
Re: Need help & advice on incremental data movement. [message #253383 is a reply to message #253340] Mon, 23 July 2007 12:48 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
if both databases are Oracle, then you need to understand the requirement that it use XML. XML is useful where the receiving system doesn't know the format of content of the incoming file beforehand. If this is a simple interface, the meatdata in the XML adds no value and simply haves the data file larger.
Re: Need help & advice on incremental data movement. [message #253384 is a reply to message #253383] Mon, 23 July 2007 12:56 Go to previous message
Rustican
Messages: 51
Registered: July 2006
Member
andrew again wrote on Mon, 23 July 2007 12:48
if both databases are Oracle, then you need to understand the requirement that it use XML. XML is useful where the receiving system doesn't know the format of content of the incoming file beforehand. If this is a simple interface, the meatdata in the XML adds no value and simply haves the data file larger.



Currently we are using two Oracle dbs. The size of the data file isn't an issue. The XML is a requirement so that an the data can be read by other applications such like a java application and apply it to a database (Oracle, DB2, MySQL, etc...)

Using the Oracle tools for extracting data (datapump, rman) outputs the data in a binary format proprietary to Oracle. If in the future it is decided to set up a non oracle database using the Oracle tools will be a no go. That's why xml was chosen to be the format to use.
Previous Topic: ORA-00600: internal error code, arguments: [16305], [], [], [], [], [], [], []
Next Topic: upgrading 9206 to 9207
Goto Forum:
  


Current Time: Thu Dec 08 08:13:34 CST 2016

Total time taken to generate the page: 0.16049 seconds