Re: Best way to replicate schema data to another database?

zenshade_at_gmail.com wrote:
> We need to essentially export the data from a single schema in our
> Oracle8i database and send it securely over a network to an Oracle9i
> database, in such a format that it can be easily imported. This needs
> to be done several times a day to keep the remote DB fairly up to date.
> On the source DB we have several other schemas that cannot be part of
> the export for security reasons, so we can't just create a remote
> backup using transaction log files, unless it is possible to filter
> those by schema.
>
> Right now, we're looking at trying to use Log Miner to do this, but
> please bonk me over the head if this is horribly naive. Is there a
> better, more efficient way?

One more efficient way, if you have only a small number of tables, indices and constraints, and a fast network, would be to set up dblinks and ctas (create table as select). You would need to investigate whether you need advanced security for net8 and whether the O8 dblink security is appropriate for your situation. For a particular cross-platform, cross-version situation I had this was much faster than exp/imp.

Another way is to make a pipe, export into compress on one side and uncompress into imp on the other. Google about for how to do this in a secure manner and be sure to use TCP. Also see http://www.oracledba.co.uk/tips/import_speed.htm

Also look into transportable tablespaces and external tables, and upgrade the dang O8.

jg

--
@home.com is bogus.
http://www.channelregister.co.uk/2005/10/25/sun_grid_slip/