Recovery Made Simple: Oracle Flashback Query

Sometimes it is a rouge query, sometimes a simple data clean up effort by the users, whatever may the cause be, inadvertent data-loss is a very common phenomenon. Backup and recovery capabilities are provided by the database management systems which ensure the safety and protection of valuable enterprise data in case of data loss however, not all data-loss situations call for a complete and tedious recovery exercise from the backup. Oracle introduced flashback features in Oracle 9i and 10g to address simple data recovery needs.

Flashback query allows a user to view the data quickly and easily the way it was at a particular time in the past, even when it is modified and committed, be it a single row or the whole table.

This article is an introduction to the flashback feature of Oracle and discusses how to use this feature in Oracle 9i and 10g. Its main focus is the usage of flashback queries, not the mechanism that governs it.

What situations call for the use of the flashback feature? According to the Oracle documentation, Flashback technologies are applicable in repairing the following user errors.

• Erroneous or malicious DROP TABLE statements
• Erroneous or malicious update, delete or insert transactions
• Erroneous or malicious batch job or wide-spread application errors

Deleted data files or the loss of data due to media failure cannot be handled by flashback queries.

Configuration before using Flashback Queries

In order to use this feature, the database instance has to be configured.
Log on to the database where the test is to be performed and run the following command at the SQL prompts:

SQL> show parameter UNDO;

NAME                    TYPE            VALUE

----------------------- --------------  -----------     

undo_management         string      	AUTO

undo_retention          integer     	900

undo_suppress_errors    boolean     	TRUE

undo_tablespace         string      	UNDO_TBSPC

This command displays all the necessary parameters for using the Flashback Queries. The value for UNDO_RETENTION is set to 900 seconds in this example which is the default value and represents at least how long the system retains undo. UNDO_RETENTION and UNDO_TABLESPACE are dynamic parameters, but UNDO_MANAGEMENT is not, requiring shut down and re-start of database instance in order for automatic undo management to take effect.

In addition to the above your DBA will have to grant:

1. FLASHBACK privilege to the user for all or a subset of objects
2. Execute privileges on the dbms_flashback package

Creating the test tables:

For the examples in this article we are going to use the following two tables:

SQL> desc employee;

Name   	Type          

------- ------------    

EMP_ID  NUMBER                               

NAME    VARCHAR2 (30)                          

AGE     NUMBER

We will also create a temporary table - employee_TEMP - with exactly the same definition as the Employee table. This temporary table will be used as a container to store the recovered data. Even though we can recover the data to the original table, I prefer to use a separate table, which makes it easier to compare the recovered data to the data currently in the actual table.

For all our tests we will:

1. Have some records entered into the employee table.
   
2. Display the records using a simple select.
   
3. Delete or update some or all of the records from the employee table and commit the transaction.
   
4. Display the employee table data again to ensure that records are, in fact removed or altered.
   
5. Try to recover the lost data into the employee_temp table.

Now that that the system is configured, privileges are granted and test tables are created, we can test the flashback query feature.

There are two approaches to using the flashback queries. One is a time based approach and the other uses the SYSTEM CHANGE NUMBER - SCN - to identify the point we want to go back to. Each of these approaches employs the AS OF clause as well as an Oracle supplied package DBMS_FLASHBACK. Both are discussed here.

Using the Flashback Query with AS OF clause:

Suppose we want to recover data we have accidentally deleted for some of the employees from the EMPLOYEE table and have committed the transaction. Fig. 1 shows how we will use the AS OF clause for recovering data to a certain point in time, at which we know our data existed.

