Oracle Flash Back Technology detailed introduction and summary _oracle

Oracle Flash-back technology , here are 4 kinds of flash back technology, the Oracle Flash-back technology to do a collation summary.

Overview:

The flash-back technology is part of Oracle's powerful database backup recovery mechanism, which provides fast and minimal loss recovery when the database is logically wrong (most flashback functions can be done in the database online state). It should be noted that the flashback technique is designed to quickly restore logic errors, beyond resurrection for physical damage or media loss, or the use of Oracle's advanced backup recovery tools such as Ramn (which is the essence of Oracle's powerful backup recovery mechanism)

Undo segment (Undo SEGMENT)

Before you talk about Flash-back technology, you need to understand a logical structure in Oracle-the undo segment. Because most flash-back technologies need to rely on undo data in the Undo section. The undo data is the information needed to reverse the result 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 is started, Oracle assigns a undo segment to it, and there is a many-to-many relationship between the transaction and the undo segment, that is, a transaction can only correspond to one undo segment, and multiple transactions may share a single undo segment (although this is not usually the case when the database is running normally).

Flash Back Technology

Oracle provides four available flash-back technology (flash-back queries, flash back delete, flash regression file, flash back to the database, each has a different underlying system structure support, but in fact these four different flashback technology part of the function is overlapping, use also need to be based on the actual scene to choose the most appropriate flashback function.

Flash back queries (flashback query)

A. Basic flash-back queries

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

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

　　　　Sql> SELECT * FROM dept as of Timestamp To_timestamp (' 2016-09-10 11:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

B. Flash-back table

Feature Description: You can retreat a table back to a point in the past

Working principle: Similarly, Oracle will first query the undo segment, extract all the changes after a point in the past, construct the SQL statement to reverse these changes, and the flashback is a separate transaction, so if the undo data expires and so on, the entire operation will be rolled back. There will be no inconsistency in the state.

Steps:

1. Enable table flashback first to support row movement on the table (setting the identity in the data dictionary to identify the operation may change the row ID, that is, the same data flash back succeeds the primary key is the same, but the row ID has actually changed)

　Sql> ALTER TABLE EMP enable row movement;

2. Flash return table operation

Sql> Flashback Table Dept to timestamp To_timestamp (' 2016-09-10 11:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

The Flash-back table may fail, and there are several possible scenarios:

Violates database constraints, such as user accidentally deleting data from a child table, now want to use the flash-back technology for rollback, just in the middle, the parent table and the data corresponding to the record has also been deleted, in this case, because the violation of FOREIGN key constraints, resulting in the flash-back table operation failed;

Undo data invalidation, for example, the undo data used to support the flashback operation is overwritten, and the flashback table operation will fail naturally;

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

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

Flash-Back deletion (Flashback drop)

Feature Description: Flash-back deletion makes it easy to restore a table that has been dropped. corresponding indexes, database constraints are also restored (except for foreign KEY constraints)

Principle Description: The drop command is actually a rename command, an earlier Oracle version (10g ago), and flash-back deletion means that all references to the table are removed from the data dictionary, although the data in the table may still exist, but it has become a ghost, unable to recover, after the 10g version, The drop command is just a rename operation, so it's easy to recover.

Flash-back Delete operation commands are simple

　　　　Sql> Flashback table Emp to before

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

　　Sql> Flashback table EMP to before drop rename 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, the 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, not enough space available, The data in the Recycle Bin is cleaned up, which is also the cause of the flash-back deletion failure.

　　　Sql> show RecycleBin;

Delete Table completely, flash back Delete also helpless

　sql> DROP TABLE EMP PURGE;

Empty the Recycle Bin

Sql> PURGE RecycleBin;

Note: The flash-back deletion is only for the drop command, and note that distinguishing between truncate and drop operations, truncate called table truncation, clears the data in the table (which adjusts the Oracle high watermark Implementation), and the table structure is not affected and is fast, The disadvantage is that this process will not produce any undo data or redo the log, if mistakenly deleted, restore abnormal trouble, to use caution. Drop deletes the data + table structure, and the flash-back deletion is only for drop operations.

Flash-back Data archiving (flashback-Archive)

Feature Description: Flash-back data archiving enables tables to have the ability to fall back to any point in time in the past, in the previous flash-back query, the Flashback table would be limited by whether the undo data was invalidated, and if the undo data was overwritten, the flashback would fail, and the flash-back deletion would be limited by whether there was enough free space in the tablespace and the flash-back data was archived, There is no such restriction.

　　Create a flash regression file

1. Create a table space for user flashback data archiving, and of course, you can use existing tablespaces.

sql> Create tablespace test_tb datafile ' test.dbf ' size 20m;

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

　　　Sql> Create Flashback archive TEST_FA tablespace TEST_TB 2 year;　

Enable flash return file for the EMP table under Scott User

1. Granting users the right to archive

　　Sql> Grant Flashback archive on TEST_FA to Scott;

2. Connect users

Sql> Conn Scott/tiger;

3. Enable the Flash return file for the EMP table

　　　sql> ALTER TABLE EMP Flashback archive Test_fa;

So far, the EMP table has the ability to query or retreat to any point in time over the past 2 years!

Flash Back (Flashback database)

Function Description: Flash back to the database can be the entire database back to a certain point in the past, Flashback table is a table of space-time shuttle, flash back to the database is the entire database of time and Space shuttle. Of course, all the work after the flash-back 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. Flashback to the database can cause downtime, of course, compared to the traditional backup recovery mechanism, the recovery process is much faster.

How it works: Flash-back databases do not use undo data, and use a different mechanism to preserve the recovery data needed for fallback, and when the flashback database is enabled, the changed blocks are continuously copied from the database buffer cache to the flashback buffer, then called the recovery writer (Recovery Writer the background process will flush the data to the Flash log file on the disk. The flashback process is an extract flashback log--> The process of copying a block image back to a data file.

Configure the Flash-back database (the flashback database requires the database to be in archive mode)

1. Specify the flashback recovery area, which is the location where the flashback logs are stored, but the Flashback recovery area is not just for storing flash logs, but many of Oracle's backup recovery technologies are used in this area, such as automatic backup of control files.

　Sql> alter system set Db_recovery_file_dest = '/flash_recovery_area ';

2. Specify the recovery area size

Sql> alter system set DB_RECOVERY_FILE_DEST_SIZE=4G;

3. Specify a flashback log retention time of 2 hours, that is, a flashback operation, you can return the database to any point in the first two hours

Sql> alter system set DB_FLASHBACK_RETENTION_TARGET=120;

4. Orderly shutdown Database--mount mode enable Flash-back database-Open database

sql> shutdown immediate;

sql> startup Mount;

Sql> ALTER DATABASE flashback on;

sql> ALTER DATABASE open;

So far, the flash-back database configuration is complete!

Using the Flash Back database feature

sql> shutdown immediate;

sql> startup Mount;

Sql> Flashback database to timestamp sysdate-60/1440;

sql> ALTER DATABASE open resetlogs;

Summarize

This article enumerates four types of flash-back technology, in which, flash-back query, including basic flash-back queries, flash-back tables and other technologies that rely on undo data (and a class of flashback technology for flashback transactions that can flash back operations on specified transactions, similar in principle, with the use of undo data to build SQL statements for reverse transactions), depend on the undo data, is naturally limited to the retention time of the undo data, which can cause a flash-back failure due to a data-overwritten revocation. Flash-back deletion is because the deletion of the table after the 10g version only behaves as a rename operation, introduces the concept of the Recycle Bin, but this Recycle Bin is only a logical division of the current tablespace, so it is limited by the available space in the current tablespace; The flash regression file provides the ability to query or rewind to any point in the past. The flashback database is a more extreme database recovery function, which is equivalent to an incomplete recovery and relies on a flashback log.

Thank you for reading, I hope to help you, thank you for your support for this site!