Oracle 11g R2 Flash back Technology

To enable the database to recover quickly from any logic error, Oracle has introduced flash back technology. With this technique, the data changes of row-level and thing-level can be recovered, the time of data recovery is reduced, and the operation is simple. The recovery of data can be realized by SQL statement, which greatly improves the efficiency of database recovery. Flashback technology is a major advance in the history of database recovery technology, fundamentally changing data recovery.

Flash back technology includes the following:

Flashback query: (FLASHBACK query): Data information in a table when querying a past point in time or an SCN value

Flashback version query: Queries for data changes in a table in the past time period or in a SCN segment FLASHBACK.

Flashback to a thing query (FLASHBACK Transaction query): View a thing or all the changes that have been made to the data over time.

Flashback Database (FLASHBACK): The state at which a database was restored to a previous point in time or an SCN value

Flashback Delete (FLASHBACK drop): Restores the deleted tables and their associated objects to their pre-deleted state.

Flashback tables (FLASHBACK table): Restores the table to a previous point in time or to the state of an SCN value.

The SCN is a number that accumulates incrementally as the DBMS is automatically maintained after an Oracle database is updated. The current SCN number can be obtained by querying the CURRENT_SCN in the data dictionary v$database.

What the flashback recovery zone means
Oracle recommends specifying a flashback recovery area (Flashrecovery AERA) as the default location for backup-to-recovery, so Oracle can automate disk-based backup and recovery. The Flashback recovery area is a storage space used to store recovery-related files, allowing users to centrally store all recovery-related files. The following files can be stored in the Flashback recovery area.

Control files

Archive log files

Flash back Log

Automatic backup of control files and SPFile

Rman Backup Set

Data file copy

The Flashback recovery area is set up and managed primarily by the following 3 initialization parameters

Db_recovery_file_dest: Specify the location of the Flashback recovery area

Db_recovery_file_dest_size: Specify the free space for the flashback recovery area

Db_flashback_retention_target: This parameter is used to control the time of data retention in the flashback log, or the earliest point in time that you want the database to revert back to. The unit is min, the default is 1440min, that is, one day. Of course, the time to rollback is also dependent on the size of the flashback recovery area, because it holds the flashback log required for fallback, so this parameter should be modified with db_recovery_file_dest_size.

If you want to undo the flashback recovery area, empty the value of the initialization parameter db_recovery_file_dest.

Db_recovery_file_dest_size can only be emptied after db_recovery_file_dest is emptied.

Setting up the Flash back database
Set the flashback recovery area, to start the Flashback database function, further configuration is required, the data must be in archive mode, set the Flash back database

The database is already in archive mode:

Database is not enabled for Flash back database

Setting up the Flashback zone

Set the data retention period for the flashback database to one day in min

Enable Flashback Log

Query whether the Flashback recovery area was successfully enabled

Whether the query successfully enabled the Flashback database

Flash back to the database

The flashback database makes it possible to quickly roll back data to a previous point in time or to a SCN, which is particularly useful for recovering a database from a logical error. It is also the best choice for recovering a database when logical corruption occurs.

Use SCN to flash back to database

View database System current SCN

Changes the current state of the database, simulates creating a table TEST10, and inserts a record

Perform a flashback database recovery to restore the database to the state before the table was created, that is, the SCN is 1238544

Open the database with the Resetlogs option

Verify the status of the database and the TEST10 table does not exist.

Querying the current oldest flashback SCN and time in the database

Flash back to the database at a specified time

Querying the current time and current SCN in the database

Change the current state of the database, simulate creating table test11, and insert 1 records

Perform a flashback database recovery to restore the database to the state before the table was created.

Open a database using Resetlogs

Verify the status of the database test11 table exists.

Limitations of Flashback Database operations:

Media failures such as corrupted or missing data files cannot be recovered using the flashback database. The flashback database can only be based on data files that are currently running correctly

After the flashback database function starts, you cannot use the flashback database if a data control file rebuild occurs or a backup recovery control file is used

Cannot use flashback database for data file shrink operations

You cannot use the Flashback database to restore the database to the SCN prior to the earliest SCN available in the Flashback log, because the flashback log file is deleted under certain conditions, not always in the Flashback recovery area

Flash back Table
The flashback table, which restores the table to a certain point in time or the specified SCN without recovering the data file, provides the DBA with an online, quick, and convenient way to recover from the error of modifying, deleting, inserting, and so on.

     利用闪回表技术恢复表中的数据的过程，实际上是对表进行DML操作的过程。oracle自动维护与表相关联的索引、触发器、约束等。

