If he is preparing a presentation, it is definitly one I won't
want to miss.
Stephen Andert>>>
wisernet100_at_yahoo.com 10/23/01 02:40AM >>>I think Joe's just doing
it to be helpfuland as a way of learning the 9i stuff... he has to play
with it to beable 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.<A
href="">
http://personals.yahoo.com--
Please see the official ORACLE-L FAQ: <A
href="">
http://www.orafaq.com-- Author: Rachel
Carmichael INET: wisernet100_at_yahoo.comFat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
Received on Tue Oct 23 2001 - 12:34:57 CDT
