Using Flashback in Oracle 9i

January 17, 2003

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.