9i new features Flashback query application-------------for DML misoperation recovery (1)

Recovery
The application of flashback query for 9i new features-------------recovery of DML misoperation



Author: Liu Yingbo

Time: 2003-12-29

Mail:liuyingbo@126.com, please correct me.



Reprint please indicate the source and the author



Before 9i, if a DML error occurred, only through backup to complete the point-in-time recovery, 9i to provide a new feature flashback Query, we can apply this feature, can be easy to achieve recovery. Note, however, that flashback query is simply a mechanism for querying and does not really undo any data.



1. What is Flashback Query?

Using the Oracle Multi-version read-consistent feature, Undo is used to provide the data in the desired front mirror. Through this function, we can see historical data and even use historical data to fix errors caused by misoperation. You can retrieve the required data by specifying a time or SCN. {Uses Oracle ' s multiversion read-consistency capabilities to restore data by applying undo as needed. You can view and repair historical data, and your can perform queries on the database as of a certain wall clock time or US Er-specified System commit number (SCN).}

As you can see, Oracle already has flashback query in previous releases, which we called multiple versions of Read consistency before. (multiple versioning features provide a consistent view across multiple user sessions, Oracle does not perform dirty read)

2. Preparatory work

The best way: The database is in automatic Undo Management state (there are also articles that must be, I looked up the Oracle document applied the word prerequisite< prerequisites; At the same time on the asktom I also found a corresponding explanation, indicating that the best is the automatic undo management, manual undo management is also possible, and there are examples.

The maximum number of time periods that can be flashed back is specified by the undo_retention initialization parameter (in seconds), as shown below to execute the command

Sql> Show Parameter Undo



NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Undo_management string AUTO

Undo_retention Integer 600

Undo_suppress_errors Boolean FALSE

Undo_tablespace string UNDOTBS1

Sql>

This is a dynamically modified parameter that can be modified using alter SYSTEM SET undo_retention =<seconds>; to modify the parameter value

More obviously, you set a relatively large undo_retention, you have to set large enough undo ROLLBACK segments.

3. How do I use Flashback Query?

There are two ways to use flashback Query:

With SQL

A flashback query using as of the SELECT statement, the syntax is as follows:

As of the SCN (timestamp) expr



By using the keyword as of can flashback Query on a table, view, materialized view, you can develop a SCN or timestamp, where timestamp is present in 9i and can have milliseconds of time units, such as

Sql> select Systimestamp from dual;



Systimestamp

---------------------------------------------------------------------------

2 September-December-03 10.15.05.171000 pm +08:00



Here, let's do an example:

Sql> Connect Scott/tiger

is connected.

Sql> CREATE TABLE Test (ID number (1));



Table has been created.



sql> INSERT INTO test values (1);



1 lines have been created.



sql> INSERT INTO test values (2);



1 lines have been created.



Sql> commit;



Submit completed.



Sql> select * from test;



Id

----------

1

2



sql> Delete from test where id=1;



1 rows have been deleted.



Sql> commit;



Submit completed.

Sql> select * from test;



Id

----------

2

Sql> SELECT * FROM Test as of timestamp (Systimestamp-interval ' a ' second);



Id

----------

1

2

sql> INSERT INTO Test (SELECT * from Test as of timestamp (Systimestamp-interva)

L ' second) where ID =1);



1 lines have been created.



Sql> commit;



Submit completed.



Sql> select * from test;



Id

----------

2

1

Now, using flashback Query, we have recovered the records that were mistakenly deleted in the test table. Of course, we can use some other methods, similar to the establishment of an intermediate table, such as a small amount of data of DML misoperation recovery.