Flash back in oracle and flash back in oracle

Flash back in oracle and flash back in oracle

Overview:

Flash back is part of Oracle's powerful Database Backup recovery mechanism. When a logical error occurs in the database, flash back technology provides fast and minimal recovery (most flash back functions can be completed online in the database ). It should be noted that the flash back technology is designed to quickly restore logical errors. For errors caused by physical damage or loss of media, flash back technology is no longer available, we still need to use some advanced Oracle backup recovery tools such as RAMN (this is the essence of Oracle's powerful Backup recovery mechanism)

UNDO SEGMENT)

Before talking about Flashback Technology, You need to first understand a logical structure in Oracle-Undo segment. Most flash back technologies rely on the Undo data in the Undo segment. Undo data is the information required to reverse the DML statement results. As long as a transaction modifies the data, the original data before the update will be written into an undo segment. (Data in the Undo segment is also used for transaction rollback ). When a transaction starts, Oracle assigns it an undo segment. There is a many-to-one relationship between the transaction and the Undo segment, that is, a transaction can only correspond to one undo segment, multiple transactions can share one undo segment (but this usually does not happen when the database runs normally ).

Flash back Technology

Oracle provides four types of flash back technologies (flash back query, flash back deletion, flash back archiving, and Flash back database) available for use. Each of these technologies has different underlying architecture support, however, in fact, some of these four different flash back technologies have overlapping functions. You also need to select the most appropriate flash back function based on the actual scenario.

Flashback Query)

A. Basic flashback Query

Function Description: queries the database status in the past period.

Working principle: Oracle will extract the required revocation data (provided that the revocation is available, that is, the revocation data has not been overwritten) for rollback, But This rollback is temporary, only visible to the current session.

SQL> select * from dept as of timestamp to_timestamp ('2017-09-10 11:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
B. Flashback table

Function Description: rolls back a table to a previous time point.

Working principle: Similarly, Oracle will first query the revocation segment, extract all changes after a certain time point in the past, and construct SQL statements that reverse these changes for rollback. The flashback operation is a separate transaction, therefore, if you cannot flash back because of Undo data expiration or other reasons, the entire operation will be rolled back and there will be no inconsistency.

Steps:

1. to enable table flashback, you must first support row movement on the table. (setting an identifier in the data dictionary to identify this operation may change the row ID, that is, after a successful flashback of the same data, the primary keys are the same, but the row ID has actually changed)

SQL> alter table emp enable row movement;
2. Flashback table operations

SQL> flashback table dept to timestamp to_timestamp ('2017-09-10 11:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
A flashback table may fail in the following situations:

Database constraints are violated. For example, if you accidentally delete data in a sub-table, you want to use the flashback table technology to perform rollback, the record corresponding to the data in the parent table is also deleted. In this case, the flashback table operation fails due to violation of the foreign key constraint;

Undo data is invalid. For example, if the Undo data that is used to support the flashback operation is overwritten, the flashback table operation will naturally fail;

Flashback cannot span DDL, that is, the table structure has been changed between the flashback point and the current point. In this case, the flashback operation also fails.

Note: The above Flash back function is based on data revocation, and data revocation will be overwritten (Expired will be overwritten, and Active will not be overwritten, when you need to use these flash back functions to restore data (specifically, when you need to use the data-based flash back function), you can detect errors in the shortest time and perform the flash back operation in the first time, to maximize the success of the flash back function.

Flashback Drop)

Function Description: flashback deletes a table that has been dropped. The corresponding index and database constraints will also be restored (in addition to foreign key constraints)

Principle Description: The Drop command is actually the Rename Command. in earlier versions of Oracle (before 10 Gb), flashback deletion means that all references to the table are deleted from the data dictionary, although the data in the table may still exist, it has become a zombie and cannot be recovered. After 10 Gb, the Drop command is only a Rename operation, so it is easy to restore.

　　

Flash back delete operation execution command is very simple

SQL> flashback table emp to before
If the table name to be restored is already in use in the current system, you can also rename the table during flashback deletion.

SQL> flashback table emp to before drop rename to emp_new
You can also view the tables deleted by the current user through the recycle bin. each user has a recycle bin, which is a logical structure and is not an independent storage space, it exists in the current tablespace. Therefore, if other operations require space, such as creating a table, and there is not enough space available, the data in the recycle bin will be cleared, this is also the cause of the flash deletion failure.

SQL> SHOW RECYCLEBIN;
You can delete a table permanently.

SQL> DROP TABLE EMP PURGE;
Clear Recycle Bin

SQL> PURGE RECYCLEBIN;
Note: flashback deletion only applies to the Drop command. Note that the truncate operation and the drop operation are distinguished. truncate is called table truncation, which clears the table data (which is implemented by adjusting the Oracle high water level Line). The table structure is not affected, the speed is very fast. The disadvantage is that this process will not generate any undo data or redo logs. If it is deleted by mistake, it will be difficult to recover the exception and you should use it with caution. The Drop operation deletes the data + table structure, and the flash deletion operation only applies to the Drop operation.

Flashback Data Archive)

Function Description: flashback Data Archiving enables the table to be rolled back to any time point in the past. In the flash query mentioned above, flashback tables are limited by whether data revocation fails, if the Undo data is overwritten and overwritten, the flash-back operation will naturally fail. The flash-back deletion is restricted by whether the tablespace has sufficient available space, but the flash-back data archiving does not.

Create a flashback Archive

1. Create a tablespace for the user to flash back to the data archive. Of course, you can also use an existing tablespace.

SQL> create tablespace test_tb datafile 'test. dbf' size 20 m;
2. Create a flash archive with a retention period of 2 years

SQL> create flashback archive test_fa tablespace test_tb retention 2 year;
　　　

Enable flashback archiving for the emp table under the scott user

1. Grant archiving permissions to users

SQL> grant flashback archive on test_fa to scott;
2. Connect to the user

SQL> conn scott/tiger;
3. Enable flash back archiving for the emp table

SQL> alter table emp flashback archive test_fa;
Now, the emp table has the ability to query or roll back to any time point in the past two years!

Flashback Database)

