Oracle Flash-Back table detailed

---Description flash back to the database

---Restore a table's contents to a specific point in time using the flash-back table

---recover from a deleted table

---Use a flashback query to view the contents of a database for any point in time

---use a flash-back version query to view versions of a row over a period of time

---View transaction history or rows using a flash-back transaction query

Advantages:

Flash-back technology fundamentally changes the recovery technology because it can only handle change data. When using this technique, the time spent recovering from an error equals the time spent making the error. When used with flash-back technology, it has a significant advantage over ease of use, availability, and restore time compared to media recovery.

The Flash back database uses a flashback log to perform a flashback. Flash-back deletion using the Recycle Bin. All other features use the restore data.

Flash back to the database-----This feature to undo changes that cause logical data corruption

That is, when a logical error occurs, the entire database can be rolled back to the point in time before the error,

The log files for the flashback database are not written by the traditional log Writer (LGWR) process, but by a new process called Recovery Writer (RVWR), which is automatically created and maintained by the RCWR process in the recovery area.

[Z Yo "http://www.2cto.com/kf/ware/vc/" target= "_blank" class= "Keylink" >vcmfjbgvadhlnzxigfl0kihbzic1lzg== "grep Rvwr|grep-v grep

Oracle 8414 1 0 14:33? 00:00:00 ORA_RVWR_ORCL

Scenarios where you can use the Flash back database include

1. User truncated table (trucate)

2. The system administrator mistakenly deleted the user

3. The user mistakenly executed a batch task, or the batch task script error, so that the data in multiple tables is confusing, we can not use the flash back table to restore

The basis of the flash-back database is the flashback log, which automatically creates a flashback log as long as we configure the Flash back database. At this point, as long as the data in the database changes, Oracle saves the old value of the data before it is modified in a flashback log, and when we need to flash back to the database, Oracle reads the logs from the flashback log and applies them to the database, returning the database to a point in time at history.

Flash back database: Reduce restore time

Flashback to a database is faster than a traditional point in time for restoring files and redo log files. As the size of the database increases, the length of time required to perform traditional point-in-time restores by restoring all data files becomes less realistic. When using a flashback database, because you do not need to restore the data files, the time to restore the database is proportional to the number of changes that need to be rolled back, rather than the database size.

The flash-back database is implemented by using a class of log files called Flash-back database logs. The Oracle database periodically logs the "front image" of a block of data in a flash back to the database log. In order to quickly roll back the data file to the time it takes to capture the flashback log (before the desired target time), you can reuse the front image of the block. Then, the changes in the Redo log file are applied to fill the interval. The flash-back database log is automatically created and managed in the Quick recovery area.

When you cannot use the Flashback database feature, you must return the database to a specific time using a incomplete recovery operation. After the flash-back database operation completes, you can open the database in read-only mode to verify that the correct target time or system change number (SCN) is being used. If not, you can flash back to the database again, or roll forward the database by performing a restore operation. Therefore, to undo the flash-back database operation, you must restore the database forward.

Note: The flash-back retention target does not guarantee that the flash back is available. If a file that must exist in the flash-back recovery area requires space, the flashback log may be deleted automatically.

After the flashback database operation completes, you must open the database by using one of the following methods:

· Verify that the correct target time or SCN is used in read-only mode

· Updates are allowed using the Resetlogs parameter

Flash Back Database limit: (Cannot use flash back database)

· The control file has been restored or recreated

· Table space has been deleted

· The data file has been shrunk

You cannot use the Flash back database to recover data files that have been deleted since the flash back target time.

The deleted data file is added to the control file and is marked offline, but does not flash back.

The flashback database cannot flash back to a data file that has shrunk since the flashback target time, and this data file must be taken offline before it can perform a flash-back operation.

Experiment 1: Enable the Flash back database

1. Set the flash-back recovery area retention time 3 days

Sys@orcl>show parameter Flashback

NAME TYPE VALUE

------------------------------------ -------------------------------- ------------------------------

Db_flashback_retention_target integer 1440//db_flashback_retention_target is used to define a time limit unit is minutes

Sys@orcl>alter system set db_flashback_retention_target=4320;

Sys@orcl>show parameter Flashback

NAME TYPE VALUE

------------------------------------ -------------------------------- ------------------------------

Db_flashback_retention_target integer 4320

2. View current flashback status, not open flashback, open Flashback (Mount status on Flash back)

Sys@orcl>select flashback_on from V$database;

flashback_on

------------------

NO

Sys@orcl>alter database flashback on;

ALTER DATABASE Flashback on

*

ERROR at line 1:

Ora-38759:database must is mounted by only one instance and not open.

Sys@orcl>shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sys@orcl>startup Mount;

ORACLE instance started.

Total System Global area 314572800 bytes

Fixed Size 1219160 bytes

Variable Size 121636264 bytes

Database buffers 188743680 bytes

Redo buffers 2973696 bytes

Database mounted.

3. Turn on flash back, must be in the archive mode

Sys@orcl>archive log list;

Database Log Mode Archive mode

Automatic Archival Enabled

Archive Destination Use_db_recovery_file_dest

Oldest online log sequence 29

Next Log sequence to archive 31

Current log Sequence 31

Sys@orcl>alter database flashback on;

Database altered.

4. Open the database and view the Flash back status

Sys@orcl>alter database open;

Database altered.

Sys@orcl>select flashback_on from V$database;

flashback_on

------------------

YES

Sys@orcl>

Experiment 2: Flashback database use-----Experimental reference Eygle step-by-step Oracle

Sys@orcl>conn Tyger/tyger

Connected.

Tyger@orcl>select Count (*) from TYGER;

COUNT (*)

----------

1

Tyger@orcl>select Count (*) from test;

COUNT (*)

----------

14

Tyger@orcl>alter session Set nls_date_format= "Yyyy-mm-dd hh24:mi:ss";

Session altered.

Tyger@orcl>select sysdate from dual;

Sysdate

-------------------

2014-03-11 15:47:14

Tyger@orcl>truncate table TYGER;

Table truncated.

Tyger@orcl>select sysdate from dual;

Sysdate

-------------------

2014-03-11 15:47:38

Tyger@orcl>truncate table test;

Table truncated.

Tyger@orcl>select sysdate from dual;

Sysdate

-------------------

2014-03-11 15:48:03

The flashback needs to be carried out in the Mount state, and the timestamp/scn/sequence can be specified to flash back.

First, flash the database back to the first point and open the database in redo only:

Sys@orcl>startup Mount;

ORACLE instance started.

Total System Global area 314572800 bytes

Fixed Size 1219160 bytes

Variable Size 130024872 bytes

Database buffers 180355072 bytes

Redo buffers 2973696 bytes

Database mounted.

Sys@orcl>flashback Database to Timestamp

2 To_timestamp (' 2014-03-11 15:47:14 ', ' yyyy-mm-dd hh24:mi:ss ');

Flashback complete.

Sys@orcl>alter database open Read only; For Read only reason: If the data recovery is not ideal, you can close the database to resume recovery

Database altered.

Sys@orcl>select Count (*) from Tyger.tyger;

COUNT (*)

----------

1

Sys@orcl>select Count (*) from tyger.test;

COUNT (*)

----------

14

Data recovery is not ideal, continue to restore---Premise: Redo only Open the database

Sys@orcl>startup Mount;

ORACLE instance started.

Total System Global area 314572800 bytes

Fixed Size 1219160 bytes

Variable Size 130024872 bytes

Database buffers 180355072 bytes

Redo buffers 2973696 bytes

Database mounted.

Sys@orcl>flashback Database to Timestamp

2 To_timestamp (' 2014-03-11 15:47:38 ', ' yyyy-mm-dd hh24:mi:ss ');

Flashback complete.

Sys@orcl>alter database open Read only;

Database altered.

Sys@orcl>select Count (*) from Tyger.tyger;

COUNT (*)

----------

0

Sys@orcl>select Count (*) from tyger.test;

COUNT (*)

----------

14

If data recovery confirmation is complete, you can open the database in Resetlogs, resume work------Reset the log, and no longer flashback to the point before resetlogs

Sys@orcl>alter database open resetlogs;

Database altered.

Oracle Flash-Back table detailed: http://blog.csdn.net/wanghui5767260/article/details/21084031