Flashback Features - 10g

To Find flash recovery area location:-

SQL> show parameter db_recovery_file_dest;

To View the Size of flash recovery area

SQL> show parameter db_recovery_file_dest_size;

View information on Flash recovery area:-

SQL> select * fro V$flash_recovery_area_usage;

To enable flashback

SQL> startup mount;
SQL> alter database flashback on;
SQL> alter database open;

Setting retention period for flashback:-

SQL> show parameter db_flashback_retention_target;

SQL> alter system set db_flashback_retention_target = no.of Seconds;


Flashback Database:-

I) Time based flashback

SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to timestamp
to_date('16-NOV-2010 07:00:0','DD-MON-YYYY HH:MI:SS');

II) SCN based flashback

SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to before scn 17786;

III) Log Sequence based flash back.

SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to sequence 2110;

Restrict Flashback for particular tablespace:-

SQL> alter tablespace tablepsace_name flashback off;

To Release Restriction:-

SQL> alter tablespace tablepsace_name flashback on;

Restriction on Flashback database:-

a) Controlfile recrated
b) Tablespace has been dropped
c) Datafile resized and shrinked
d) Database opened with resetlogs.

Views:-

V$FLASHBACK_DATABASE_LOG
V$FLASHBACK_DATABASE_STAT


Flashback Drop:-

Dropped objects will be stored in recyclebin with different name in the same
tablespace, so that we can flashback the droped objects if we required it.

user_recyclebin;
dba_recyclebin;

To Restore a table from recylebin:-

SQL> flashback table table_name to before drop;

If the table has been dropped and a new table has been created with the
same name then we cannot restore the droped table with the same name, hence we need to rename the table during flashback drop.

SQL> flashback table table_name to before drop rename to new_table_name;

To drop objects permanently:-

SQL> drop table table_name purge;

or

SQL> drop table table_name;
SQL> purge table table_name

To drop all the objects of a particualr tablespace

SQL> purge tablespace users;

To drop all the objects in Recycle bin

SQL> purge recyclebin;

Noet : Whenever a user is dropped , his objects will not get stored in recyclebin.

Flashback query:-

a) As of Timestamp
b) As of Scn

To perform flashback query the below parameter has to be set.

undo_retention =
undo_management = Auto


SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> select count(*) from emp;

COUNT(*)
----------
600000

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
17-NOV-10 12.47.57.417073 PM +05:30

SQL> delete from emp where rownum < 100000;

99999 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)
----------
499999

SQL> select systimestamp from dual;

SYSTIMESTAMP
-----------------------------------
17-NOV-10 12.48.44.493649 PM +05:30

SQL> select count(*) from emp as of
timestamp to_date('17-NOV-10 12.47.57','DD-MON-YY HH.MI.SS');

COUNT(*)
----------
600000


SQL> select
timestamp_to_scn(to_date('17-NOV-10 12.47.57','DD-MON-YY HH.MI.SS'))
FROM DUAL;

TIMESTAMP_TO_SCN(TO_DATE('17-NOV-1012.47.57','DD-MON-YYHH.MI.SS'))
------------------------------------------------------------------
160894

SQL> select count(*) from emp as of scn 160894;

COUNT(*)
----------
600000


To Recover the deleted data, we can create an anoher table using CTAS method
using the flashback query feature.

SQL> create table emp_new as select * from emp as of scn 160894;

Table created.

SQL> select count(*) from emp_new;

COUNT(*)
----------
600000

Flashback Version Query:-

SQL> select versions_xid,versions_startscn,versions_endscn,
versions_operation,empno,ename from emp
versions between timestamp minvalue and maxvalue
/

SQL> select versions_xid,versions_startscn,versions_endscn,
versions_operation,empno,ename from emp
versions between scn minvalue and maxvalue
/

SQL> select versions_xid, versions_startscn, versions_endscn, versions_operation, empno, ename from emp versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPNO ENAME
---------------- ----------------- --------------- - ---------- ------------------------------
0800280075000000 164370 I 1 Ahamed
05002A006F000000 164366 D 1 MANZOOR
05002A006F000000 164366 D 1 MANZOOR
05002A006F000000 164366 D 1 MANZOOR
02000D0073000000 164358 164366 U 1 MANZOOR
02000D0073000000 164358 164366 U 1 MANZOOR
02000D0073000000 164358 164366 U 1 MANZOOR
09002C006F000000 164152 164358 I 1 Ahamed
09002C006F000000 164152 164358 I 1 Ahamed
09002C006F000000 164152 164358 I 1 Ahamed


Flashback Transaction Query:-

View :- Flashback_transaction_query

SQL> select xid,undo_sql from flashback_transaction_query
where xid = '0800280075000000';

XID
----------------
UNDO_SQL
--------------------------------------------------------------------------------
0800280075000000
delete from "MANZOOR"."EMP" where ROWID = 'AAACYAAAEAAAAAKAAD';

Using the above undo_sql we can flashback the transactions.

Flashback Table

Used to Flashback a table to a specified time.

SQL> select count(*) from emp;

COUNT(*)
----------
1

SQL> alter table emp enable row movement;

Table altered.

SQL> flashback table emp to timestamp
to_date('17-NOV-10 13:54:00','DD-MON-YY HH24:MI:SS') ;

Flashback complete.

SQL> select count(*) from emp;

COUNT(*)
----------
3

SQL> select
timestamp_to_scn(to_date('17-NOV-10 13:54:00','DD-MON-YY HH24:MI:SS'))
from dual;

TIMESTAMP_TO_SCN(TO_DATE('17-NOV-1013:54:00','DD-MON-YYHH24:MI:SS'))
--------------------------------------------------------------------
164335

SQL> flashback table emp to scn 164335;

Flashback complete.

SQL> select count(*) from emp;

COUNT(*)
----------
3