Function Description: You can roll back the entire database to a certain time point in the past. A flashback table is a time-and-space shuttle of a table. A flashback database is a time-and-space shuttle of the entire database. Of course, all the work after the flash back point is lost. In fact, it is equivalent to the incomplete recovery of the database. Therefore, you can only open the database in resetlogs mode. Flash back to the database will cause downtime. Of course, the restoration process will be much faster than the traditional backup recovery mechanism.

Working principle: Flash back does not use data revocation. Another mechanism is used to retain the data to be restored. When flash back is enabled, the changed data block is constantly copied from the database buffer cache to the Flash buffer, which is then called the Recovery Writer) the background process will refresh the data to the Flash log file on the disk. The flash back process is a process of extracting the flash back log --> copying a block image back to a data file.

Configure the flash back database (the flash back database requires the database to be in the archive mode)

1. specify the flash recovery area, that is, the location where the flash back log is stored. However, the flash recovery area is not only used to store the flash back log. Many Oracle backup recovery technologies use this area, for example, automatic backup of control files will be stored in this area.

SQL> alter system set db_recovery_file_dest = '/flash_recovery_area ';
2. specify the size of the recovery area

SQL> alter system set db_recovery_file_dest_size = 4G;
3. Specify the retention time of the flash back log to 2 hours. That is, you can roll back the database to any time point in the previous two hours through the flash back operation.

SQL & gt; alter system set db_flashback_retention_target = 120;
4. Close the database in sequence-enable the flash back database in mount mode-enable the database

Copy code
SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

SQL> alter database open;
Copy code
So far, the configuration of the flash back database is complete!

　　

Use the flashback Database Function

Copy code
SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to timestamp sysdate-60/1440;

SQL> alter database open resetlogs;
Copy code
　　

Summary

This article lists four types of flash back technologies. Flash back queries, including basic flash back queries and flash back tables, all rely on data revocation. Another type of flash back technology is flash back transactions, A flash-back operation can be performed on a specified transaction. The principle is similar. The SQL statement used to reverse the transaction is constructed by means of data revocation.) depending on data revocation, the data retention time is naturally limited, A flashback may fail because the data is overwritten. Flash-back deletion is because the deletion of a table after 10 Gb is only represented as a rename operation. The concept of the recycle bin is introduced, but this recycle bin is only a logical partition of the current tablespace, therefore, it is limited by the available space of the current tablespace. The flash back archive function can be used to query or roll back to any time point in the past. The flash back database is a more extreme database restoration function, this is equivalent to Incomplete recovery, depending on the flash log.

The above article is reproduced Original: https://www.cnblogs.com/chengxiao/p/5860823.html