Differences between the flash-back query 9i and 10g of Oracle Flashback

Oracle has provided the Flashback Query function based on rollback segments starting from 9i, which can be used to restore wrong DML operations. in Oracle 10 Gb, the flash-back query has been greatly improved. It is no longer limited to flash-back queries. It can also be used to recover incorrect DDL (Drop) operations, flash-back tables, and Flash-back databases. the following describes the differences between the two versions:

Oracle9i flash back principle:

Flash query is provided in Oracle 9i, which is implemented by a new package DBMS_FLASH. you can use the flashback query to obtain the database image view at a certain time point before the misoperation DML (Delete, Update, Insert) in a timely manner. You can use the system time or system change number (SCN: system Change Number. the flash-back query function relies entirely on the automatic rollback segment Management (AUM). misoperation such as Drop cannot be recovered.

Because Oracle9i uses undo as the pre-image storage point of the flashback query, undo_management = auto and undo_retention should be set properly. there are still five minutes of problems. In 9i, flashback query based on time is very easy to have a large error, but fortunately, 10g has improved this. in fact, the main reason is that it takes 5 minutes for the 9i scn to synchronize with the time. That is to say, if the newly inserted data is less than 5 minutes, flashback query will be made based on the time. No data can be found.

1. Find the relationship between SCN and Time: SELECT to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss '),

To_char (DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SCN

FROM dual;

2. query data by time flashback: SELECT * FROM test AS OF TIMESTAMP

TO_TIMESTAMP ('2017-04-09 11:25:17 ', 'yyyy-MM-DD HH: MI: ss ');

3. Flash back data through SCN: SELECT * FROM test as of scn 23565583;

Bytes -------------------------------------------------------------------------------------------------------

Oracle10g flashback principle:

The Oracle Flashback Database feature allows you to use the SQL statement Flashback Database statement to roll the Database to the previous time point or SCN, without the need to restore the Database. flash back to the database can quickly return the database back to the time point before the misoperation or human error, such as the "undo" Operation in Word, you can quickly achieve time-based recovery without using backup. oracle creates a new Flashback Logs to record database Flashback operations. if you want to flash back to the database, you need to set the following parameters: 1. location where DB_RECOVER_FILE_DEST logs are stored ,.

2. DB_RECOVER_FILE_DEST_SIZE: size of the recovery zone. Oracle will automatically create a recovery zone when creating a database, but it is disabled by default. You need to execute the alter database flashback on command.

SQL> flashback database to time to_timestamp (xxx );

SQL> flashback database to scn xxx;

The Oracle Flashback Table feature allows the use of the Flashback Table statement to ensure that the data is flashed back to the previous time point of the Table. similar to the Flashback Query in Oracle 9i, the rollback segment information is used to restore one or more tables to a previous time point (one snapshot ). note that the Flashback Table is not equal to the Flashback Query. The Flashback Query only queries a previous snapshot point and does not change the status of the current Table, the Flashback Table changes the current Table and its affiliated objects to return to the previous time point.

Flashback table tablename to timestamp xxx or

Flashback table tablename to scn xxx

For example, SQL> flashback table test to timestamp to_timestamp ('2017-05-07 15:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');

Oracle Flashback Drop feature provides a function similar to the recycle bin function to restore accidentally deleted tables. when a table is deleted, Oracle 10 Gb does not immediately release the space occupied by the deleted table, but automatically rename the deleted table (to avoid repeated names of similar objects) and put it in the recycle bin. the so-called recycle bin is similar to the recycle bin in Windows. It is a virtual container used to store all deleted objects. The deleted objects in the recycle bin occupy the same space when they are created. if the deleted table needs to be restored, you can use the Flashback Drop function.

SQL> show recyclebin;

SQL> drop table test_drop;

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST_DROP BIN $ B + XkkO1RS5K10uKo9BfmuA ===$ 0 TABLE: 14: 30: 47

SQL> flashback table test_drop to before drop; or

SQL> flashback table "BIN $ B + XkkO1RS5K10uKo9BfmuA = $0" to before drop;

Finally, clear the recycle bin:

1. Clear a single table in the recycle bin: purge table test_drop

2. Clear the entire recycle bin: purge recyclebin

3. Clear different object recycle bin: purge user_recyclebin or purge dba_recyclebin

4. Completely delete a table: SQL> drop table test_drop purge; then it cannot be restored.