FlashBack Architecture

There are a number of flashback levels

row level flashback query, flashback versions query, flashback transaction query
table level flashback table, flashback drop
database level flashback database

Oracle 10g has several error-correction techniques that use undo data, however they are only available if you use automatic undo management (AUM),

There are two other flashback technologies that do not use the undo data, they use flashback logs and recyclebin instead.

DBMS_FLASHBACK, flashback table query, flashback transaction query, flashback version query and select .. as of .. statements all use the undo segments. Flashback database uses the flashback logs and flashback drop uses the recycled bin.

When using flashback, if any operations violate a constraint the flashback operation will be rolled back, you can disable constraints but it’s probably not a good idea. If you have a table using a foreign key it is a good idea to flashback both tables. Flashback technology requires you to lock the whole table if it cannot it will fail immediately.

RMAN can only do flashback database and no other flashback technology.

Flashback Query

Using flashback query involves using a select statement with an AS OF clause. you can select data from a past point in time. If you get a ORA-08180 it means that the data is no longer available in the undo segments.

Privilege grant flashback on table_test to pvalle;
grant flashback on any table to pvalle;
Flashback query (time)

select * from employees as of timestamp
to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')
where last_name = valle;

select employee_id, name from hr.employee as of timestamp (systimestamp - interval '15' minute);

Flashback query (SCN)

select * from employees as of scn 4542;

Note: using a scn will put you with 3 secs if you need to be dead accurate use timestamps instead.

Take the whole session back in time

grant execute on dbms_flashback to test01;

