Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: options for read only mirror with 10g SE or SE One

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

From: Carel-Jan Engel <>
Date: Mon, 27 Sep 2004 21:13:32 +0200
Message-Id: <>

This discussion is diverting somewhat from what the Original Poster was asking for.
Standby in all appearances (except Logical Standby, but that is EE) tends to be in recovery mode. So, there is quite few read-only access possible to the second database, which happened to be the original question. I don't think that opening and closing the database in read-only/recovery mode every five minutes will come close to the SLA that running the reports needs.
In flight information display systems on airports we used triggers to achieve the goal Marc is looking for.
We had a bunch of PL/SQL generating views and SQL, that actually created a complete set of triggers on all tables in a given schema. The triggers took care of creating entries in a logging table. This table was read asynchronously by the 'standby database'. This was a system with appr. 2 - 10K transactions per day. Yes, there was quite some overhead, but this was some 10 years ago, and advanced replication (AR) wasn't there, nor was Advanced Queueing. So, we built our own AR/AQ solutions, based on triggers and the dbms_alert package.

These days I would go for AR or Streams, and with 10g, maybe even SQL Apply/Logical Standby.

So Marc, I think you're stuck: standby (manual/semi-automatic) doesn't help you. AR is part of EE as well, IIRC. So, it's either generating triggers and go the cumbersome management way, or going EE. The trigger thing might very well beat your reporting load, so, maybe running the reports on the primary and put the standby (manual standby) on the second server is a better solution. I understand your fear of RAC complexity completely, however, just to have all options passed your mind, think of this: RAC with SE on two small servers. OLTP/batches connect to one instancer, reports to the other (no load balancing and maybe even no failover config). The standby database (manual apply to avoid EE licensing) can run on the second server as well. Now you've off-loaded the OLTP instance from reports, and you have two databases. You pay per processor, but are free to run two or more instances on the same server. The recovery process can only hurt the reporting (and vice versa) but that shouldn't be too bad. I'm sure Mogens will love this config, as he is director of a small database consultancy company ;-)

Mark is right about the licensing. I've seen customers getting the standby for 75% off, but you need a license. Only a cold standby, no Oracle software running, can go for free. As soon as it runs at 10 calendar days or more during a year, be it for failover or testing the scenario, you need to license that cold server as well.

Best regards,

Carel-Jan Engel

If you think education is expensive, try ignorance. (Derek Bok) ===

On Mon, 2004-09-27 at 16:05, Mark W. Farnham wrote:

> Clarification was made that getting individual manual standby strategies
> correct was not generally supported, and support did not cover getting free
> consulting in this regard, but physical recovery was most certainly NOT
> desupported. License issues regarding the recovery machine were not
> generally addressed until much later, probably when standby recovery became
> an official feature with more robust built in support for keeping it
> "right."
> I'm not sure who did it "first." I do remember hearing "but you're only
> allowed to do that with support from Oracle Consulting" some number of years
> after I had considered it a routine way to support rapid recovery and the
> creation of databases (cancel recovery, copy, startup with rename open
> resetlogs) as a convenient frozen image for decision support and loading
> time series information into long term data stores (warehouses, marts,
> etc.).
> At this writing, though, I believe that Oracle will insist that you must
> license the additional machine. If you don't plan that the machine would
> actually be for fail-over, though, I suppose that the additional license for
> a manual solution could be for quite a modest machine and a small number of
> users. I'm not sure whether the latest specification for the DataGuard
> license requires licensing for the same level as the primary machine (albeit
> with discounts), but a small machine manual standby certainly opens the
> question of possible major savings. Some folks over the years have
> negotiated things such as loading an alternate system for verification of
> the integrity and recoverability of backups, but I think Oracle tends to put
> a "certain number of times per year" limit in new licenses covering that
> issue.
> Regards,
> mwf

Received on Mon Sep 27 2004 - 14:08:44 CDT

Original text of this message