Re: Undo Retention of 5 days; anyone?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 13 Feb 2004 17:20:43 -0000
Message-ID: <013a01c3f255$b64c6df0$6702a8c0@Primary>

It's been a bit of time since I played with it, but I think SMON simply updates the oldest row in smon_scn_time every five minutes. So if you dare to insert more rows into the table, you can flashback further.

Also, when I first tested it (9.0.1), even a flashback to SCN would use the table to find the closest time for some reason, and then flash back to the SCN for that time ! This could test could be bypassed by
setting event 10311 - so perhaps that would be sufficient to break the 5 day barrier.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "Indy Johal" <Indy.Johal_at_prnewswire.com> To: <oracle-l_at_freelists.org> Cc: <oracle-l_at_freelists.org>; <oracle-l-bounce_at_freelists.org> Sent: Friday, February 13, 2004 4:03 PM Subject: Re: Undo Retention of 5 days; anyone?

Arup
Here is small cut out from one of the Oracle Openworld presentation title" ORACLE DATABASE 10 g TIME NAVIGATION: HUMAN ERROR CORRECTION" by Tammy Bednar from Oracle

"New in Oracle Database10g is the ability to query data in the past for more than 5 days if the UNDO_RETENTION is set for greater then 5 days. Oracle will maintain the undo for that period of time as long as the Undo Tablespace datafiles are allocated enough disk space"

Now I am surprised with the result mentioned by one of the expert in earlier email in the list that he tested it with more than 5 days. The only exception I can count is
-->either if the database is down for few days and then in that case
SMON_SCN_TIME can have timestamp for more than 5 days OR
--> If the SCN flashback method is used and not the Timestamp.

Also the Timestamp Mapping to SMON_SCN_TIME are been referred at lot of places like here is one of the presentation link http://www.aoug.at/Files/doag2003_x.pdf

Also this is been mentioned by Kirti in one of his presentaion slide named " Back to th past with Oracle 9i Flashback query"

Thanks

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."

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
-----------------------------------------------------------------

Received on Fri Feb 13 2004 - 11:20:43 CST