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

On Sun, 26 Sep 2004 14:28:33 -0700, Marc Slemko <identd_at_gmail.com> 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
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l