select from flashback oracle 恢复数据,使用Flashback Transaction方法来恢复数据表数据

进行精细粒度的数据误操作还原，是我们在实际工作中经常遇到的场景。Oracle基于Redo Log和Undo机制，提供实现了诸多分支技术，如Flashback、Log Miner等来进行多粒度的数据恢复。在Oracle 11g中，dbms_flashback.transaction_backout方法提供了在数据库online状态下，直接逆回数据库事务和相关依赖事务的能力。

本篇主要介绍如何使用logminer和Flashback包新方法，来实现Oracle事务的逆回操作。

1、环境介绍

笔者使用Oracle 11g进行测试，版本为11.2.0.4。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE11.2.0.4.0Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

由于需要使用Logminer组件，所以数据库层面需要切换到归档模式，同时启动最小数据级别的补充日志(Supplemental Log)。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size2253664 bytes

Variable Size973081760 bytes

Database Buffers620756992 bytes

Redo Buffers7319552 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

启动数据库进入read write状态。

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence38

Next log sequence to archive40

Current log sequence40

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_MIN

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

YES

2、实验数据构建

为了有一个干净的数据环境，全新创建一个用户Test，进行测试。

SQL> create user test identified by test;

User created

SQL> grant connect, resource to test;

Grant succeeded

构建数据表emp，插入部分数据作为初始状态。

SQL> create table test.emp as select * from scott.emp where 1=0;

Table created

SQL> select * from test.emp;

EMPNO ENAMEJOBMGR HIREDATESALCOMM DEPTNO

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

SQL> desc test.emp;

NameTypeNullable Default Comments

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

EMPNONUMBER(4)Y

ENAMEVARCHAR2(10) Y

JOBVARCHAR2(9)Y

MGRNUMBER(4)Y

HIREDATE DATEY

SALNUMBER(7,2)Y

COMMNUMBER(7,2)Y

DEPTNONUMBER(2)Y

SQL> insert into test.emp values (10,'AAA','STF', null,sysdate-10000,1000,100,'10');

1 row inserted

SQL> insert into test.emp values (20,'BBB','STF', 10,sysdate-10000,500,100,'10');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test.emp;

EMPNO ENAMEJOBMGR HIREDATESALCOMM DEPTNO

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

10 AAASTF1988/2/5 131000.00100.0010

20 BBBSTF10 1988/2/5 13500.00100.0010

此时，SCN时间点如下，作为工作的起始时间点：

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

1795785

之后进行了一系列的DML操作。

SQL> insert into test.emp values (30,'CCC','STF', 10,sysdate-10000,500,100,'10');

1 row inserted

SQL> insert into test.emp values (40,'DDD','MANG', null,sysdate-10000,5000,1000,'10');

1 row inserted

SQL> insert into test.emp values (50,'EEE','STF', 10,sysdate-10000,500,100,'10');

1 row inserted

SQL> insert into test.emp values (60,'FFF','STF', null,sysdate-20000,5000,100,'10');

1 row inserted

SQL> commit;

Commit complete

SQL> update test.emp set comm=1000 where empno=50;

1 row updated

SQL> commit;

Commit complete

SQL> update test.emp set comm=1000 where empno=60;

1 row updated

SQL> commit;

Commit complete

操作之后，数据库时间点如下：

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

1795891

数据肯定发生了变化，现在实验目标是将数据恢复回去，恢复到SCN=1795785时间点Emp数据表的状态。

3、数据恢复实验

首先，需要创建一个数据表changed_tables，记录下从Log Miner中抽取出的与数据表EMP相关的事务信息。

SQL> create table changed_tables (table_name varchar2(256), xid raw(8), scn number);

Table created

SQL> desc changed_tables;

NameTypeNullable Default Comments

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

TABLE_NAME VARCHAR2(256) Y

XIDRAW(8)Y

SCNNUMBERY

创建一个Stored Procedure，用于从Log Miner视图中将相关事务操作保存在changed_tables中。

SQL> CREATE OR REPLACE PROCEDURE extract_txn_ids (lcrscn IN NUMBER, escn in number) AS

