ORACLE Flash Back

1. Overview

Flashback technology is part of the powerful backup mechanism of ORALCE, which provides fast and small-loss recovery when a logical error occurs in the database (most flashback functions can be done online in the database). It is important to note that the flashback technology is designed to quickly restore logic errors, for physical damage or media loss errors, the flashback technology is beyond resurrection, but also with the help of some of Oracle's advanced backup recovery tools such as Rman to complete.

2. Undo section (Undo segment)

Undo segment, a logical structure in oralce. Most flashback data relies on revocation data in the undo segment. The Undo statement is the information required to reverse the DML statement, and as long as a statement modifies the data, the pre-UPDATE statement is written to an undo segment. (The transaction rollback also needs to use the data in the Undo segment), when the transaction starts, the database assigns it a undo segment, the transaction and the undo segment have a one-to-many relationship, that is, a transaction can only have a single undo segment, and multiple transactions may share a revocation segment (which normally does not occur when the database is running normally).

3. Flash Back Technology

Oracle offers four types of flash-back technology available: Flashback query, Flashback Delete, flashback file, flash back database. Each has a different underlying architecture support. Once these four different flashback technology features overlap, the use of the actual scene to choose the most appropriate flashback function.

Test data preparation

Insert into student (stu_id, stu_name, sex, credits) values (' 0001 ', ' King ', ' 2 ', ' 83 ');

Insert into student (stu_id, stu_name, sex, credits) values (' 0002 ', ' Liu Yi ', ' 1 ', ' 85 ');

commit;

3.1 Flash back Query

? Flash back to basic query

Function Description: You can query the database state for a period of time in the past.

How it works: Oralce extracts the required undo data (provided the revocation is available, i.e. the undo data can be overwritten) for rollback, but the rollback is temporary and visible only to the current session.

Select * from student as of timestamp to_timestamp (' 2016-08-31-13:37:00 ', ' Yyyy-mm-dd hh24:mi:ss ');

? Flash back Table

Feature Description: Rolls back a table to a point in time past.

How it works: Similarly, Oracle will first query the undo segment to extract all changes from a previous point in time. Constructs an SQL statement that reverses these changes to fallback. The flashback operation is a separate transaction, so because of the reason that the undo transaction expires, the entire operation is rolled back and there is no inconsistency in the operation.

① Enable table flashback the first thing to support row movement on a table (setting an identity in a data dictionary to identify this operation might change the row ID, i.e. the same data flashback the primary key ID is the same, but the row ID has changed)

Alter Table Student enable row movement;

② Flash Back table operation

