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 yourself at the time of
interest, such as before doing a DELETE.
You must disable flashback before enabling it again for a different time.
You cannot nest ENABLE /DISABLE pairs.
Only the state of table data is affected by a flashback query. During a
query, the current state of the data dictionary is used.
You cannot perform a flashback query on a remote table through a database
link.
Well you've read all of the propaganda, lets see what it looks like in
real life, I've stolen the example from the oracle docs since its an easy one to
follow, more than likely if you end up doing his it will be alot more
complicated. This script will be in a format that you should just be able
to cut/paste in sqlplus and run it.
------------------------ BEGINNING OF SCRIPT
--First off, let's make sure we're not already in flashback mode
execute dbms_flashback.disable;
-- we'll drop a few tables, since we want a clean setup
drop table employee;drop table keep_scn;
-- create those tables.-- notice the self referential integrity for
employee <-> mgr
create table keep_scn (scn number);
create table employee (employee_no number(5) primary
key,employee_name varchar2(20),employee_mgr
number(5)constraint mgr_fkey references employee on delete
cascade,salary
number,hiredate date);
-- populate some data
insert into employee values (1, 'John Doe', null, 1000000,
'5-jul-81');insert into employee values (10, 'Joe Johnson', 1, 500000,
'12-aug-84');insert into employee values (20, 'Susie Tiger', 10, 250000,
'13-dec-90');insert into employee values (100, 'Scott Tiger', 20, 200000,
'3-feb-86');insert into employee values (200, 'Charles Smith', 100, 150000,
'22-mar-88');insert into employee values (210, 'Jane Johnson', 100, 100000,
'11-apr-87');insert into employee values (220, 'Nancy Doe', 100, 100000,
'18-sep-93');insert into employee values (300, 'Gary Smith', 210, 75000,
'4-nov-96');insert into employee values (310, 'Bob Smith', 210, 65000,
'3-may-95');commit;
-- anonymous pl/sql block to get the SCN we are playing with,
-- we want to do this BEFORE the delete.declareI
number;beginI := dbms_flashback.get_system_change_number;insert into
keep_scn values (I); end;/
-- lets delete some data, notice in the data that scott supervises 3
employees
delete from employee where employee_name = 'Scott Tiger';commit;
-- notice that all of scott's employees are gone select lpad(' ',
2*(level-1)) || employee_name Namefrom employeeconnect by prior
employee_no = employee_mgrstart with employee_no = 1order by
level;
-- lets enable the query flashback mode and flashback to that --
SCN we stored before the delete
declare
restore_scn number;
begin
select scn into restore_scn from
keep_scn;dbms_flashback.enable_at_system_change_number (restore_scn);
end;/
-- lets see what the data looked like before the delete
select * from employee;
-- disable the query flashback
execute dbms_flashback.disable;
-- END OF SCRIPT
Now before anyone sends me hate email about getting an ORA-1466 error,
take a note of:
SCNs are only recorded every 5 minutes for use by flashback queries.
This scripts was written that way to make sure you were reading
it. The chance your script runs longer than 5 minutes is slim to
none. So how do you handle it so the script run successfully?
Create the table(s), go to lunch then run the rest of the script and it
should run fine.
Now as an exercise for you to think about, before you called the
disable procedure of dbms_flashback, how could you have saved the data to
restore back into the employee table?
This concludes this week's 9i topic, feel free to send an email about
(dis)likes to 9i_at_oracle-dba.com
Thanks for the nice comments and requests for changes, i'll try to get
through them all this evening.
Joe
Received on Mon Oct 22 2001 - 10:49:26 CDT
