9i new features Flashback query application-------------for DML misoperation Recovery (2)

Recovery
With a dbms_flashback bag.



The Dbms_flashback package provides the following functions:



Enable_at_time: Sets the flashback query time for the current session

Enable_at_system_change_number: Set the Flashback query SCN for the current session

Get_system_change_number: Get the SCN of the current database

DISABLE: Turn off the current session's flash-back query



Such as:

Sql> select Dbms_flashback.get_system_change_number from dual;



Get_system_change_number

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

8053651

When a session is set to Flashback query mode, subsequent queries are based on the database state of that point in time or SCN, and the flash-back query automatically expires even if disable is not explicitly specified. Any DML and DDL operations are not allowed when the session runs in the flashback query state. If you want to use DML operations for data recovery, you must use the Pl/sql cursor (in fact, this provides us with a method of data recovery). Even if the session is running in flashback query mode, the Sysdate function will not be affected and will still return the current correct system time.

Let's use an example to illustrate how to use the Dbms_flashback package to recover data.

Assuming that all the data in the Scott.emp table has been deleted because of the misoperation, now we want to recover.

sql> Delete from EMP;

Rows deleted.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from EMP;

COUNT (*)

----------

0

Then execute the following SQL to create a stored procedure to recover the data

CREATE OR REPLACE PROCEDURE prc_recoveremp is

CURSOR C_emp is

SELECT * from Scott.emp;

V_row C_emp%rowtype;

BEGIN

Dbms_flashback. Enable_at_time (systimestamp-interval ' 1 ' Day);

OPEN c_emp;

Dbms_flashback. DISABLE;

LOOP

FETCH c_emp

into V_row;

EXIT when C_emp%notfound;

INSERT into Scott.emp

VALUES

(V_row. EMPNO,

V_row. ENAME,

V_row. JOB,

V_row. MGR,

V_row. HireDate,

V_row. SAL,

V_row.comm,

V_row. DEPTNO);

End LOOP;

Close c_emp;

COMMIT;

End Prc_recoveremp;

Sql> Execute prc_recoveremp;

Pl/sql procedure successfully completed.

Sql> Select COUNT (*) from EMP;

COUNT (*)

----------

14

By the end of this, checking the EMP table, you can see that all the data has been recovered.

Note: After we create the cursor in the stored procedure, we execute the dbms_flashback. DISABLE, only

So that we can do DML operations in this session. Otherwise, a ORA-08182 error is generated, in

Flashback mode, user cannot perform DML or DDL operations.

Above we have described how to apply Flashback query to restore DML errors, but are based on point-in-time (timestamp), in fact, although timestamp can be accurate to milliseconds, but because {Oracle every 5 minutes will produce the SCN Corresponding to a time record, that is, usually only the SCN, but every 5 minutes will record SCN and time} (this paragraph requires in-depth research), when the use of timestamp to do flashback is likely to produce deviations, 5 minutes for the reason is the table Sys.sm On_scn_time, we can have a look at:

The record for this table is 1440 lines, so you can take a few lines to see



THREAD time_mp TIME_DP SCN_WRP Scn_bas

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

1 1072772527 2003-12-30 0 8052536

1 1072772834 2003-12-30 0 8053330

1 1072773142 2003-12-30 0 8054053

1 1072773446 2003-12-30 0 8054845

As you can see, the timestamp of each row is about 5 minutes, and in fact, every 5 minutes, Smon deletes the oldest data and inserts the current information, which also calculates why no matter how big your undo RETENTION is, flashback Query can only take 5 days ( 1440*5/24/60). So flashback query based on the SCN is the most accurate

For example, take a look at:

Sql> select * from Lyb;



No rows selected

sql> INSERT into LYB values (1);



1 lines have been created.



Sql> commit;



Submit completed.



Sql> select Dbms_flashback.get_system_change_number from dual;



Get_system_change_number

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

8058302



Sql> Delete from Lyb;



1 rows have been deleted.



Sql> commit;



Submit completed.



Sql> select Dbms_flashback.get_system_change_number from dual;



Get_system_change_number

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

8058379



Sql> SELECT * from Lyb as of SCN 8058302

2;



Id

----------

1

Sql> SELECT * from Lyb as of SCN 8058379

2;



No rows selected

Sql>





So, based on the SCN recovery is able to achieve precision!

Of course, the problem that we obviously encounter is, if the real mistake is done, where do I record the SCN? Here is the design of another Oracle very useful tool, Logminer, Next introduction!



Note: The SYS user does not allow the execution of the Dbms_flashback package, resulting in a ORA-08185 error.

Flashback not supported for user SYS



Reference:

Otn.oracle.com

Asktom.oracle.com
Seraphim (Zhang) "Use flashback Query to recover the wrong operation data"