Use Flashback Query to restore malfunctions
Author: Kamus Seraphim (Zhang Leyi)
Date: 2003-10
Mail:
Kamus@itpub.net
Reprint, please indicate the source and the author
The new Flashback Query function in Oracle9i is updated for accidental deletion or mistakes and has commit
The situation provides a simple and fast recovery method, and it is only possible before Oracle provides flashback inquiry.
It can be doubtful if it is more troublesome than flashback query by backup.
What is Flashback Query
Using Oracle Multi-version reads consistent features, the data in the previous mirror is provided through UNDO when needed.
Using this feature, you can see historical data (huh, just like time backflow. Moonlight box?), Even historical data
To repair errors caused by misuse. The required data can be retrieved by specifying time or SCN.
Precondition
The database must be in the Automatic Undo Management state.
The time period of the maximum flashback query is specified by the undo_retention initialization parameter (in seconds)
You can modify the parameter value via the alter system set undo_retention =
How to use Flashback Query
SQL
Use the SELECT statement as the AS OF to flash back to query, the syntax is as follows:
SQL> Show parameter undo_retention
Name Type Value
----------------------------------- --- ---------------------------
Undo_retention integer 10800
SQL> Show parameter undo_management
Name Type Value
----------------------------------- --- ---------------------------
undo_management string auto
Use the AS OF keyword to perform Flashback Query on the table, view, or materialized view, if the SCN is specified,
Then the expr section must be a number. If timestamp is specified, then the expr must be a TimeStamp.
Type value. The result of the query will return data on the specified SCN or point in time.
Below we use the Scott solution to make an experiment.
[zhangley @ linux9 bin] $ sqlplus / nolog
SQL * Plus: Release 9.2.0.1.0 - Production on Th twu oscum 9 23:44:07 2003
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
SQL> Connect Scott / Tiger
Connected.
SQL> SELECT SAL from Emp where Empno = 7369;
Sal
------------
800
SQL> Update EMP SET SAL = 4000 WHERE EMPNO = 7369;
1 row updated.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT SAL from Emp where Empno = 7369; SAL
------------
4000
Remarks: TimeStamp (SystimeStamp - Interval '1' Day) refers to the query distance from the current time
The data of the time ago, if we have to query an hour ago, then you need to replace day to HOUR,
The query 10 '10' Minute will be replaced with '1' Day.
The above demonstrates the method of recovering the false updated field, but if you want to use AS in the subquery part of Update
Of then the query can only return a record, otherwise it will be an error. as follows:
SQL> SELECT EMPNO, SAL from EMP;
Empno Sal
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
SQL> SELECT SAL FROM EMP
2 as of timestamp (SystimeStamp - Interval '1' Day);
3 WHERE EMPNO = 7369;
Sal
------------
800
SQL> Update EMP SET SAL =
2 (Select Sal from EMP
3 as of timestamp (SystimeStamp - Interval '1' Day)
4 WHERE EMPNO = 7369)
5 WHERE EMPNO = 7369;
1 row updated.
SQL> SELECT SAL from Emp where Empno = 7369;
Sal
------------
800
SQL> commit;
7839 5000
7844 1500
7876 1100
Empno Sal
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> Update EMP SET SAL = 4000;
14 rows updated.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT EMPNO, SAL from EMP;
Empno Sal
---------- ----------
7369 4000
7499 4000
7521 4000
7566 4000
7654 4000
7698 4000
7782 4000
7788 4000
7839 4000
7844 4000
7876 4000
Empno Sal
---------- ----------
7900 4000
7902 4000
7934 4000
14 rows selected.
SQL> SELECT Empno, Sal from EMP
2 as of timestamp (SystimeStamp - Interval '1' Day);
Empno Sal
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
Empno Sal
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> Update Emp A set Sal =
SELECT SAL FROM EMP B
3 as of timestamp (SystimeStamp - Interval '1' Day)
4 where a.empno = b.empno);
As of timestamp (SystimeStamp - Interval '1' Day)
*
Error At Line 3:
ORA-00907: Missing Right Parenthesis
In fact, the above grammar is no problem, but it may be because of the particularity of the flashback query results in the above SQL error.
And this error Update has a large amount of data, the recovery is really flashback, for this situation I
We can have two processing methods, one is to use the dbms_flashback package, will be introduced later, another party
The law is still directly using SQL, but adding a temporary table as a transit, as follows:
SQL> CREATE TABLE Empsal_Temp As
2 SELECT Empno, Sal from EMP
3 as of timestamp (SystemStamp - Interval '1' Day);
Table created.
SQL> Update Emp A set Sal =
2 (Select Sal from Empsal_Temp B
3 where a.empno = B.Empno);
14 rows updated.
SQL> SELECT EMPNO, SAL from EMP;
Empno Sal
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
Empno Sal
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> commit;
COMMIT COMPLETE.
SQL> DROP TABLE Empsal_Temp;
Table Dropped.
In this way, we have completed the recovery of the error data. Cool !! Right?: D
Introduction 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
For example: Select dbms_flashback.get_system_change_number from dual;
Disable: Close the flashback query of the current session
After setting a session to flashback query mode, subsequent queries will be based on that time point or SCN
Database status, if the session ends, even if there is no clear designation of disable, flashback query will automatically lose
effect.
When the session runs in the flashback query state, no DML and DDL operations are not allowed. If you want to use DML
Operation to perform data recovery, you must use the PL / SQL cursor.
Even if the session runs in the flashback query mode, the sysdate function will not be affected, and will still return to the current
Indeed 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.
All recovery in the above examples are based on time points, and the SCN-based flashback query is described below.
Since there is already a time point recovery, why need SCN based on SCN, let's take an example.
[zhangley @ Linux9 ORINUX] $ SQLPLUS Scott / Tiger
SQL * Plus: Release 9.2.0.1.0 - Production On Sat Oct 11 02:26:20 2003
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.1.0 - Production
SQL> SELECT * from dept;
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
SQL> SET TIME ON;
02:26:50 SQL> INSERT INTO Dept Values (60, 'Flash', 'beijing');
1 row created.
02:27:53 SQL> commit;
COMMIT COMPLETE.
02:27:57 SQL> Delete from dept where deptno = 60;
1 row deleded.
02:28:19 SQL> commit;
COMMIT COMPLETE.
02:28:21 SQL> SELECT * from dept as of timestamp (systemestamp
Interval '1' Minute);
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
02:29:49 SQL> SELECT * from dept as of timestamp (SystimeStamp -
Interval '2' minute);
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
02:31:11 SQL> Select * from dept as of timestamp (SystimeStamp -
Interval '3' Minute);
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
02:31:19 SQL> SELECT * from dept as of timestamp (systemestamp
Interval '4' minute);
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
02:31:30 SQL> Select * from dept as of timestamp (SystimeStamp)
Interval '5' minute);
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
02:31:39 SQL> SELECT * from dept as of timestamp (systemestamp
Interval '6' minute;
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
We can find that we have found all the previous mirroes before 1 minutes to 6 minutes without finding the newly inserted deptNO = 60 record, although TimeStamp can be accurate to milliseconds, but it is clear that we are difficult to accurately
Time points positioning to milliseconds. As for why, Biti has discussed in a post on the forum, here is temporary
Do not pursue the cause of this result. In this case, using SCN-based flashback query is the most solving the problem
good idea.
Some of the test examples of the past are all in the dbms_flashback package after INSERT data.
Get_system_change_number function Returns the SCN at the time, then use the AS OF SCN to enter
This is impossible in practical applications because this function will not be run before the misoperation. So I
To use Logminer to analyze RedOg, get the error update or delete data at the time.
SCN.
For the installation and use of Logminer, please check the documentation yourself.
Below is an example in combining the Logminer flashback query, in order to describe it, it is assumed that the data is deleted to the current
Online Redo Log did not do LOG SWITCH, that is, we only need to analyze the current Active
Online Redo log is OK.
SQL> Connect / as sysdba
Connected.
SQL> SELECT B.MEMBER, A.STATUS from V $ log A, V $ logfile b where A.Group # = b.group #;
MEMBER STATUS
------------------------------------------------------------------------------------------ ------------
/oracle/oradata/oralinux/redo01.log inactive
/oracle/oradata/oralinux/redo02.log inactive
/ Oracle/oradata/oralinux/redo03.log current
SQL> EXECUTE DBMS_LOGMNR.Add_logfile (logfilename =>
'/oracle/oradata/oralinux/redo03.log' ,Options => dbms_logmnr.new);
PL / SQL Procedure SuccessFully Completed.
SQL> EXECUTE DBMS_LOGMNR.Start_logmnr (DictFileName =>
'/oracle/admin/oralinux/orcldict.ora';
PL / SQL Procedure SuccessFully Completed.
SQL> SELECT SCN, SQL_REDO from (SELECT * FROM V $ logmnr_contents where
SQL_REDO LIKE 'Delete%' Order by SCN DESC) WHERE ROWNUM <2;
SCN SQL_REDO
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------
543523 delete from "scott". "Dept" where "deptno" = '60' and "d
Now we have found the SCN of the removal of that record is 543523.
SQL> Select * from scott.dept as of scN 543523; deptno DName LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
60 Flash Beijing
Get it! We use SCN to flash back to query to find the data before deleting, at which point the SQL described above
Methods or DBMS_FLASHBACK methods can be restored.
Several attention:
1. Flashback Query is invalid for DDL operations (such as DROP), can only apply to DML error
(Update, Delete)
2. SYS users do not allow DBMS_FLASHBACK packages, will generate ORA-08185 errors,
Flashback NOT Supported for User Sys
3. May need to authorize other users to allow other users to execute dbms_flashback packages, need to be
Row: Grant Execute On DBMS_FLASHBACK TO SCOTT;
4. Flashback Query is not limited to data recovery, equally applicable to DSS and OLAP, such as need to query
The new order generated within the previous hour, then the AS OF can be used to get the query knots in two point points.
The difference in fruit.
5. If you use Logon Triggers, you can support all time points without changing the code.
Report query function.
6. Flashback Query's query speed relies on how much UNDO needs to be implemented, that is, how long it is to query
The front database snapshot, the smoother the speed of the backtrack, the slower the speed.
7. Flashback Query does not really undo any data, just a mechanism for a query.
8. Flashback Query will not tell you what changes have occurred until the end of the data, which is the function of logminer.
9. If there is a huge amount of data in the table that needs to be recovered, the flashback query will be an extremely expensive operation.
It is possible to make a time point recovery more efficient.
Reference: Oracle9i Database Concepts Release 2 (9.2)
Oracle9i Supplied PL / SQL Packages and Types Reference Release 2 (9.2)
Oracle9i SQL Reference Release 2 (9.2)
Flashback Query
Www.oracle-base.com
