Oracle Flash-Back table detailed

 

---description Flash back database

---Use the Flashback table to restore the contents of the table to a specific point in time past

---Recovering from a delete table

---Use a flashback query to view database content at any point in time

---Use the Flashback version query to view the versions of a row over a period of time

---to view transaction history or rows using a flashback transaction query

Restores the data in the table and its associated objects, such as indexes, constraints, triggers, and so on.

The so-called flashback table, is to return the table data to a certain point in history, such as fall back to the user mistakenly delete the data before the point of time, so that the data deleted by mistake back, during this operation, the database is still available and does not require additional space.

The Flashback table leverages the value of the data recorded in the undo tablespace before it is changed, so if the undo data is required to flash back to the table, because the retention time exceeds the value specified by the initialization parameter undo_retention, which causes the undo database data to be overwritten by other transactions, You cannot revert to the specified time unless the retention guarantee option is specified on the Undo table space.

With the Flashback table, you can restore a set of tables to a specific point in time without performing a traditional point-in-time restore operation.

When the database is online, the flashback table operation can be done in situ by simply fallback the changes made to the specified table and its related objects.

The Flashback table statement can be executed as a single transaction. All tables must be successfully flashed back, or the entire transaction will be rolled back.

· With the Flashback table, you can restore one or more tables to a specific point in time without restoring the backup

· Perform a flash-back table operation after retrieving data from a rollback segment table space

· Perform a flashback table operation requires Flashback table permission

· You must initiate a row movement on the table to perform the flashback operation

because the operation of the Flashback table modifies the data in the tables, it is possible to cause data rows to move. For example, a row of data is currently in a data block, and when the table is flashing back to a previous point in time, the row of data in the B data block, so in the operation of the flashback table, the data row from the current a data block to the B data block, so before the flash back to the table, you must start data row movement characteristics.

Flash Back table: Precautions

· The entire Flashback TABLE statement is executed in one transaction. Data manipulation Language (DML) mutex.

· all existing indexes are preserved. The deleted index is not recreated. The associated commit-time manifested view is also automatically preserved.

· FLASHBACK table Statements Flash back to the tables specified in the FLASHBACK table statement as long as they do not violate any table constraints. You cannot perform a flash-back table operation on system tables, remote tables, and fixed tables.

1. Experiment: Flash Back Table usage

[Email Protected]>conn Tyger/tyger
Connected.

[Email protected]>create Table Tyger as SELECT * from Scott.emp;
Table created.

[Email protected]>select count (*) from Tyger;
COUNT (*)
----------
-

[email protected]>select dbms_flashback.get_system_change_number from dual;  //view current system SCN

Get_system_change_number
------------------------
1045621

[email protected]>delete from Tyger where RowNum <5;
4 rows deleted.

[Email protected]>commit;
Commit complete.

[Email protected]>select count (*) from Tyger;
COUNT (*)
----------
Ten

[email protected]>alter table Tyger enable row movement; //Enable row Movement of table Tyger
Table altered.

[Email protected]>flashback table Tyger to SCN 1045621;
Flashback complete.

[Email protected]>select count (*) from Tyger;
COUNT (*)
----------
14

Lab 2: Enable the Flashback database

1. Set the flash recovery zone retention time 3 days

[Email protected]>show parameter Flashback
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
Db_flashback_retention_target integer 1440Db_flashback_retention_target used to define a time-limit unit is minutes

[Email protected]>alter system set db_flashback_retention_target=4320;

[Email protected]>show parameter Flashback
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
Db_flashback_retention_target integer 4320

2. Check the current flashback status, do not turn on flashback, turn on flash back (Mount status on flashback)

[Email protected]>select flashback_on from V$database;
flashback_on
------------------
NO

[Email Protected]>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.

[Email Protected]>shutdown Immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[Email Protected]>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 flashback, must be open in archive mode

[Email protected]>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

[Email Protected]>alter database flashback on;
Database altered.

4. Open the database to see the flashback status

[Email protected]>alter database open;
Database altered.

[Email protected]>select flashback_on from V$database;
flashback_on
------------------
YES

Lab 3: Application of Flashback database -----Experimental Reference Eygle

[Email Protected]>conn Tyger/tyger
Connected.
[Email protected]>select count (*) from Tyger;
COUNT (*)
----------
1

[Email protected]>select count (*) from test;
COUNT (*)
----------
14

[Email protected]>alter session Set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';
Session altered.

[Email protected]>select sysdate from dual;
Sysdate
-------------------
2014-03-11 15:47:14

[Email protected]>truncate table Tyger;
Table truncated.

[Email protected]>select sysdate from dual;
Sysdate
-------------------
2014-03-11 15:47:38

[Email protected]>truncate table test;
Table truncated.

[Email protected]>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 for Flash back.

First, flash the database back to the first point of time and open the database in Redo only mode:

[Email Protected]>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.

[Email protected]>flashback database to timestamp
2 To_timestamp (' 2014-03-11 15:47:14 ', ' yyyy-mm-dd hh24:mi:ss ');
Flashback complete.

[Email protected]>alter database open Read only; //With Read only reason: If the data recovery is not ideal, you can shut down the database to continue the recovery
Database altered.

[Email protected]>select count (*) from Tyger.tyger;
COUNT (*)
----------
1

[Email protected]>select count (*) from tyger.test;
COUNT (*)
----------
14

Data recovery is not ideal, proceed with recovery---Premise: Open database with Redo only

[Email Protected]>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.

[Email protected]>flashback database to timestamp
2 To_timestamp (' 2014-03-11 15:47:38 ', ' yyyy-mm-dd hh24:mi:ss ');
Flashback complete.

[Email protected]>alter database open Read only;
Database altered.

[Email protected]>select count (*) from Tyger.tyger;
COUNT (*)
----------
0

[Email protected]>select count (*) from tyger.test;
COUNT (*)
----------
14

If the data recovery confirmation is complete, you can open the database with Resetlogs and restore Re- work ------ Reset the log and no longer flashback to a point in time before Resetlogs

[Email protected]>alter database open resetlogs;
Database altered.

Oracle Flash-Back table detailed