Flashback Technology

fra = flash recovery area / fast recovery area
aum = automatic undo management
scn = system change number
da  = data archive

select log_mode, flashback_on from v$database;

FlashBack Database Procedure
1. Put DB in mount mode
2. RMAN -> Flashback to  -time, scn, restore point
3. Open DB with RESETLOGS
or
    Open DB Read Only for testing

show parameter retention;
undo_retention = 900 (15 min)
db_flashback_retention_target = 1440 (means 24 hours)

show parameter undo;

show parameter recovery;

make sure system is in archivelog mode.

select log_mode from v$database;

to change retention value

alter system set db_flashback_retention_target=2880;  (48 hours)


To set flashback on for the database:

select log_mode, flashback_on from v$database;

shutdown immediate;
startup mount;
alter database flashback on;

alter database open;

To confirm:

select log_mode, flashback_on from v$database;


Restore Points

create restore point rp1;

select scn, time, name from v$restore_point;


create restore point test guarantee flashback database;

drop restore point rp1;


To check the flashback window (how far back we can go to flashback our data)

select oldest_flashback_scn, oldest_flashback_time
from v$flashback_database_log;


Test Scenario

create table scott.t1 as select * from scott.emp;

select count(*) from scott.t1;

select current_scn from v$database;

create restore point emp1;

truncate table scott.t1;

select count(*) from scott.t1;

we get 0


Go to RMAN


$rman target /

shutdown immediate;
startup mount;
FLASHBACK DATABASE TO RESTORE POINT "TEST";
sql 'alter database open read only';
or
alter database open resetlogs;

Now check the data in scott.t1 table.



Oracle total recall, flashback data archive

create tablespace tbs1
datafile '/u01/prod/tbs01.dbf'
size 100m
autoextend on
next 100m
maxsize 10G;

Now we will create the flashback archive on this tablespace.

create flashback archive fba1
tablespace tbs1
quota 10G
retention 1 year;

If we have multiple data archives we can set any one default.

alter flashback archive fba1 set default;

To change retention policy:

ALTER FLASHBACK ARCHIVE fba1 MODIFY RETENTION 2 YEAR;

ALTER FLASHBACK ARCHIVE fba1 MODIFY RETENTION 1 MONTH;

show user;
SYS

This is super user and has all the privileges.

To grant this to others:

grant flashback archive administer to scott;

select flashback_archive_name from dba_flashback_archive;

grant flashback archive on fba1 to scott;


We can assign a table to this flashback archive.

create table scott.t2 as select * from scott.t1;

alter table scott.t2 flashback archive fba1;

delete from scott.t2;

select * from scott.t2 as of timestamp
to_timestamp('2015-12-20 10:15:00','YYYY-MM-DD HH24:MI:SS');

If we find the required rows we can insert the same into our table.

insert into t1 select * from t1 as of timestamp
to_timestamp('2015-12-20 10:45:00','YYYY-MM-DD HH24:MI:SS');

We can unlink the table from flashback archive.

alter table scott.t1 no flashback archive;

desc dba_flashback_archive;

desc dba_flashback_archive_ts;

desc dba_flashback_archive_tables;


Flashback Table (undrop a table)
Flashback Query

Flashback Transaction


Flashback Query

select product_id, purchprice
from products
as of timestamp / scn
where product_id=100;

update products set salesprice =
(select salesprice from products
as of timestamp "to_timestamp('2015-12-20 10:15:00','YYYY-MM-DD HH24:MI:SS')
where product_id=100)
where product_id=100;


Flashback Version Query

select product_id, purchprice
from products
versions between timestamp <t1> and <t2>
where product_id=100;

alter system set recyclebin = 'ON';
alter system set recyclebin = 'OFF';

by default this is on

show recyclebin;

show parameter recyclebin;

select flashback_on from v$database;

disconnect;

conn scott/tiger

create table t3 as select * from emp;

select count(*) from t3;

commit;

drop table t3;

select * from t3;
(table doesn't exist)

show recyclebin; (this works only in the same user where we dropped a table)

its here

flashback table t3 to before drop;

select count(*) from t3;

To empty the recyclebin:

purge recyclebin;

To use flashback table feature we must enable the row movement for that table:

select * from t3 order by empno;

Row movement is oracle's ability to restructure the rows.

select row_movement from user_tables
where table_name = 'T3';

alter table t3 enable row movement;

select row_movement from user_tables
where table_name = 'T3';

Here we can go to a particular time backwards.
We must know the good time.

select to_char(oldest_flashback_time, 'mm/dd/yy hh24:mi:ss')
from v$flashback_database_log;

Whatever time comes as the result of the above query we can go back upto that time.


Testing Scenario

select * from t3 where deptno = 10;

delete from t3 where deptno = 10;

There is a constraint problem, so we disable the constraint.

alter table dept disable constraint dept_mgr_fk;

flashback table t3 to timestamp
to_date('20-DEC-15 10:30:20','DD-MON-YY HH24:MI:SS');

select * from t3 where deptno=10;


Flashback Transaction Query

conn / as sysdba
alter database add supplemental log data (primary key) columns;

desc flashback_transaction_query;
desc emp;
select ename, sal from scott.emp where empno = 7900;

update emp set sal = sal * 2 where empno = 7900;

commit;

Now come to flashback:

select ename, sal, versions_xid from emp
versions between scn minvalue and maxvalue
where empno = 7900;

We can see the last 2 records in the result.

based on that 

execute dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('txnno'));

here 1 means only one transaction
txnno paste the actual transaction id

now select that row to see the old result

select ename from emp
where ename between 'A%' and 'C%';

delete from emp where ename like 'A%';

commit;

select ename from emp as of timestamp
to_timestamp('23-05-12 12:31:00','DD-MM-YY HH24:MI:SS');