Using Flashback in Oracle 9i

Ajay Gursahani

Oracle
9i’s Flashback feature, DBMS_FLASHBACK, provides the user with the ability to view data in a database “as it was,” at a specified point in the past, even though committed
transactions have since been applied.

The
subprograms available under DBMS_FLASHBACK package are listed below:

ENABLE_AT_TIME

ENABLE_AT_TIME
is used to ‘flashback’ to a specified time for which to view the database.
Flashback is enabled for the entire session.

DBMS_FLASHBACK.ENABLE_AT_TIME (query_time IN TIMESTAMP);

query_time

This is an input parameter, which accepts TIMESTAMP as an input value.

Example:

execute dbms_flashback.enable_at_time(‘2002-12-10 10:00:00’);

or you can use the TO_TIMESTAMP function:

execute dbms_flashback.enable_at_time(TO_TIMESTAMP
(’15-03-2002 22:00:00′,’DD-MM-YYYY HH24:MI:SS’));

In
the example above, we are setting the time to 15^th March 2002 10:00PM

ENABLE_AT_SYSTEM_CHANGE_NUMBER

The
ENABLE_AT_SYSTEM_CHANGE_NUMBER is used to
‘flashback’ to a point, specified by SCN (System Change Number), from which you
want to view the database. It enables Flashback for the entire session.

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (query_scn IN NUMBER);

query_scn

query_scn
is an input parameter that accepts a numeric value in the form of SCN (System
Change Number). SCN is generated every time a transaction is committed.

Example:

execute dbms_flashback.enable_at_system_change_number(100);

To
view from the 100^th transaction and on of a database, specify SCN of
100.

GET_SYSTEM_CHANGE_NUMBER

The
GET_SYSTEM_CHANGE_NUMBER function returns the current SCN. The datatype is an
Oracle NUMBER type; once obtained, the SCN can be stored for later use. This is
very useful if we want to take snapshots.

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER( RETURN NUMBER);

DISABLE

This
procedure disables the Flashback mode for the entire session.

DBMS_FLASHBACK.DISABLE;

Example:

EXECUTE dbms_flashback.enable_at_time(TO_TIMESTAMP
(’12-DEC-2002 10:00:00′,’DD-MON-YYYY HH24:MI:SS’));
SELECT * FROM emp WHERE empno=5678;
EXECUTE dbms_flashback.disable;

In this example, the flashback session has been
enabled from 12^th
Dec 2002
at 10:00AM.

Next, we query emp table and later disable
flashback for the entire session.

Please note:

1. Use the Flashback option only if Oracle Server is configured to use Automatic Undo Management. Set UNDO_MANAGEMENT parameter in init.ora to AUTO
2. We have to define a parameter in init.ora file, which will set UNDO_RETENTION period (in seconds). The default period is 900 seconds.
3. Use READ-ONLY operations only when the Flashback is enabled, i.e. DML or DDL statements cannot be executed.
4. FLASHBACK time specified by ENABLE_AT_TIME procedure should be less than 5 minutes from the current system time.
5. FLASHBACK cannot be used in the middle of a transaction
6. An UNDO TABLESPACE must be created when using Automatic Undo Management mode.

A typical example of the use of Flashback
would be if we have an ITEM_MASTER table and all the records are accidentally
deleted around 4:00PM. We can FLASHBACK to 3:50pm, a few minutes before
DELETE took place, and populate ITEM_MASTER table.

Example I:

DECLARE
CURSOR cur_item_mst is
	SELECT * FROM item_master;
		v_row_item_mst item_master%ROWTYPE;
BEGIN
	Dbms_flashback.enable_at_time(TO_TIMESTAMP
	(’20-DEC-2002 15:50:00′,’DD-MON-YYYY HH24:MI:SS’));
	Open cur_item_mst;
	Dbms_flashback.disable;
	Loop
		Fetch cur_item_mst INTO v_row_item_mst;
		Exit when cur_item_mst%NOTFOUND;
		INSERT INTO item_master VALUES (
			v_row_item_mst.itemno,
			v_row_item_mst.description,
			v_row_item_mst.rate);
	END LOOP;
	CLOSE cur_item_mst;
	COMMIT;
END;
/

Example II:

SELECT to_char(sysdate,’DD-MON-YY HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’D
——————
26-DEC-02 10:00:00
SQL> SELECT * FROM test;
UNIQUE_ID      	NAME        	SALARY
————— ————— ———–
1		ANDY		4500.00
2		ALAN		3500.00
3		JACK		3600.00
4		PETER		4000.00
5		JOE		2900.00
5 rows selected

After approx 20 minutes…

SELECT to_char(sysdate,’DD-MON-YY HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’D
——————
26-DEC-02 10:20:00
SQL> SELECT * FROM test;
UNIQUE_ID      	NAME        	SALARY
————— ————— ———–
1		ANDY		4500
2		ALAN		3500
3		JACK		3600
4		PETER		4000
5		JOE		2900
5 rows selected
SQL> UPDATE test SET SALARY=10000;
5 rows updated
SQL> SELECT * FROM test;
UNIQUE_ID      	NAME        	SALARY
————— ————— ———–
1		ANDY		10000
2		ALAN		10000
3		JACK		10000
4		PETER		10000
5		JOE		10000
5 rows selected

Now if we FLASHBACK to 10:10:00 we will get the data as
it was prior to the UPDATE statement:

SQL>execute dbms_flashback.enable_at_time(TO_TIMESTAMP
(’26-DEC-02 10:10:00′,’DD-MON-YY HH24:MI:SS’));
PL/SQL procedure successfully completed
SQL> SELECT * FROM test;
UNIQUE_ID      	NAME        	SALARY
————— ————— ———–
1		ANDY		4500
2		ALAN		3500
3		JACK		3600
4		PETER		4000
5		JOE		2900
5 rows selected

This article has discussed what FLASHBACK is, and its uses–viewing past data or recovering lost data.