Oracle Flash Back Technology (Flashback)

Flashback technology has a flashback table, a flashback delete, a flashback query, a flashback transaction query, a flashback transaction, a flashback database, and a flashback data archive. Among them, the flashback query, Flashback transaction query is used to "observe" the past; the Flashback data archive is not a standalone function, its function is to extend the time window of the flashback query; The flashback table, the Flashback delete table can "go back" to the past in a table, and the flashback transaction can "go back" to the past in the transaction unit. The flashback database is able to "go back" to the past in a database.

I. Flash back table (Flashback tables)

The flashback table is used to undo the undo table space, so the table can be flashed back how long before the data file Undo_retention,undo table space to start the auto-growth function, whether set guarantee, such as the impact of three factors.

1. Flash back to the exact time

Sql> Flashback table Scott.emp to timestamp to_timestamp (' 2014-09-16 04:32:00 ', ' yyyy-mm-dd hh24:mi:ss ');

2. Flash back 10 minutes ago

Sql> Flashback table Scott.emp to timestamp (systimestamp-interval ' minute);

3. Flash the scott.emp back to the SCN at 1086000.

Sql> Flashback table Scott.emp to SCN 1086000;

--about how the SCN and timestamps are converted see Oracle shredding 31st

4. Flash the scott.emp and scott.dept two tables back to the SCN 1086000 (primarily for tables with foreign KEY constraints)

Sql> Flashback table Scott.emp,scott.dept to SCN 1086000;

Use the Flash back table to note the following:

(1) The returned table must have the Line mobility feature enabled

Sql> ALTER TABLE dept enable row Movement;

(2) The performer of the "FLASHBACK table" command must have the "FLASHBACK any table" system permission or have the "FLASHBACK" object permission on the table being flashed back.

(3) "FLASHBACK TABLE" belongs to the DDL command and is implicitly committed.

(4) Any table of SYS users cannot use this feature.

Ii. Flash-back deletion table (Flashback Drop)

The Flashback deletion list refers to the effect of withdrawing "DROP table".

1. Flashback of deleted scott.emp form

Sql> Flashback table Scott.emp to before drop;

2. When the table is deleted, a new table with the same name is created, and if an attempt is made to flash back to the original table using the above command, it will be reported ora-38312:original name is used by an existing object error and can be renamed.

Sql> Flashback table Test to before drop rename to Test1;

3. If the table name is duplicated, the flashback is followed by a post-in-first-out principle.

4. The recovered Recycle Bin object can be indicated on flashback

Sql> Flashback table "Bin$ayid7zbbjwngukjadqiiua==$0" to before drop;

The Flashback Delete Table works by: When the "drop table" command executes, the table and its indexes are not actually deleted, and the space occupied is simply assigned to another database object: The Recycle Bin object, essentially the equivalent of renaming. Note: The tablespace allocates the space of the Recycle Bin object to the segment that requires space under the pressure of autogrow, and the data file does not grow automatically until the Recycle Bin object is exhausted.

5. Can disable Recycle Bin function

Sql> alter system set recyclebin= ' OFF ' scope=spfile;

6. Delete all objects from the current user Recycle Bin

Sql> purge RecycleBin;

Third, flash back query (Flashback)

Querying past data in a table is called a flashback query, and there are two main ways: 1. Flash back to point in time query. Use the "as of" clause of the SELECT command with the PL/SQL package to dbms_flashback the query over a point in time. 2. Flash back to the version query. Queries using the "versions between" clause of the SELECT command over a period of time.

Flashback Point-in-time query

Using the "as of" clause

1. Check the salary of employee number No. 7788 at specific time

Sql> Select Sal from emp as of timestamp to_timestamp (' 2014-09-16 10:02:30 ', ' Yyyy-mm-dd,hh24:mi:ss ') where empno=7788;

2. Check the wages of Employee No. No. 7788 at five minutes ago

Sql> Select Sal from emp as of timestamp (systimestamp-interval ' 5 ' minute) where empno=7788;

3. Query specific SCN

Sql> SELECT * from emp as of SCN 1095000;

4. Change the salary of employee number No. 7788 to a value up to 15 minutes ago

sql> Update emp Set sal= (select Sal from emp as of timestamp (Systimestamp-interval ' minute) where empno=7888) whe Re empno=7788;

Using the Dbms_flashback Package

Using the Enable_at_time or ENABLE_AT_SCN stored procedure of the Dbms_flashback package locks a session-level flashback time target into the flashback mode, and subsequent query commands can omit "as of" until the call is Dbms_flashback _disable the stored procedure until it is closed.

For example, the flashback mode session is fixed at 15 minutes ago:

sql> exec dbms_flashback.enable_at_time (systimestamp-interval ' minute);

Now for the query, note that the table that was queried at this point was 15 minutes ago.

Sql> Select Sal from emp where empno=7788; --Ignoring the "as of" clause

When you access date functions such as Sysdate, Systimestamp, and so on, their return value is still the current value, not the value before 15 minutes.

DML and DDL execution error when in Flash-back session mode

sql> Update emp set sal=4000 where empno=7788;
Update emp set sal=4000 where empno=7788
*
ERROR at line 1:
Ora-08182:operation not supported while in Flashback mode

If the query is complete, call the disable stored procedure to turn off Flashback session mode.

sql> exec dbms_flashback.disable;

Flash back version Query

The Flashback version query can run through a certain length of time window, and can return data at different points in the time window by using only one query command.

For example, the salary of employee number NO. 7788 will be modified first through 3 transactions. Its value is 4000, then 5000, then 10000, and finally 3000.

Sql> SelectSal fromEmpwhereEmpno=7788; SAL----------      4000SQL> UpdateEmpSetSal= the whereEmpno=7788;1row updated. SQL> Commit;CommitComplete . SQL> UpdateEmpSetSal=10000 whereEmpno=7788;1row updated. SQL> Commit;CommitComplete . SQL> UpdateEmpSetSal= the whereEmpno=7788;1row updated. SQL> Commit;CommitComplete.

Perform a flashback version query

Sql> SelectEmpno,sal fromEMP2Versionsbetween timestamp(Systimestamp-Interval' the'Minute andMaxValue3  whereEmpno=7788; EMPNO SAL----- ---------- 7788         the 7788       10000 7788         the 7788        4000

With "versions between", we can see that in 15 minutes, the salary of employee number No. 7788 is 4 values, indicating that there are 3 transactions that have been modified. To be able to see the order of these transactions, you can use pseudo-fields in the query list. As shown below:

Sql> Select  2VERSIONS_XID,VERSIONS_STARTSCN,VERSIONS_ENDSCN,3Empno,sal4   fromEMP5Versionsbetween timestamp(Systimestamp-Interval' the'Minute andMaxValue6  whereEmpno=7788  7  Order  by 2nulls first; Versions_xid versions_startscn versions_endscn EMPNO SAL---------------- ----------------- --------------- ----- ----------060002000f0300001097139       7788       400002001100fb0200001097139       1097148       7788        the03001d001e0300001097148       1097153       7788      100000900170000030000       1097153                     7788        the

Where Versions_xid for the transaction number VERSIONS_STARTSCN and VERSIONS_ENDSCN are the SCN at the start of the transaction, and the SCN at the beginning of the next transaction that modifies the line. The SCN numbers of the two fields are easily derived from the actual order of modification: 4000,5000,10000, and finally 3000.

Reference: "Courage: Oracle 11g Database Recovery Technology"

Oracle Flash Back Technology (Flashback)