Oracle Database 10g Flashback Technology.

Flashback Database allows the DBA to “roll back” a table, set of tables or the entire database to a previous point-in-time.

First of all change the undo_retention parameter of your database.

SQL>show parameter undo_retention;

# The default value is 900 seconds.

SQL>alter system set undo_retention=2400 scope=both;

# I am changing it to 40 minutes which equals 2400 seconds.

Also enable the retention guarantee parameter for
your database'e undo tablespace.

SQL>show parameter tablespace;
# My current undo tablespace is named as UNDOTBS1

SQL>select tablespace_name, retention from dba_tablespaces
    where tablespace_name = 'UNDOTBS1';
#shows NOGUARANTEE which means retention guarantee
#is not enabled.

SQL>alter tablespace undotbs1 retention guarantee;
#enable the retention guarantee with the above command.

SQL>select tablespace_name, retention from dba_tablespaces
    where tablespace_name = 'UNDOTBS1';

# shows GUARANTEE

FLASHBACK QUERY

In oracle 10g, the "as of" clause is available in a select query
to retrieve the state of a table as of a given timestamp or SCN.

Unlock the users to work with.

SQL>alter user hr identified by hr account unlock;
SQL>alter user scott identified by tiger account unlock;

SQL>grant insert, update, delete, select 
on hr.employees to scott;
SQL>grant insert, update, delete, select 
on hr.departments to scott;
SQL>grant flashback on hr.employees to scott;
SQL>grant flashback on hr.departments to scott;
SQL>grant flashback on hr.employees to hr;
SQL>grant flashback on hr.departments to hr;
SQL>grant select any transaction to scott;
SQL>grant create any table to scott;
SQL>grant drop any table to scott;
SQL>grant select any table to scott;
SQL>grant select on v_$database to scott;
SQL>grant select on v_$database to hr;


SQL>connect scott/tiger;
#Connect to the database as scott.


SQL>select current_scn from v$database;
#check the current scn number before making any changes.

481166


SQL>delete from hr.employees where employee_id in (195,196);
#delete employees which are no longer working in the company.
SQL>commit;

SQL>SELECT * FROM HR.EMPLOYEES
as of timestamp systimestamp - interval '5' minute
where hr.employees.employee_id not in
(select employee_id from hr.employees);

#displays the list of records that were deleted 5 minutes ago.

SQL>create table hr.employees_deleted as
SELECT * FROM HR.EMPLOYEES
as of timestamp systimestamp - interval '5' minute
where hr.employees.employee_id not in
(select employee_id from hr.employees);

#restore those deleted rows to a new table "employees_deleted".

FLASHBACK TABLE

With Oracle 10g flashback feature the state of rows in a table
can be restored to a point of in the past along with all the
indexes, triggers and constraints while the database is online,
increasing the overall availability of the database. The table
can be restored as of a timestamp or an SCN.

In order to use flashback table on a table, you must enable row
movement on the table before performing the flashback operation.

SQL>connect sys/sys as sysdba;
SQL>alter table hr.employees enable row movement;

SQL>alter table hr.departments enable row movement;

SQL>connect scott/tiger;
Connect to database as scott.


SQL>delete from hr.employees
where employee_id in (195,196);
# values which were deleted earlier.

SQL>commit;

If two or more than two tables having parent-child relationship 
along with constraints and rows were deleted from both tables, 
they can be flashed back with the following command.

SQL>flashback table hr.employees, hr.departments
to scn (481166);
#Recommended approach.

#Flashback to timestamp can also be used.

SQL>flashback table hr.employees, hr.departments
to timestamp systimestamp - interval '10' minute;

In order to flashback a single table use the following command.

SQL>flashback table hr.employees
to timestamp systimestamp - interval '10' minute;

#flashback the tables to 10 minutes in the past.

SQL>select employee_id, first_name, last_name from hr.employees
where employee_id in (195,196);
#check the records

RECOVERING A DROPPED TABLE USING FLASHBACK TABLE COMMAND.

To flash back a table to before a drop table operation, you only
need the privileges necessary to drop the table.

Connect to the database as hr

SQL>drop table hr.employees cascade constraints;
# employees table dropped accidently

SQL>select * from RECYCLEBIN;
# check for the dropped table in the recyclebin.

