Oracle Flashback Table flash recovery

Author: runming QQ: 226399587 http://blog.csdn.net/runming918
1. Restore Flashback Table from RECYCLEBIN
To put it simply, if we regard the flashback query as a recovery record, the flashback table is used to restore the table (because the record is stored as a table, the flashback table should also be considered as a recovery record, but compared with the flashback query, it has a larger granularity). At the same time, a new function called Recycle Bin is introduced in Oracle10g (mainly for tables and their associated objects, for example, what is the index constraint?) the deleted table is not actually deleted. Instead, it is renamed and put into recyclebin by modifying the data dictionary. If you want to restore objects in recycle bin, using flashback table is the easiest way. In addition, the flashback table also provides a method similar to the as of scn/timestamp in the flashback query, with the help of undo data, restore an existing table to the status at a specified time point or scn.
1. Restore Flashback Table from RECYCLEBIN
To restore the table in recyclebin, note the following statement: Flashback table [objName] to before drop. This obj_name can be the table name, it can also be an object table in recyclebin (multiple tables can be operated at the same time, and table names can be separated by commas). Because this function restores the deleted table, therefore, the official website also has another title: flashback drop.
Select t. original_name, t. type from recyclebin t;
You can query the content in the recyclebin of the recycle bin after the drop operation. To restore the data, run the following command:
Flashback table [objName] to before drop;
The Flashback table statement also prompts a rename to [newTBname] clause. If the table to be restored already has a table with the same name in the current schema, we recommend that you specify a new table name for the table to be restored by using the rename to clause during recovery, otherwise the database reports a ORA-38312 error.
Flashback table tbname to before drop rename to tbname_bak;
PS: The data instance details the difference between Oracle Purge and drop in another blog.
PS is another simple visualization method: Use PL/SQL to connect to the database. Click Recycle Bin and the content in the Bin is the record of your Recycle Bin. Find the record you want to Restore and right-click Restore.
2. Flashback Table recovery from UNDO
In some cases, the table we want to process is not accidentally deleted, but has been modified multiple times. We hope to reply to a previous time point, through the previous learning, you must say no. You can use the flashback query. That's right. The flashback query does. But the flashback query only queries the records, if you want to restore the data, you still need to write the corresponding insert or update statements. There may be a considerable number of where conditions for judgment. If not, the data that may be recovered is wrong. The boss is not standing in front of him. We don't need to show our skillful fingers at this moment. Therefore, we need a more efficient, rigorous, and simple way: flashback table tbname to scn/timestamp helps you achieve your dream.
The usage of Scn and timestamp should be familiar with the previous flashback query. The usage of the scn or timestamp specified in the flashback table is the same as that of the previous one.
Record the current system scn (if you do not know the exact scn, the restoration can only pass through the time, but as mentioned earlier, the time is not accurate. If the restoration is achieved by specifying the timestamp, you need to know the approximate time of the operation ).
We can use the as of scn/as of timestamp/Versions between and other Flashback query methods to query the time point of the data we want. Then use:
Flashback table tb_name to scn 235923450;
In this way, the data can return to the State of scn at this time point, which is very convenient, saving the tedious delete and update steps. We only need to find the desired time point.
3. Considerations for Flashback Table
A. based on the undo table recovery, the recovered table must be enabled row movement, otherwise the ORA-08189 error will be reported, about the row movement knowledge, in my Oracle Partition summary has introduced. To check whether row movement is enabled for a table, you can query (or all_tables, dba_tables) in user_tables. For example:
Select row_movement from user_tables where table_name = 'tb _ name ';
To enable or disable row movement for a table, use the following statements:
Alter table TB_NAME ENABLE/disable row movement;
B. For undo-based table restoration, pay attention to the ddl impact mentioned in the preceding constraints.
C. for undo-based table restoration, flashback table actually performs dml operations (dml locks will be applied to the operated table). Therefore, you need to pay attention to the impact of triggers on it. By default, flashback table to scn/timestamp will automatically disable triggers that are different from the operation table during execution. If you want to continue using trigger during this period, you can add the enable triggers clause after the flashback table.
D. The index is automatically maintained for undo-based table restoration, but the statistics are not restored to the specified time point.
E. flashback drop cannot restore the integrity of the reference table based on the recycle bin. This is easy to understand. After the table is deleted, it cannot be controlled whether it is modified by the reference table, therefore, if the table has a primary or foreign key constraint, the constraint is disable after restoration and must be manually processed by the dba.
F. Restore A table based on the recycle bin. The operated table must exist in the local management tablespace. Flashback drop cannot restore the table deleted in the dictionary management tablespace or the system table.
G. when a table is restored Based on the recycle bin, the associated objects of the recovered table, such as its index, are not automatically restored to the name before deletion, but the name automatically generated by the system, if you have naming rules for table index constraints, you must manually change the name of the index constraint after restoring the table.
H. when you delete a table, the materialized view that depends on the table is also deleted, but the materialized view is not placed in the recycle bin. Therefore, when you execute flashback table to before drop, you cannot restore the materialized view that depends on it. You need to manually create a new view by the dba.
I. compared with the deleted table, when the data file space is insufficient, oracle will first clear the index of the deleted table, therefore, if you execute the flashback table to before drop command and find that the index is missing, it indicates that you have missed the best recovery time.
J. the Flashback table command allows you to operate on multiple tables at the same time. table names can be separated by commas. If you specify multiple tables when executing a flashback table command, remember that a single flashback table is in the same transaction. Therefore, the recovery operations for these tables are either successful or failed.