Oracle Flashback Technology 10g and 11g

                                Oracle Flashback Technology:-

 To view past states of database objects or
 To return database objects to a previous state without using point-in-time media recovery.
With flashback features,

you can do the following:-
Perform queries that return past data
Perform queries that return metadata that shows a detailed history of changes to the database
Recovr tables or rows to a previous point in time
Automatically track and archive transactional data changes
Roll back a transaction and its dependent transactions while the database remains online.

Roll back active transactions.
Recover terminated transactions by using database or process recovery.
Provide read consistency for SQL queries.

Type of FLASHBACK Methods:-

1.Flashback Query
2.Flashback Version Query
3.Flashback Transaction Query 
if not enable flashback we can retrive the the from undo
4.Flashback Table
5.Flashback Drop (Recycle Bin)
6.Flashback Database.

1.Flashback Query:-

Flashback Query allows the contents of a table to be queried with reference to a specific point in time,
 using the AS OF clause.

CREATE TABLE T1(id  NUMBER(10));

SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

insert into t1 values(1);
commit;
SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
delete from t1;
comit;

SELECT COUNT(*) FROM   t1 AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');
SELECT COUNT(*) FROM   t1 AS OF SCN 722452;

2.Flashback Version Query:-

 To retrieve metadata and historical data for a specific time interval
 Metadata for each row version includes start and end time, type of change operation, and identity of the transaction.

 CREATE TABLE t2 (id  NUMBER(10),name  VARCHAR2(50));
INSERT INTO t2 (id, name) VALUES (1, 'dinesh');
COMMIT;
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

UPDATE t2 SET name = 'haja' WHERE id = 1;
COMMIT;
UPDATE t2 SET name = 'senthil' WHERE id = 1;
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200

SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description 
FROM   flashback_version_query_test
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-03-29 14:59:08', 'YYYY-MM-DD HH24:MI:SS')
       AND TO_TIMESTAMP('2004-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
WHERE  id = 1;

3.Flashback Transaction Query:-

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries.

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('0600030021000000');
select rowid,rownum,xid,id form t1;


4.Flashback Table:-

The FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements.

You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
There must be enough information in the undo tablespace to complete the operation.
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).


CREATE TABLE t3 (id  NUMBER(10));
ALTER TABLE t3 ENABLE ROW MOVEMENT;
SELECT current_scn FROM v$database;
INSERT INTO t3 (id) VALUES (1);
COMMIT;
SELECT current_scn FROM v$database;
---FLASHBACK TABLE t3 TO SCN 715315;

SELECT COUNT(*) FROM t3;

----FLASHBACK TABLE t3 TO SCN 715340;

SELECT COUNT(*) FROM t3;

5.Flashback Drop (Recycle Bin):-

In Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin.
DROP TABLE my_table PURGE;
The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege
this not use undo and Flashback logs;
ALTER SESSION SET recyclebin = OFF;
ALTER SESSION SET recyclebin = ON;

-- System level.
ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = ON;

CREATE TABLE t4 (id  NUMBER(10));

INSERT INTO t4 (id) VALUES (1);
COMMIT;

DROP TABLE t4;

SHOW RECYCLEBIN

FLASHBACK TABLE t4 TO BEFORE DROP;

FLASHBACK TABLE t4 TO BEFORE DROP RENAME TO t4_old;

Several restrictions apply relating to the recycle bin.

Only available for non-system, locally managed tablespaces.
There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
The objects in the recycle bin are restricted to query operations only (no DDL or DML).
Flashback query operations must reference the recycle bin name.
Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
Tables with Fine Grained Access policies aer not protected by the recycle bin.

6.FLASHBACK DATABASE:-

FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery.
Flashback must be enabled before any flashback operations are performed.
If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.

To Turn on Flash back:-
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;


CREATE TABLE fls_db (id  NUMBER(10));
insert into fls_db values(1);
commit;
-- Flashback 5 minutes.
CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;
\===============

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;

CREATE RESTORE POINT respt1;
CREATE RESTORE POINT resptguar GUARANTEE FLASHBACK DATABASE;
Existing restore points can be displayed using the V$RESTORE_POINT view.
To flashback to this restore point, we would issue the following command.

FLASHBACK DATABASE TO RESTORE POINT respt1;