Oracle Flash-Back technology detailed

Overview:

The flashback technology is part of Oracle's powerful database backup recovery mechanism, which provides fast and minimal recovery (most of the flashback functionality can be done in the database online state) When a logical error occurs in the database. It is important to note that Flashback technology is designed to quickly restore logic errors, for physical damage or media loss errors, the flashback technology is beyond Resurrection , Or you have to use Oracle's advanced backup recovery tools like Ramn to do it (this is the essence of Oracle's powerful backup recovery mechanism)

Undo section (Undo SEGMENT)

Before you talk about flashback technology, you need to understand a logical structure in Oracle-the undo segment . Because most flashback technologies need to rely on revocation data in the undo segment. Revoking data is the information required to reverse the results of a DML statement, and as long as a transaction modifies the data, the original data before the update is written to an undo segment. (The transaction rollback also uses the data in the Undo segment). When a transaction starts, Oracle assigns it a undo segment, and there is a many-to-one relationship between the transaction and the undo segment, that is, a transaction can only have one undo segment, and multiple transactions may share an undo segment (although this typically does not happen when the database is running normally).

Flash Back Technology

Oracle provides four available flash back technologies (flashback query, flashback Delete, flashback file, Flashback database), each with different underlying architecture support, but in fact these four different flashback technology part of the function is overlapping, use also need to choose the most suitable flashback function according to the actual scene.

Flashback Queries (Flashbackquery)

A. basic flash back query

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

How it works: Oracle extracts the required undo data (provided the revocation is available, that is, the undo data has not been overwritten) for rollback, but this rollback is temporary and is visible only for the current session.

SQL>Select*from as of  timestamp to_ Timestamp ('2016-09-10 11:00:00','yyyy-mm-dd hh24:mi:ss ');

B. Flash-back table

Function Description: A table can be rolled back to a point in the past

How it works: Similarly, Oracle will first query the undo segment, extract all changes from a previous point in time, construct a rollback of the SQL statement that reverses the changes, and the flashback is a separate transaction, so the entire operation is rolled back because of a reason such as revocation of data expiration. There will be no inconsistent state.

Steps:

1. Enable table flashback The first thing to support row movement on a table is to set the identity in the data dictionary to identify that the operation may change the row ID, that is, the same data flashback succeeds the primary key is the same, but the row ID has actually changed.

SQL>altertable emp enable row movement;

2. Flash Back table operation

SQL>tabletotimestamp to_timestamp ('2016-09-10 11:00:00 ','yyyy-mm-dd hh24:mi:ss');

The flashback table may fail, and there are several possible scenarios:

　　　　　　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, exactly in the middle, the parent table and the data corresponding to the record is also deleted, in this case, because of the violation of FOREIGN key constraints, resulting in the flashback table operation failed;

　　　　　　revocation of data invalidation , such as to support the flashback operation of the revocation data is overwritten, this case the Flashback table operation will naturally fail;

　　　　　　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 will fail.

　　　　Note: The above flashback is based on the revocation data, and the revocation data will be rewritten (expired will be rewritten, the active will not be rewritten), so when you need to use these kinds of flashback function to recover the data (when it is necessary to use the Undo data based on the flashback function), The shortest time to find errors, the first time to perform a flashback operation in order to maximize the success of the Flash back function.

Flash back Delete (Flashback drop )

Function Description: Flash back delete makes it easy to restore a table that has been dropped. The corresponding index, database constraints are also restored (except for foreign KEY constraints)

Principle Description: The drop command is actually the rename command, the earlier version of Oracle (before 10g), the flashback delete means that all references to the table have been deleted from the data dictionary, although the data in the table may still exist, but it has become a ghost, unable to recover, after 10g version, The drop command is just a rename operation, so it's easy to recover.

　　

Flash back delete operation execution command is simple

SQL>table 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

SQL>tabletodrop to Emp_new

You can also view the current user through the Recycle Bin those tables have been deleted, each user has a Recycle Bin, this Recycle Bin is a logical structure, it is not a separate storage space, it exists in the current table space, so 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 deletion failed.

SQL> SHOW recyclebin;

Completely delete the table, flash back Delete also helpless

SQL>DROPTABLE EMP PURGE;

Emptying the Recycle Bin

SQL> PURGE recyclebin;

　Note: The flashback removal is only for the drop command, note that the truncate operation and the drop operation are distinguished, truncate is called table truncation, and the data in the table is emptied (tuning the Oracle High watermark implementation), the table structure is unaffected, the speed is fast, The drawback is that this process does not produce any undo data or redo logs, if mistakenly deleted, recovery of abnormal trouble, to be cautious use. Drop deletes the data + table structure, and the flashback delete is only for the drop operation.

Flash Back Data archive (Flashback 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 undo data is overwritten, the flashback will fail, and the flashback is limited to the space available in the tablespace, and the flash back data is archived. There are no such restrictions.

Create a flashback file

1. Create a table space where users can archive the data, or use a table space that already exists.

SQL>Create'test.dbf'

2. Create a flash return file with a retention period of 2 years

  SQL>Create2year;

　　　

Enable the Flash return file for the EMP table under the Scott user

1. Give users permission to archive

SQL>Grant on to Scott;

2. Connect users

SQL> conn Scott/Tiger;

3. Enable the Flash return file for the EMP table

SQL>altertable emp Flashback archive Test_fa;

At this point, the EMP table has the ability to query or fall back to any time in the past 2 years!

Flash back database (Flashback)

Function Description: The Flashback database can roll back the entire database to a certain point in time, 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, which in fact 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, although the recovery process can be 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 chunks are constantly copied from the database buffer cache 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 a process of extracting the flashback log and copying the block image back to the data file.

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

1. Specify the flashback recovery area, that is, the location of the flashback log, but the Flashback recovery area is not just for the flashback log, many of Oracle's backup and recovery technologies are used in this area, such as automatic backup of control files, etc.

SQL>alterset='/flash_recovery_area';

2. Specify the recovery area size

SQL>alterset db_recovery_file_dest_size=

3. Specifies that the flashback log is retained for 2 hours, which means that the database can be rolled back to any point in the first two hours via the flashback operation

SQL>alterset db_flashback_retention_target=

4. Orderly shutdown Database--mount mode enable flashback database-Open Database

SQL>shutdown  immediate;　　　SQL>  startup Mount;　　　SQL>alterdatabaseon; SQL>alterdatabaseopen;

At this point, the Flashback database configuration is complete!

　　

Using the Flashback database feature

SQL>shutdown  immediate;　　　SQL>  startup Mount; SQL>databasetotimestamp sysdate-/ 1440　　　; SQL>alterdatabaseopen resetlogs;

Summarize

　　This article lists four types of flashback techniques, in which flashback queries, including basic flashback queries, flashback tables, and other technologies rely on the undo data (there is a class of flashback techniques that can be flashback to a specified transaction, similar to the principle of using undo data to construct an SQL statement for reversing a transaction), depending on the undo data, is naturally limited by the retention time of the revocation data, which may result in a flashback failure due to the revocation of the data overwrite. 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 table space, so it is limited by the available space of the current tablespace, and the flashback file provides the ability to query or fall back to any point in the past. The flashback database is one of the more extreme database recovery functions, which is equivalent to incomplete recovery and relies on the flashback log.

Oracle Flash-Back technology detailed