RE: Oracle Total Recall performance vs Materialized Views

The trade-off is slightly different in that you do need to let the log application lag and additional aggregations are not possible.  

Kyle also mentioned a way to do this directly with Delphix, which should work fine whether or not you're on dataguard. Doing it on the dataguard server has the very interesting ability to utilize what is usually quite a bit of excess CPU and i/o horsepower over what is required to keep logs up to date.  

mwf  

From: Adam Musch [mailto:ahmusch_at_gmail.com] Sent: Monday, July 29, 2013 2:29 PM
To: mwf_at_rsiz.com
Cc: cicciuxdba_at_gmail.com; oracle-l-freelists Subject: Re: Oracle Total Recall performance vs Materialized Views  

Mark:

One could do the following, instead:

At 11:50 on the standby database, stop automatic log application.

At 12:10 or thereabouts, recover the database to exactly midnight.

Open the database read only. Log transport should still run, but not managed recovery.

When done using the standby database as a reporting environment, resume the recovery process. If all the logs are present and one has turned off the checksumming on the standby, we've seen redo log application on a standby run at about an 8:1 time ratio.

That would eliminate the need to clone any other databases or to perform resetlogs; it's just another standby frozen in time at midnight, and if you're not using active data guard, it's not like the standby could be queried in any event.  

On Mon, Jul 29, 2013 at 11:34 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

Two things seem likely to be useful:

2) Move the whole shooting match to a frozen point in time clone of a physical dataguard database.
Notice that you cancel recovery (for a while, in your case shortly before the redo through 00:00:00 is applied seems best), shut down the physical dataguard instance, clone the dataguard database,
start recovery and roll THE CLONE (which has its own copy of the online redo logs) forward to exactly the time (or event) you want, shut down the clone,
startup rename resetlogs THE CLONE,
and then resume recovery on the untouched molested original physical dataguard database.

mwf  

--

Adam Musch
ahmusch_at_gmail.com

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 29 2013 - 23:16:00 CEST