Explanation of oracle flash back version and flash back transaction Query

--- Indicates flashing back to the database

--- Use a flashback table to restore the table content to a specific time point in the past

--- Restore from the deleted table

--- Use the flashback query to view the database content at any point in time

--- Use flashback version query to view versions of a row within a period of time

--- Use flashback transaction query to view transaction processing history or rows

Advantages:

Flash back technology can only process and change data, so it fundamentally changes the recovery technology. When this technology is used, the time taken to recover from an error is equal to the time taken to create an error. When flash back technology is used, it has obvious advantages in ease of use, availability, and restoration time compared with media restoration.

The flash back database uses the flash back log to execute the flash back. Flash back to delete the recycle bin. All other functions use restoration data.

Flash back time browsing

The flash back technology provides the function to query the previous version of the solution object, query historical data, and perform change analysis. Each transaction process generates a new database version logically. With the flash back technology, you can view these versions to find errors and their causes.

· Flash query: Query all data at a specific time point.

· Flash back VERSION Query: Check that all versions of the row between two times have changed the transaction processing of the row.

· Flash back transaction processing query: view all changes made by the transaction processing.

When you use the flashback query function, you can query the database from a specific time point. You can use the as of clause of the select statement to specify the timestamp of the data to be viewed. This helps analyze data differences.

Experiment 1: flashback Query

Experiment 1: flashback query: as of timestamp

SYS @ ORCL> conn tyger/tyger

Connected.

TYGER @ ORCL> create table fb_query as select * from scott. dept;

Table created.

TYGER @ ORCL> select * from fb_query;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

TYGER @ ORCL> set time on;

At 09:51:36 TYGER @ ORCL> delete fb_query where deptno = 10;

1 row deleted.

At 09:51:53 TYGER @ ORCL> commit;

Commit complete.

At 09:51:57 TYGER @ ORCL> select * from fb_query;

DEPTNO DNAME LOC

-------------------------------------

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

09:52:06 TYGER @ ORCL> select * from fb_query as of timestamp sysdate-1/1440;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Experiment 2: Flash back query application

At 10:25:04 TYGER @ ORCL> drop table fb_tyger purge;

Table dropped.

At 10:25:10 TYGER @ ORCL> create table fb_tyger as select * from scott. dept;

Table created.

At 10:25:33 TYGER @ ORCL> select * from fb_tyger;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

At 10:25:44 TYGER @ ORCL> select sysdate from dual;

SYSDATE

---------

14-MAR-14

At 10:26:02 TYGER @ ORCL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

Session altered.

At 10:26:30 TYGER @ ORCL> select sysdate from dual;

SYSDATE

-------------------

2014-03-14 10:26:38

10:26:38 TYGER @ ORCL> update fb_tyger set dname = '';

4 rows updated.

At 10:26:51 TYGER @ ORCL> commit;

Commit complete.

At 10:26:54 TYGER @ ORCL> select * from fb_tyger;

DEPTNO DNAME LOC

-------------------------------------

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

10:27:12 TYGER @ ORCL> select * from fb_tyger as of timestamp to_timestamp ('2017-03-14 10:26:38 ', 'yyyy-mm-dd hh24: mi: ss ');

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Here an error occurs:

ERROR at line 1:

ORA-01466: unable to read data-table definition has changed

Reference: http://blog.csdn.net/wanghui5767260/article/details/21227101

10:29:21 TYGER @ ORCL> select * from fb_tyger as of timestamp sysdate-3/1440;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

At 10:29:35 TYGER @ ORCL> select * from fb_tyger;

DEPTNO DNAME LOC

-------------------------------------

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

At 10:46:22 TYGER @ ORCL> set time off

TYGER @ ORCL> update fb_tyger t

2 set dname =

3 (select dname from fb_tyger as of timestamp

4 to_timestamp ('2017-03-14 10:26:38 ', 'yyyy-mm-dd hh24: mi: ss ')

5 where t. deptno = fb_tyger.deptno );

4 rows updated.

TYGER @ ORCL> commit;

Commit complete.

TYGER @ ORCL> select * from fb_tyger;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Experiment 3: Flash back query as of scn

TYGER @ ORCL> conn/as sysdba

Connected.

SYS @ ORCL> grant execute on dbms_flashback to tyger;

Grant succeeded.

TYGER @ ORCL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1107246

TYGER @ ORCL> select * from fb_tyger;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

TYGER @ ORCL> delete fb_tyger where deptno <= 30;

3 rows deleted.

TYGER @ ORCL> commit;

Commit complete.

TYGER @ ORCL> select * from fb_tyger;

DEPTNO DNAME LOC

-------------------------------------

40 OPERATIONS BOSTON

TYGER @ ORCL> select * from fb_tyger as of scn 1107246;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Experiment 4: Use the PL/SQL package dbms_flashback

