不小心Truncate表的事情也是有的, 其中大部份时因为工具连错了库, 从儿跑错了角本. 遇到这种事情而没有备份时怎么办呢? 首先要停止数据库, 将这个表所在的表空间的文件拷贝出来, 因为Oracle在Truncate只时将相应Segment的第一个块格式化掉了, 而后面的都还存在, 到下次用时到才真正地重新格式化.
下面来讲一个Truncate表后进行恢复的例子: SQL> CREATE TABLE T_TRUNCATE AS SELECT * FROM TAB;
Table created.
SQL> SELECT COUNT(*) FROM T_TRUNCATE;
COUNT(*)
----------
14
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> TRUNCATE TABLE T_TRUNCATE;
Table truncated.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
在Truncate时只是Segment Header格式化了, 并将Data Object ID换成一个新的值, 我们可以在AUL中用DESC命令来查看:
AUL> desc anysql.t_truncate
Storage(OBJ#=9976 OBJD=9977 TS=4 FILE=4 BLOCK=5235 CLUSTER=0)
No. SEQ INT Column Name Type
--- --- --- ----------------------------- ----------------
1 1 1 TNAME VARCHAR2(30) NOT NULL
2 2 2 TABTYPE VARCHAR2(7)
3 3 3 CLUSTERID NUMBER
要恢复这个表的数据, 首先要在AUL中运行SCAN EXTENT命令, 因为Segment Header被格式化了, 所以Extent Map也可能丢失, 而Scan Extent则将扫描整个数据文件并将Extent分配信息写入AULEXT.TXT文件:
AUL> SCAN EXTENT FILE 4
2006-12-18 21:32:10
2006-12-18 21:32:24
恢复的关键是要获得这个表原来的Data Object ID, 在这个例子中我在Truncate表后什么也没有做就关闭数据库进行恢复了. 从上面的DESC命令可以看出表的Segment Header是(4,5235), 而新的Data Object ID是9977, 老的Data Object ID我们可以从Segment Header的后面一个数据块中得到, 如果这个表有几个Free List Group, 则可能还要再后面几个块. 用AUL的ORADUMP命令来看一下后面一个块:
AUL> ORADUMP FILE 4 BLOCK 5236
RDBA=0x01001474(4/5236)=16782452,type=0x06,fmt=0xa2,seq=0x02,flag=0x04
seg/obj=0x000026f8=9976,csc=0x0000.0006caf5,itc=3,typ=1 - DATA
FLG=0x32, fls=0, nxt=0x01001471(4/5233)=16782449
......
可以看到原来的Data Object ID是9976, 现在可以恢复了, 先不指定原来的Data Object ID试试?
AUL> unload table anysql.t_truncate;
2006-12-18 21:33:37
Unload OBJD=9977 FILE=4 BLOCK=5235 CLUSTER=0 ...
2006-12-18 21:33:37
接下来指定原来的Data Object ID, 再试试?
AUL> unload table anysql.t_truncate object 9976;
2006-12-18 21:33:45
Unload OBJD=9976 FILE=4 BLOCK=5235 CLUSTER=0 ...
P_MV_FACT_SALES|TABLE
TIME_DIM|TABLE
FACT_SALES|TABLE
MV_FACT_SALES|TABLE
SEG$|TABLE
NUMTEST|TABLE
T_OBJECTS|TABLE
T_LOBTEST|TABLE
T_INCLOB|TABLE
CF_XXK|TABLE
T_TESTDMP|TABLE
T_CLOBDEMO|TABLE
T_BLOBDEMO|TABLE
T_TRUNCATE|TABLE
2006-12-18 21:33:45
可以看到14条数据全回来了, 当然数据库是复杂的, 如果是一个很大的表, 还是不能保证可以100%恢复的.
最近至少看到二次错误地截断(Truncate)表的例子, 并在网上询问如何恢复, 在这儿我给出AUL/MyDUL的解决方案, 下面是我用的一个测试表:
ASQL> DESC TRUNCDEMO
NO# NAME NULLABLE TYPE
--- ----------------- -------- ------------
1 COL1 VARCHAR2(20)
ASQL> SELECT * FROM TRUNCDEMO;
COL1
-----
ROW 1
ROW 2
2 rows returned.
接下来我们来截断表, 其实这个操作只是重新格式化了段头块(Segment Header), 并分配一个新的数据对象号(Data Object ID), 当然空间分配信息也改了, 除非加了重用空间选项(Reuse Storage). 来看一下这个操作的前后变化:
ASQL> SELECT DATA_OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;
DATA_OBJECT_ID OBJECT_NAME
-------------- -----------
13676 TRUNCDEMO
1 rows returned.
ASQL> truncate table truncdemo;
Truncate Table Succeed.
ASQL> SELECT DATA_OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;
DATA_OBJECT_ID OBJECT_NAME
-------------- -----------
13677 TRUNCDEMO
1 rows returned.
由于在System表空间中已经记录了新的信息, 因此用当前的System信息是不能恢复过来的,在AUL/MyDUL中可以当作没有System时的情况来处理,在下面的命令中, 我们用Truncate后的数据对象号就不能进行恢复, 而使用Truncate以前的就可以, 当然空间不能被重新利用了是恢复的前提.
AUL> unload object 13676 column varchar file 4;
2006-09-18 22:38:58
ROW 1
ROW 2
2006-09-18 22:39:04
AUL> unload object 13677 column varchar file 4;
2006-09-18 22:39:10
2006-09-18 22:39:10
AUL>
因此在意外发生Truncate后, 如果没有备份可以恢复, 首先要做的事是备份一下当前的文件, 免得空间被重用. 而Truncate之前的数据对象号在AUL/MyDUL中是很容易找出来的. 到此已经说明了如何恢复Truncate表了.
跟据原理可以创建一个恢复包Recover_Truncate_Data，然后我们可以做个实验进行验证恢复效果如何：
第一步：创建表
create table truntab1 as select * from dba_objects;
第二步：查询表中记录数
select count(*) from truntab1;
--72622
第三步：truncate表中业务数据
truncate table truntab1;
第四步：确认表中记录数为零
select count(*) from truntab1;
-- 0
第五步：设置恢复前环境变量
set serveroutput on size 10000000 --//设置大点,默认为2000 bytes
exec dbms_output.enable(999999999999999999999); --//默认为2000 bytes
注意：如果不不进行设置，为报PLSQL ORA-20000: ORU-10027: buffer overflow, limit of 10000
第六步：实施truncate表中数据恢复
declare
tgtowner varchar2(30);
tgttable varchar2(30);
datapath varchar2(4000);
datadir varchar2(30);
rects varchar2(30);
recfile varchar2(30);
rstts varchar2(30);
rstfile varchar2(30);
blksz number;
rectab varchar2(30);
rsttab varchar2(30);
copyfile varchar2(30);
begin
tgtowner := 'SYS'; --指定表名的属用户
tgttable := 'TRUNTAB1'; --指定需要恢复的表名
datapath := 'D:\app\Administrator\oradata\lmis\'; --数据文件所在位置
datadir := 'FY_DATA_DIR';
Recover_Truncate_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);
Recover_Truncate_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);
Recover_Truncate_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);
end;
第七步：查看输出内容和构造表名：
15:32:44: Directory Name: FY_DATA_DIR4
15:32:45: Recover Tablespace: FY_REC_DATA4; Data File: FY_REC_DATA4.DAT
15:32:46: Restore Tablespace: FY_RST_DATA4; Data File: FY_RST_DATA4.DAT
15:32:48: Recover Table: SYS.TRUNTAB1$2
15:32:48: Restore Table: SYS.TRUNTAB1$$2
15:33:04: [fill_blocks] Data Blocks formatted.
15:33:05: [copy_file] begin copy file: FY_DATA_DIR4\FY_REC_DATA4.DAT => FY_DATA_DIR4\FY_REC_DATA_COPY.DAT
15:33:05: [copy_file] completed.
15:33:05: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
15:33:05: begin to recover table SYS.TRUNTAB1
15:33:19: [restore_table] Trying to restore data to SYS.TRUNTAB1$$2
15:33:20: [restore_table] Expected Records in this round: 411
15:33:20: [restore_table] 411 records recovered
此处省略N行输出............................................
15:33:44: [restore_table] Expected Records in this round: 0
15:33:44: [restore_table] 0 records recovered
15:33:44: 1033 truncated data blocks found.
15:33:44: 72622 records recovered in backup table SYS.TRUNTAB1$$2
15:33:44: Recovery completed.
PL/SQL procedure successfully completed
从红色字体可以看出，恢复72622条，刚好是truncate前业务表中记录数，恢复临时表为：SYS.TRUNTAB1$$2
第七步：查看输出内容和构造表名：
insert into truntab1 select * from SYS.TRUNTAB1$$2
第八步：验证数据是否完全恢复
select count(*) from truntab1;
--72622
至此，truncate掉的数据成功恢复，并且此方法也可以恢复drop table tablename purge删除的数据，
第九步：清理恢复产生的表空间和数据文件
特别提醒：恢复完成后，该方法会在数据库中产生一个表空间：FY_RST_DATA*，恢复一次产生一个，记得及时清理！否则会导致服务器RMAN备份失败ORA-19566 超出损坏块限制（切记）
truncate原理：
? ? ? ?TRUNCATE不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据（如存储段头和扩展段图）。也就是说，此时，其基本数据并未被破坏，而是被系统回收、等待被重新分配————因此，要恢复被TRUNCATE的数据，需要及时备份其所在的数据文件。
? ? 方法：用存储过程包Fy_Recover_Data ? ? 它是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包，这个包是由行内有影响力的DBA大师黄炜先生通过PLSQL编写的，再这里再次感谢他的无私技术分享。Fy_Recover_Data去本文附近中下载
好了，闲话少说，下面通过oracle数据库中scott用户自带的emp表做测试：
步骤1：先把Fy_Recover_Data包拷贝到oracle相关目录下
步骤2：在scott用户下创建test_emp表：
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME ? ? ? TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS ? ? ? TABLE
DEPT ? ? ? TABLE
EMP ? ? ? ? ? ? ? ?TABLE
SALGRADE ? ? ? TABLE
SQL> select count(*) from emp;
? COUNT(*)
----------
14
SQL> create table test_emp ?as select * from emp;
Table created.
SQL> select count(*) from test_emp;
? COUNT(*)
----------
14
步骤3：用truncate删除test_emp表：
SQL> truncate table test_emp;
Table truncated.
SQL> select count(*) from test_emp;
? COUNT(*)
----------
0
步骤4：在linux中的oracle用户下解压FY_Recover_Data.zip包
$ unzip FY_Recover_Data.zip
Archive: ?FY_Recover_Data.zip
? inflating: FY_Recover_Data.SQL?
步骤5：恢复
1）在sys用户下执行存储过程
SQL> @/home/oracle/FY_Recover_Data.SQL
Package created.
Package body created.
2）查看test_emp表在数据文件中的目录
SQL> select file_name from dba_data_files f, dba_tables t where t.owner='SCOTT' and t.table_name='TEST_EMP' and t.tablespace_name = f.tablespace_name;
FILE_NAME
--------------------------------------------------------------------------------
/u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf
3）通过脚本恢复，可以用sqlplus命令行或者plsql developer执行
declare
? ? ? tgtowner varchar2(30);
? ? ? tgttable varchar2(30);
? ? ? datapath varchar2(4000);
? ? ? datadir varchar2(30);
? ? ? rects varchar2(30);
? ? ? recfile varchar2(30);
? ? ? rstts varchar2(30);
? ? ? rstfile varchar2(30);
? ? ?blksz number;
? ? ?rectab varchar2(30);
? ? ?rsttab varchar2(30);
? ? ?copyfile varchar2(30);
? ?begin
? ? ?tgtowner := 'SCOTT'; --table owner
? ? ?tgttable := 'TEST_EMP'; ?--table name
? ? ?datapath := '/u03/oracle/oradata/WUTONG/datafile/'; ? ?--必须和test.t1表所在的数据文件的目录相同
? ? ?datadir := 'FY_DATA_DIR'; ? ? ? ?--oracle中目录的名字，可以修改
? ? ?Fy_Recover_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);
? ? ?Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);
? ? ?Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);
? ?end;
? ?以上SQL脚本产生2个表空间(2个数据文件)，还有1个copy文件。
4）切换到scott用户下查看会发现多了些不一样以test_emp的表，这时找到相关有数据的表，把数据插入原表test_emp
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME ? ? ? TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS ? ? ? TABLE
DEPT ? ? ? TABLE
EMP ? ? ? TABLE
SALGRADE ? ? ? TABLE
TEST_EMP ? ? ? TABLE
TEST_EMP$ ? ? ? TABLE
TEST_EMP$$ ? ? ? TABLE
7 rows selected.
SQL> insert into test_emp select * from TEST_EMP$$;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test_emp;
? COUNT(*)
----------
14
当你看到这一步的时候，说明truncate的表已经完全恢复了，恭喜你数据恢复成功！紧张的压力随之而释放，脸上露出灿烂的笑容和自豪感（做DBA很辛苦，数据库能保持正常运行，DBA在幕后做了大量的工作，有时是不会不被公司其他人理解的。。。。。）
步骤6：恢复数据后，把恢复时产生的2个表空间删除，再删除对应数据文件
SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/oracle/oradata/WUTONG/datafile/o1_mf_system_cx3xt90z_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_sysaux_cx3xt930_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_undotbs1_cx3xt93b_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
/u03/oracle/oradata/WUTONG/datafile/FY_REC_DATA.DAT
/u03/oracle/oradata/WUTONG/datafile/FY_RST_DATA.DAT
7 rows selected.
SQL>?drop tablespace FY_REC_DATA INCLUDING CONTENTS;
Tablespace dropped.
SQL>?drop tablespace FY_RST_DATA INCLUDING CONTENTS;
Tablespace dropped.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/oracle/oradata/WUTONG/datafile/o1_mf_system_cx3xt90z_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_sysaux_cx3xt930_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_undotbs1_cx3xt93b_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
然后去操作系统下把对应的数据文件删除即可
---------------------
945
非常没帮助
没帮助
一般
有帮助
非常有帮助
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
