Re: Oracle Total Recall performance vs Materialized Views

   Thanks for all your replies :-)

    We discussed using DataGuard (this is how it's done in other locations) but there were conflicting requirements here (other schemas that need to use the same database and write on their own tables) and the need to add indexes as needed for new reports (query tuning is pretty much non-existent here, and way above my paygrade to discuss). We also analyzed the posibility of replicating the schema with GoldenGate from the online schema and have the replicats down until midnight, tart them up let them apply all the trails until midnight and then shut them down again. This was rejected due to limited space in the shared filesystem (this is an Exadata, so we are very restricted in some areas, sadly space is one of them). And certain misgivings most people have about Golden Gate.

    Delphix actually did turn up in my search, we are too far in the project and way to close to major deadlines to even consider bringin in a new tool (probably spend a good deal of time getting the "money" stuff done) and we have no test environment. (Yes, I am very well aware of the risks we are taking anyway, but management thinks they are acceptable). Delphix, however does seem like a tool worth remembering for the next project...

    We ended up trying to patch the system and recreated some materializaed views and grouped the refresh jobs to make them "mostly consistent"...

    I also looked into Oracle Total Recall, but what was brought up to reject it was that some queries have very strict performance requirements which can be guaranteed by a materialized view but that reading the flashback data archive was too much overhead. I was wondering if somebody benchmarked this. Run a query against a materialized view and then run a query against a table using flashback to sysdate-1. I know it will be slower to run the flashback query, but my question is how much slower.

   Thanks,
Cheers

Alan.-

On Mon, Jul 29, 2013 at 6:16 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> yep. That also works.****
>
> ** **
>
> 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