Set a recovery point in Oracle10gR2 to easily flash back

Source: Internet
Author: User
Welcome to the Oracle community forum and interact with 2 million technical staff to access the flash query function introduced by Oraclehrefsearch.e800.com. cnoracletarget_balkaltoracle9i to display the query results of the previous time point. However, the table itself has not changed. Oracle10g enhances the flash back function.

Welcome to the Oracle Community Forum, interact with 2 million technicians> go to Oracle href = "http://search.e800.com.cn/Oracle" target = _ blank alt = "Oracle"> the flashback query feature introduced by Oracle 9i displays the query results for the previous time point. However, the table itself has not changed. Oracle 10g enhanced the flash back function

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Href = "http://search.e800.com.cn/Oracle" target = _ blank alt = "Oracle">Oracle9i introduces the flash back query function to display the query results of the previous time point. However, the table itself has not changed.Oracle 10gEnhanced the flashback function to permanently flash back a table. Now Oracle 10 Gb Second Edition adds the specifiedRestorePoint to make this process more convenient.

Storage

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 command is simple:

Create restore point restore_point_name;

The flashback any table or select any dictionary permission requires 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 (installed but not enabled. 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.