2lname VARCHAR2(256);

3vsql varchar2(2000);

4BEGIN

5dbms_logmnr.start_logmnr(startscn => lcrscn,

6endscn => escn,

7OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.CONTINUOUS_MINE);

8insert into changed_tables

9select distinct seg_name,xid,scn

10from v$logmnr_contentswhere seg_owner = 'TEST'

11and scn >= lcrscn

12and scn<= escn;

13commit;

14END;

15/

Procedure created

执行存储过程，输入起始和截止操作的SCN时间点。

SQL> exec extract_txn_ids(1795785,1795891);

PL/SQL procedure successfully completed

获取到的数据结果。

SQL> select * from changed_tables;

TABLE_NAME XIDSCN

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

EMP02001500640700001795812

EMP06000D00E30500001795883

EMP04000D00BC0400001795877

EMP02001500640700001795827

EMP02001500640700001795844

EMP02001500640700001795835

6 rows selected

创建第二个存储过程，逐事务调用dbms_flashback.transaction_backout方法。

SQL> create or replace procedure txn_backout(sscn in number)

2as

3txn_array sys.xid_array := sys.xid_array();

4i number;

5begin

6i := 1;

7--initialize xid_array from changed_tables

8

9for txn in (select distinct xid from changed_tables)

10loop

11txn_array.extend;

12txn_array(i) := txn.xid;

13i := i + 1;

14end loop;

15i := i - 1;

16-- 3 input variables are passed to transaction_backout

17-- i number of txns

18-- txn_array array of txn ids

19-- sscn starting point to logminer

20

21dbms_flashback.transaction_backout (

22numtxns => i,

23xids => txn_array,

24options => dbms_flashback.cascade,

25scnhint => sscn

26);

27

28--issue commit as dbms_flashback.transaction_backout does not include commit and ----txn backout.

29commit;

30END;

31/

Procedure created

执行过程程序。

SQL> exec txn_backout(sscn => 1795785);

begin txn_backout(sscn => 1795785); end;

ORA-55510: ??无法启动挖掘

ORA-06512: ?? "SYS.DBMS_FLASHBACK", line 37

ORA-06512: ?? "SYS.DBMS_FLASHBACK", line 70

ORA-06512: ?? "SYS.TXN_BACKOUT", line 21

ORA-06512: ?? line 1

遇到了错误信息，检查错误代码。

[oracle@NCR-Standby-Asm ~]$ oerr ora 55510

55510, 0000, "Mining could not start"

// *Cause: Mining could not start for the following reasons.

//1. A logminer session was processing

//2. The database was not mounted or not opened for read and write

//3. Minimum supplemental logging was not enabled

//4. Archiving was not enabled

// *Action: Fix the mentioned problems and try again. Note that if

//you enable supplemental logging now, you will not be able to

//remove a transaction that has committed without supplemental

//logging.

Oracle在错误解释中介绍了几种报错的原因情形，只有一种是比较可能，就是当前Logminer的日志操作还存在，没有被停止。Log Miner是需要手工关闭的。

SQL>exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed

重新执行操作。

SQL> exec txn_backout(sscn => 1795785);

PL/SQL procedure successfully completed

SQL> select * from test.emp;

EMPNO ENAMEJOBMGR HIREDATESALCOMM DEPTNO

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

10 AAASTF1988/2/5 131000.00100.0010

20 BBBSTF10 1988/2/5 13500.00100.0010

执行成功，同时数据emp被逆转回原来的时间点。最后，我们补充一下关闭归档和补充日志的操作。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size2253664 bytes

Variable Size973081760 bytes

Database Buffers620756992 bytes

Redo Buffers7319552 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database drop supplemental log data;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log modeNo Archive Mode

Automatic archivalDisabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence38

Current log sequence40

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_MIN

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

NO

4、结论

本篇介绍了一种通过Log Miner和Flashback Transaction结合来恢复小规模事务，逆转误操作的情况。这种操作相对于Flashback Query的好处在于联动Cascade功能，可以将事务全部逆转。