Syntax:

· Specify the time for enabling session flash back:

DBMS_FLASHBACK.ENABLE_AT_TIME (query_time in timestamp );

· Enable session flash back to specify SCN:

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (query_scn in number );

· Disable FLASHBACK:

DBMS_FLASHBACK.DISABLE;

TYGER @ ORCL> conn/as sysdba

Connected.

SYS @ ORCL> grant execute on dbms_flashback to tyger;

Grant succeeded.

SYS @ ORCL> conn tyger/tyger

Connected.

TYGER @ ORCL>

TYGER @ ORCL>

TYGER @ ORCL>

TYGER @ ORCL> create table fb_query1 as select * from scott. dept;

Table created.

TYGER @ ORCL> create table fb_query2 as select * from scott. dept;

Table created.

TYGER @ ORCL> commit;

Commit complete.

TYGER @ ORCL> select * from fb_query1;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

TYGER @ ORCL> select * from fb_query2;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

TYGER @ ORCL> set time on;

11:03:38 TYGER @ ORCL> update fb_query1 set loc = '';

4 rows updated.

At 11:03:52 TYGER @ ORCL> commit;

Commit complete.

11:03:54 TYGER @ ORCL> update fb_query2 set dname = '';

4 rows updated.

At 11:04:14 TYGER @ ORCL> commit;

Commit complete.

At 11:04:15 TYGER @ ORCL>

At 11:04:15 TYGER @ ORCL> select * from fb_query1;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

At 11:04:23 TYGER @ ORCL> select * from fb_query2;

DEPTNO DNAME LOC

-------------------------------------

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

// The flashback is located 5 minutes ago. If you access time functions such as sysdate, the current value is returned instead of 5 minutes ago.

11:04:30 TYGER @ ORCL> exec dbms_flashback.enable_at_time (sysdate-5/1440 );

PL/SQL procedure successfully completed.

At 11:05:09 TYGER @ ORCL> select * from fb_query1;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

At 11:05:29 TYGER @ ORCL> select * from fb_query2;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

// In the flash session mode, DML and DDL operations are not allowed.

11:05:45 TYGER @ ORCL> update fb_query1 set dname = '';

Update fb_query1 set dname =''

*

ERROR at line 1:

ORA-08182: operation not supported while in Flashback mode

At 11:05:59 TYGER @ ORCL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

At 11:06:18 TYGER @ ORCL> select * from fb_query1;

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

At 11:06:30 TYGER @ ORCL> select * from fb_query2;

DEPTNO DNAME LOC

-------------------------------------

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

11:06:37 TYGER @ ORCL> update fb_query1 set dname = ''where deptno = 10;

1 row updated.

At 11:07:10 TYGER @ ORCL> select * from fb_query1;

DEPTNO DNAME LOC

-------------------------------------

10

20 RESEARCH

30 SALES

40 OPERATIONS

// Sys users are not allowed to use the dbms_flashback package.

At 11:07:20 TYGER @ ORCL> conn/as sysdba

Connected.

11:07:35 SYS @ ORCL> set time off

SYS @ ORCL> exec dbms_flashback.enable_at_time (sysdate-5/1440 );

BEGIN dbms_flashback.enable_at_time (sysdate-5/1440); END;

*

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

ORA-06512: at "SYS. DBMS_FLASHBACK", line 3

ORA-06512: at line 1

Experiment 2: flashback version

--- Flashback allows you to audit table rows and retrieve information about transactions that affect rows. Then, you can use the returned transaction processing identifier to perform transaction processing mining (by using LogMiner) or flash back version query.

Version refers to the changes in data rows caused by each transaction. Each change is a version. oracle provides a flashback version query, so that we can clearly see the entire change process of the Data row. The changes here are all changes caused by committed transactions, and changes caused by uncommitted transactions are not displayed, the Flash version query uses the undo data recorded in the undo tablespace.

Use a pseudo-column to get versions within a period of time

Pseudo columns: versions_starttime, versions_endtime, versions_startscn, versions_endscn, versions_xid, versions_operation,

Versions_startscn versions_starttime

If the SCN and time are null during the operation, the row is created outside the query range.

Versions_endscn versions_endtime

If the SCN and time are null, the row is deleted or not changed within the query range.

Versions_xid

Transaction ID

Versions_operation

Operation performed on this row I (insert) D (delete) U (update)

Minvalue maxvalue

Version Max time and min time

Note:

The versions clause cannot be used to query the following special tables:

· External table

· Temporary table

· Fixed table (the table starting with x $, that is, the internal table of oracle, used as the base table of the data dictionary). The following SQL statement can query the relevant table

Select * from v $ fixed_table

Versions clauses cannot be used to query views. However, you can use the versions clause in the view definition.

