• Oracle-truncate误删数据恢复

    前言：

            truncate操作误删数据之后想恢复数据通常比较困难，因为truncate操作属于ddl操作无法使用直接undo闪回查询方式恢复数据，并且由于空间大小、备份时间以及变更操作不规范原因，往往在执行操作之前没有对表进行CTAS备份或者其他有效的逻辑备份导致恢复truncate的误删数据困难。

            本文接下来将介绍常用的5种truncate误删数据的恢复办法，希望对读者有所借鉴或是启发。

    数据恢复介绍：

            在进行数据恢复之前，我们需要先获取一个非常关键的数据，就是truncate操作的时间点，这个时间点的准确性对数据的恢复非常关键，因为会影响数据恢复的完整性，时间点可以从以下途径获取

            1 执行人员的大脑记忆，获取的时间点相对模糊

            2 数据库的审计，操作记录日志，获取的时间点准确

            3 dba_objects视图的last_ddl_time，获取时间点准确，前提是truncate是最近一次的DDL操作

            4 从redo日志里面获取，获取的时间点准确

            5 从历史相关视图获取v$sql,v$active_session_history获取的时间点准确，单不一定有记录

    方式一：通过基于时间点的rman备份恢复

            这种方式是相对稳妥的方式，但缺点也比较明显全量数据恢复时间相对较长，需要一倍空间以及恢复环境，而Oracle也推出更加简便快速的不用恢复全量数据的恢复方式，在Oracle10G以上可以使用TSPITR基于时间点恢复表空间的方式恢复数据，具体操作步骤可以参考Oracle-表空间基于时间点恢复(TSPITR)，而在Oracle12c版本以上可以使用基于时间点的recover table的方式直接从备份里面基于时间快速恢复表或者是表分区，具体操作步骤可以参考Oracle-12c新功能基于时间点recover table

    方式二:通过expdp,exp等逻辑备份恢复

            可以这种方式恢复数据简单，灵活，但是逻辑备份通常没有增量的方式，都是单次的固定时间点执行全量备份，所以备份恢复的数据数据点与删除时间点可能会出现相差较多的问题，导致恢复的数据出现不完整的情况

    方式三：flashback闪回数据库

            flashback闪回数据库，将整个数据库数据闪回到误删数据前的时间点，这种方式的缺点非常明显就是闪回是将整个数据库的数据一起闪回，而这样的恢复方式在生产主库环境操作几乎不可能，因为不可能为了一张表的数据，将整个数据库的数据都闪回到之前的时间点，因此这种方式合适在备库环境进行，在备库开启了flashback的功能情况下，我们可以选择在备库上进行闪回恢复数据

            具体操作步骤如下        

    1. --查看备库闪回日志的最老时间点，要低于误删数据的操作时间点
    2. SQL> select *
    3. SQL> from v$flashback_database_log;
    4. OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    5. -------------------- ------------------- ---------------- -------------- ------------------------
    6.             17239007 2023-10-11 17:42:31             1440      209715200                        0
    7. --将备库启动到mount
    8. SQL> shutdown immediate;
    9. SQL> startup mount;
    10. --备库闪回到truncate之前的时间点
    11. SQL>flashback database to timestamp to_timestamp('2023-10-11 17:45:00','yyyy-mm-dd hh24:mi:ss');
    12. --只读打开数据库，确认是要恢复的数据
    13. SQL> alter database open read only;
    14. SQL> select count(*) from test.testdebug;
    15. ​
    16. ​
    17.   COUNT(*)
    18. ----------
    19.     172864
    20. ​
    21. --备库开启到快照库模式导出表数据
    22. SQL> alter database convert to snapshot standby;
    23. SQL> Alter database open;
    24. --expdp导出表数据
    25. expdp 'userid="/ as sysdba"' dumpfile=test.dmp tables=test.testdebug directory=expdir logfile=test.log 
    26. --转为物理备库恢复同步
    27. SQL> shutdown immediate;
    28. SQL> startup mount ;
    29. SQL> alter database convert to physical standby;
    30. --重新恢复同步
    31. SQL> shutdown immediate;
    32. SQL> startup;
    33. SQL> alter database recover managed standby database using current logfile  disconnect from session;

    方式四：延迟备库恢复

            通过ADG延迟备库功能恢复数据，延迟备库是通过主动设置备库日志的应用延时，从而避免主库的操作在备库被立即应用，这样在主库发生误删数据时，备库的数据可以保证在延迟时间期限内未被删除，这样我们可以从延迟备库中恢复数据

            具体操作步骤如下

    1. --备库立即​取消日志应用
    2. SQL> alter database recover managed standby database cancel;
    3. --主库切换日志
    4. SQL> alter system switch logfile;
    5. --备库启动到mount状态
    6. SQL> shutdown immediate;
    7. startup mount 
    8. --备库收到recover恢复到truncate之前的时间点
    9. run
    10. {
    11. set until time "to_date('2023-10-11 16:45:00','yyyy-mm-dd hh24:mi:ss')";
    12. recover database;
    13. }
    14. --只读打开备库
    15. SQL> alter database open read only;
    16. --确认是要恢复的数据
    17. SQL> select count(*) from test.testdebug;
    18. ​
    19.   COUNT(*)
    20. ----------
    21.      86432
    22. ​
    23. --备库开启到快照库模式导出表数据
    24. SQL> alter database convert to snapshot standby;
    25. SQL> Alter database open;
    26. --expdp导出表数据
    27. expdp 'userid="/ as sysdba"' dumpfile=test.dmp tables=test.testdebug directory=expdir logfile=test.log 
    28. --转为物理备库重新恢复同步
    29. SQL> shutdown immediate;
    30. SQL> startup mount ;
    31. SQL> alter database convert to physical standby;
    32. --重新恢复同步
    33. SQL> shutdown immediate;
    34. SQL> startup;
    35. SQL> alter database recover managed standby database delay 120  disconnect from session;

    方式五：通过数据挖掘方式恢复数据

            在数据库没有开启闪回功能，没有有效备份以及延迟备库环境的情况下，想恢复truncate误删数据方法只剩下从已有的数据块里面挖掘恢复数据，前提是误删的数据块还未被覆盖，如果数据块已经被覆盖，那么数据可能就再也找不回来了

            所以，在没有有效的恢复方式的情况下，应尽可能的避免数据块被覆盖导致数据丢失，最好将数据所在的表空间设置为只读保留误删环境，如果有备库环境，可以立即取消日志应用然后将备库激活为快照库作为数据挖掘的环境

    alter tablespace test1 read only;

            接下来进行数据挖掘，truncate表操作我们可以通过10046以及redo日志内容大概了解到truncate包含了以下的步骤

    1. --10046
    2. 1 将truncate表加上独占锁
    3. LOCK TABLE "TESTDEBUG" IN EXCLUSIVE MODE  NOWAIT 
    4. 2 执行truncate
    5. truncate table testdebug
    6. 3 更新统计信息监控的数据
    7. update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :drops
    8. eg, timestamp = :time where obj# =88185
    9. 4 删除基本superobj$下的信息
    10. delete from superobj$ where subobj# = 88185
    11. 5 删除基表统计信息
    12. delete from tab_stats$ where obj#=88185
    13. 6 更新基表tab$的信息，包含更新data_object_id为新的值，文件块的信息
    14. update tab$ 
    15. 7 删除基表索引统计信息
    16. delete from ind_stats$ where obj#=:1
    17. 8 更新基表索引对象信息,包含更新data_object_id为新的值,文件块的信息
    18. update ind$ set 
    19. 9 更新基表seg$信息,包含更新extent的信息
    20. update seg$
    21. 10 更新基表obj$信息，包含dataobj
    22. --从redo 日志里面，我们还可以看到truncate期间直接更新的块信息
    23. 1 segment header(dataobj#、LHWM、HHWM、extent map、aux map以及extents个数)
    24. 2 L1,L2块(dataobj#,extent map)

            从中我们可以发现，truncate操作涉及修改的基表、块信息较为复杂，想直接通过修改回原来的块信息，基表信息的恢复方式难度较大，因此我们选择直接从块里面挖掘数据，关键点如下

            1.使用rowid的方式直接访问误删数据所在的数据块，避免访问segment header,L1,L2块

            2.误删数据可能包含在这以下3种块

    • 空闲块(dba_free_space)

    • 被分配给其他段但还没被使用覆盖(也就是每个段最后申请的一个extent)

    • 当前对象所在段的第一个extent

            3.obj$基表的data_object_id要跟挖掘原来数据的rowid里面的data_object_id一致，不然访问会出现无效rowid的报错

            4.根据误删数据可能所在所在块的RELATIVE_file_id,block_id以及data_object_id，使用dbms_rowid.rowid_create函数构造误删数据的rowid

    数据挖掘步骤如下:

            1.找到被truncate表上一次的data_object_id

            2 更新误删表在obj$基表上的data_object_id为原来的删除前的值

            3.通过dba_extents,dba_free_space获取误删数据可能所在的块

            4.通过dbms_rowid.rowid_create来创建误删数据的的rowid

            5.利用rowid来挖掘抽取数据

            6.验证挖掘的数据

            7 将误删表在obj$基表上的data_object_id修改为删除后的值

            8.将数据插入原表

    具体操作步骤如下:

            假设误删了表test.testdebug数据

    truncate table test.testdebug;

            通过redo 查找truncate之前的data_object_id

    1. --dump出删除操作期间的redo
    2. alter system dump logfile '+DATADG/db/onlinelog/group_2.262.1132512359';
    3. --搜索关键字Newobjd,找出修改之前的dataobj#
    4. Redo on Level1 Bitmap Block
    5.  Change objd
    6. Newobjd: 184880
    7. CHANGE #2 CON_ID:0 TYP:0 CLS:8 AFN:7 DBA:0x01c000c8 OBJ:184880 SCN:0x000000000178875c SEQ:1 OP:13.22 ENC:0 RBL:0 FLG:0x0000
    8. Redo on Level1 Bitmap Block
    9.  Change objd
    10. Newobjd: 184890

            更新误删表在obj$基表上的data_object_id为原来的删除前的值

    1. update obj$ set dataobj#=184880 where obj#=184880;
    2. commit;
    3. alter system flush shared_pool;
    4. alter system flush buffer_cache;

            通过dba_extents,dba_free_space获取误删数据可能所在的块,并生成rowid

    1. --存储过程执行用户权限
    2. SQL> grant dba,resource,connect to <username>;
    3. SQL> grant select on dba_extents to <username>;
    4. SQL> grant select on dba_free_space to <username>;
    5. SQL> grant select on dba_tables to <username>;
    6. SQL> grant create table to <username>;
    7. --存储过程get_data_rowid参数
    8. 1.p_old_owner truncate表的用户名
    9. 2.p_table truncate的表名
    10. 3.p_old_data_object_id 表truncate前的data_object_id
    11. 4.p_block_row 单个块最多包含的行数，根据表的列数量进行调整
    12. 5.p_parallel 挖掘数据的并行度
    13. --创建存储过程get_data_rowid
    14. create or replace procedure get_data_rowid(p_old_owner in varchar2,p_table in varchar2,p_old_data_object_id number,p_block_row number,p_parallel number)
    15. is
    16.    v_fno number;
    17.    v_s_bno number;
    18.    v_e_bno number;
    19.    v_rowid rowid;
    20.    v_owner varchar2(50);           -- truncate表用户名
    21.    v_table varchar2(100);          -- truncate表名
    22.    v_data_obj number;              -- truncate前dba_objects.data_object_id
    23.    v_block_row number;             -- 理论上每个块最多存放接近680行数据
    24.    v_parallel  number;
    25.    v_rowid_table varchar2(40);      -- 存放rowid的表
    26.    v_table_seq  number; 
    27.    v_sql1 varchar(1000);
    28.    v_sql2  varchar(1000);  
    29. begin
    30.   v_owner := p_old_owner;
    31.     v_table := p_table;
    32.     v_data_obj := p_old_data_object_id;
    33.   v_block_row:=p_block_row;
    34.   v_parallel:=p_parallel;
    35.   begin 
    36.   for t in 1..v_parallel loop
    37.     v_rowid_table:='rowid_data_table_'||t;
    38.     v_sql1:='create table '||v_rowid_table||'(BLOCK_ID NUMBER,ROW_ID rowid)';
    39.     execute immediate v_sql1;
    40.   end loop;
    41.   EXCEPTION
    42.     when OTHERS then
    43.     raise;
    44.   commit;
    45.   end;
    46.     for i in (select relative_fno, block_id, blocks --通过dba_extents,dba_free_space获取误删数据可能所在的块
    47.               from dba_extents
    48.              where owner = v_owner
    49.                and segment_name = v_table
    50.                and extent_id = 0
    51.             union all
    52.             select relative_fno, block_id, blocks
    53.               from dba_free_space
    54.              where tablespace_name in (select tablespace_name
    55.                                          from dba_tables
    56.                                         where owner = v_owner
    57.                                           and table_name = v_table)
    58.             union all
    59.             select relative_fno, block_id, blocks
    60.               from (select relative_fno,
    61.                            block_id,
    62.                            blocks,
    63.                            row_number() over(partition by owner, segment_name, PARTITION_NAME order by extent_id desc) rn
    64.                       from dba_extents
    65.                      where tablespace_name in
    66.                            (select tablespace_name
    67.                               from dba_tables
    68.                              where owner = v_owner
    69.                                and table_name = v_table)
    70.                        and extent_id > 0)
    71.              where rn = 1) loop
    72.     v_fno:=i.relative_fno;
    73.     v_s_bno:=i.block_id;
    74.     v_e_bno:=i.block_id+i.blocks-1;
    75.         for j in v_s_bno .. v_e_bno loop
    76.         begin
    77.          for x in 0..v_block_row loop  -- 通过dbms_rowid.rowid_create来创建潜在的rowid
    78.            v_rowid:=dbms_rowid.rowid_create(1, v_data_obj,v_fno,j,x);
    79.            v_table_seq:=mod(j,v_parallel)+1;
    80.            v_rowid_table:='rowid_data_table_'||v_table_seq;
    81.            v_sql2:='insert into '||v_rowid_table||' values(:1,:2)';
    82.            execute immediate v_sql2 using j,v_rowid;
    83.          end loop;
    84.      exception
    85.       when others then
    86.       raise;
    87.      end;
    88.     commit;
    89.         end loop;
    90.     end loop;
    91. end;
    92. /
    93. --执行存储过程生成要挖掘的rowid
    94. exec get_data_rowid('TEST','TESTDEBUG',184880,100,16);
    95. --这里使用了16个并行，所以rowid会分布在rowid_data_table_1-16表里面

            利用rowid来挖掘抽取数据

    1. --创建存放挖掘数据的备份表
    2. create table test.testdebug_bak tablespace users as select * from TEST.testdebug where 1=2;
    3. --存储过程insert_data_by_rowid参数
    4. 1.p_old_owner truncate表的用户名
    5. 2.p_table truncate的表名
    6. 3.p_bak_owner 备份表的用户名
    7. 4.p_bak_table 备份表的表名
    8. 5.p_slave 并行的子进程号
    9. --创建存储过程insert_data_by_rowid
    10. create or replace procedure insert_data_by_rowid(p_old_owner in varchar2,p_table in varchar2,p_bak_owner in varchar2,p_bak_table in varchar2,p_slave number)
    11. is
    12.    v_owner varchar2(50);           
    13.    v_table varchar2(100);          
    14.    v_bak_owner varchar2(50);
    15.    v_bak_table varchar2(100);
    16.    v_sql varchar2(2000);
    17.    v_slave number;
    18.    v_err_code varchar2(50);
    19.    v_err_msg varchar2(500);
    20.    v_rowid_table VARCHAR(40);
    21.    c_rowid  sys_refcursor;
    22.    v_rowid dbms_sql.urowid_table;
    23.    v_sql1 varchar2(1000);
    24.    v_sql2 varchar2(1000);
    25. begin
    26.   v_owner := p_old_owner;
    27.     v_table := p_table;
    28.     v_bak_owner:=p_bak_owner;
    29.   v_bak_table:=p_bak_table;
    30.   v_slave:=p_slave;
    31.   v_rowid_table:='rowid_data_table_'||v_slave;
    32.   v_sql1:='select row_id from '||v_rowid_table;
    33.   v_sql2 := 'insert into '||v_bak_owner||'.'||v_bak_table||' select * from '||v_owner||'.'||v_table||' where rowid=:1';
    34.   open c_rowid for v_sql1;
    35.   LOOP
    36.   fetch c_rowid bulk collect into v_rowid limit 10000;
    37.   exit when v_rowid.count=0;
    38.   begin 
    39.   forall i in v_rowid.first..v_rowid.last SAVE EXCEPTIONS
    40.     execute immediate v_sql2 using v_rowid(i);
    41.   EXCEPTION
    42.     when OTHERS 
    43.     then
    44. --    DBMS_OUTPUT.put_line (SQLERRM);  
    45. --    FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT  
    46. --    LOOP  
    47. --        DBMS_OUTPUT.put_line ('Oracle error is '|| SQLERRM (-1 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));  
    48. --    END LOOP;
    49.       null;
    50.   end;
    51.   commit;
    52.   end loop;
    53.   close c_rowid;
    54. end;
    55. /
    56. --16个窗口并行挖掘，注意会消耗大量的PGA进程内存以及产生大量单块读IO
    57. set serveroutput on 
    58. set timing on 
    59. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',1);
    60. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',2);
    61. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',3);
    62. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',4);
    63. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',5); 
    64. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',6);
    65. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',7);
    66. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',8);
    67. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',9);
    68. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',10);
    69. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',11);
    70. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',12);
    71. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',13); 
    72. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',14);
    73. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',15);
    74. exec insert_data_by_rowid('TEST','TESTDEBUG','TEST','TESTDEBUG_BAK',16);

            验证挖掘的数据，将数据插回原表

    1. --从表的字段逻辑，业务数据逻辑，表数据量验证数据
    2. select count(*)  from test.testdebug_bak;
    3. select sum(SESSIONID+ENTRYID+STATEMENT)  from test.testdebug_bak;
    4. --将误删表在obj$基表上的data_object_id修改为删除后的值
    5. update obj$ set dataobj#=184890 where obj#=184880;
    6. commit;
    7. alter system flush shared_pool;
    8. alter system flush buffer_cache;
    9. --表空间设置为读写
    10. alter tablespace test1 read write;
    11. --将数据插回原表，完成数据挖掘工作
    12. insert into test.testdebug select * from test.testdebug_bak;

    总结：

            5种恢复方式综合来看，延迟备库恢复以及闪回备库从恢复时间、恢复难度以及恢复数据完整性来看整体最好，是恢复truncate误删数据的首选，这也是为什么对于重要的核心数据库通常建议配置一主两备的原因，RAC（主）+实时备库(flashback on 8小时)+延迟备库(delay 24小时)+合理有效的rman备份策略的配置可以最大限度的保障数据安全   

         

  • 相关阅读:
    解决maven 不支持http问题
    黑客（网络安全）技术——如何高效自学
    CSDN学院 ＜ 华为战略方法论进阶课 ＞ 正式上线！
    系统学习Python——字符串（str）：字符串格式化表达-[基础知识]
    nacos应用
    linux shell(一)
    计算机毕业设计Java城市交通海量数据管理系统(源码+系统+mysql数据库+lw文档)
    【C++】类和对象（上）
    WPF之Window无边框拖动、特殊形状、Grid拖拽
    基于JAVA音乐播放器计算机毕业设计源码+系统+数据库+lw文档+部署
  • 原文地址：https://blog.csdn.net/sinat_36757755/article/details/133821824