Re: options for read only mirror with 10g SE or SE One

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: identd@xxxxxxxxx
  • Date: Mon, 27 Sep 2004 09:48:26 +0100

On Sun, 26 Sep 2004 14:28:33 -0700, Marc Slemko <identd@xxxxxxxxx> wrote:
> I'm sure this is a fairly common desire, however I haven't found any
> good answers yet.  We have some applications that do not yet demand
> any of the scalability, availability or performance features of
> enterprise edition, except that we want a way to have a read only copy
> of the database on a physically separate machine.
> The requirements for the copy:
> - should be almost up to date (near instant desired, 5-10 minutes is ok)
> - does not need to be synchronous with the primary database
> - needs to be accessible for read only queries
> - no automated failover required, manual failover needs to be
> possible, loss of 5-10 minutes data is acceptable.
> - needs to be able to automatically replicate new tables and other
> basic DDL changes in addition to DML changes.
> - both the primary and the copy will be located in the same data center

You are describing manual standby. 

> The current business requirements for this application make using
> enterprise edition a very hard sell from the cost perspective right
> now, just to get the ability to have some type of read only
> replicated/standby/etc. database.  This is especially true given that
> Microsoft SQL Server includes reasonably usable replication support in
> their standard edition.  However, there is a possibility of future
> requirements justifying Oracle EE, and a general desire to use Oracle
> for the application for ease of future scaling and migration.
> Options I am aware of:
> - What is described as "basic replication" in Oracle's product family
> comparison list, with a note of "Updateable materialized view site".
> This doesn't seem to be an attractive option since, from what I
> understand these need to be setup for each table, and do not support
> DDL changes.  I am, however, having a very hard time mapping this
> feature in Oracle's product family comparison list to what it actually
> means.  Searching for "basic replication" in the 10g docs does not
> return a single result.

I suspect in 10g they mean Oracle Streams - I don't believe that in SE
you can use streams to capture DDL (or at least you can but you'd have
to roll your own).

> - Create a standby database in manual recovery mode and setup scripts
> to copy the redo logs from the primary database.  As I understand it,
> it is possible to do this and have the database online in read only
> mode.  Not overly attractive since it is reinventing the wheel.

I'm afraid I don't understand this. You are asking for a read-only
copy of a database and don't require automated failover. Manual
standby gives you this and has done since at least version 8 (I
*believe* since 7.3.4) . It isn't DataGuard but it doesn't sound like
it needs to be for you (or the cost of DataGuard is unattractive for
you - which I understand).

> - some third party product that can do something resembling the
> previous option.  I don't have any good leads on possible products
> that are cost effective for this.
> - implementing it manually with db links and a bunch of triggers and
> stuff.  Even less attractive than copying the redo logs over in terms
> of reinveneting the wheel and dealing with all the corner cases.
> Any other suggestions?

RAC is free with 10g SE (not SE One). 

Niall Litchfield
Oracle DBA

Other related posts: