Use the dbms_flashback toolkit to implement the flashback query function

Use the dbms_flashback toolkit to implement the flashback query function

Flashback Query is a convenient logical recovery function based on Oracle Undo expired data. With the support of Undo Tablespace, We can query the time version of a data table at a specific time point (or SCN point) in the past.

The standard Flashback Query statement requires the use of the as of timestamp | as of scn statement after the data table, which is used to specify the past time point of the data table to be viewed. This method is indeed very convenient from the perspective of the database administrator, especially those who directly access the background to save data.

However, in both cases, there are some problems with the as of specified time. The first is the statements in the application. The code embedded into the application cannot be easily modified. That is to say, the as of statements cannot be added to the procedure or package. On the other hand, data at one time point may involve version operations on multiple data tables. Specifying tables one by one may cause many problems. Therefore, the preceding problem can be solved by specifying the context of the dbms_flashback package in the past time point.

1. Environment Description

The author uses Oracle 11gR2 for testing. The specific version is 11.2.0.4.

SQL> select * from v $ version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

Currently, no supplemental log data is configured. The key parameters configured for Undo are as follows:

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUPPLEMENTAL_LOG_DATA_MIN

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

NO

SQL> show parameter undo

NAME TYPE VALUE

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

Undo_management string AUTO

Undo_retention integer 9000

Undo_tablespace string UNDOTBS1

The description of the dbms_flashback package is as follows:

SQL> desc dbms_flashback

Element Type

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

ENABLE_AT_TIME PROCEDURE

ENABLE_AT_SYSTEM_CHANGE_NUMBER PROCEDURE

DISABLE PROCEDURE

GET_SYSTEM_CHANGE_NUMBER FUNCTION

NOCASCADE CONSTANT

NOCASCADE_FORCE CONSTANT

NONCONFLICT_ONLY CONSTANT

CASCADE CONSTANT

TRANSACTION_BACKOUT PROCEDURE

In my previous articles, dbms_flashback.get_system_change_number is often used to obtain the SCN Number of the system, and the transaction_backout method is used to reverse the overall transaction policy. This article focuses on the enable_at_time, enable_at_system_change_number, and disable methods.

2. dbms_flashback Time Machine

Enable_at_time and enable_at_system_change_number both play the same role and are used to reverse the context of the current session to a previous time point. The difference lies only in the time point or SCN number.

After the two methods are correctly executed, all queries are performed based on the specified time point, similar to the "time machine" in the movie ". The Flashback Query Process does not need to be manually specified after the data table.

Note: Same as flashback query, the dbms_flashback method cannot be used by SYS users. If it is used, an error is returned.

SQL> exec dbms_flashback.enable_at_system_change_number (Fig => 2107410 );

Begin dbms_flashback.enable_at_system_change_number (query_scn => 2107410); end;

ORA-08185 :??? § SYS ??? §??????

ORA-06512 :?? "SYS. DBMS_FLASHBACK", line 12

ORA-06512 :?? Line 1

Our demonstration experiment will be conducted under scott. Grant the scott user the execution permission for the dbms_flashback package.

SQL> grant execute on dbms_flashback to scott;

Grant succeeded

Switch to scott user to create an experiment data table.

SQL> create table test as select empno, sal from emp where rownum <4;

Table created

SQL> select * from test;

EMPNO SAL

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

7369 800.00

7499 1600.00

7521 1250.00

The system time and SCN number are as follows:

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

2107631

SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;

TO_CHAR (SYSDATE, 'yyyy-MM-DDHH2

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

13:49:13

And then perform the so-called "misoperations ".

SQL> update test set sal = 1000 where empno = 7521;

1 row updated

SQL> commit;

Commit complete

SQL> select * from test where empno = 7521;

EMPNO SAL

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

7521 1000.00

Traditional Flashback Query policy.

SQL> select * from test as of scn 2107631 where empno = 7521;

EMPNO SAL

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

7521 1250.00

The following uses the dbms_flashback method to specify an SCN number.

SQL> exec dbms_flashback.enable_at_system_change_number (query_scn => 2107631); -- query is enabled;

PL/SQL procedure successfully completed

SQL> select * from test where empno = 7521;

EMPNO SAL

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

7521 1250.00

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed

-- Recover data after disable

SQL> select * from test where empno = 7521;

EMPNO SAL

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

7521 1000.00

Note: after using the enable method, we can directly query the past time point method. If the operation ends, you need to disable the set context time using the disable method.

If the timestamp method is specified, the effect is the same.

SQL> exec dbms_flashback.enable_at_time (query_time => to_timestamp ('2017-06-29 13:49:13 ', 'yyyy-mm-dd hh24: mi: ss '));

PL/SQL procedure successfully completed

SQL> select * from test where empno = 7521;

EMPNO SAL

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

7521 1250.00

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed

Finally, let's take a look at how the results would be if we modified the relevant data and irrelevant data in the past time context?

SQL> exec dbms_flashback.enable_at_system_change_number (Fig => 2107631 );

PL/SQL procedure successfully completed

SQL> select * from test where empno = 7521;

EMPNO SAL

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

7521 1250.00

SQL> select * from test;

EMPNO SAL

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

7369 800.00

7499 1600.00

7521 1250.00

SQL> delete test where empno = 7499;

Delete test where e-mapreduce = 7499

ORA-08182: operation not supported in flashback Mode

SQL> update test set sal = 1000 where empno = 7369;

Update test set sal = 1000 where empno = 7369

ORA-08182: operation not supported in flashback Mode

SQL> insert into test values (1000,1000 );

Insert into test values (1000,1000)

ORA-08182: operation not supported in flashback Mode

SQL> create table m as select * from test;

Create table m as select * from test

ORA-08182: operation not supported in flashback Mode

Like the time machine, the history cannot be changed.

3. Conclusion

The Dbms_flashback toolkit provides many functions and meaningful scenarios about the flash back technology. With the help of dbms_flashback's flashback query context, we can conveniently query and retrieve context historical data.

1 2 Next Page