Flashback table student to timestamp to_timestamp (' 2018-08-31 11:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

If there is no ① operation, the error will be

The flashback table may fail, possibly in the following situations:

? Violation of the database constraints, such as the user accidentally deleted the data in the child table, and now want to use the Flashback table technology fallback, just in the middle, the parent table and the record corresponding to the record is also deleted, due to violate the foreign key constraints, resulting in the Flash back table failure;

? Revocation data invalidation, the revocation data used to support the flashback operation is overwritten, resulting in the failure of the flashback table operation;

? The flashback cannot span the DDL, that is, the table structure has changed between the flashback point and the current point, and the flashback operation fails.

3.2 Flash Back Delete

Function Description: Flash back Delete can easily restore a table that has been dropped. The corresponding index, database constraints are also restored (except for foreign KEY constraints)

How it works: The drop command is actually the rename command, the earlier version of Oracle, the flashback delete means that all references to the table have been removed from the data dictionary, although the data in the table may still exist, but it has become a ghost, there is no way to recover, after the 10G version, The drop command is just a rename operation, so it's easy to recover,

The Flash back delete operation command is simple

Flashback table student to before;

If the name of the table you want to restore is already in use on the current system, you can also rename the table when the flash back is deleted

Flashback table student to before drop rename to Student_new;

The Recycle Bin can also be used to see which tables of the current user have been deleted, each user has a recycle Bin, which is a logical structure, it is not a separate storage space, he exists in the middle of the current table, if there are other operations need space, such as now need to create a table, there is not enough space available, The data in the Recycle Bin is cleaned up, which is why the flashback failed.

Show RecycleBin; --pl/sql execution of this command is unsuccessful

Completely delete the table, flash back Delete also powerless

Drop Table student purge;

Emptying the Recycle Bin

Purge RecycleBin;

Flashback delete only for the drop command, truncate operation for table truncation, will clear the table data (adjust Oralce high watermark implementation), the table structure is not affected, fast, the disadvantage is that this process will not produce any undo data or redo the log, if mistakenly deleted, restore the abnormal trouble. The drop operation removes the data + table structure and the flashback delete is only for the drop operation.

3.3 Flash Back Data archive (Flashback Dada Archive)

Function Description: The Flashback data archive can make the table have the ability to fall back to any point in the past, the flashback query mentioned above will be limited to whether the revocation data is invalidated, if the revocation data is overwritten, the flashback will fail, and the flashback deletion is limited by the availability of sufficient space, and the flash back data archive, There are no such restrictions.

Create a flashback file

① Create a tablespace (or use a table space that already exists) for a user to flash back the data archive

Create tablespace test_space datafile ' test_space.dbf ' size 20m;

② Create a flash return file with a 2-year retention period

Create Flashback archive test_fa tablespace test_space retention 2 year;

③ giving users permission to archive

Grant Flashback Archive on Test_fa to KDJL;

④ enabling a flashback file for the student table

Alter Table Student Flashback archive Test_fa;

At this point, the student table has the ability to query or fall back to any time in the past two years.

3.4 Flash back to the database

Function Description: The Flashback database can roll back the entire database to some point in the past, the flashback table is the time-space shuttle of a table, and the flashback database is the time-space shuttle of the whole database. Of course, all the work after the flashback point is lost, in fact, it is equivalent to the incomplete recovery of the database. So you can only open the database in Resetlogs mode. The flashback database can cause downtime, but the recovery process is much faster than the traditional backup recovery mechanism.

How it works: The flashback database does not use undo data, another mechanism is used to preserve the recovery data needed for fallback, and when the flashback database is enabled, the changed data blocks are constantly copied from the database buffer buffer to the flashback buffer, then called the Recovery writer (recovery Writer) will flush this data to the Flashback log file on disk. The flashback process is the process of extracting the block impression back into the data file, a flashback log.

Configuring the Flashback database (the flashback database requires the database to be in archive mode)

① Specifies the flashback recovery area, which is where the flashback log is stored, but the Flashback recovery area is not just for the flashback log, but many of the backup and recovery techniques used in the Oralce are available in this area, such as automatic backup of control files.

Alter system set db_recovery_file_dest = '/flash_recovery_area ';

② specifying the recovery area size

Alter system set db_recovery_file_dest_size=4g;

③ specifies that the flashback log is retained for 2 hours, which means that the database can be rolled back to any point within 2 hours via the flashback operation.

Alter system set db_flashback_retention_target=120;

④ orderly shutdown database, enable flashback database in Mount mode, open database

shutdown immediate;

Startup Mount;

Alter Database Flashback on;

Alter Database Open;

⑤ using Flash back database

shutdown immediate;

Startup Mount;

Flashback Database to timestamp sysdate-60/1440;

Alter Database Open Resetlogs;

4. Summary

This article lists four types of flashback techniques, in which flashback queries, including basic flashback queries, flashback tables, and other technologies rely on the revocation of data (there is a kind of technology for flashback transactions, can be a flashback to transactions, similar principle, with the help of undoing the data to build the SQL statement for reversing the transaction), dependent on the revocation of data, is dependent on the retention time of the revocation data, which may cause the flashback to fail because the undo transaction is overwritten. Flashback deletion is due to the fact that the deletion of the table after the 10G version only manifests itself as a rename operation, introducing the concept of Recycle Bin, but this Recycle Bin is only a logical division of the current tablespace, so it is limited by the current available tablespace. A flashback file provides the ability to query or fall back to any point in time. The flashback database is a more extreme data recovery function, which is equivalent to incomplete recovery and relies on the flashback log.

Flash back technical details: https://www.cnblogs.com/chengxiao/p/5860823.html

ORACLE Flash Back