Oracle Flashback Technology
you can use the various features of Oracle Flashback to view past states of data and rewind your database without restoring backups or performing point-in-time recovery.In general, flashback features are more efficient and less disruptive than media recovery in most situations in which they apply.
Flashback technology is a set of features in Oracle database that make your work easier to view past states of data or to move your database objects to a previous state without using point in time media recovery.
View past states of data or move database objects to previous state means you have performed some operations like DML + COMMIT and now you want to rollback that operation, this can be done easily through FLASHBACK technology without using point in time media recovery.
Flashback Database depends on the flashback logs to perform flashback. Flashback Drop uses the recycle bin. All other techniques
use undo data.Not all flashback features modify the database. Some are simply methods to query other versions of data. these are tools to investigate a problem and aid in recovery. The results of flashback queries help you do one of two things:
- Determine the type of database-modifying flashback operation to perform to fix the problem.
- Feed the result set of these queries into an INSERT, UPDATE, or DELETE statement that enables you to easily repair the erroneous data.
- Flashback Data Archive enables you to use the preceding logical flashback features to access data from far back in the past.
Below Feature support by Flashback:
- Flashback Query
- Flashback Version Query
- Flashback Transaction Query
- Flashback Table
- Flashback Drop (Recycle Bin)
- Flashback Database
- Flashback Query Functions
Flashback Query
Using Oracle flashback query, you can retrieve the committed data as it was at a past point in time. With the Flashback Query feature, you can perform queries as of a certain time. By using the AS OF clause of the SELECT statement, you can specify the time stamp for which to view the data. This is useful for analyzing a data discrepancy.
Note: TIMESTAMP and SCN are valid options for the AS OF clause
Query table data from specific point in time using timestamp and scn:
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn),TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') AS current_time FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) CURRENT_TIME
----------- --------------------------------------------------------------------------- -----------------------------
1622677 26-JAN-18 03.33.06.000000000 AM 26-jan-2018 03:33:08
SQL> select * from emp;
EMPNO NAME
---------- --------------------------------------------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
6 rows selected.
SQL> insert into emp values(666,'GGGG');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from emp;
EMPNO NAME
---------- --------------------------------------------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
666 GGGG
666 GGGG
666 GGGG
9 rows selected.
SQL> commit;
Commit complete.
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn),TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') AS current_time FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) CURRENT_TIME
----------- --------------------------------------------------------------------------- -----------------------------
1622718 26-JAN-18 03.34.21.000000000 AM 26-jan-2018 03:34:24
SQL> SELECT * FROM EMP AS OF scn 1622677;
EMPNO NAME
---------- --------------------------------------------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
6 rows selected.
SQL> SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP('26-jan-2018 03:33:08','dd-mon-yyyy hh24:mi:ss');
EMPNO NAME
---------- --------------------------------------------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
6 rows selected.
SQL>
SQL> SELECT * FROM EMP AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;
EMPNO NAME
---------- --------------------------------------------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
6 rows selected.
SQL>
Flashback Version Query
The Flashback Version Query feature enables you to use the VERSIONS clause to retrieve all the versions of the rows that exist between two points in time or two SCNs.
The rows returned by Flashback Version Query represent a history of changes for the rows across transactions. Flashback Version Query retrieves only committed occurrences of the rows. Uncommitted row versions within a transaction are not shown. The rows returned also include deleted and subsequently reinserted versions of the rows.You can use Flashback Version Query to retrieve row history.
The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version. The pseudocolumns available are
VERSIONS_XID :Identifier of the transaction that created the row version
VERSIONS_OPERATION :Operation Performed. I for Insert, U for Update, D for Delete
VERSIONS_STARTSCN :Starting System Change Number when the row version was created
VERSIONS_STARTTIME :Starting System Change Time when the row version was created
VERSIONS_ENDSCN :SCN when the row version expired.
VERSIONS_ENDTIME :Timestamp when the row version expired
column versions_starttime format a16
column versions_endtime format a16
set linesize 120
select versions_xid,versions_starttime,versions_endtime,
versions_operation,name from emp versions between
timestamp TO_TIMESTAMP ('26-01-2018 04:08:19','DD-MM-YYYY HH24:MI:SS')
and TO_TIMESTAMP ('26-01-2018 04:19:33','DD-MM-YYYY HH24:MI:SS') where empno=999;
SQL>
SQL> column versions_starttime format a16
column versions_endtime format a16
set linesize 120
select versions_xid,versions_starttime,versions_endtime,
versions_operation,name from emp versions between
timestamp TO_TIMESTAMP ('26-01-2018 04:08:19','DD-MM-YYYY HH24:MI:SS')
and TO_TIMESTAMP ('26-01-2018 04:15:19','DD-MM-YYYY HH24:MI:SS') where empno=999;
SQL> SQL> SQL> 2 3 4
VERSIONS_XID VERSIONS_STARTTI VERSIONS_ENDTIME V NAME
---------------- ---------------- ---------------- - --------------------------------------------------
0200010096040000 26-JAN-18 04.12. I KUMAR
57 AM
SQL>
SQL>
SQL> update emp set name='ANURAG' WHERE EMPNO=999;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD
-------------------
26-01-2018 04:19:33
SQL>
column versions_starttime format a16
column versions_endtime format a16
set linesize 120
select versions_xid,versions_starttime,versions_endtime,
versions_operation,name from emp versions between
timestamp TO_TIMESTAMP ('26-01-2018 04:08:19','DD-MM-YYYY HH24:MI:SS')
and TO_TIMESTAMP ('26-01-2018 04:19:33','DD-MM-YYYY HH24:MI:SS') where empno=999;
SQL> SQL> SQL> SQL> 2 3 4
VERSIONS_XID VERSIONS_STARTTI VERSIONS_ENDTIME V NAME
---------------- ---------------- ---------------- - --------------------------------------------------
0500010097040000 26-JAN-18 04.19. U ANURAG
13 AM
0200010096040000 26-JAN-18 04.12. 26-JAN-18 04.19. I KUMAR
57 AM 13 AM
SQL> select timestamp_to_scn(to_timestamp('26/01/2018 04:12:57','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
SCN
----------
1624146
SQL> select timestamp_to_scn(to_timestamp('26/01/2018 04:19:13','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
SCN
----------
1624327
SQL>
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, empno,name
FROM emp
VERSIONS BETWEEN SCN 1624146 AND 1624327
WHERE empno = 999;
VERSIONS_STARTSCN VERSIONS_STARTTI VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V EMPNO NAME
----------------- ---------------- --------------- ---------------- ---------------- - ---------- ----------
1624328 26-JAN-18 04.19. 0500010097040000 U 999 ANURAG
13 AM
1624147 26-JAN-18 04.12. 1624328 26-JAN-18 04.19. 0200010096040000 I 999 KUMAR
57 AM 13 AM
The VERSIONS clause cannot be used to query the following types of tables:
External tables
Temporary tables
Fixed tables
Flashback Version Query
The Flashback Version Query feature enables you to use the VERSIONS clause to retrieve all the versions of the rows that exist between two points in time or two SCNs.
The rows returned by Flashback Version Query represent a history of changes for the rows across transactions. Flashback Version Query retrieves only committed occurrences of the rows. Uncommitted row versions within a transaction are not shown. The rows returned also include deleted and subsequently reinserted versions of the rows.You can use Flashback Version Query to retrieve row history.
The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version. The pseudocolumns available are
VERSIONS_XID :Identifier of the transaction that created the row version
VERSIONS_OPERATION :Operation Performed. I for Insert, U for Update, D for Delete
VERSIONS_STARTSCN :Starting System Change Number when the row version was created
VERSIONS_STARTTIME :Starting System Change Time when the row version was created
VERSIONS_ENDSCN :SCN when the row version expired.
VERSIONS_ENDTIME :Timestamp when the row version expired
column versions_starttime format a16
column versions_endtime format a16
set linesize 120
select versions_xid,versions_starttime,versions_endtime,
versions_operation,name from emp versions between
timestamp TO_TIMESTAMP ('26-01-2018 04:08:19','DD-MM-YYYY HH24:MI:SS')
and TO_TIMESTAMP ('26-01-2018 04:19:33','DD-MM-YYYY HH24:MI:SS') where empno=999;
SQL>
SQL> column versions_starttime format a16
column versions_endtime format a16
set linesize 120
select versions_xid,versions_starttime,versions_endtime,
versions_operation,name from emp versions between
timestamp TO_TIMESTAMP ('26-01-2018 04:08:19','DD-MM-YYYY HH24:MI:SS')
and TO_TIMESTAMP ('26-01-2018 04:15:19','DD-MM-YYYY HH24:MI:SS') where empno=999;
SQL> SQL> SQL> 2 3 4
VERSIONS_XID VERSIONS_STARTTI VERSIONS_ENDTIME V NAME
---------------- ---------------- ---------------- - --------------------------------------------------
0200010096040000 26-JAN-18 04.12. I KUMAR
57 AM
SQL>
SQL>
SQL> update emp set name='ANURAG' WHERE EMPNO=999;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD
-------------------
26-01-2018 04:19:33
SQL>
column versions_starttime format a16
column versions_endtime format a16
set linesize 120
select versions_xid,versions_starttime,versions_endtime,
versions_operation,name from emp versions between
timestamp TO_TIMESTAMP ('26-01-2018 04:08:19','DD-MM-YYYY HH24:MI:SS')
and TO_TIMESTAMP ('26-01-2018 04:19:33','DD-MM-YYYY HH24:MI:SS') where empno=999;
SQL> SQL> SQL> SQL> 2 3 4
VERSIONS_XID VERSIONS_STARTTI VERSIONS_ENDTIME V NAME
---------------- ---------------- ---------------- - --------------------------------------------------
0500010097040000 26-JAN-18 04.19. U ANURAG
13 AM
0200010096040000 26-JAN-18 04.12. 26-JAN-18 04.19. I KUMAR
57 AM 13 AM
SQL> select timestamp_to_scn(to_timestamp('26/01/2018 04:12:57','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
SCN
----------
1624146
SQL> select timestamp_to_scn(to_timestamp('26/01/2018 04:19:13','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
SCN
----------
1624327
SQL>
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, empno,name
FROM emp
VERSIONS BETWEEN SCN 1624146 AND 1624327
WHERE empno = 999;
VERSIONS_STARTSCN VERSIONS_STARTTI VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V EMPNO NAME
----------------- ---------------- --------------- ---------------- ---------------- - ---------- ----------
1624328 26-JAN-18 04.19. 0500010097040000 U 999 ANURAG
13 AM
1624147 26-JAN-18 04.12. 1624328 26-JAN-18 04.19. 0200010096040000 I 999 KUMAR
57 AM 13 AM
The VERSIONS clause cannot be used to query the following types of tables:
External tables
Temporary tables
Fixed tables
