Working with FLASHBACK in oracle Database

FLASHBACK in Oracle DATABASE:=============================  Flashback is nothing but going to previous state,same case with OracleDatabase also,here we go to a previous state of Database.Advantages and Applications of Flashback:--------------------------------------------1)Useful in recovering the lost data.2)Useful in saving time and system resources.2)Usefull in performing Data testing.Disadvantages:---------------1)Additional Overhead on Databaseprerequisites:---------------prerequisite 1: Configure FRA(Flash Recovery Area)    SQL> alter system set db_recovery_file_dest_size=1g;    System altered.    SQL> alter system set db_recovery_file_dest=’/oracle/test_flash’;    System altered.Note: If it is a RAC database the FRA should point to the shared storage.Ensure you estimate appropriately db_recovery_file_dest_size and have enough space. STORAGE_SIZE column from V$RESTORE_POINT can help you for that.prerequisite 2:The Oracle database should be in archivelogSQL> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     70Next log sequence to archive   73Current log sequence           73Note:Below steps can be use to switch Database to archive log mode.    [oracle@orclbox ~]$ sqlplus /nolog    SQL*Plus: Release 11.2.0.2.0 Production    Copyright (c) 1982, 2010, Oracle.  All rights reserved.    SQL> conn / as sysdba    Connected.    SQL> alter system set log_archive_dest_1=’location=/oracle/test_flash’;    System altered.    SQL> shutdown immediate    Database closed.    Database dismounted.    ORACLE instance shut down.    SQL> startup mount    ORACLE instance started.    Total System Global Area 1653518336 bytes    Fixed Size                  2227032 bytes    Variable Size            1275069608 bytes    Database Buffers          369098752 bytes    Redo Buffers                7122944 bytes    Database mounted.    SQL> alter database archivelog;    Database altered.    SQL> alter database open;    Database altered.    SQL> archive log list    Database log mode              Archive Mode    Automatic archival             Enabled    Archive destination            /oracle/test_flash    Oldest online log sequence     2    Next log sequence to archive   4    Current log sequence           4    SQL>prerequisite 2:On the Flashback in DatabaseSQL> alter database flashback on;Database altered.SQL> select log_mode,flashback_on from v$database;LOG_MODE     FLASHBACK_ON------------ ------------------ARCHIVELOG   YESNote:To off the flashback use the below statement.SQL> alter database flashback off;Database altered.SQL> select log_mode,flashback_on from v$database;LOG_MODE     FLASHBACK_ON------------ ------------------ARCHIVELOG   NOExample 1: Let us consider one eg.of restoring a table to a particular scn(system change number):Below i'm creating one 'test' user with some test data,than I will check the scn after committing the changes,than I will delete the data and finally I will restore that table using flashback scn technique.SQL> create user test identified by test;User created.SQL> grant connect,resource to test;Grant succeeded SQL> insert into test.flashback_test values(1);    1 row created.    SQL> insert into test.flashback_test values(2);    1 row created.    SQL> insert into test.flashback_test values(3);    1 row created.    SQL> commit;    SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)----------- ---------------------------------------------------------------------------     931892 28-JUL-12 09.49.40.000000000 AM    SQL> delete from test.flashback_test;    3 rows deleted.    SQL> commit;    Commit complete.    SQL> select * from test.flashback_test;    no rows selectedRestore of table flashback_test using flashback database:===================================================== step 1:Shut down the instances of DatabaseSQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut downStep 2:Bring the Database in mount stateSQL> startup mountORACLE instance started.Total System Global Area 1071333376 bytesFixed Size                  1341312 bytesVariable Size             620759168 bytesDatabase Buffers          444596224 bytesRedo Buffers                4636672 bytesDatabase mounted.Step 3:Use flashback database command to go to scn when table was dropped.If  you want to revert the database to SCN=930717 where flashback_test table has 3 rowsSQL> flashback database to scn 930717;Flashback complete.    SQL> alter database open resetlogs;--set log sequence no. and the database will be synchonized.Verify the Data:    SQL> select * from flashback_test;    COL1    ———-    1    2    3Example 2:Flashback to timestampLets say you have to drop some table on a particular day and the user know the time when he had dropped that table,than we can use the below steps to recover that table using flashback technique.flashshback to timestamp:======================step 1:Shut down the instances of DatabaseSQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut downStep 2:Bring the Database in mount stateSQL> startup mountORACLE instance started.Total System Global Area 1071333376 bytesFixed Size                  1341312 bytesVariable Size             620759168 bytesDatabase Buffers          444596224 bytesRedo Buffers                4636672 bytesDatabase mounted.step 3: flashback to timestamp 28-JUL-12 09.49.40SQL>flashback database to timestamp TO_TIMESTAMP('2012-07-28 09:49:40','YYYY-MM-DD HH24:MI:SS');    Flashback complete.    SQL> select * from test.flashback_test;    COL1    ———-    1    2    3Example 3: Enable recyclebin and get back the lost data.Step 1:Enable recycle bin when the Database is in open state.SQL>alter session enable recyclebin=on;Step 2:Recover lost table using below command.      SQL> FLASHBACK TABLE test.flashback_test TO BEFORE DROP;    Flashback complete.    SQL> select * from test.flashback_test;    COL1    ———-    1    2    3Example 4:Create guaranteed restore point and restore that restore point:========How we can restore the entire data which is lost in a particular period,this can be use to test data also. This is very vital feature of Oracle flashback.Create Restore point:=====================Step 1:Create restore point 'test_rest1':    SQL> create restore point test_rest1 guarantee flashback database;    Restore point created.    * To view the restore points:    SQL> select NAME,SCN,TIME,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE         from V$RESTORE_POINT         where GUARANTEE_FLASHBACK_DATABASE='YES';SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE         from V$RESTORE_POINT         where GUARANTEE_FLASHBACK_DATABASE='YES';  2    3NAME--------------------------------------------------------------------------------       SCN----------TIME                                                                        GUA--------------------------------------------------------------------------- ---STORAGE_SIZE------------TEST_REST1    93053728-JUL-12 08.57.51.000000000 AM                                             YES           0NAME--------------------------------------------------------------------------------       SCN----------TIME                                                                        GUA--------------------------------------------------------------------------- ---STORAGE_SIZE------------TEST_REST2    93066028-JUL-12 09.02.54.000000000 AM                                             YES    30203904NAME--------------------------------------------------------------------------------       SCN----------TIME                                                                        GUA--------------------------------------------------------------------------- ---STORAGE_SIZE------------TEST1    93254928-JUL-12 10.30.01.000000000 AM                                             YES     8192000Restore Restore point:=======================step 1:shut the databaseSQL> shut immediateORA-01031: insufficient privilegesSQL> conn /as sysdbaConnected.SQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut downstep 2:Startup in mount stateSQL> startup mountORACLE instance started.Total System Global Area 1071333376 bytesFixed Size                  1341312 bytesVariable Size             620759168 bytesDatabase Buffers          444596224 bytesRedo Buffers                4636672 bytesDatabase mounted.step 3:flashback Database to restore point 'test1':SQL> FLASHBACK database TO RESTORE POINT test1;Flashback completeNote:To restore a table below command can be used.FLASHBACK TABLE emp TO RESTORE POINT test1;step 4:Open the Database with resetlogsSQL> alter database open resetlogs;Database alteredstep 6:Verify the dataSQL> select *from test.flashback_test;      COL1----------         1         2         3Step 7:Monitor flashback  v$flashback_database_log    SQL> select oldest_flashback_scn from v$flashback_database_log;OLDEST_FLASHBACK_SCN--------------------              930314Note: Dropping restore point.SQL> DROP RESTORE POINT TEST_REST1;Restore point dropped.Note:Possible flashback options available are:SQL>flashback table test.flashback_test to (SYSDATE-1/24);-- be sure to substitute your SCNFLASHBACK DATABASE TO SCN 19513917;orFLASHBACK DATABASE TO RESTORE POINT bef_damage;/*FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';FLASHBACK DATABASETO TIMESTAMP to_timestamp('2002-11-11 16:00:00','YYYY-MM-DD HH24:MI:SS');*/Offcourse,we do have flashback query,flashback transaction query,but above are very useful.Hope it helps....Enjoy DBA tasks and practice...Best regards,Rafi.