RE: {9i New Feature: Query Flashback }: This one is long

[Stephen Andert]

If he is preparing a presentation, it is definitly one I won't want to miss.   Stephen Andert >>> [EMAIL PROTECTED] 10/23/01 02:40AM >>> I think Joe's just doing it to be helpful and as a way of learning the 9i stuff... he has to play with it to be able to write about it --- Larry Elkins <[EMAIL PROTECTED]> wrote: > As always Joe, we appreciate these updates. And if it is never > ending, > that's fine by me. The way you have taken care to write up the > features, > issues, and caveats makes me think this series is destined for a > presentation? > > Technet also has a series going on regarding 9i features. It is > fairly > useful from a high level standpoint. Of course, it is Oracle's side > of the > story. A bit different from Joe's going through the features and > giving the > "real story" and experiences. > > Regards, > > Larry G. Elkins > The Elkins Organization Inc. > [EMAIL PROTECTED] > 214.954.1781 > -----Original Message----- > Sent: Monday, October 22, 2001 12:05 PM > To: Multiple recipients of list ORACLE-L > > > Query Flashback > > This is part 3 of a what will seem to be a never-ending series on new > 9i > features. :) > > This topic follows up on last weeks on Automated Undo > Management(which is a > requirement for Query Flashback). > > ---------------------------------------------------------------------------- > ---- > > What is Query Flashback? > > Flashback Query lets you view and repair historical data. It offers > the > ability to perform queries on the database as of a certain wall clock > time(look under the limitations section about this)  or > user-specified > system change number (SCN). Once the errors are identified, undoing > the > updates is a straightforward process that can be done without > intervention > from the database administrator. More importantly, the restoration > can be > achieved with no database downtime. > > ---------------------------------------------------------------------------- > ---- > > Setting Up the Database for Flashback Query > > Use automatic undo management to maintain read consistency, rather > than the > older technique using rollback segments. > > You MUST HAVE an undo tablespace to make this work.  Now did I try it > with > Rollback segments, nope, but based on what I've read it would make no > sense > to even try it as we all know that RBS get reused. > > Set the UNDO_RETENTION init.ora parameter to a value that represents > how far > in the past you might want to query(it is in seconds). If you only > need to > recover data immediately after a mistaken change is committed, the > parameter > can be set to a small value. If you need to recover deleted data from > days > before, you might need to say 86400 * number of days(since 60 * 60 > *24 = > 86400). > > Now keep in mind, if you tell Oracle to keep like one days worth of > undo, > you set the UNDO_RETENTION to 86400 and there is not enough free > space in > the tablespace to keep that much, then Oracle will ignore that keep > time and > start reusing the oldest undo. > > Grant EXECUTE privilege on the DBMS_FLASHBACK package to whoever > needs it. > > ---------------------------------------------------------------------------- > ---- > > Potential applications of flashback query are: > > Recovering lost data or undoing incorrect changes, even after the > changes > are committed. For example, a user who deletes or updates rows and > then > commits can immediately repair a mistake. > > Comparing current data against the data at some time in the past. For > example, you might run a weekly report that shows the change from > last week, > rather than just the current aggregate data. > > Checking the state of transactional data at a particular time. For > example, > you might want to verify an account balance on a certain day. > > ---------------------------------------------------------------------------- > ---- > > Important notes about query flashback > > Flashback Query does NOT undo anything. > > Flashback Query does NOT tell you what changed thats what LogMiner > does(thats coming up in a few weeks). > > Flashback Query can be used to undo changes and can be very efficient > if you > know the rows that need to be moved back in time. > > Flashback Query does not work through DDL operations that modify > columns, or > drop or truncate tables. > > > ---------------------------------------------------------------------------- > ---- > > Limitations of Flashback Query > > Some DDLs that alter the structure of a table, such as drop/modify > column, > move table, drop partition, truncate table/partition, and so on, > invalidate > the old undo data for the table. It is not possible to retrieve a > snapshot > of data from a point earlier than the time such DDLs were executed. > An > attempt to perform such a query will result in a ORA-1466(unable to > read > data, tbl definition has changed) error. This restriction does not > apply to > DDL operations that alter the storage attributes of a table, such as > PCTFREE, INITTRANS, MAXTRANS, and so on. Operations such as adding > new > extents, constraints or partitions are also exempted from this > restriction. > > ***************************************** IMPORTANT > *********************************************** > > The time specified in DBMS_RESUMABLE.ENABLE_AT_TIME is mapped to an > SCN > value. Currently, the SCN-time mapping is recorded every 5 minutes > after > database startup. Thus it might appear as if the specified time is > being > rounded down by up to 5 minutes. > > For example, assume that the SCN values 1000 and 1005 are mapped to > the > times 8:41 and 8:46 AM respectively. A flashback query for a time > anywhere > between 8:41:00 and 8:45:59 AM is mapped to SCN 1000; a flashback > query for > 8:45 AM is mapped to SCN 1005. > > Due to this time-to-SCN mapping, a flashback query for a time > immediately > after creation of a table may result in an ORA-1466 error. An > SCN-based > flashback query therefore gives you a more precise way to retrieve a > past > snapshot of data. > > Because SCNs are only recorded every 5 minutes for use by flashback > queries, > you might specify a time or SCN that is slightly after a DDL > operation, but > the database might use a slightly earlier SCN that is before the DDL > operation. So the previous restriction might also apply if you try to > perform flashback queries to a point just after a DDL operation. > > ***************************************** IMPORTANT > *********************************************** > > Currently, the flashback query feature keeps track of times up to a > maximum > of 5 days. This period reflects server uptime, not wall-clock time. > For > example, if the server is down for a day during this period, then you > can > specify as far back as 6 days. To query data farther back than this, > you > must specify an SCN rather than a date and time. You must record the > SCN > === message truncated === __________________________________________________ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael   INET: [EMAIL PROTECTED] Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing).