Replication/DataGuard/Standby for reporting... reliability *and* async??

I'm using 10.2.0.2 on Windows 2003.

Requirement is to implement a read-only copy of a database, for running resource-intensive queries. 'Near' current data (~1 day old) is acceptable.

-Standby database can be out-of sync with production, up to about 24
hours; real-time updates not necessary.
-Standby is open for read-only queries for daytime hours;
-Standby us updated with redo generated from Primary twice daily
(10pm, 5am).
-Standby will NOT be used for DRP. Reporting *only*.

The need for read-only status during the day seems to require that redo log files be collected on the Standby, applied, then removed. Real-time application of redo to the standby instance via ARCHIVE_LOG_DEST_2 on the primary would not work.

The previous iteration of this was under 8i on AIX. The basic approach had been very basic:

-Primary database - only one local ARCHIVE_LOG_DEST is implemented.
-Redo files are rcpd to the standby server, then archived until the
next full cold backup, via an hourly shell script.

-Standby Database - held open in read-only mode except for twice-daily
updates
-Taken down and updated with all accrued redo, then restarted; accrued
redo deleted.

10g has a few more options.

I've investigated the use of AR, and Dataguard - but what I have not identified is a provision in either of these which allows for the automatic transfer of redo to a standby server, in such a way as the standby can be held open in read only mode for queries, then taken offline for occasional updates with accrued redo.

The only mechanisms I can see at this point are OS-level transfer of redo files ('the old way'), and logical standby.

AR and Dataguard seem to have tools such as FAL for resolving sequence gaps, but again, it appears that the target must be in a state that it can take the redo application in real time. (which again seems to mean I can't hold it open in read only mode for querying).

Logical standby looks like an option, but I've seen some concerns in the group archives about the reliability of Logical Standby. Granted, these concerns were in posts that are a little old now.

I'm continuing to pore through documentation on all these topics, and should have a small test environment available to me shortly, to vet out the details - but I'm hoping that someone who's established the same scenario (reporting database under Windows) could indicate what approach worked for them.

Thanks,

BD Received on Wed Mar 21 2007 - 15:10:09 CDT