Re: Replication Advice

Why haven't you considered doing it with archived redo logs?

Daniel Morgan

Keith Boulton wrote:

> I am currently working on a utility to snapshot data from multiple databases
> to a repository database. This can copy an entire scheme or groups of
> tables. In the case of tables, the data can be appended to an existing table
> with the addition of date stamp and source columns. This is an 8.1.5 NT
> environment.
>
> The main problems seem to be performance, ensuring a point-in-time snapshot
> and complexity.
>
> The options I've considered:
>
> export/import
> pros: supports point in time; supports all datatypes e.g. long
> cons: performance -> cannot avoid generating redo; need to write special
> code to do appends; requires an intermediate file (this is not UNIX); have
> encountered bugs in exp/imp
>
> replication api:
> pros: presumably can be tuned to perform well; supports point in time;
> doesn't require an intermediate file
> cons: doesn't support longs; doesn't support append
>
> dump to text and use sqlloader
> pros: can use direct path; can set point in time on export
> cons: more complex; requires intermediate file; support for longs with
> embedded crlf etc could be difficult
>
> pl/sql
> pros: simple to code; can avoid redo
> cons: doesn't support longs; cannot get point-in-time
>
> To avoid overload the source servers, I would like to be able to do
> something like
> for each snapshot source
> set transaction read only
> for each snapshot source
> copy tables
>
> This is not possible with export.
>
> I also cannot see any way of setting a read-only transaction in the source
> databases while still being able to write to the target database.
>
> An alternative would be too write a client program that opened multiple
> connections, but I don't especially want to write a pro*c or OCI program,
> the performance of ADO/odbc is bad and longs cause problems and OO4OLE has
> problems with longs.
>
> Any suggestions?
>
> Cheers
>
> Keith Boulton
Received on Sat Feb 09 2002 - 07:13:36 CST