SQL>flashback table hr.employees to before drop;
# recover the dropped table from the recyclebin.

FLASHBACK VERSION QUERY

With this feature you can view the entire history of changes of
a given row between two SCNs or timestamps.

Suppose some of the following changes are made to the employees
and departments table in the hr schema.

Connect to the database as scott.

Before starting check the current SCN number.

SQL>connect sys/sys as sysdba
SQL>select dbms_flashback.get_system_change_number from dual;

1673400

SQL> update hr.employees set salary = salary*1.5 
where employee_id=195;

SQL>delete from hr.employees where employee_id = 196;

SQL>insert into hr.departments values (660, 'Security', 100, 1700);

SQL>update hr.employees set manager_id = 100
where employee_id = 195;
SQL>commit;

SQL>update hr.employees set department_id = 660 
where employee_id = 195;
SQL>update hr.employees set salary = salary*1.5
where employee_id=195;
SQL>commit;

Check the ending SCN number.

SQL> select dbms_flashback.get_system_change_number from dual;

1673495


Now by using Flashback Version Query, the HR user can see the
entire history of changes between specified timestamps or SCNs.

Now let us see the changes made for the two employees with IDs 
195 and 196.



SQL>select versions_startscn startscn, versions_endscn endscn,
versions_xid xid, versions_operation oper,
employee_id empid, last_name name, manager_id mgrid, salary sal
from hr.employees
versions between scn 1673400 and 1673495
where employee_id in (195,196);

Meaning of Pseudocolumn:

VERSIONS_START{SCN|TIME} The starting SCN or timestamp when the
change was made to the row.

VERSIONS_END{SCN|TIME} The ending SCN or timesamp.

VERSIONS_XID The transaction ID of the transaction that created
the row.

VERSIONS_OPERATION The operation that was performed on the row
(I=insert, D=Delete, U=Update).


FLASHBACK TRANSACTION QUERY

After identifying the above changes, we can use Flashback
Transaction Query to identify any other changes that were
made by the transaction containing the inappropriate changes.
Once identified, all changes within the transaction can be
reversed as a group to maintain referential integrity.

It is recommended that the supplemental log data should
be enabled in the database from the very beginning.
If it is not enabled, then enable it by the following sql command.

SQL> alter database add supplemental log data;

Suppose if the last update query to the employees table needs to
be reversed in the FLASHBACK QUERY section we will query the
FLASHBACK_TRANSACTION_QUERY.

SQL>select start_scn, commit_scn, logon_user,
operation, table_name, undo_sql
from flashback_transaction_query
where xid = hextoraw('04000F0097030000');

In the undo_sql column it will show a sql entry which can be
used to reverse the effects of the original transaction.

update "HR"."EMPLOYEES" set "SALARY" = '2800' where ROWID = 
'AAAMAEAAFAAAABYABc';

In order to restore the changes execute the above command in the
SQL prompt.

SQL>update "HR"."EMPLOYEES" set "SALARY" = '2800' where ROWID = 
'AAAMAEAAFAAAABYABc';
SQL> commit;

FLASHBACK DATABASE

The flashback database command returns the database to a past
time or SCN, providing a fast alternative to performing
incomplete database recovery. Database can be flashed back to
scn ot timestamp.

After a flashback database operation in order to have write
access to the flashed back database, you must reopen it with
an "alter database open resetlogs" command.

To enable flashback database make sure your database is in
archivelog mode after confirming do the following.

SQL>shutdown immediate;
SQL>startup mount exclusive;  
# flashback database command demands that the database should be 
# mounted in exclusive mode.
SQL>alter database flashback on;
SQL>alter database open;

check the database's flashback status.

SQL>select FLASHBACK_ON from V$DATABASE;
this column should show a value of "yes".

check the following view to check the how far you can flashback 
the database.

SQL>select * from V$FLASHBACK_DATABASE_LOG;

suppose the database is running for over 1 hour and you need to 
flash it back then do the following.

SQL>shutdown;
SQL>startup mount exclusive;
SQL>flashback database to timestamp sysdate-1/24;

The Database will be flashed back and all the changes made since the 
past hour will be lost.

SQL>alter database open resetlogs;

Disable Flashback logging.

SQL>shutdown immediate;
SQL>startup mount exclusive;
SQL>alter database flashback off;
SQL>alter database open;