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 http://www.niall.litchfield.dial.pipex.com -- //www.freelists.org/webpage/oracle-l