利用logminer，恢复delete误删除操作的数据

来源：互联网发布：arraypush php 编辑：程序博客网时间：2024/04/30 22:34

使用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

SQL> exec dbms_logmnr.add_logfile(LogFileName => 'F:\REDO\REDO03A.LOG', Options => dbms_logmnr.new);
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