Set a recovery point in Oracle 10gR2 for easy Flashback

The Flash query function introduced by Oracle 9i displays the query results of the previous time point. However, the table itself has not changed. Oracle 10 Gb enhanced the flashback function to permanently flash back a table. Now, Oracle 10g version 2 makes this process more convenient by adding a specified recovery point.

Generally, you provide a time label or system change number SCN for the flashback table statement), indicating how far you want to flash back. The number of flashback times is limited to the number of system storage revocation times .) The problem is that the time tag is just an approximate value, and an accurate SCN requires a special transaction to be identified. Before starting the update, you can apply the GET_SYSTEM_CHANGE_NUMBER process in DBMS_FLASHBACK to display the current SCN, but you must remember it.

You do not need to know the exact SCN for the application Recovery point. After the restoration point is set up, it restores the current SCN. Therefore, the restoration point name is equivalent to the corresponding SCN.

The syntax of the create restore point creation recovery POINT command is simple:

Create restore point restore_point_name;

The flashback any table or select any dictionary permission must be set up to a normal unguaranteed recovery point. Once the recovery POINT is no longer needed, the corresponding drop restore point command will delete it.

List A shows the correct permissions provided to OE mode, creates A recovery point, and creates a flashback table after an error UPDATE that misses the correct WHERE clause.

SQL> CONNECT / AS SYSDBA Connected. SQL> GRANT SELECT ANY DICTIONARY TO oe; Grant succeeded. SQL> CONNECT OE/OE Connected. SQL> -- Tables must have row movement enabled to flash back SQL> ALTER TABLE warehouses 2 ENABLE ROW MOVEMENT; Table altered. SQL> CREATE RESTORE POINT my_update; Restore point created. SQL> COLUMN name FORMAT a25 SQL> COLUMN time FORMAT a32 SQL> SQL> SELECT name, scn, time, guarantee_flashback_database 2 FROMv$restore_point; NAME SCN TIME GUA ------------------------- ---------- ------------------------------ MY_UPDATE 734934 10-AUG-06 03.32.29.000000000 PM NO SQL> UPDATE warehouses 2 SET warehouse_name = 'Bellevue, WA'; 9 rows updated. SQL> COMMIT; Commit complete. SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses; WAREHOUSE_ID WAREHOUSE_NAME ------------ ----------------------------------- 1 Bellevue, WA 2 Bellevue, WA 3 Bellevue, WA 4 Bellevue, WA 5 Bellevue, WA 6 Bellevue, WA 7 Bellevue, WA 8 Bellevue, WA 9 Bellevue, WA 9 rows selected.SQL> CONNECT / AS SYSDBA Connected. SQL> GRANT SELECT ANY DICTIONARY TO oe; Grant succeeded. SQL> CONNECT OE/OE Connected. SQL> -- Tables must have row movement enabled to flash back SQL> ALTER TABLE warehouses 2 ENABLE ROW MOVEMENT; Table altered. SQL> CREATE RESTORE POINT my_update; Restore point created. SQL> COLUMN name FORMAT a25 SQL> COLUMN time FORMAT a32 SQL> SQL> SELECT name, scn, time, guarantee_flashback_database 2 FROMv$restore_point; NAME SCN TIME GUA ------------------------- ---------- ------------------------------- MY_UPDATE 734934 10-AUG-06 03.32.29.000000000 PM NO SQL> UPDATE warehouses 2 SET warehouse_name = 'Bellevue, WA'; 9 rows updated. SQL> COMMIT; Commit complete. SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses; WAREHOUSE_ID WAREHOUSE_NAME ------------ ----------------------------------- 1 Bellevue, WA 2 Bellevue, WA 3 Bellevue, WA 4 Bellevue, WA 5 Bellevue, WA 6 Bellevue, WA 7 Bellevue, WA 8 Bellevue, WA 9 Bellevue, WA 9 rows selected. SQL> FLASHBACK TABLE warehouses 2 TO RESTORE POINT my_update; Flashback complete. SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses; WAREHOUSE_ID WAREHOUSE_NAME ------------ ----------------------------------- 1 Southlake, Texas 2 San Francisco 3 New Jersey 4 Seattle, Washington 5 Toronto 6 Sydney 7 Mexico City 8 Beijing 9 Bombay 9 rows selected. SQL> FLASHBACK TABLE warehouses 2 TO RESTORE POINT my_update; Flashback complete. SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses; WAREHOUSE_ID WAREHOUSE_NAME ------------ ----------------------------------- 1 Southlake, Texas 2 San Francisco 3 New Jersey 4 Seattle, Washington 5 Toronto 6 Sydney 7 Mexico City 8 Beijing 9 Bombay 9 rows selected.

List

Recovery points are stored in the control file, so they are still valid even if the database is disabled and installed. This means you can also use them in the flashback database command.

Author: Bob Watkins (OCP, mcba, MCSE, MCT) is a computer professional with 25 years of experience and has worked as a technical trainer, consultant, and database administrator.

(