Oracle flashback-Flashback table

1. Description of the flashback Table Function
Restore the data table to a previous time point or SCN number.
Note: This function changes the current table and its affiliated objects to a previous state.

2. system parameters to be modified
Undo_management. The parameter must be "Auto" to ensure that the rollback segment uses the SMU mode.
Undo_retention, in seconds. Adjust the rollback time range.
Confirm System Parameters

Iii. Principles
The principles of the flashback table and the flashback query are roughly the same. It also uses the Undo information to restore the table object to a previous time point (a snapshot ), therefore, make sure that

Aum (automatic undo Management) has enough retention values. The flashback table technology is based on the content in the Undo segment, so it is limited by the undo_retenton parameter. Yes

To use the flashback feature, you must enable automatic undo tablespace management.

However, a flashback table is not equal to a flashback query. The differences are as follows:
Flash query only queries a previous snapshot and does not change the status of the current table.
A flashback table returns the restoration of the current table and its affiliated objects to the previous time point.

Iv. features:
1. online operations
2. restore any data to the specified time point (or SCN)
3. automatically restore relevant attributes
4. Distributed consistency
5. Data Consistency. All related objects are automatically consistent.

V. Syntax:
SQL> flashback table flashback_table_test to timestamp to_timestamp ('2017-12-29 16:44:17 ', 'yyyy-mm-dd hh24: MI: ss ');
SQL> flashback table flashback_table_test to SCN 662421;
SQL> flashback table flashback_table_test to timestamp to_timestamp ('2017-12-29 16:44:17 ', 'yyyy-mm-dd hh24: MI: ss') Enable triggers;

Vi. Prerequisites for using the flashback table
1. Common users must have the flashback any table system permission. The command is as follows:
2. The table has the select, insert, delete, and alter permissions.
3. the table must have row Movement (row movement ).

7. The following is a specific experiment:
SQL> show parameter undo

Name type value
----------------------------------------------------------------------------------------
Undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs1

1. Create the flashback_table_test table
SQL> conn HR/hr
Connected.
SQL> Create Table flashback_table_test as select * from employees;

Table created.

2. query the data volume in the flashback_table_test table.
SQL> select count (*) from flashback_table_test;

Count (*)
----------
107

3. Create indexes and triggers for the flashback_table_test table (the trigger is null and no operation is performed)
SQL> Create index ind_test on flashback_table_test (employee_id );

SQL> Create or replace trigger tr_test
After update on flashback_table_test
For each row
Begin
NULL;
End
Tr_test;
/

Trigger created

4. Record the current time point and try to restore to this time point
SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') time, to_char (dbms_flashback.get_system_change_number) SCN from dual;

Time SCN
----------------------------------------------------------------------------------------------------------------------
2012-12-29 16:44:17 662421

Oracle 10 Gb can be obtained through the following SCN.
SQL> select current_scn from V $ database;

Current_scn
-----------
662403

5. delete data from the flashback_table_test table.
SQL> Delete from flashback_table_test;

107 rows deleted.

SQL> commit;

Commit complete.

6. query the flashback_table_test after the data is deleted, and confirm that no data exists in the table.
SQL> select count (*) from flashback_table_test;

Count (*)
----------
0

7. Delete the index ind_test in the flashback_table_test table.

SQL> drop index ind_test;

Index dropped

8. Change the tr_test trigger

SQL> Create or replace trigger tr_test
After insert on flashback_table_test
For each row
Begin
NULL;
End
Tr_test;
/

Trigger created

9. Ensure the row movement function in the table

SQL> ALTER TABLE flashback_table_test enable row movement;

Table altered

10. Restore the flashback_table_test table to the recorded time point (or SCN). Because a trigger exists in the table, the keyword enable triggers is used;
SQL> flashback table flashback_table_test to timestamp to_timestamp ('2017-12-29 16:44:17 ', 'yyyy-mm-dd hh24: MI: ss') Enable triggers;

Flashback complete.

11. view the recovery result as follows: (the data is returned)
SQL> select count (*) from flashback_table_test;

Count (*)
----------
107

But the index does not return
SQL> select index_name from user_indexes where table_name = 'flashback _ table_test ';

Index_name

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

SQL> select object_name, status from user_objects where object_name in ('tr _ test', 'ind _ test ');

Object_name status
--------------------------------------------
Tr_test valid

SQL> set pages 0
SQL & gt; Set line 100
SQL> set long 2000
SQL> select text from user_source t where T. Name = 'tr _ test ';
Trigger tr_test
After insert on flashback_table_test
For each row
Begin
NULL;
End
Tr_test;

7 rows selected.

Summary:

1. In a real high-availability environment, flashback table is of little significance and limited. Therefore, the row migration function must be ensured.
2. Stop write operations during the flashback table process
3. You can use flashback table to flash the deleted table back to a previous time point, but the table index cannot be restored normally, because the drop index process does not record undo.
4. The recovered trigger itself is modified and does not change with the table flashback to the time point before modification. The keyword enable triggers can only ensure that the trigger status is normal, rather than content rollback.
5. Because the principle uses its undo information to restore its objects, the truncate data cannot be restored.
6. It is better to use flashback query to restore data.
7. the flashback table function cannot restore the content of the table to be truncate because the Undo information is not recorded during the truncate operation.

Other notes:
1. If a key value, such as a primary key, is reused before the flash-back delete operation, it will violate the primary key constraints and fail to flash back.
2. If the Undo information required for the flashback does not exist, a ORA-08180: No snapshot found based on specified time (snapshot based on specified time not found) error is thrown.
3. If a record affected by flashback is locked by another user, a ORA-00054: Resource busy and acquire with Nowait specified (resource busy) error is thrown.
4. Table definitions cannot change during flashback; otherwise, the ORA-01466: unable to read data-table definition has changed Error
5. If row movement is not enabled before flashback, The ORA-08189: cannot flashback the table because row movement is not enabled will be received
6. For a reference relationship, it is recommended to roll back the main table together, otherwise, will receive the ORA-02091: Transaction rolled back, ORA-02291 Error
7. In sys mode, tables cannot be flashed back.