Use dbms_flashback package
The DBMS_FLASHBACK package provides the following functions:
Enable_at_time: Set the flashback query time of the current session
Enable_at_system_change_number: Set the flashback query SCN of the current session
GET_SYSTEM_CHANGE_NUMBER: SCN gets the current database
Disable: Close the flashback query of the current session
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, the subsequent queries will be based on the time point or SCN database status. If the session ends, the flashback query will automatically invalid even if there is no clear designation of disable. Whether DML and DDL operations are not allowed when the session runs in the flash query state. If you want to use DML to perform data recovery, you must use a PL / SQL cursor (in fact, here is a method for providing us with a data recovery). Even if the Session runs in the flashback query mode, the sysdate function will not be affected, and still returns the currently correct system time.
Here we use an example to show how to use the dbms_flashback package to recover data.
Suppose all the data in the Scott.emp table is deleted due to false operation, now we have to recover.
SQL> Delete from EMP;
14 rows deleded.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT Count (*) from EMP;
Count (*)
------------
0
Then do the following SQL to create a stored procedure for recovery 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
At this success, check that the EMP table can see all the data has been restored.
Remarks: DBMS_FLASHBACK.DISABLE will be executed after we created a cursor during the stored procedure.
In this way we can do DML operations in this session. Otherwise, ORA-08182 errors will be generated, in
Flashback Mode, User Cannot Perform DML OR DDL Operations. Above we have already introduced how to apply Flashback Query to restore DML's misoperation, but all based on timestamp, actually, although timestamp can be accurate to milliseconds, but because {Oracle will be generated every 5 minutes. The SCN corresponds to a TIME to record, that is to say only the SCN is only recorded, but the SCN and TIME} will be recorded every 5 minutes (this paragraph requires deep exquisite). When using TimeStamp to make Flashback, it is possible to produce a deviation. 5 minutes from the table is the table sys.smon_scn_time, we can check it out:
The record is a total of 1440 lines, and there are several 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
It can be seen that each line of timestamp is about 5 minutes, in fact, every 5 minutes, SMON deletes the oldest data and inserts current information, this can also calculate why you don't have your undo recuery, Flashback Query Only 5 days (1440 * 5/24/60) can be used. So SCN-based Flashback Query is the most accurate
For example, take a look:
SQL> SELECT * from LYB;
Unselected
SQL> INSERT INTO LYB VALUES (1);
It has created a row.
SQL> commit;
Submitted.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from DUAL
GET_SYSTEM_CHANGE_NUMBER
---------------------------------------------------------------------------------------------------------------------------------------
8058302
SQL> Delete from LYB;
1 line has been deleted.
SQL> commit;
Submitted.
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 ;
Unselected
SQL>
Therefore, SCN-based recovery is able to do accurate!
Of course, we obviously encountered problems is that if you really misunderstand, will I record SCN? Here is a tool for another Oracle, Logminer, next to introduce!
Note: SYS users do not allow DBMS_FLASHBACK packages, which will generate ORA-08185 errors,
Flashback NOT Supported for User Sys Reference:
OTN. Oracle.com
AskTom.Orcle.comseraphim (Zhang Leyi) "Data of Flashback Query Restore Mrror"
