Re: Interesting Oracle Data Load Scenario

"Matt" <mccmx_at_hotmail.com> wrote in message news:cfee5bcf.0405190713.a7dec31_at_posting.google.com...
> Hello all,
>
> I have a requirement for building an environment which will allow me
> to maintain 2 synchronised copies of our production database. This is
> not for High Availability purposes or Reporting purposes but for
> segregating online users from data loading users.
>
> Here is the scenario:
>
> The online users will update the primary copy of the database and
> these changes will be copied to the second database (doesn't have to
> be realtime). A data conversion process will be loading a large amount
> of data into the secondary database at the same time.
>
> At the end of the loading process, the secondary database (which will
> be a consolidated copy of the 2 sets of changes) will be copied over
> the primary database. The reason for this is twofold....

So while this is happening you cann't access the primary database. Also you can't make any changes in the primary database while it was copied over to the secondary for the data load. (otherwise you loose the data when you restore the database 2ndary to primary)
>
> 1. We want to minimise the performance overhead of the data load
> process on the main/primary database.
>
> 2. If there is a failure/problem with the data load we want to avoid
> a point in time recovery on the primary database. Instead we can
> rebuild the secondary database from the primary and restart the
> database conversion procedure.
>
> Physical Standby is not an option since the standy DB can't be
> updated. Advanced replication is a candidate but we don't have any
> primary key constraints at the back end (Ref integrity is driven by
> the application).
>
> Can Logical Standby be used for this purpose.....?
>
> Thanks in advance..
>
> Matt

You would be much better served RAC'ing the instance and not having 2 databases or buying sufficient hardware and spending time tuning the application and data loads. My guess is if you use bind variables, and tune the application you should be able to do both things at the same time. (The administration and complexity of what you are trying to do is insane and costly)

Jim Received on Wed May 19 2004 - 19:23:20 CDT