| Need help & advice on incremental data movement. [message #253335] |
Mon, 23 July 2007 10:10  |
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 #253383 is a reply to message #253340] |
Mon, 23 July 2007 12:48  |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
