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.
