Flashback Query

From Oracle Ver. 9i Oracle has introduced Flashback Query feature.  It is useful to recover from accidental statement failures. For example, suppose a user accidently deletes rows from a table and commits it also then, using flash back query he can get back the rows.

Flashback feature depends upon on how much undo retention time you have specified. If you have set the UNDO_RETENTION parameter to 2 hours then, Oracle will not overwrite the data in undo tablespace even after committing until 2 Hours have passed. Users can recover from their mistakes made since last 2 hours only.

For example, suppose John gives a delete statement at 10 AM and commits it. After 1 hour he realizes that delete statement is mistakenly performed. Now he can give a flashback  AS.. OF query to get back the deleted rows like this.

Flashback Query

SQL>select * from emp as of timestamp sysdate-1/24;

Or

SQL> SELECT * FROM emp AS OF TIMESTAMP 
      TO_TIMESTAMP('2007-06-07 10:00:00', 'YYYY-MM-DD HH:MI:SS')

To insert the accidently deleted rows again in the table he can type

SQL> insert into emp (select * from emp as of timestamp sysdate-1/24)

Using Flashback Version Query

You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.

The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version. The pseudocolumns available are

VERSIONS_XID                  : Identifier of the transaction that created the row version
VERSIONS_OPERATION  : Operation Performed. I for Insert, U for Update, D for Delete
VERSIONS_STARTSCN    : Starting System Change Number when the row version was created
VERSIONS_STARTTIME  :Starting System Change Time when the row version was created
VERSIONS_ENDSCN        :SCN when the row version expired.
VERSIONS_ENDTIME      :Timestamp when the row version expired

To understand let’s see the following example

Before Starting this example let’s us collect the Timestamp

SQL> select to_char(SYSTIMESTAMP,’YYYY-MM-DD HH:MI:SS’) from dual;

TO_CHAR(SYSTIMESTAMP,’YYYYY
---------------------------
2007-06-19 20:30:43

Suppose a user creates a emp table and inserts a row into it and commits the row.

SQL> Create table emp (empno number(5),name varchar2(20),sal  
                                number(10,2));

SQL> insert into emp values (101,’Sami’,5000);
SQL>commit;

At this time emp table has one version of one row.

Now a user sitting at another machine erroneously changes the Salary from 5000 to 2000 using Update statement

SQL> update emp set sal=sal-3000 where empno=101;
SQL> commit;

Subsequently, a  new transaction updates the name of the employee from Sami to Smith.

SQL>update emp set name=’Smith’ where empno=101;
SQL> commit;

At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 101. The query uses Flashback Version Query pseudocolumns

SQL> Connect / as sysdba
SQL> column versions_starttime format a16
SQL> column versions_endtime format a16
SQL> set linesize 120;

SQL> select versions_xid,versions_starttime,versions_endtime,
   versions_operation,empno,name,sal from emp versions between
   timestamp to_timestamp(‘2007-06-19 20:30:00’,’yyyy-mm-dd hh:mi:ss’)
    and to_timestamp(‘2007-06-19 21:00:00’,’yyyy-mm-dd hh:mi:ss’);

VERSION_XID  V STARTSCN  ENDSCN  EMPNO NAME     SAL
-----------  - --------  ------  ----- -------- ----
0200100020D  U 11323            101   SMITH     2000      
02001003C02  U 11345             101   SAMI      2000
0002302C03A  I 12320             101   SAMI      5000

The Output should be read from bottom to top, from the output we can see that an Insert has taken place and then  erroneous update has taken place and then again update has taken place to change the name.

The DBA identifies the transaction 02001003C02 as erroneous and issues the following query to get the SQL command to undo the change

SQL> select operation,logon_user,undo_sql
      from flashback_transaction_query
           where xid=HEXTORAW(’02001003C02’);

OPERATION  LOGON_USER UNDO_SQL
---------  ---------- ---------------------------------------

U           SCOTT       update emp set sal=5000 where ROWID =  
                                                   'AAAKD2AABAAAJ29AAA'

Now DBA can execute the command to undo the changes made by the user

SQL> update emp set sal=5000 where ROWID ='AAAKD2AABAAAJ29AAA'

1 row updated

Using Flashback Table to return Table to Past States.

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations.

Flashback Table uses information in the undo tablespace to restore the table. Therefore, UNDO_RETENTION parameter is significant in Flashing Back Tables to a past state. You can only flash back tables up to the retention time you specified.

Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

ALTER TABLE table ENABLE ROW MOVEMENT;

The following example performs a FLASHBACK TABLE operation the table emp

FLASHBACK TABLE emp TO TIMESTAMP

     TO_TIMESTAMP('2007-06-19 09:30:00', `YYYY-MM-DD HH24:MI:SS');

The emp table is restored to its state when the database was at the time specified by the timestamp.

Example:-
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP

      TO_TIMESTAMP('2007-06-21 14:00:00','YYYY-MM-DD HH:MI:SS')

      ENABLE TRIGGERS;

You have to give ENABLE TRIGGERS option otherwise, by default all database triggers on the table will be disabled.

Recovering Drop Tables (Undo Drop Table)

In Oracle Ver. 10g  Oracle introduced the concept of Recycle Bin i.e. whatever tables you drop the database does not immediately remove the space used by table. Instead, the table is renamed and placed in Recycle Bin. The FLASHBACK TABLE…BEFORE DROP command will restore the table.

This feature is not dependent on UNDO TABLESPACE so UNDO_RETENTION parameter has no impact on this feature.

For Example, suppose a user accidently drops emp table

SQL>drop table emp;

Table Dropped

Now for user it appears that table is dropped but it is actually renamed and placed in Recycle Bin. To recover this dropped table a user can type the command

SQL> Flashback table emp to before drop;

You can also restore the dropped table by giving it a different name like this

SQL> Flashback table emp to before drop rename to emp2;

Purging Objects from Recycle Bin

If you want to recover the space used by a dropped table give the following command

SQL> purge table emp;

If you want  to purge objects of  logon user  give the following command

SQL> purge recycle bin;

If you want to recover space for dropped object of a particular tablespace give the command

SQL> purge tablespace hr;

You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:

SQL>PURGE TABLESPACE hr USER scott;

If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:

SQL>PURGE DBA_RECYCLEBIN;

To view the contents of Recycle Bin give the following command

SQL> show recycle bin;

Permanently Dropping Tables
If you want to permanently drop tables without putting it into Recycle Bin drop tables with purge command like this

SQL> drop table emp purge;

This will drop the table permanently and it cannot be restored.

Flashback Drop of Multiple Objects With the Same Original Name

You can create, and then drop, several objects with the same original name, and they will all be stored in the recycle bin. For example, consider these SQL statements:

CREATE TABLE EMP ( ...columns ); # EMP version 1

DROP TABLE EMP;

CREATE TABLE EMP ( ...columns ); # EMP version 2

DROP TABLE EMP;

CREATE TABLE EMP ( ...columns ); # EMP version 3

DROP TABLE EMP;

In such a case, each table EMP is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in this example:

FLASHBACK TABLE EMP TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name. You can retrieve it and assign it a new name using a RENAME TO clause. The following example shows the retrieval from the recycle bin of all three dropped EMP tables from the previous example, with each assigned a new name:

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_3;

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_2;

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_1;

Important Points:

1.             There is no guarantee that objects will remain in Recycle Bin. Oracle might empty recycle bin whenever Space Pressure occurs i.e. whenever tablespace becomes full and transaction requires new extents then, oracle will delete objects from recycle bin

2.             A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.

3.     There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.

Flashback Database: Alternative to Point-In-Time Recovery

Oracle Flashback Database, lets you quickly recover the entire database from logical data corruptions or user errors.

To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.

Once you set these parameters, From that time on, at regular intervals, the database copies images of each altered block in every datafile into flashback logs stored in the flash recovery area. These Flashback logs are use to flashback database to a point in time.

Enabling Flash Back Database

Step 1. Shutdown the database if it is already running and set the following parameters

           DB_RECOVERY_FILE_DEST=/d01/ica/flasharea
           DB_RECOVERY_FILE_DEST_SIZE=10G
           DB_FLASHBACK_RETENTION_TARGET=4320

(Note: the db_flashback_retention_target is specified in minutes here we have specified  3 days i.e. 3x24x60=4320)

Step 2. Start the instance and mount the Database.

            SQL>startup mount;

Step 3. Now enable the flashback database by giving the following command

            SQL>alter database flashback on;

Now Oracle start writing Flashback logs to recovery area.

To how much size we should set the flash recovery area.

After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

This will show how much size the recovery area should be set to.

 How far you can flashback database.

To determine the earliest SCN and earliest Time you can Flashback your database,  give the following query:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME

      FROM V$FLASHBACK_DATABASE_LOG;

Example: Flashing Back Database to a point in time

Suppose, a user erroneously drops a schema at 10:00AM. You as a DBA came to know of this at 5PM. Now since you have configured the flashback area and set up the flashback retention time to 3 Days, you can flashback the database to 9:50AM by following the given procedure

1.             Start RMAN

$rman target /

2.             Run the FLASHBACK DATABASE command to return the database to  9:59AM by typing the following command

RMAN> FLASHBACK DATABASE TO TIME timestamp('2007-06-21 09:59:00');

or, you can also type this command.

RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-8/24);

3. When the Flashback Database operation completes, you can evaluate the results by opening the database read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.

        RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

               At this time, you have several options

              

               Option 1:-

   If you are content with your result you can open the database by performing ALTER 
  DATABASE OPEN RESETLOGS

       SQL>ALTER DATABASE OPEN RESETLOGS;

               Option 2:-

               If you discover that you have chosen the wrong target time for your Flashback Database operation, you can use RECOVER DATABASE UNTIL to bring the database forward, or perform FLASHBACK DATABASE again with an SCN further in the past. You can completely undo the effects of your flashback operation by performing complete recovery of the database:

       RMAN> RECOVER DATABASE;

               Option 3:-

               If you only want to retrieve some lost data from the past time, you can open the database read-only, then perform a logical export of the data using an Oracle export utility, then run RECOVER DATABASE to return the database to the present time and re-import the data using the Oracle import utility

4.             Since in our example only a schema is dropped and the rest of database is good, third option is relevant for us.

Now, come out of  RMAN and run EXPORT utility to export the whole schema

$exp  userid=system/manager file=scott.dmp  owner=SCOTT

5.             Now Start RMAN and recover database to the present time

$rman target /

RMAN> RECOVER DATABASE;

6.             After database is recovered shutdown and restart the database in normal mode and import the schema by running IMPORTutility

$imp userid=system/manager file=scott.dmp