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
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.
declare
I number;
begin
I := 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 Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no = 1
order 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 [EMAIL PROTECTED]
Thanks for the nice comments and requests for changes, i'll try to get
through them all this evening.
Joe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
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).