In order to use the database Flashback feature, the following conditions must be met

The user has Falshbackany table system permissions, or Flashback object permissions with the action table

User has Select/insert/delete/alter object permissions on the action table

Starting the row movement attribute of the manipulated table can be done in the following ways:

sql> ALTER table name ENABLE rowmovement;

Flash back table Syntax format:

FLASHBACK TABLE [schema].table to SCN | TIMESTAMP expression [enable| DISABLE TRIGGERS]

Parameter description:

SCN: The state when the table is restored to the specified SCN

TIMESTAMP: Restore a table to a specified amount of point in time

enable| Disabletriggers: In the process of recovering data in a table, the trigger on the table is disabled or activated (by default it is a reference)

To illustrate:

Log in with a Scott user

Create a table, insert a record, commit a thing

Queries the current SCN number and, if the current user does not have permission to query V$database, logs in with the SYS user, granting the current user access to the data dictionary.

Update records, and submit things

View records in a table

Delete a id=3 record

Start the row movement feature of the test01 table

Restore the test01 table to the state of 2015-11-22 05:44:48

Restores the test01 table to a state where the SCN is 1240503.

Flash back Delete
Flashback removal can recover a table deleted using the DROP TABLE statement, which is a table recovery mechanism for accidental deletion. The implementation of the flashback removal feature is achieved primarily through the "Recycle Bin" technology in the Oracle database. In an Oracle database, when a drop table operation is performed, the space of the table and its associated objects are not immediately retracted, but instead they are renamed and placed in a logical container called the Recycle Bin until the user decides to permanently delete them or if the table space or storage space for the table is insufficient. The table is actually deleted, in order to use the flash-back deletion technology of the database, you must turn on the Recycle Bin of the database

Start the Recycle Bin to set the parameter RecycleBin to on, by default the Recycle Bin is started

If you do not have a startup you can use:

View the Recycle Bin. When a drop table is executed, the table and associated objects are named and saved in the Recycle Bin, and the deleted table and its associated objects can be obtained by querying the User_recyclebin dba_recyclebin view.

Viewing a deleted table by User_recyclebin

SYS users and system users are not supported, and after the two user tables are deleted, they cannot be retrieved from the Recycle Bin, and the query is "empty"

If a purge phrase is used when deleting a table, the table and its associated objects are directly freed, the space is recycled, and the information is not entered in the Recycle Bin

Emptying the Recycle Bin, because its associated object information is saved in the Recycle Bin at the deleted table level, its storage space is not released, so the Recycle Bin needs to be emptied periodically. or clear the Unused object (table, index, tablespace) in the Recycle Bin to free the disk space it occupies

The syntax is as follows:

PURGE [table name | Index index]

[RecycleBin | Dba_recyclebin] | [Tablespacetablespace [user User]]

Parameter description:

Table: Clears the specified table from the Recycle Bin

Index: Clears the specified index from the Recycle Bin and reclaims its disk space

RecycleBin: Empty the user Recycle Bin and reclaim disk space for all objects

Dba_recyclebin: Empty the Recycle Bin for the entire database system, only users with SYSDBA permissions can use the

Tablespace: Clears the specified amount of space in the Recycle Bin and reclaims disk space

User: Clears objects for specific users in the specified tablespace in the Recycle Bin and reclaims disk space

Flashback delete operation, basic syntax for flashback deletion

FLASHBACK TABLE [schema.] Table to Beforedrop [RENAME to table]

Flash back Delete

Example of flashback removal:

Note: Only locally managed tables in non-system table spaces can use the flashback delete operation.

Flash back Query
Allows data to be viewed based on point-in-time timestamp or SCN, in addition to viewing old data, and retrieving old data to undo erroneous changes when needed.

Using the Scott user login, a flashback query based on the timestamp of the EMP table

Sql> Show user;

USER is "SCOTT"

Update salary for employee number 7900, update to 2000, and submit things

Update the salary for employee number 7900, update two times, submit things.

Update salary for employee number 7900, update to 3500, and submit things

Check the post-update salary for employee number No. 7900.

What is the salary for the first one hours of the No. 7900 employees?

Query the first thing submitted, the second thing has not been submitted when 7900 employees wages

Query the second thing submitted, the third thing has not been submitted when the employee's salary of No. No. 7900

If you want, you can restore the data to a state at a certain point in the past.

If you are interested in this, please scan the QR code below for free for more details

Oracle 11g R2 Flash back Technology