· The versions clause cannot be used across DDL commands.

· Filter out the rows that have been scaled down

Experiment: Flash back VERSION Query

TYGER @ ORCL> create table tyger as select ename, job, sal from scott. emp where rownum <5;

Table created.

TYGER @ ORCL> select * from tyger;

ENAME JOB SAL

-----------------------------

Smith clerk 857

Allen salesman 1656

Ward salesman 1306

Jones manager 3031

TYGER @ ORCL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

Session altered.

TYGER @ ORCL> select sysdate from dual;

SYSDATE

-------------------

2014-03-14 14:41:46

TYGER @ ORCL> update tyger set sal = sal + 100 where ename = 'Smith ';

1 row updated.

TYGER @ ORCL> commit;

Commit complete.

TYGER @ ORCL> update tyger set sal = sal + 100 where ename = 'Smith ';

1 row updated.

TYGER @ ORCL> commit;

Commit complete.

TYGER @ ORCL> update tyger set sal = sal + 100 where ename = 'Smith ';

1 row updated.

TYGER @ ORCL> commit;

Commit complete.

TYGER @ ORCL> select * from tyger;

ENAME JOB SAL

-----------------------------

Smith clerk 1157

Allen salesman 1656

Ward salesman 1306

Jones manager 3031

TYGER @ ORCL> col starttime for a30

TYGER @ ORCL> l

1 select to_char (versions_starttime, 'yyyy-mm-dd hh24: mi: ss') as starttime,

2 versions_xid, ename, job, sal

3 from tyger versions between timestamp to_date ('2017-03-14 14:41:46 ', 'yyyy-mm-dd hh24: mi: ss ')

4 * and sysdate where ename = 'Smith'

TYGER @ ORCL>/

STARTTIME VERSIONS_XID ENAME JOB SAL

---------------------------------------------------------------------------

2014-03-14 14:42:32 080016000F020000 smith clerk 1157

2014-03-14 14:42:26 01002C00F1010000 smith clerk 1057

14:42:17 0600180025020000 smith clerk 957

Smith clerk 857

Or

TYGER @ ORCL> col versions_starttime for a22

TYGER @ ORCL> col versions_endtime for a22

TYGER @ ORCL> l

1 select versions_starttime, versions_endtime, versions_xid, versions_operation, ename

2 * from tyger versions between timestamp to_timestamp ('2017-03-14 14:41:46 ', 'yyyy-mm-dd hh24: mi: ss') and maxvalue order by 1

TYGER @ ORCL>/

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME

-----------------------------------------------------------------------

14-MAR-14 02.42.17 PM 14-MAR-14 02.42.26 PM 0600180025020000 U SMITH

14-MAR-14 02.42.26 PM 14-MAR-14 02.42.32 PM 01002C00F1010000 U SMITH

14-MAR-14 02.42.32 PM 080016000F020000 U SMITH

WARD

ALLEN

JONES

14-MAR-14 02.42.17 PM SMITH

7 rows selected.

Experiment 3: flashback transaction Query

The flashback transaction query is implemented by querying the flashback_transaction_query view.

By querying this view, you can obtain information about the transaction execution, and even the UNDO statements.

Each transaction has a transaction ID and SCN Association.

Flash transaction query is a diagnostic tool used to view changes made to the database at the transaction processing level. In this way, you can diagnose database problems and analyze and audit transaction processing.

You can use the FLASHBACK_TRANSACTION_QUERY view to determine all necessary SQL statements. These statements can be used to restore specific transaction processing or modifications made within a specific period of time.

· In databases, DDL operations are only a series of space management operations and changes made to data dictionaries. The changes made to the data dictionary are displayed when you execute a DDL flash transaction query on the transaction processing.

· When the flashback transaction query involves a table that has been deleted from the database, the table name is not reflected. Instead, the Object ID is used.

· If the user executes the transaction, only the user ID, not the user name, is displayed in the Flash transaction query.

TYGER @ ORCL> conn/as sysdba

Connected.

SYS @ ORCL>

SYS @ ORCL>

SYS @ ORCL> select undo_ SQL from flashback_transaction_query where xid = '080016000f020000 ';

UNDO_ SQL

--------------------------------------------------------------------------------

Update "TYGER". "TYGER" set "SAL" = '000000' where ROWID = 'aaanq3aagaaaaymaa ';

SYS @ ORCL> grant select any transaction to tyger;

Grant succeeded.

SYS @ ORCL> conn tyger/tyger

Connected.

TYGER @ ORCL> select undo_ SQL from flashback_transaction_query where xid = '080016000f020000 ';

UNDO_ SQL

--------------------------------------------------------------------------------

Update "TYGER". "TYGER" set "SAL" = '000000' where ROWID = 'aaanq3aagaaaaymaa'; // undo statement