| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> replication
Greetings,
I am looking for advice regarding Oracle replication. We are on 8.1.6 EE, and will be upgrading to Oracle9 later this year. At that time, we also plan to establish another Oracle instance on a separate sun machine; 1 instance will serve as a staging area, the second will be a production reporting database. We need a way to quickly move processed data from the stage instance to the production instance on a daily basis.
Methods we have discussed, pros and cons (please feel free to comment):
Export/Import and flat file transfers have been ruled out due to speed.
Transportable Tablespaces:
Pros: fastest method of moving large amounts of data
Cons: Constraints - our tables are very integrated, lots of foreign keys,
just about every tablespace set would have to include a core set of
reference tables, or the entire thing (500GB) would need to be in the
same tablespace set; not including constraints means re-building them
in the production instance, including indexes for PKs and UKs (I think)
and probably other problems. How do others handle these problems?
Also, this transfers all data, when only a very small percentage of
rows (< 1% of total rows) has actually changed that day. Seems inefficient.
Oracle Replication:
Pros: The documentation seems to address our situation, replicating a small
(relative to total db size) batched amount of data daily. (2-4 GB) Cons: Looks complex, 2 books (~760 pgs, ~360 pages in Oracle9), 13 packages.
No experience with this - How well does it work? Is it difficult to
set up? Any comments regarding speed? Can replication be set up for existing
tables, or do they need to be re-created and re-loaded as a materialized view?
We are also considering another solution, basically borrowing many of the ideas from Oracle replication and writing it ourselves. This would be a home-grown solution involving table triggers, additional tables to store the daily changes, and scripts to propagate the changes over database links. But before we decide, I wanted to hear what others had to say regarding Oracle replication.
Thanks for any advice.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Becker
INET: beckerb_at_mfldclin.edu
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jan 10 2002 - 15:11:00 CST
 |
 |