Re: Undo Retention of 5 days; anyone?

In 9i Flashback Query, if we try to use the Timestamp method, then it always round the time to nearest 5 minute interval based on the Timestamp in SMON_SCN_TIME and that is why you might not been able to get the desired result as been required to some absolute time. This is the reason, SCN method is been preferred. Mine assumption is based on the SMON_SCN_TIME limitation of only 1440 records populated every 5 minute and so keeping only 5 days of record. So it is almost certain that Flash back timestamp method round the Timestamp to nearest timestamp in SMON_SCN_TIME and so I was thinking that what will happen to the flashback query that refere to timestamp no longer in the SMON_SCN_TIME. I know I had tried it some time back and got some error like snaphot of such time cannnot be recover or like but at that time I was also having UNDO_RETENTION also less than 5 days.

Indy Johal
Manager, Database Administration
PR Newswire
indy_johal_at_prnewswire.com
http://www.prnewswire.com

(201) 946-5687 [W]
(201) 400-3960 [M]

"We tell your story to the world."

Mladen Gogala <mgogala_at_adelphia.net>
Sent by: oracle-l-bounce_at_freelists.org
02/12/2004 10:30 PM
Please respond to oracle-l  

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        Re: Undo Retention of 5 days; anyone?

I must say that this discussion has suddenly become very interesting. Mapping of SCN to time is an aspect of the whole thing that I haven't thought about. Please, share with the list any conclusions and information about this thing that you may discover.

On 02/12/2004 10:16:14 PM, Arup Nanda wrote:
> Indy,
>
> Thanks for the update on the SCN time limitation. I wasn't thinking
> about
> that. If you do find some information on that limitation, I'll
> appreciate if
> you could please share that with me.
>
> The theretical limit of undo_retention is some 136 years!
>
> Thanks.
>
> Arup
>
>
> ----- Original Message -----
> From: "Indy Johal" <Indy.Johal_at_prnewswire.com>
> To: <oracle-l_at_freelists.org>
> Sent: Thursday, February 12, 2004 4:27 PM
> Subject: Re: Undo Retention of 5 days; anyone?
>
>
> > Arup
> > If I am correct, can we go beyond 5 days of Flashback recovery
> using
> > Timestamp method in Oracle 9i. I know somebody referred earlier in
> the
> > mail that he recover the data which is older than 15 days. I know
> that
> > some documentation says that it is not possible as timestamp
> mapping
> to
> > SCN is maintained in SMON_SCN_TIME for only 5 days. I am not having
> any
> > configuration to test it so thought I can check with you.
> >
> > Thanks
> >
> > Indy Johal
> > http://www.prnewswire.com
> >
> > (201) 946-5687 [W]
> > (201) 400-3960 [M]
> >
> > "We tell your story to the world."
> >
> >
> >
> >
> > "Arup Nanda" <orarup_at_hotmail.com>
> > Sent by: oracle-l-bounce_at_freelists.org
> > 02/12/2004 12:42 AM
> > Please respond to oracle-l
> >
> >
> > To: <oracle-l_at_freelists.org>
> > cc:
> > Subject: Undo Retention of 5 days; anyone?
> >
> >
> > List,
> > I have a very unusual request from users to have UNDO_RETENTION set
> to 5
> > days. Normally I set to about 5 hours, not days. Don't ask why.
> They
> have
> > inherited a bad design that requires flashing back to 5 days ago
> and, no,
> > they can't redesign it.
> >
> > I'm curious if anyone has actually done it, i.e setting it to a
> very
> high
> > value. Any input will be highly appreciated.
> >
> > Thanks a lot in advance.
> >
> > Arup
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> >
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------