//? ? ? ? ? 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 ENAME? ? ? JOB? ? ? ? MGR HIREDATE? ? ? ? ? SAL? ? ? COMM DEPTNO

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

? 10 AAA? ? ? ? STF? ? ? ? ? ? 1988/2/5 13? 1000.00? ? 100.00? ? 10

? 20 BBB? ? ? ? STF? ? ? ? ? 10 1988/2/5 13? ? 500.00? ? 100.00? ? 10

执行成功，同时数据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 Size? ? ? ? ? ? ? ? ? 2253664 bytes

Variable Size? ? ? ? ? ? 973081760 bytes

Database Buffers? ? ? ? ? 620756992 bytes

Redo Buffers? ? ? ? ? ? ? ? 7319552 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 mode? ? ? ? ? ? ? No Archive Mode

Automatic archival? ? ? ? ? ? Disabled

Archive destination? ? ? ? ? ? USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence? ? 38

Current log sequence? ? ? ? ? 40

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_MIN

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

NO

4、结论

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