SQL> INSERT INTO EMPLOYEE_TEMP
    (SELECT * FROM EMPLOYEE AS OF TIMESTAMP ('13-SEP-04 8:50:58','DD-MON-YY HH24: MI: SS'

Figure 1 - Using the AS OF clause with time

Now if we ran a SELECT statement on EMPLOYEE_TEMP table, we will see all the lost data in this temporary table, which we can add to the actual employees table.

Using a point in time is way of going back, another way of telling the system how far to go back is the use of SCN - System Change Number. The procedure is the same as earlier, trying to recover lost data using the DBMS_FLASHBACK utility. Only this time instead of using the time we are using the system change number - SCN to enter the flashback mode. This SCN number can be obtained before the transaction is initiated by using the GET_SYSTEM_CHANGE_NUMBER function of the DBMS_FLASHBACK utility as follows.

SQL> select DBMS_FLASHBACK. GET_SYSTEM_CHANGE_NUMBER from dual;

You do not have to be in the flashback mode to run this statement.
As you might have already guessed, the AS OF clause is used with SCN number as shown in Figure 2.

SQL> INSERT INTO EMPLOYEE_TEMP
	(SELECT * FROM EMPLOYEE AS OF SCN 10280403339);

Figure 2 - Using the AS OF clause - with SCN

We have recovered the data again but this time using the SCN number.

Using the DBMS_FLASHBACK package:

Prior to Oracle 9i release 2, the only way to use the flashback query feature was through the use of the utility package DBMS_FLASHBACK. In order to use this method, the user had to specify the intention to enter the flashback mode by supplying the time to which the user wished to go back to. This was done by using the ENABLE_AT_TIME function of the DBMS_FLASHBACK package to enter the flashback mode, followed by a DISABLE function to resume normal operation. This is shown in figure 2.

1 DECLARE
2   CURSOR emp_cur IS
3    SELECT * FROM   EMPLOYEE;
4  v_rec emp_cur%rowtype;   
5 BEGIN
6	DBMS_FLASHBACK.ENABLE_AT_TIME ('13-SEP-04 08:10:58');
7	open emp_cur;
8	DBMS_FLASHBACK.DISABLE;
9    LOOP
10	fetch emp_cur into v_rec; 
11     EXIT WHEN emp_cur%NOTFOUND; 
12   INSERT INTO EMPLOYEE_TEMP VALUES
13    (v_rec.emp_id,
14     v_rec.name, 
15     v_rec.age ); 
16  END LOOP; 
17 close emp_cur;
18   COMMIT;
19 END;

Figure 3 - Using the DBMS_FLASHBACK package with time

Again using the similar procedure as before, we will recover data into out EMPLOYEE_TEMP table using the SCN number instead of time as shown in the Figure 2, by replacing line 6 with this line below and we are able to recover again.

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (10280403339);

Notice that we have entered into the flashback query mode in figure 2 line 6, DML statements INSERT, UPDATE, or DELETE are not allowed until we exit flashback query mode by issuing DBMS_FLASHBACK.DISABLE. Because of this limitation the cursor for loop cannot be used in using the DBMS_FLASHBACK if we are using any of the above DML statements inside the loop. Code with cursor for loops will run fine but will not go back to the point in time we wanted to go and instead will fetch the data that is currently in the table to be recovered rendering the whole exercise useless.
An addendum to the previous point is that FLASHBACK mode can be entered only at the beginning of a transaction. If a DML statement has been issued, it must be committed before we can enter the flashback mode.

It is worth mentioning that even when we use time instead of SCN number Oracle still maps that time to an SCN number stored in the SMON_SCN_TIME every 5 minutes by Oracle background process SMON.

Oracle 10G enhancements to Flashback:

Oracle 10G has enhanced the flashback feature further and has turned it into a much more powerful feature by introducing numerous additions. Some of the more common ones are discussed here.

• Flashback Table
• Flashback Drop
• Flashback Database
• Flashback Versions Query
• Flashback Transaction Query

The first three of these features are discussed here.

Flashback Table

Just like the flashback query helps retrieve rows of a table, FLASHBACK TABLE helps restore the state of a table to a certain point in time even if a table structure changed has occurred since then. The following simple command will take us to the table state at the specified timestamp.

SQL> FLASHBACK TABLE Employee TO 
           TIMESTAMP ('13-SEP-04 8:50:58','DD-MON-YY HH24: MI: SS');

Not only does this command restore the tables but also the associated objects like indexes, constraints etc.

Flashback Drop

So far we have recovered the lost data to a particular point-in-time back into a table that exists in the database. Oracle 10g has provided another useful feature term as the Flashback drop. For our example if a DROP TABLE has been issued for the table EMPLOYEE we can still restore the whole table by issuing the following command.

SQL> FLASHBACK TABLE EMPLOYEE TO BEFORE DROP;

Bringing back dropped tables could not be any easier than this.

RECYCLE BIN

It is worthwhile to take a little detour and familiarize ourselves with the feature in Oracle 10g that enabls us to flashback. Oracle has introduced the RECYCLE BIN which is a logical entity to hold all the deleted objects and works exaclty like the recylce bin provided in Windows operating system for example. All the deleted objects are kept n the recylce bin, these objects can be retrieved from the recycle bin or deleted permanently by using the PURGE command. Either an indivisual object like a table or an index can be deleted from the recycle bin:

SQL> PURGE TABLE Employee;

or the whole recylce bin can be 'emptied out' by using the PURGE command:

SQL> PURGE recyclebin;

If you take a look at the contents of the recycle bin using the following query,

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin;

OBJECT_NAME                   	 ORIGINAL_NAME   TYPE

------------------------------------------------------------------     

BIN$G/gHMigrTRqHQukZSIpSLw==$0 	 EMPOLYEE        TABLE

BIN$1UiHeUR7SymGHo20pTfGXA==$0	 EMPLOYEE        TABLE

BIN$6d6677f5T+K++npt+5p/jQ==$0	 EMP_IDX1        INDEX

you will notice that the tables are not saved under their original names, instead they are saved under their recycled names along with the column ORIGINAL_NAME that contans the actual names of the objects. When the table is recoverd form the recycle bin, the views and procedure using these tables that were rendered invalid at the time the table was dropped remain invalid. The actual names of these objects indexes, views etc. have to retrived from the recycle bin manually and applied to the table again. So for our example the table rerieved from the recycle bin would have an index named
BIN$6d6677f5T+K++npt+5p/jQ==$0v instead of EMP_IDX1 and the actual name have to be recoverd and applied from the recycle bin manually like this:

SQL> drop index BIN$6d6677f5T+K++npt+5p/jQ==$0;
SQL> create index EMP_IDX1 on EMPLOEE (EMPNO);

Flashback database

So far we have discussed the recovery of individual rows or individual objects. This logically leads to the discussion of recovering the whole database to a point in time. Knit tightly with the recovery manager - RMAN, the Flashback datbase feature provided in Oracle 10g, provides yet another way of easy and efficient, point-in-time recovery in case of data corruption or data loss. This is much faster than the traditional approach to point-in-time recovery since no redo logs are required when using this approach. About the Flashback database feature, Oracle documentation says the best: "Flashback Database is like a 'rewind button' for your database."

As is generall y the case with most softwares, there is a speed/space trade-off here as well. Flashback Database requires the creation and configurtion of an Oracle Flash Recovery Area before this feature can be used.

Flash Recovery Area created by the DBA, is the allocation of space on the disk to hold all the recovery related files in one, centralized place. Flash Recovery Area contains the Flashback Logs, Redo Archive logs, backups files by RMAN and copies of control files. The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters. Now when the setup is complete, let's see how the flashback database is used.

For this test suppose that a transaction ran that made significant changes ti the database, yet this is not what the user intended. Going back and retrieving individual objects and then recovernig and restoring the original data can be a very extensive, yet timeconsuming and error-prone exercise. It is time to use the FLASHBACK DATABASE.

First the flashback is enabled to make Oracle database enter the flashback mode. The database must be mounted Exclusive and not open. The database has to be in the ARCHIVELOG MODE before we can use this feature. This is shown as below.

SQL> ALTER DATABASE ARCHIVELOG;

Now startup the database in EXCLUSIVE mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT EXCLUSIVE

Now enter the flashback mode (the database should not be open at this time)

> ALTER DATABASE FLASHBACK ON;

Issue the flashback command and take the database to the state it was in, one hour ago.

SQL> Flashback database to timestamp sysdate-(1/24);

After the system comes back with FLASHBACK COMPLETE, open the database.

SQL> ALTER DATABASE OPEN RESETLOGS;

Now if you select from any of the tables that were affected, you will see that the affected tables are in the original state, i.e. an hour ago. And once again, we have the option of using SCN instead of timestamp.

As is evident from this section, that while other flashback features are available to the users of the database, the flashback database involves the DBA because of the system level activities that have to be performed. Nevertheless, the whole exercise is much simpler and easier than the traditional point-in-time recovery.

Conclusion:

Flashback query is a powerful and useful feature introduced in Oracle 9i, and enhanced greatly in Oracle 10g, that can help us recover data, lost or corrupted, due to human error. One big advantages of using flashback over point-in-time recovery is that for the latter not only transactions from the time of error to the current time would be lost but also the system will be unavailable for the duration of the recovery. For flashback query, on the other hand, there will be no down time needed and repair or recovery is less labor and time intensive than what it used to be in earlier versions of Oracle. With the new features like Recycle Bin, Flashback databases and Flashback Drop in Oracle 10g, the flashback capability introduced in 9i has been improved tremendously now turning a small feature into a powerful tool in the new Oracle releases.

Flashback is an insurance feature. Just like having car insurance does not mean that we can be careless on the road, FLASHBACK too, should be considered another tool in the belt, rather than a luxury that allows us to be careless about the data simply because we have the ability to recover it easily.