execute dbms_flashback.enable_at_time(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS'));

execute dbms_flashback.disable;

Reinserting

insert into employees
select * from employees as of timestamp
to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')
where last_name = valle;

insert into employees select * from employees as of scn = 4542;

List the flashback entries select * from flashback_transaction_query;
Obtain a time from an SCN select scn_to_timestamp(1408640) as ts from dual;
Obtain SCN from time point select timestamp_to_scn(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')) as scn from dual;
What is in the undo tablespace

select begin_time, end_time, tuned_undoretention from v$undostat;

Note: the time is in seconds

Flashback Version Query

Flashback version query provides you with all the versions of a row between two points in time or SCN, this is useful if you want to audit a table finding out what happened to a row. However there are some points to remember:

The limitations of flashback version query are:

The most useful columns to obtain are below:

Flashback version query (time)

select versions_xid as xid, versions_startscn as start_scn, versions_endscn as end_scn,   versions_operation as operation, empname
from employees
  versions between timestamp minvalue and maxvalue
order by versions_startscn;

Flashback version query (SCN) select versions_xid as xid, versions_startscn as start_scn, versions_endscn as end_scn,   versions_operation as operation, empname
from employees
  versions between scn minvalue and maxvalue
where emp_id = 863;
Obtain a time from an SCN select scn_to_timestamp(1408640) as ts from dual;
Obtain SCN from time point select timestamp_to_scn(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')) as scn from dual;

Flashback Transaction Query

Identifies which transaction or transactions were responsible for a certain change in a table's data during a specified time period. Basically it queries the flashback_transaction_query view. It provides the SQL code that will undo the change, flashback transaction query can use an index path to retrieve data instead of reading the entire redo log file.

Flashback transaction considerations:

Consider setting the retention guarantee option for the undo tablespace, this will ensure that the unexpired data in the undo segments is preserved.

Flashback transaction query will contain the following columns

If you have chained rows or use clustered tables then oracle recommends that you should turn on supplemental logging on at the database level.

Privilege grant select any transaction to pvalle;
Supplemental logging alter database add supplemental log data;
Display undo segments select operation, undo_sql, table_name from flashback_transaction_query;
Flashback transaction query

select operation, undo_sql, table_name
from flashback_transaction_query
where start_timestamp >= to_timestamp ('04-12-2007 05:00:00', 'DD-MM-YYYY HH:MI:SS')
and commit_timestamp <= to_timestamp ('04-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')
and table_owner='VALLEP';

Note: this will give you the SQL to reserver the change that was applied to the database,just cut and paste to use.

Obtain a time from an SCN select scn_to_timestamp(1408640) as ts from dual;
Obtain SCN from time point select timestamp_to_scn(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')) as scn from dual;

You use flashback version query to obtain the xid and then use the xid in the flashback transaction query statement

flashback version and flashback transaction query

select versions_xid as xid, versions_startscn as start_scn, versions_endscn as end_scn, versions_operation as operation,
empname from employees
versions between scn minvalue and maxvalue
as of scn 7920
where emp_id = 222;

XID                         start_scn   end_scn   operation  empname    salary
---------------------------------------------------------------------------------
0003002F00038BA9            2266                  I           Paul      20000

Now use the XID in the flashback transaction query to obtain the SQL to undo the change.

select xid, start_scn, commit_scn, operation, logon_user, undo_sql
from flashback_transaction_query
where xid=hextoraw('0003002F00038BA9');

XID                   start_scn    commit_scn   operation   user       undo_sql
--------------------------------------------------------------------------------------------------------
0003002F00038BA9      195243       195244       delete      vallep     insert into HR.EMP
                                                                       ('EMPNO', 'EMPNAME', 'SALARY')
                                                                       values ('222', 'Paul', '20000');

Flashback Table

There are two distinct table related flashback table features in oracle, flashback table which relies on undo segments and flashback drop which lies on the recyclebin not the undo segments.

Flashback table lets you recover a table to a previous point in time, you don't have to take the tablespace offline during a recovery, however oracle acquires exclusive DML locks on the table or tables that you are recovering, but the table continues to be online.

When using flashback table oracle does not preserve the ROWIDS when it restores the rows in the changed data blocks of the tables, since it uses DML operations to perform its work, you must have enabled row movement in the tables that you are going to flashback, only flashback table requires you to enable row movement.

If the data is not in the undo segments then you cannot recover the table by using flashback table, however you can use other means to recover the table.

Restriction on flashback table recovery

Privilege grant flashback on table_test to pvalle;
grant flashback on any table to pvalle;
Enable row movement alter table test enable row movement;
Display row movement select table_name, row_movement from user_tables;
Flashback table (SCN) flashback table test to scn 4587309871;
Flashback table (time)

flashback table employees to timestamp to_timestamp
('04-12-2007 05:00:00', 'DD-MM-YYYY HH:MI:SS')

flashback table employees to timestamp (systimestamp - interval '15' minute);

flashback table employess to timestamp (sysdate - 1);

flashback table employees, depts to timestamp (sysdate - 1);

Enable triggers

flashback table employees to timestamp (sysdate -1) enable triggers;

Note: Oracle disables triggers by default when falshing back a table

Flashback Drop

Flashback drop lets you reinstate previously dropped tables exactly as it was before the drop, below is a table of what is kept where when a table is dropped:

If two tables exist in the recyclebin with the same name the newest one will be restored unless you state which one you want to restore. If you restore a table it is removed from the recyclebin.

Recover flashback table <table_name> to before drop
Recover and rename

flashback table <table_name> to before drop rename to <new name>;

Note: use if table name already exists

Recover same name table flashback table 'BIN$hfkjdshfkhs' to before drop;
List recycle bin select object_name, original_name, type from user_recyclebin;
select object_name, original_name, type from dba_recyclebin;
select * from recyclebin; (shows only current user info)
show recyclebin;
drop table completely

drop table <table_name> purge;

Note: table will not be in recyclebin

drop table from r/bin

purge table <table_name>

Note: table will be gone from recyclebin

truncate table

truncate table

Note: table will not be in the recyclebin

drop user

drop user test cascade

Note: will not store anything in the recyclebin

purge recyclebins purge recyclebin; (purge user recyclebin)
purge dba_recyclebin;(purges all recyclebins)
purge tablespace test user test03; (purge test03 from tablespace test)
List dropped table contents

select * from "BIN$NwM/FEjuSUORrgxHUPR3WA==$0";

Note: the double quotes

Naming Convention BIN$globalUID$version

Space pressure on a tablespace will cause it to purge the recyclebins of the users within that tablespace, it is based on a FIFO order. When a tablespace has the auto extend feature turned on it will clear down the recyclebin first, then auto extend.

Limitations on flashback drop:

Flashback Database

The database can be taken back in time by reversing all work done sequentially. The database must be opened with resetlogs as if an incomplete recovery has happened. This is ideal if you have a database corruption (wrong transaction, etc) and require the database to be rewound before the corruption occurred. If you have media or a physical problem a normal recovery is required.

Flashback database is not enabled by default, when enabled flashback database a process (RVWR – recovery Writer) copies modified blocks to the flashback buffer. This buffer is then flushed to disk (flashback logs). Remember the flashback logging is not a log of changes but a log of the complete block images. Not every changed block is logged as this would be too much for the database to cope with, so only as many blocks are copied such that performance is not impacted. Flashback database will construct a version of the data files that is just before the time you want. The data files probably will be in a inconsistent state as different blocks will be at different SCN’s, to complete the flashback process, Oracle then uses the redo logs to recover all the blocks to the exact time requested thus synchronizing all the data files to the same SCN. Archiving mode must be enabled to use flashback database. An important note to remember is that Flashback can never reserve a change only to redo them.

The advantage in using flashback database is speed and convenience with which you can take the database back in time.

You can use rman, sql and Enterprise manager to flashback a database. If the flash recovery area does not have enough room the database will continue to function but flashback operations may fail. It is not possible to flashback one tablespace, you must flashback the whole database. If performance is being affected by flashback data collection turn some tablespace flashbacking off.

You cannot undo a resized data file to a smaller size. When using ‘backup recovery area’ and ‘backup recovery files’ controlfiles , redo logs, permanent files and flashback logs will not be backed up.

Enable

select log_mode from v$database; (must use archivelog mode)
alter system set db_recovery_file_dest=’c:/flash_recovery_area’;
alter system set dba_recovery_file_dest_size=8G;
alter system set db_flashback_retention_target=4320; (3 days – time in minutes)
shutdown immediate;
startup mount;
alter database flashback on; (RVWR process will start)
alter database open;

Monitoring

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
select flashback_on from v$database; (check its enabled)
select retention_target, estimated_flashback_size, flashback_size from v$flashback_database_log;
select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;
select end_time, flashback_data, db_data, redo_data from v$flashback_database_stat; (flashback growth)

Flashback Buffer select * from v$sgastat where name like 'flashback%';
Flashback database example

startup mount;
flashback database to timestamp to_timestamp('15-02-07 10:00:00', 'dd-mm-yy hh24:mi:ss');
alter database open read only; (check schema ok start db, if not continue, optional)
shutdown abort;
startup mount;
flashback database to timestamp to_timestamp('15-02-07 10:02:00', 'dd-mm-yy hh24:mi:ss');
alter database open read only; (check schema ok start db, if not continue, optional)

When happy………………

alter database open resetlogs;

Flashback using RMAN

flashback database to time = to_date('15-02-07 10:00:00', 'dd-mm-yy hh24:mi:ss');
flashback database to scn = 2765665;
flashback database to sequence=2123 thread=1;

Tablespace Flashback Configuration
Turn flashback on (tablespace) alter tablespace <tablespace_name> flashback on; (must be in mount mode)
Turn flashback off (tablespace) alter tablespace <tablespace_name> flashback off; (this can be done in open or mount mode)
Display tablespaces with flashback select name, flashback_on from v$tablespace;
Flashback space usage select * from v$flash_recovery_area_usage;

Note: if one or more tablespaces are not generating flashback data, then before carrying out a flashback operation the files making up the tablespace must be taken offline. Offline files are ignored by recover and flashback. Remember that you must make these files to the same point as the flashback otherwise the database will not open.

Flashback Recovery Area

The alert log and DBA_OUTSTANDING_ALERTS will hold status information regarding the flash recovery area. You can use the commands backup copy or backup for flash recovery area. Controlfiles and redo logs are permanently stored in the flash recovery area.

Monitoring

select * from v$recovery_file_dest;

Note: this details space_used, space_limit, space_reclaimable, # of files

What is using the Flashback recovery area select * from v$flash_recovery_area_usage;
Backing up flashback area

rman> backup recovery area;
Note: includes backup sets, datafile and archive, controlfile excludes: flashback log, current controlfile, online redo logs

rman> backup recovery files;
Note: includes all files whether or not they are in the flash recovery area

Using Restore Points

There is a new enhancement to recovery techniques by allowing you to create restore points, a restore point is alias for an SCN, which elimates the need to research and record SCN's or timestamps which you need for flashback database and flashback table operations, all you need to do is refer to the restore point when recovering.

The restore point does not guarantee that the data will remain in the flashback logs necessary for a flashback database operation to succeed under all circumstances. By creating a restore point that will guarantee that all data will be preserved, you can be assured that the restore will be successful, remember that you will need enough space in the flash recovery area to hold the necessary data. A guaranteed restore point does not depend on flashback logs, thus you can create a guaranteed restore point with flashback logging turned off, they use a logging mechanism that's similar to flashback logs but it's separate from them, thus if you are using guaranteed restore points it better to turn off flashback logging otherwise you may end up filling up the flash recovery area.

One note is that you can only restore back to the restore point, you cannot restore back to a point in time using restore points, you must then use the backups and archived logs to do a point in time recovery.

Not Guaranteed
create create restore point large_update;
remove drop restore point large_update;
Guaranteed
create guaranteed restore point create restore point test_guarantee guarantee flashback database;
remove guaranteed restore point drop restore point test_guarantee;
Other Operations
Using a restore point flashback database to restore point large_update;
flashback table test to restore point table_update;
Displaying restore points select name, scn, storage_size, time, guarantee_flashback_database from v$restore_point;
Flashback databse running

select flashback_on from v$database;

Note: you should get a restore point only reply if using restore points