Sunday, July 29, 2012

Working with FLASHBACK in oracle Database




FLASHBACK in Oracle DATABASE:
=============================

  Flashback is nothing but going to previous state,same case with Oracle
Database 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 Database


prerequisites:
---------------

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 archivelog

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     70
Next log sequence to archive   73
Current log sequence           73

Note: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 Database

SQL> alter database flashback on;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

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

ARCHIVELOG   YES

Note: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   NO



Example 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 selected


Restore of table flashback_test using flashback database:
=====================================================

 step 1:Shut down the instances of Database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database 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 rows

SQL> 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
    3

Example 2:Flashback to timestamp

Lets 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 Database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.


step 3: flashback to timestamp 28-JUL-12 09.49.40

SQL>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
    3

Example 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
    3

Example 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    3

NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST1
    930537
28-JUL-12 08.57.51.000000000 AM                                             YES
           0


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST2
    930660
28-JUL-12 09.02.54.000000000 AM                                             YES
    30203904


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST1
    932549
28-JUL-12 10.30.01.000000000 AM                                             YES
     8192000

Restore Restore point:
=======================

step 1:shut the database

SQL> shut immediate
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

step 2:Startup in mount state

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.


step 3:flashback Database to restore point 'test1':

SQL> FLASHBACK database TO RESTORE POINT test1;

Flashback complete

Note:To restore a table below command can be used.
FLASHBACK TABLE emp TO RESTORE POINT test1;


step 4:Open the Database with resetlogs

SQL> alter database open resetlogs;

Database altered

step 6:Verify the data

SQL> select *from test.flashback_test;

      COL1
----------
         1
         2
         3

Step 7:Monitor flashback  v$flashback_database_log

    SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
              930314

Note: 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 SCN
FLASHBACK DATABASE TO SCN 19513917;
or
FLASHBACK 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 DATABASE
TO 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.













1 comment: