The simplest Oracle data recovery select as of usage, oracle Data Recovery

The simplest Oracle data recovery select as of usage, oracle Data Recovery

You perform a Flashback Query by using a SELECT statementwith an as of clause. you use a flashback query to restrieve data as it existed at some time in the past. the query explicitly references a past time by menasof timestamp or SCN. it returns committed data that was current at that point intime.
You can perform a flashback query by executing a select statement with the as of clause to retrieve data that exists at a certain time in the past. A flashback query is used to reproduce existing data, this query explicitly references a time period or SCN number in the past. The data returned by the flash query is the data submitted at a certain time point in the past.

Potential uses of Flashback Query include:
It may be used in the flash-back query:
⊙ Recovering lost data or undoing incorrect, committed changes. For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
Recover lost data or cancel submitted errors. For example, if you accidentally delete or update a row and submit the row, you can immediately cancel the error.
⊙ Comparing current data with the corresponding data at some time in the past. for example, you might run a daily report that shows the change in data from yesterday. you can compare the individual rows of table data or find intersections or unions of sets of rows.
Compare the consistency between the current data and historical data. For example, you may need to generate a daily report for data update the previous day. You can compare each row of the table or find the intersection and Union of rows.
⊙ Checking the state of transactional data at a participant time. For example, you cocould verify the account balance of a certain day.
Check the status of transaction data at a specific time. For example, you can verify your account balance on a certain day.
⊙ Simplifying the application design, by removing the need to store some kinds of temporal data. By using a Flashback Query, you can retrieve past data directly from the database.
Remove temporary data stored as needed to simplify application design. You can directly obtain the data from the database through Flash query.
⊙ Applying the packaged applications such as report generation tools to past data.
Historical data of packaged applications (such as report generation tools)
⊙ Providing self-service error correction for anapplication, thereby enabling users to undo and correct their errors.
Provides self-service error correction for applications, allowing users to cancel or correct their errors.
 
Example:
 
SQL> conn/as sysdba;
Connected.
SQL> set pagesize 200
SQL> select * from scott. dept;
 
DEPTNO DNAME LOC
---------------------------------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Now let's add a piece of data and submit it:
 
SQL> insert into scott. dept values (50, 'error data', 'China ');
One row has been created.
 
SQL> select * from scott. dept;

DEPTNO DNAME LOC
---------------------------------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 Error Data CHINA
 
SQL> commit; (10:51:00)

Submitted.
Under normal circumstances, because the commit operation has been performed, rollback is no longer valid. What should I do if I want data before 10:51:00?
Flash back with timestamp:
SQL> select * from scott. dept as of timestamp to_timestamp ('2017-12-09 10:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
 
DEPTNO DNAME LOC
---------------------------------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Flash-back queries mainly rely on the undo data of tablespaces. If you want to trace data for a longer period of time, you need to set a large undo_tablespaces size and undo_retention.
 
If you want to directly update the status of the current table to a certain time in history, you can directly use the flashback Keyword:
 
 
SQL> alter table scott. dept enable row movement;

The table has been changed.
 
SQL> flashback table scott. dept to timestamp to_timestamp ('2017-12-09 10:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');

Flash back complete.
 
Note ]:
Flash back is not omnipotent. When a table has a large data size or too long time, if a large flash back space and time are not set, the flash back operation will fail and the following error may occur: