Oracle delete和truncate实践操作之一
Oracle delete和truncate实践操作之一
2019-08-15 16:27 空白葛 阅读(…) 评论(…) 编辑 收藏
实践说明
本文章主要记录在Oracle中，delete和truncate进行数据删除之后，如何进行数据恢复。由于网上对delete和truncate的区别说明较多，此处不过多介绍两者区别。
注：由于环境和版本以及其他因素，本文章中并非最佳解决方法，仅供参考。
实践过程
环境准备
1、在本机准备Oracle bbed执行程序（需要通过指定包编译获取） delete主要还原工具
2、FY_Recover_Data工具下载（HelloDBA大神作品）truncate主要还原工具
Delete删除恢复过程
1、准备测试数据
1 SQL> create tablespace test1 datafile '+DATA' size 5M autoextend on next 1M maxsize 15M; 2 Tablespace created. 3 4 SQL> select TABLESPACE_NAME,FILE_ID,STATUS,FILE_NAME,BYTES/1024/1024 from dba_data_files; 5 6 TABLESPACE_NAME FILE_ID STATUS FILE_NAME BYTES/1024/1024 7 -------------------- ---------- --------- ------------------------------------------------------------ --------------- 8 USERS 4 AVAILABLE +DATA/orcl/users01.dbf 52.5 9 UNDOTBS1 3 AVAILABLE +DATA/orcl/undotbs01.dbf 255 10 SYSAUX 2 AVAILABLE +DATA/orcl/sysaux01.dbf 1060 11 SYSTEM 1 AVAILABLE +DATA/orcl/system01.dbf 770 12 UNDOTBS2 5 AVAILABLE +DATA/orcl/undotbs02.dbf 82.25 13 TEST1 6 AVAILABLE +DATA/orcl/datafile/test1.268.1016378251 5 14 15 6 rows selected. 16 17 SQL> create user test identified by test default tablespace test1; 18 User created. 19 20 SQL> grant dba to test; 21 Grant succeeded. 22 23 SQL> conn test/test 24 Connected. 25 SQL> create table t1 as select * from dba_objects where object_id<10; 26 Table created. 27 SQL> commit; 28 29 Commit complete.
2、delete操作本质
在执行delete后，数据并没有在物理上删除，只是把对应记录标记为删除，而且delete后，对表的HWM没有改造，表的大小依然那么大，delete数据后，占用的空间一直都在，别的表记录不能在此写入。
1 SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,t.object_id from TEST.T1 t; 2 FILE# BLOCK# OBJECT_ID 3 ---------- ---------- ---------- 4 6 131 2 5 6 131 3 6 6 131 4 7 6 131 5 8 6 131 6 9 6 131 7 10 6 131 8 11 6 131 9 12 13 8 rows selected.
上述记录为T1表所在的数据文件6，以及对应的BLOCK#131。通过以下两种方法可以查看其block中对应行的标识。
方法一、dump出对应的数据块
1 SQL> alter system dump datafile 6 block 131; 2 System altered. 3 4 SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1)); 5 SPID 6 ------------------------ 7 26716 8 9 10 [oracle@dbrac1 trace]$ pwd 11 /u01/app/oracle/diag/rdbms/orcl/orcl1/trace 12 [oracle@dbrac1 trace]$ ls -lrt *26716* 13 -rw-r----- 1 oracle asmadmin 614 8月 15 15:34 orcl1_ora_26716.trm 14 -rw-r----- 1 oracle asmadmin 12708 8月 15 15:34 orcl1_ora_26716.trc 15 16 。。。。。。。。 17 block_row_dump: 18 tab 0, row 0, @0x1f30 19 tl: 80 fb: --H-FL-- lb: 0x0 cc: 14 <==============fb : HFL即代表为正常记录 20 col 0: [ 3] 53 59 53 21 col 1: [ 6] 43 5f 4f 42 4a 23 22 col 2: *NULL* 23 col 3: [ 2] c1 03 24 col 4: [ 2] c1 03 25 col 5: [ 7] 43 4c 55 53 54 45 52 26 col 6: [ 7] 78 71 08 18 0c 26 24 27 col 7: [ 7] 78 71 08 18 0c 26 24 28 col 8: [19] 32 30 31 33 2d 30 38 2d 32 34 3a 31 31 3a 33 37 3a 33 35 29 col 9: [ 5] 56 41 4c 49 44 30 col 10: [ 1] 4e 31 col 11: [ 1] 4e 32 col 12: [ 1] 4e 33 col 13: [ 2] c1 06 34 tab 0, row 1, @0x1ee2 35 tl: 78 fb: --H-FL-- lb: 0x0 cc: 14 <==============fb: HFL即代表为正常记录 HDFL代表删除记录 36 col 0: [ 3] 53 59 53 37 col 1: [ 6] 49 5f 4f 42 4a 23 38 col 2: *NULL* 39 col 3: [ 2] c1 04 40 col 4: [ 2] c1 04
方法二、通过bbed查看相应块行记录
1 ASMCMD> cp TEST1.268.1016378251 /home/grid/test1.dbf <===============bbed不能直接读取ASM中数据文件，需要CP出来 2 copying +DATA/ORCL/DATAFILE/TEST1.268.1016378251 -> /home/grid/test1.dbf 3 4 BBED> info all 5 File# Name Size(blks) 6 ----- ---- ---------- 7 1 /home/oracle/bbed/asm_disk_header 0 8 2 /home/oracle/bbed/asm_disk_header2 0 9 6 /home/oracle/bbed/test1.dbf 1920 10 11 BBED> set dba 6,131 <================设置数据文件6，Block 131 12 DBA 0x01800083 (25165955 6,131) 13 BBED> p *kdbr[0] 14 rowdata[560] 15 ------------ 16 ub1 rowdata[560] @8108 0x2c <=================此处2c代表正常记录 3c代表删除记录 17 18 BBED> x /rcccccccc 19 rowdata[560] @8108 20 ------------ 21 flag@8108: 0x2c (KDRHFL, KDRHFF, KDRHFH) 22 lock@8109: 0x00 23 cols@8110: 14 24 25 col 0[3] @8111: SYS 26 col 1[6] @8115: C_OBJ# 27 col 2[0] @8122: *NULL* 28 col 3[2] @8123: col 4[2] @8126: col 5[7] @8129: CLUSTER 29 col 6[7] @8137: xq...&$ 30 col 7[7] @8145: xq...&$ 31 col 8[19] @8153: 2013-08-24:11:37:35 32 col 9[5] @8173: VALID 33 col 10[1] @8179: N 34 col 11[1] @8181: N 35 col 12[1] @8183: N 36 col 13[2] @8185:
注：以上关于Block的内容可参考 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26715884/viewspace-2114424/
3、删除记录
1 SQL> delete from test.t1; 2 8 rows deleted. 3 4 SQL> commit; 5 Commit complete. 6 7 SQL> alter system flush buffer_cache; <============需要将脏块刷进数据文件 8 System altered. 9 10 11 SQL> alter system dump datafile 6 block 131; 12 System altered. 13 14 SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1)); 15 SPID 16 ------------------------ 17 19634 18 19 [oracle@dbrac1 trace]$ pwd 20 /u01/app/oracle/diag/rdbms/orcl/orcl1/trace 21 [oracle@dbrac1 trace]$ ls -lrt *19634* 22 -rw-r----- 1 oracle asmadmin 322 8月 15 15:50 orcl1_ora_19634.trm 23 -rw-r----- 1 oracle asmadmin 8370 8月 15 15:50 orcl1_ora_19634.trc 24 [oracle@dbrac1 trace]$ vi orcl1_ora_19634.trc 25 。。。。。 26 block_row_dump: 27 tab 0, row 0, @0x1f30 28 tl: 2 fb: --HDFL-- lb: 0x2 <=================== fb:标识已经变成HDFL，为已删除状态。 29 tab 0, row 1, @0x1ee2 30 tl: 2 fb: --HDFL-- lb: 0x2 31 tab 0, row 2, @0x1e96 32 tl: 2 fb: --HDFL-- lb: 0x2 33 tab 0, row 3, @0x1e4a 34 tl: 2 fb: --HDFL-- lb: 0x2 35 tab 0, row 4, @0x1dfb 36 tl: 2 fb: --HDFL-- lb: 0x2
4、通过bbed还原数据
1 ASMCMD> cp TEST1.268.1016378251 /home/grid/test2.dbf 2 copying +DATA/ORCL/DATAFILE/TEST1.268.1016378251 -> /home/grid/test2.dbf <=======为了减少麻烦，将数据库关闭后，将文件重新拿出来，命名test2.dbf 3 BBED> info all 4 File# Name Size(blks) 5 ----- ---- ---------- 6 1 /home/oracle/bbed/asm_disk_header 0 7 2 /home/oracle/bbed/asm_disk_header2 0 8 6 /home/oracle/bbed/test1.dbf 1920 9 7 /home/oracle/bbed/test2.dbf 1920 <========删除数据后的数据文件 10 11 BBED> set dba 7,131 12 DBA 0x01c00083 (29360259 7,131) 13 BBED> p *kdbr[0] 14 rowdata[560] 15 ------------ 16 ub1 rowdata[560] @8108 0x3c <======标记已调整为3c证明数据处于删除状态 17 BBED> dump 18 File: /home/oracle/bbed/test2.dbf (7) 19 Block: 131 Offsets: 8108 to 8191 Dba:0x01c00083 20 ------------------------------------------------------------------------ 21 3c020e03 53595306 435f4f42 4a23ff02 c10302c1 0307434c 55535445 52077871 22 08180c26 24077871 08180c26 24133230 31332d30 382d3234 3a31313a 33373a33 23 35055641 4c494401 4e014e01 4e02c106 010641c7 24 25 <32 bytes per line> 26 27 BBED> m /x 2c <============通过修改数据块标识，调整行记录状态 28 File: /home/oracle/bbed/test2.dbf (7) 29 Block: 131 Offsets: 8108 to 8191 Dba:0x01c00083 30 ------------------------------------------------------------------------ 31 2c020e03 53595306 435f4f42 4a23ff02 c10302c1 0307434c 55535445 52077871 32 08180c26 24077871 08180c26 24133230 31332d30 382d3234 3a31313a 33373a33 33 35055641 4c494401 4e014e01 4e02c106 010641c7 34 35 <32 bytes per line> 36 37 BBED> sum apply 38 Check value for File 7, Block 131: 39 current = 0x0000, required = 0x0000
BBED> x /rcccccccccc
rowdata[560] @8108
————
flag@8108: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8109: 0x02
cols@8110: 14
col 0[3] @8111: SYS
col 1[6] @8115: C_OBJ# <===============在还原之后，数据可以查看
5、验证数据
ASMCMD> rm -rf TEST1.268.1016378251 ASMCMD> cp /home/grid/test2.dbf +DATA/orcl/DATAFILE/test2.dbf copying /home/grid/test2.dbf -> +DATA/orcl/DATAFILE/test2.dbf SQL> startup mount ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2259840 bytes Variable Size 645923968 bytes Database Buffers 390070272 bytes Redo Buffers 5632000 bytes Database mounted. SQL> alter database rename file '+DATA/orcl/DATAFILE/TEST1.268.1016378251' to '+DATA/orcl/DATAFILE/test2.dbf'; Database altered. SQL> alter database open; Database altered. SQL> select OWNER,OBJECT_NAME from test.t1; <=============此处查出结果，与第4步结果一致，证明恢复成功 OWNER OBJECT_NAME ------------------------------ ---------------------------------------- SYS C_OBJ#
6、总结
以上过程看似步骤简单，实际上蕴含很多知识点。
1） Block块删除行后，标识位改变算法（本次实践的重点）
2）使用bbed读取ASM中数据文件方法
3）查看Block块内容方法
4）从本地传文件至ASM磁盘组方法
5）bbed中一些常用的命令
关于Truncate表的恢复方法，详见下一章节Oracle delete和truncate实践操作之二。
版权声明：本文为bicewow原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。