做了一个实验,恢复被truncate的数据,主要使用hellodba的脚本。很简单,也很实用
操作步骤
下载脚本:
http://www.hellodba.com/download/FY_Recover_Data.zip
解压完是一个sql脚本文件
在数据库里运行,会创建一个package和package body,要用SYS用户操作,以下操作均为SYS用户
然后使用如下语句执行:
select file_name from dba_data_files f, dba_tables t where t.owner=’TEST’ and t.table_name=’T1’ and t.tablespace_name = f.tablespace_name;
查出丢失的表所在的数据文件,生产上要及时备份,防止覆盖
随后执行
执行
set serveroutput on 可以让匿名块显示执行结果
–开始恢复
SQL> 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 := 'TEST'; --table owner
tgttable := 'T1'; --table name
datapath := '/var/db_file/download/'; --必须和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;
/
PL/SQL procedure successfully completed
16:50:23: Directory Name: FY_DATA_DIR2
16:50:23: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
16:50:23: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
16:50:24: Recover Table: SCOTT.TEST$
16:50:24: Restore Table: SCOTT.TEST$$
16:50:29: [fill_blocks] Data Blocks formatted.
16:50:29: [copy_file] begin copy file: FY_DATA_DIR2\FY_REC_DATA.DAT =>
FY_DATA_DIR2\FY_REC_DATA_COPY.DAT
16:50:29: [copy_file] completed.
16:50:29: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
16:50:29: begin to recover table SCOTT.TEST
16:50:30: [restore_table] Trying to restore data to SCOTT.TEST$$
16:50:30: [restore_table] Expected Records in this round: 238
16:50:30: [restore_table] 238 records recovered
16:50:37: [restore_table] Trying to restore data to SCOTT.TEST$$
16:50:37: [restore_table] Expected Records in this round: 0
16:50:37: [restore_table] 0 records recovered
16:50:37: 5 truncated data blocks found.
16:50:37: 238 records recovered in backup table SCOTT.TEST$$
16:50:37: Recovery completed.
PL/SQL procedure successfully completed.
结果大致如上,会有一个中间表为 XXX$$,复制此表即可恢复数据
4.收尾
恢复数据后,把
恢复时产生的2个表空间删除,再删除对应数据文件
drop tablespace FY_REC_DATA INCLUDING CONTENTS;
drop tablespace FY_RST_DATA INCLUDING CONTENTS;
$ cd /var/db_file/download/
$ rm FY_REC_DATA_COPY.DAT FY_REC_DATA.DAT FY_RST_DATA.DAT