使用DBA账户操作
1.打开追加日志
SQL>alter database add supplemental log data;
SQL>select supplemental_log_data_min from v$database;SUPPLEME
--------
YES
2.记录DML操作前后的SCN
SQL> select * from test;A
----------
1
2
3
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3640931
SQL> delete test;
3 rows deleted
SQL> commit;
Commit complete
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3640943
SQL> select * from test;
A
----------
3.查询当前REDO日志文件,执行logmnr包
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
4 INACTIVE
5 INACTIVE
6 CURRENT
SQL> select group#,member from v$logfile where group#=6;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
6 F:\REDO\REDO03A.LOG
PL/SQL procedure successfully completed
SQL> exec dbms_logmnr.start_logmnr(Options =>dbms_logmnr.dict_from_online_catalog ,startscn => 3640931,endScn =>3640943);
PL/SQL procedure successfully completed
SQL> col sql_undo for a50;
SQL> col sql_redo for a50;
SQL> set pagesize 1000
SQL> select operation,sql_redo,sql_undo from v$logmnr_contents;
OPERATION SQL_REDO SQL_UNDO
-------------------------------- -------------------------------------------------- --------------------------------------------------
START set transaction read write;
DELETE delete from "BYS"."TEST" where "A" = '1' and ROWID insert into "BYS"."TEST"("A") values ('1');
= 'AAASA9AAEAAAAEDAAA';
DELETE delete from "BYS"."TEST" where "A" = '2' and ROWID insert into "BYS"."TEST"("A") values ('2');
= 'AAASA9AAEAAAAEDAAB';
DELETE delete from "BYS"."TEST" where "A" = '3' and ROWID insert into "BYS"."TEST"("A") values ('3');
= 'AAASA9AAEAAAAEDAAC';
COMMIT commit;
4.使用查出的UNDO SQL恢复误删除的SQL
这里根据UNDO_SQL中语句,把删除的三条数据 TEST表A列VALUES为1和2和3的行进行恢复。SQL> insert into bys.test values('1');
1 row inserted
SQL> insert into bys.test values('2');
1 row inserted
SQL> insert into bys.test values('3');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
----------
1
2
3