oracle-Flash Back Technology

Flash back Technology (flashback) starts with a 9i version of the flashback query, and is applied in the 10g version, and is enhanced in 11g

11g Flash back technology projects:

Flash Back Database technology

Enabling the database to roll back quickly to a previous time or to an SCN (System change number) is particularly useful for recovering a database from a logical error, and is the best choice for recovering a database when most logical damage occurs. This feature is not based on the undo data, but on the flashback log

Using a flashback database recovery is much faster than using a traditional recovery method, because recovery is no longer affected by the size of the database. In other words, the traditional recovery time (MTTR) is determined by the size of the data needed to be rebuilt and the size of the archive log to be applied, and the recovery time of the flashback database is determined by the number of changes that need to be backed up during the recovery process, rather than the size of the data file and archive log. The flashback database consists of the recovery writer (RVWR) background process and the Flashback database log.

Flash Back Recovery Area Configuration

For example, set the Flash recovery area size to 4G

Alter system set DB_RECOVERY_FILE_DEST_SIZE=4G Scope=both

Note: If you want to deactivate the Flashback recovery area, simply empty the parameter db_recovery_file_dest parameter value

Flash Back Database configuration

Requires that the database must meet the following configuration:

1. Setting up the database must be running in archive mode (Archivelog)

2. The database parameter db_flashback_retention_target specifies how long it takes to flash back to the database

3. Need to start the Flashback database function in Mount state using the ALTER DATABASE flashback on command

Change:

Connect system/xcn258 as Sysdba

Shutdown immediate

ALTER DATABASE Archivelog;

alter system set DB_FLASHBACK_RETENTION_TARGET=7200;

ALTER DATABASE flashback on;

ALTER DATABASE on;

Application of Flash back database technology

Example: Using a flashback database to achieve recovery of an SCN

First, query V$flashback_database_log view to get OLDEST_FLASHBACK_SCN;

This example: 1553800

Close the database; Shutdown immediate

startup Mount;

Flashback database to SCN 1553800

Finally, open the database with Resetlogs

ALTER DATABASE open resetlogs;

Flash Back Table Technology

　　

　　A flashback table is a flashback technique that restores or sets a table to a specific point in time without requiring incomplete recovery. When using the Flashback table, all related objects can be recovered. The Flashback table technique is based on the revocation data, so to get back to the table at a point in time, you must make sure that the parameters associated with the Undo table space are set appropriately. Related to parameter undo_management,undo_tablespace,undo_retention settings

Flash Back Table parameters

--enable triggers: Indicates that the trigger is enabled after recovery and defaults to the Disable state

--to before drop: Indicates revert to before deletion

--rename to table: Indicates a replacement table name

Flash Back Table Application

First create the sample table, then delete some rows, and finally restore the data with the Flashback Table command

In Scott mode, create a table dept2

Connect Scott/tiger

CREATE TABLE Dept2 as SELECT * from dept;

Set time on

Delete from dept2 where deptno=30; (18:58:10)

Commit

ALTER TABLE DEPT2 enable row movement; (must be opened first)

Falshback table Dept2 to timestamp to_timestamp (' 2016-03-20 18:58:10 ', ' yyyy-mm-dd hh24:mi:ss ');

Flash-Back Discard technology

　　

　　A flashback drop is a backup of the discarded database objects and their associated objects in the Recycle Bin so that they can be recovered in a timely manner if necessary. The discarded objects are not deleted from the database until the Recycle Bin is emptied, which allows the database to recover the tables that were deleted by accident or by mistake

The Recycle Bin is the logical memory of all discarded tables and their associated objects, and when a table is discarded (drop), the Recycle Bin stores the table and its associated objects in the Recycle Bin. Associated objects stored in the Recycle Bin include indexes, constraints, triggers, nested tables, large binary object LOB segments, and LOB index segments

The Oracle Recycle Bin records the user's drop action in a system table, writes the deleted object to a data dictionary, and clears the Recycle Bin space using the Purge command when it is confirmed that the deleted object is no longer needed

To avoid the same name: in the Recycle Bin, the deleted object name is converted: bin$globaluid$version Globlauid a globally unique, 24 character-length object (not related to the name of the object before it was deleted)

Recycle Bin Application

such as: give data preparation, delete table, view Recycle Bin information, restore and query after recovery situation

Connect Scott/tiger

CREATE TABLE Dept_copy as SELECT * from dept;

SELECT * from tab (view table information from DD)

drop table dept_copy;

Select Object_name,original_name from User_recyclebin; (View the table you just deleted from the Recycle Bin)

Flashback table Dept_copy to before drop;

Flash back version Query technology

　　Mainly rely on AUM (automatic undo Management), AUM refers to the use of the revocation table space records to increase, delete, change the method of data

Select Version Query

　　

Flash Back Transaction Query technology

　　A flashback transaction query is a diagnostic tool that helps identify transaction-level changes that occur in a database and can be used for data analysis of transactional audits. With flashback transaction analysis, you can identify all changes that have occurred during a specific time period, or you can perform transaction-level restores of database tables

A flashback transaction query is based on the revocation data, and it also uses the initialization parameter Undo_retentio to determine how long the revoked data has been committed in the database.

The Flashback version query above can be used to audit all changes to a table over time, but this is just a matter of discovering what has been done in a certain period of time and cannot be undone for the wrong transaction. Flashback transaction query enables undo processing because the historical operation and undo statements of a transaction can be obtained from the Flashback_transaction_query view, that is, you can audit what a transaction does, You can also revoke a transaction that has already been committed.

Flash Back Data archiving technology

Flashback data Archive, this technique differs from the many flashback techniques described above (except for the flashback database, where other flashback technologies rely on undo undo data), which is distinguished from undo by storing the changed data in the created Flash regression file area So that you can set up a separate storage policy via the flashback file area so that you can flash back to the old data before the specified time without affecting the undo policy. Also, you can specify which database objects need to save historical change data as needed, rather than saving the change data for all objects in the database, which can significantly reduce space requirements

Flashback Data archive is not a record of all changes to the database, but only the changes to the specified table. So flashback data Archive is the protection of objects and is a beneficial complement to flashback database.

oracle-Flash Back Technology