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 <elkinsl_at_flash.net> 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.
> elkinsl_at_flash.net
> 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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Tue Oct 23 2001 - 03:41:29 CDT
