PURPOSE
——-
This article introduces the new 9i Flashback feature which provides the ability
to issue a query and access data as it was at some point in the past. An example
of how cursors can be used to implement the flashback feature is also presented.
SCOPE & APPLICATION
——————-
This article is intended for DBAs who wish to view data at some point in the
past. This is particularly useful in cases where data may has been mistakenly
deleted or updated and it is not desirable or practical to perform a point-in-
time recovery to retrieve the lost data.
Oracle9i: Recovering lost data through Flashback
————————————————
An important usage of flashback queries is the ability to take prior data and
make it current data again. While flashback query doesn’t enable you to directly
bring that data forward into the present, cursors can be used to effectively
accomplish this.
Flashback queries are implemented using the same undo structures that the 9i
database already maintains to provide transactional support. Beginning with
Oracle9i, the database has the ability to automatically manage the undo segments
in a specified undo tablespace via Automatic Undo Management(AUM). Before using
Flashback Query, it is strongly recommended to set up the database to run with
AUM. This is because Flashback Query relies on the undo_retention period which is
only valid under AUM. This undo retention period specifies how long committed
transactions should be retained in the undo segments before being potentially
overwritten. This period then becomes the limit for how far you can “flashback”.
Please note that if you do not create a large enough undo tablespace, the undo
information can get overwritten before the undo retention period has elapsed.
For more information on setting up a database under AUM, please refer to
Note: 135090.1.
INITIAL SETUP:
————–
o Begin by creating an undo tablespace:
SQL> create undo tablespace UNDOTBS datafile
‘/database/901/V901/undotbs01.dbf’ size 100M;
o Once you create the undo tablespace, you need to setup the following
parameters in the init.ora or in the spfile to configure the instance to use
AUM, specify a retention time(in this case 1200 seconds) and use the undo
tablespace that was created:
undo_management=auto
undo_retention=1200
undo_tablespace=UNDOTBS
Note: Both UNDO_RETENTION and UNDO_TABLESPACE can be altered dynamically,
but UNDO_MANAGEMENT cannot. You will need to restart the database instance
to change its setting.
o In order to use the flashback query feature, the user must have the EXECUTE
privilege on the DBMS_FLASHBACK package.
SQL> connect / as sysdba
Connected.
SQL> grant execute on dbms_flashback to testuser;
Grant succeeded.
———————————–
EXAMPLE#1: Flashback to date/time:
———————————–
To issue a flashback query, you first need to call a procedure in the
DBMS_FLASHBACK package to go back in time to either a specific date/time value
or to a system change number(SCN). To specify a date/time value, use the
DBMS_FLASHBACK.ENABLE_AT_TIME procedure as shown in this example below:
o Create a table in the testuser schema which is a copy of the current data in
the scott.emp table(it is assumed that testuser has the necessary privileges
to access the emp table in scott’s schema):
SQL> connect testuser/testuser;
Connected.
SQL> create table emp_flash as select * from scott.emp;
Table created.
SQL> select count(*) from emp_flash;
COUNT(*)
———-
15
o Flashback to date/time – in this case, 5 minutes prior to the current time:
================================================================================
NOTE: Wait at least 5 minutes from the time the emp_flash table is created above
and this next step. This is due to the fact that flashback times can be rounded
down by as much as 5 minutes and this may result in the flashback time actually
being before the table was created. An ORA-01466 error will be received in this
case. See below under Additional Information for details.
================================================================================
SQL> delete from emp_flash;
15 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp_flash;
COUNT(*)
———-
0
SQL> execute DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate – 5/1440);
PL/SQL procedure successfully completed.
SQL> select count(*) from emp_flash;
COUNT(*)
———-
15
SQL> execute DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
SQL> select count(*) from emp_flash;
COUNT(*)
———-
0
The query executed between DBMS_FLASHBACK.ENABLE_AT_TIME and
DBMS_FLASHBACK.DISABLE returned data as of 5 minutes in the past. Notice
that after the DBMS_FLASHBACK.DISABLE command, the emp_flash table again
has no rows.
——————————————-
EXAMPLE#2: Flashback to Recover Lost Data:
——————————————-
While the technique used in EXAMPLE#1 allows you to look at data the way it was
in the past, any attempt to bring those deleted rows into the present using
INSERT or UPDATE statement will fail. You can only issue a SELECT statement when
you enable flashback query mode. DML statements are not allowed until you exit
flashback query mode by using the DBMS_FLASHBACK.DISABLE procedure.
This example will show a more useful usage of flashback queries, which is the
ability to take that prior data and make it current data again through the use
of a cursor. This goal is achieved by using cursors because of their ability to
work with both prior and current data at the same time. When you open a cursor
in flashback query mode, the contents of the cursor stay in that mode even after
a call to DBMS_FLASHBACK.DISABLE.
To bring prior data forward into the present, you need to enable flashback
query mode, open a cursor to return the data you want to work with from the past,
and then exit flashback query mode. The cursor continues to return data from
the past, while any DML statements you now issue affect the present.
o Create a table in the testuser schema:
SQL> connect testuser/testuser;
Connected.
SQL> create table emp_recover as select * from scott.emp;
Table created.
SQL> select count(*) from emp_recover;
COUNT(*)
———-
15
================================================================================
NOTE: As before, wait at least 5 minutes from the time the emp_flash table is
created above and this next step.
================================================================================
SQL> VARIABLE SCN_SAVE NUMBER;
SQL> EXECUTE :SCN_SAVE := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
PL/SQL procedure successfully completed.
SQL> PRINT SCN_SAVE
SCN_SAVE
———-
6.4455E+12
SQL> select count(*) from emp_recover;
COUNT(*)
———-
15
SQL> delete from emp_recover;
15 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp_recover;
COUNT(*)
———-
0
—————————————————————————
The following will bring the deleted rows back into the present:
—————————————————————————
SQL> DECLARE
2 CURSOR FLASH_RECOVER IS
3 select * from emp_recover;
4 emp_recover_rec emp_recover%ROWTYPE;
5 begin
6 DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:SCN_SAVE);
7 open FLASH_RECOVER;
8 DBMS_FLASHBACK.DISABLE;
9 loop
10 FETCH FLASH_RECOVER INTO emp_recover_rec;
11 EXIT WHEN FLASH_RECOVER%NOTFOUND;
12 insert into emp_recover
13 values
14 (emp_recover_rec.empno,
15 emp_recover_rec.ename,
16 emp_recover_rec.job,
17 emp_recover_rec.mgr,
18 emp_recover_rec.hiredate,
19 emp_recover_rec.sal,
20 emp_recover_rec.comm,
21 emp_recover_rec.deptno);
22 end loop;
23 CLOSE FLASH_RECOVER;
24 commit;
25 end;
26 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp_recover;
COUNT(*)
———-
15
Additional Information
———————-
o Flashback query results are always based on an SCN(System Change Number).
Oracle9i tracks SCNs at five-minute intervals and logs the information
for the last five days of operation. The SCN/time mapping is recorded every
5 minutes after database startup. The DBMS_FLASHBACK.ENABLE_AT_TIME
procedure uses this log to determine the SCN associated with the time you
specify and uses that SCN as the basis for any flashback queries you issue.
This introduces two limitations when using DBMS_FLASHBACK.ENABLE_AT_TIME.
The first is that if you need to go back more than five days, you will have
to manually determine and use an SCN since the mapping information is not
retained for more than 5 days. The second is that any time value you pass to
DBMS_FLASHBACK.ENABLE_AT_TIME is effectively rounded down to the nearest
five-minute increment. This may result in data from a time up to 5 minutes
earlier than you intend. If you must maintain precision, you should use an
SCN.
o The SYSDATE function always returns the current system date, even when used
in a flashback query session. This has ramifications for queries that use
SYSDATE. If you have a query that returns all transactions executed today,
and that query determines “today” using SYSDATE, unexpected results may occur
if you flashed back to yesterday. You’ll need to think through the
repercussions of using SYSDATE in any flashback queries that you execute.
o Similar to the SYSDATE function, the
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function always returns the current
SCN, regardless of whether you are in flashback query mode.
o Flashback query mode can be entered only at the beginning of a transaction.
If you’ve issued DML statements to change your data, then you must COMMIT
before you can flash back. In addition, you will not be able to flash back
to a point in time prior to the most recent change in a table’s structure.
That’s because flashback queries use current data dictionary data. Even a
simple change to a column datatype limits your ability to flash back.
Oracle9i New Feature: Flashback
Checked for relevance on 16-Dec-2008
PURPOSE
This bulletin provides an example of code and setup of the new Oracle9i
Flashback feature.
SCOPE & APPLICATION
This is intended for anyone willing to use Flashback for any user error
recovery.
Oracle9i Flashback Example:
===========================
Set up the database for Flashback Use
————————————-
1. Prerequisite: init.ora or spfile.ora parameter UNDO_MANAGEMENT = AUTO
SQL> show parameter undo
NAME TYPE VALUE
——————————– ———– ————————-
undo_management string MANUAL
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS
Enable Automatic Undo Management (AUM).
If any modification needs to be done, restart the database after switching
the init.ora or spfile.ora parameter UNDO_MANAGEMENT to AUTO.
Be aware you must provide an existing UNDO tablespace through the
UNDO_TABLESPACE parameter.
2. Set an UNDO retention period
SQL> ALTER SYSTEM SET undo_retention = 1200;
System altered.
This command sets 1200 seconds during which undo information is retained. All
committed undo information in the system is retained for at least 20 minutes.
This ensures that all queries running for 20 minutes or less do not receive
an ORA-1555 (snapshot too old) error, under normal circumstances.
Set up User and Tables
———————-
1. Create a FLASH user:
SQL> create user flash identified by gordon;
User created.
SQL> grant connect, resource to flash;
Grant succeeded.
SQL> grant execute on dbms_flashback to flash;
Grant succeeded.
SQL> connect flash/gordon
Connected.
SQL> set echo on
2. Create a user table and another table to keep the time when rows existed:
SQL> CREATE TABLE tst(t number(5));
Table created.
SQL> INSERT INTO tst VALUES(1);
1 row created.
SQL> INSERT INTO tst VALUES(2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE keep_date (date_scn_tracking date);
Table created.
SQL> SELECT * FROM tst;
T
———-
1
2
3. Delete a row
SQL> execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
SQL> INSERT INTO keep_date select sysdate from dual;
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DELETE FROM tst WHERE t = 1;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tst;
T
———-
2
Only 1 row is left in the table.
4. Enable Flashback
SQL> COMMIT;
Commit complete.
SQL> declare
2 restore_scn date;
3 begin
4 select date_scn_tracking into restore_scn from keep_date;
5 dbms_flashback.enable_at_time(restore_scn);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM tst;
T
———-
1
2
Flashback is able to display the 2 rows that existed in the past, before the
row deletion.
Please Note:
————
The ALTER SYSTEM SET undo_retention = 1200 urges Oracle to keep undo information
for 1200 seconds. However, when the database needs more undo space, it might
grab undo space in which non-expired information is stored which was committed
less than 1200 seconds ago. Oracle only overwrites non-expired committed undo
information when exhausting all free space of expired undo information and of
the undo tablespace.
So, there is no guarantee that the flashback query will succeed.
However, AUM space constraint management is developed such that a transaction
needing undo space to complete will be accommodated. This could lead to runaway
usage of undo space by uncontrolled transactions. To prevent this, a new
Resource Manager directive, UNDO_POOL, can be setup to limit the amount of undo
space consumed by a transaction.
Limitations of Flashback query:
——————————–
1. Specifying a time will only find the flashback copy to the nearest five
minute interval. This is also true of the get_system_change_number.
2. When using flashback time based and your database is running
continuously, you can never flashback more than 5 days,
irrespective of UNDO_RETENTION.
© 2005 – 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
最新评论