目录
一、简介二、实验环境介绍三、 实验代码四、实验过程4.1、准备数据4.2、执行误操作4.3、开始恢复4.4、清理临时数据五、附录：包FY_Recover_Data的内容
一、简介
之前整理的内容：http://blog.itpub.net/26736162/viewspace-2082965/
误操作执行truncate后的恢复方法很多，这里再次介绍fy_recover_data包，由大师Fuyunca开发。该包采用纯plsql语句恢复被truncate掉的表，操作比较简单，包的内容参考本文后边的附录部分。
Fy_Recover_Data是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包。由纯PLSQL编写，原理图如下：
二、实验环境介绍
| 项目 | db |
|---|---|
| db 类型 | 单实例 |
| db version | 19.3.0.0 |
| db 存储 | FS |
| 主机IP地址/hosts配置 | 192.168.66.35 |
| OS版本及kernel版本 | CentOS 7.6 |
| 归档模式 | Archive Mode |
| ORACLE_SID | lhrsdb |
三、 实验代码
1-- 准备数据 2set timing on; 3set serveroutput on; 4create table lhr.LHRTB_210702 as SELECT * FROM dba_objects; 5SELECT COUNT(1) FROM lhr.LHRTB_210702; 6INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702; 7INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702; 8commit; 9SELECT COUNT(1) FROM lhr.LHRTB_210702;10SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='LHRTB_210702';11truncate table lhr.LHRTB_210702;12SELECT COUNT(1) FROM lhr.LHRTB_210702;131415-- 数据已经被truncate掉了，下边我们来恢复16@/tmp/FY_Recover_Data.pck17exec fy_recover_data.recover_truncated_table('LHR','LHRTB_210702');18SELECT COUNT(1) FROM lhr.LHRTB_210702$$;19alter table lhr.LHRTB_210702 nologging;20insert /*+append*/ into lhr.LHRTB_210702 select * from lhr.LHRTB_210702$$;21commit;22alter table lhr.LHRTB_210702 logging;23SELECT COUNT(1) FROM lhr.LHRTB_210702;242526-- 清理数据27drop tablespace FY_REC_DATA including contents and datafiles;28drop tablespace FY_RST_DATA including contents and datafiles;
四、实验过程
4.1、准备数据
1Microsoft Windows [版本 10.0.17134.765] 2(c) 2018 Microsoft Corporation。保留所有权利。 3 4C:\Users\lhrxxt>sqlplus sys/lhr@192.168.66.35:11521/lhrsdb as sysdba 5 6SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 2 16:00:36 2021 7 8Copyright (c) 1982, 2014, Oracle. All rights reserved. 91011Connected to:12Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production1314SYS@192.168.66.35:11521/lhrsdb> set timing on;15SYS@192.168.66.35:11521/lhrsdb> set serveroutput on;16SYS@192.168.66.35:11521/lhrsdb>17SYS@192.168.66.35:11521/lhrsdb> select * from v$version;1819BANNER BANNER_FULL BANNER_LEGACY CON_ID20-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------21Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 022 Version 19.3.0.0.0232425Elapsed: 00:00:00.15262728SYS@192.168.66.35:11521/lhrsdb> create user lhr identified by lhr;2930User created.3132Elapsed: 00:00:00.2633SYS@192.168.66.35:11521/lhrsdb> grant dba to lhr;3435Grant succeeded.3637Elapsed: 00:00:00.1238SYS@192.168.66.35:11521/lhrsdb>39SYS@192.168.66.35:11521/lhrsdb> create table lhr.LHRTB_210702 as SELECT * FROM dba_objects;4041Table created.4243Elapsed: 00:00:01.8344SYS@192.168.66.35:11521/lhrsdb>45SYS@192.168.66.35:11521/lhrsdb> INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;464772690 rows created.4849Elapsed: 00:00:00.6750SYS@192.168.66.35:11521/lhrsdb>51SYS@192.168.66.35:11521/lhrsdb> INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;5253145380 rows created.5455Elapsed: 00:00:00.9156SYS@192.168.66.35:11521/lhrsdb> commit;5758Commit complete.5960Elapsed: 00:00:00.1161SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702;6263 COUNT(1)64----------65 2907606667Elapsed: 00:00:00.1968SYS@192.168.66.35:11521/lhrsdb>69SYS@192.168.66.35:11521/lhrsdb> SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='LHRTB_210702';7071D.BYTES/1024/102472-----------------73 457475Elapsed: 00:00:00.60
4.2、执行误操作
1SYS@192.168.66.35:11521/lhrsdb> truncate table lhr.LHRTB_210702; 2 3Table truncated. 4 5Elapsed: 00:00:00.59 6SYS@192.168.66.35:11521/lhrsdb> 7SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702; 8 9 COUNT(1)10----------11 01213Elapsed: 00:00:00.18
4.3、开始恢复
1SYS@192.168.66.35:11521/lhrsdb> @D:\FY_Recover_Data.pck 2 3Package created. 4 5Elapsed: 00:00:00.29 6 7Package body created. 8 9Elapsed: 00:00:00.6710SYS@192.168.66.35:11521/lhrsdb> select * from v$tablespace;1112 TS# NAME INC BIG FLA ENC CON_ID13---------- ------------------------------ --- --- --- --- ----------14 1 SYSAUX YES NO YES 015 0 SYSTEM YES NO YES 016 2 UNDOTBS1 YES NO YES 017 4 USERS YES NO YES 018 3 TEMP NO NO YES 01920Elapsed: 00:00:00.1621SYS@192.168.66.35:11521/lhrsdb>22SYS@192.168.66.35:11521/lhrsdb> exec fy_recover_data.recover_truncated_table('LHR','LHRTB_210702');2316:03:26: New Directory Name: FY_DATA_DIR2416:03:26: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT2516:03:26: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT2616:03:27: Recover Table: LHR.LHRTB_210702$2716:03:27: Restore Table: LHR.LHRTB_210702$$2816:03:49: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT2916:03:49: begin to recover table LHR.LHRTB_2107023016:03:50: New Directory Name: TMP_HF_DIR3116:03:52: Recovering data in datafile /opt/oracle/oradata/LHRSDB/users01.dbf3216:03:52: Use existing Directory Name: TMP_HF_DIR3316:06:25: 5662 truncated data blocks found.3416:06:25: 290760 records recovered in backup table LHR.LHRTB_210702$$3516:06:25: Total: 5662 truncated data blocks found.3616:06:25: Total: 290760 records recovered in backup table LHR.LHRTB_210702$$3716:06:25: Recovery completed.3816:06:25: Data has been recovered to LHR.LHRTB_210702$$3940PL/SQL procedure successfully completed.4142Elapsed: 00:02:59.2743SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702$$;4445 COUNT(1)46----------47 2907604849Elapsed: 00:00:00.5250SYS@192.168.66.35:11521/lhrsdb>51SYS@192.168.66.35:11521/lhrsdb> alter table lhr.LHRTB_210702 nologging;5253Table altered.5455Elapsed: 00:00:00.2556SYS@192.168.66.35:11521/lhrsdb>57SYS@192.168.66.35:11521/lhrsdb> insert /*+append*/ into lhr.LHRTB_210702 select * from lhr.LHRTB_210702$$;5859290760 rows created.6061Elapsed: 00:00:01.9962SYS@192.168.66.35:11521/lhrsdb> commit;6364Commit complete.6566Elapsed: 00:00:00.9167SYS@192.168.66.35:11521/lhrsdb> alter table lhr.LHRTB_210702 logging;6869Table altered.7071Elapsed: 00:00:00.1172SYS@192.168.66.35:11521/lhrsdb>73SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702;7475 COUNT(1)76----------77 2907607879Elapsed: 00:00:00.25
4.4、清理临时数据
1SYS@192.168.66.35:11521/lhrsdb> select * from v$tablespace; 2 3 TS# NAME INC BIG FLA ENC CON_ID 4---------- ------------------------------ --- --- --- --- ---------- 5 1 SYSAUX YES NO YES 0 6 0 SYSTEM YES NO YES 0 7 2 UNDOTBS1 YES NO YES 0 8 4 USERS YES NO YES 0 9 3 TEMP NO NO YES 0 10 6 FY_REC_DATA YES NO YES 0 11 7 FY_RST_DATA YES NO YES 0 12 137 rows selected. 14 15SYS@192.168.66.35:11521/lhrsdb> set pagesize 9999 line 9999 16SYS@192.168.66.35:11521/lhrsdb> col TS_Name format a30 17SYS@192.168.66.35:11521/lhrsdb> col PDBNAME format a15 18SYS@192.168.66.35:11521/lhrsdb> col TS_NAME format a20 19SYS@192.168.66.35:11521/lhrsdb> col LOGGING format a10 20SYS@192.168.66.35:11521/lhrsdb> WITH wt1 AS 21 2 (SELECT ts.TABLESPACE_NAME, 22 3 df.all_bytes, 23 4 decode(df.TYPE, 24 5 'D', 25 6 nvl(fs.FREESIZ, 0), 26 7 'T', 27 8 df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ, 28 9 df.MAXSIZ, 29 10 ts.BLOCK_SIZE, 30 11 ts.LOGGING, 31 12 ts.FORCE_LOGGING, 32 13 ts.CONTENTS, 33 14 ts.EXTENT_MANAGEMENT, 34 15 ts.SEGMENT_SPACE_MANAGEMENT, 35 16 ts.RETENTION, 36 17 ts.DEF_TAB_COMPRESSION, 37 18 df.ts_df_count 38 19 FROM dba_tablespaces ts, 39 20 (SELECT 'D' TYPE, 40 21 TABLESPACE_NAME, 41 22 COUNT(*) ts_df_count, 42 23 SUM(BYTES) all_bytes, 43 24 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ 44 25 FROM dba_data_files d 45 26 GROUP BY TABLESPACE_NAME 46 27 UNION ALL 47 28 SELECT 'T', 48 29 TABLESPACE_NAME, 49 30 COUNT(*) ts_df_count, 50 31 SUM(BYTES) all_bytes, 51 32 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) 52 33 FROM dba_temp_files d 53 34 GROUP BY TABLESPACE_NAME) df, 54 35 (SELECT TABLESPACE_NAME, 55 36 SUM(BYTES) FREESIZ 56 37 FROM dba_free_space 57 38 GROUP BY TABLESPACE_NAME 58 39 UNION ALL 59 40 SELECT tablespace_name, 60 41 SUM(d.BLOCK_SIZE * a.BLOCKS) bytes 61 42 FROM gv$sort_usage a, 62 43 dba_tablespaces d 63 44 WHERE a.tablespace = d.tablespace_name 64 45 GROUP BY tablespace_name) fs 65 46 WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME 66 47 AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)) 67 48 SELECT (SELECT A.TS# 68 49 FROM V$TABLESPACE A 69 50 WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#, 70 51 t.TABLESPACE_NAME TS_Name, 71 52 round(t.all_bytes / 1024 / 1024) ts_size_M, 72 53 round(t.freesiz / 1024 / 1024) Free_Size_M, 73 54 round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, 74 55 round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per, 75 56 round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g, 76 57 round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 / 77 58 MAXSIZ, 78 59 3) USED_per_MAX, 79 60 round(t.BLOCK_SIZE) BLOCK_SIZE, 80 61 t.LOGGING, 81 62 t.ts_df_count 82 63 FROM wt1 t 83 64 UNION ALL 84 65 SELECT to_number('') TS#, 85 66 'ALL TS:' TS_Name, 86 67 round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M, 87 68 round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m, 88 69 round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, 89 70 round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per, 90 71 round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size, 91 72 to_number('') "USED,% of MAX Size", 92 73 to_number('') BLOCK_SIZE, 93 74 '' LOGGING, 94 75 to_number('') ts_df_count 95 76 FROM wt1 t 96 77 order by TS# 97 78 ; 98 99 TS# TS_NAME TS_SIZE_M FREE_SIZE_M USED_SIZE_M USED_PER MAX_SIZE_G USED_PER_MAX BLOCK_SIZE LOGGING TS_DF_COUNT100---------- -------------------- ---------- ----------- ----------- ---------- ---------- ------------ ---------- ---------- -----------101 0 SYSTEM 910 4 906 99.54 32 2.764 8192 LOGGING 1102 1 SYSAUX 670 30 640 95.476 32 1.952 8192 LOGGING 1103 2 UNDOTBS1 345 271 74 21.413 32 .225 8192 LOGGING 1104 3 TEMP 33 27 6 18.182 32 .018 8192 NOLOGGING 1105 4 USERS 344 2 341 99.291 32 1.042 8192 LOGGING 1106 6 FY_REC_DATA 0 0 0 100 0 100 8192 LOGGING 1107 7 FY_RST_DATA 57 3 54 95.154 32 .165 8192 LOGGING 1108 ALL TS: 2358.625 338 2021 85.678 1921091108 rows selected.111112Elapsed: 00:00:00.59113SYS@192.168.66.35:11521/lhrsdb>114SYS@192.168.66.35:11521/lhrsdb> drop tablespace FY_REC_DATA including contents and datafiles;115116Tablespace dropped.117118Elapsed: 00:00:12.01119SYS@192.168.66.35:11521/lhrsdb>120SYS@192.168.66.35:11521/lhrsdb>121SYS@192.168.66.35:11521/lhrsdb> drop tablespace FY_RST_DATA including contents and datafiles;122123Tablespace dropped.124125Elapsed: 00:00:11.43126SYS@192.168.66.35:11521/lhrsdb> drop table lhr.LHRTB_210702$$;127drop table lhr.LHRTB_210702$$128 *129ERROR at line 1:130ORA-00942: table or view does not exist131132133Elapsed: 00:00:00.15134SYS@192.168.66.35:11521/lhrsdb>
总体而言用fy_recover_data包是非常好的，fy_recover_data可以恢复truncate的数据，但不能恢复drop的数据。
五、附录：包FY_Recover_Data的内容
1create or replace package sys.FY_Recover_Data is 2 --------------------------------------------------------------------------- 3 -- WWW.HelloDBA.COM --- 4 -- Created By: Fuyuncat --- 5 -- Created Date: 08/08/2012 --- 6 -- Email: Fuyuncat@gmail.com --- 7 -- Copyright (c), 2014, WWW.HelloDBA.COM All rights reserved. --- 8 -- Latest Version: http://www.HelloDBA.com/download/FY_Recover_Data.zip --- 9 -- --- 10 -- Update Logs --- 11 -- 15/08/2012, Fuyuncat: --- 12 -- 1. Fixed Bug in Clean_Up_Ts (Not change TS status correctly) --- 13 -- 2. Added Exception Handle when Restore Data --- 14 -- 3. Added Parameter in recover_table, --- 15 -- to balance Fault Tolerance and Performance --- 16 -- --- 17 -- 16/08/2012, Fuyuncat: --- 18 -- 1. Enhanced corrupted block processing, get rows as possilbe --- 19 -- --- 20 -- 17/08/2012, Fuyuncat: --- 21 -- 1. Omit the LOB columns raised ORA-22922 exception --- 22 -- --- 23 -- 20/08/2012, Fuyuncat: --- 24 -- 1. Omit the LOB columns via db link --- 25 -- --- 26 -- 22/08/2012, Fuyuncat: --- 27 -- 1. Updated logging and tracing interface --- 28 -- --- 29 -- 19/02/2014, Fuyuncat: --- 30 -- 1. Temp Restore and Recover tablespace --- 31 -- will be created on temp folder --- 32 -- 2. Handle tablespace has files located at diff folders --- 33 -- 3. Handle tables on ASM --- 34 -- --- 35 -- 05/03/2014, Fuyuncat: --- 36 -- 1. Fixed bugs --- 37 -- 2. Use existing dirctory if applicable --- 38 -- 3. Recover data from offline files --- 39 --------------------------------------------------------------------------- 40 41 type r_cursor is REF CURSOR; 42 type o_fileprop is record( 43 file# number, 44 status$ number); 45 type t_fileprops is table of o_fileprop; 46 47 /************************************************************************ 48 ** recover truncated table 49 ** 50 ** tgtowner: Owner of Target Table to be recovered; 51 ** tgttable: Name of Target Table to be recovered; 52 ** datapath: Absolute path of Data Files; 53 ** fbks: block number to be filled in recovery table; 54 ** offline_files: Offline data files that data should be recovered from; 55 ** foramt: full_path_file1;full_path_file2...; 56 ************************************************************************/ 57 procedure recover_truncated_table(tow varchar2, 58 ttb varchar2, 59 fbks number default 1, 60 tmppath varchar2 default null, 61 offline_files varchar2 default null); 62 63 /************************************************************************ 64 ** dump a block in raw, for testing 65 ** 66 ** hdfile: Data file name; 67 ** srcdir: data file directory 68 ** blknb: block number to be dumped; 69 ** blksz: block size; 70 ************************************************************************/ 71 procedure dump_seg_block_raw(hdfile varchar2, 72 srcdir varchar2, 73 blknb number, 74 blksz number default 8192); 75 /* 76 procedure test_chain(filename varchar2, 77 blknum number, 78 startpos number, 79 repcont raw, 80 srcdir varchar2 default 'FY_DATA_DIR'); 81 */ 82 /************************************************************************ 83 ** Set Initial parameters 84 ** 85 ** tracing: trace the process for debug; 86 ** logging: show logging information; 87 ** repobjid: replace the data object id wiht the recover table data object id; 88 ************************************************************************/ 89 procedure init_set(tracing boolean default true, 90 logging boolean default true, 91 repobjid boolean default true); 92end FY_Recover_Data; 93/ 94create or replace package body sys.FY_Recover_Data is 95 --------------------------------------------------------------------------- 96 -- WWW.HelloDBA.COM --- 97 -- Created By: Fuyuncat --- 98 -- Created Date: 08/08/2012 --- 99 -- Email: Fuyuncat@gmail.com --- 100 -- Copyright (c), 2014, WWW.HelloDBA.COM All rights reserved. --- 101 -- Latest Version: http://www.HelloDBA.com/download/FY_Recover_Data.zip --- 102 --------------------------------------------------------------------------- 103 104 s_tracing boolean := false; 105 s_logging boolean := true; 106 s_repobjid boolean := false; 107 108 procedure init_set(tracing boolean default true, 109 logging boolean default true, 110 repobjid boolean default true) as 111 begin 112 s_tracing := tracing; 113 s_logging := logging; 114 s_repobjid := repobjid; 115 end; 116 117 procedure trace(msg varchar2) as 118 begin 119 if s_tracing then 120 dbms_output.put_line(to_char(sysdate, 'HH24:MI:SS') || ': ' || msg); 121 end if; 122 end; 123 124 procedure log(msg varchar2) as 125 begin 126 if s_logging then 127 dbms_output.put_line(to_char(sysdate, 'HH24:MI:SS') || ': ' || msg); 128 end if; 129 end; 130 131 function d2r(dig varchar2, len number default 0) return raw is 132 begin 133 --trace('[d2r] hextoraw(lpad(trim(to_char('||dig||', ''XXXXXXXX'')),'||len||',''0''))'); 134 return hextoraw(lpad(trim(to_char(dig, 'XXXXXXXX')), len, '0')); 135 end; 136 137 /************************************************************************ 138 ** Copy file 139 ** 140 ** srcdir: Directory of Source File; 141 ** srcfile: Source File Name; 142 ** dstdir: Directory of Destination File; 143 ** dstfile: Destination File Name; 144 ************************************************************************/ 145 procedure copy_file(srcdir varchar2, 146 srcfile varchar2, 147 dstdir varchar2, 148 dstfile in out varchar2) as 149 --p_srcdir varchar2(255) := upper(srcdir); 150 --p_srcfile varchar2(255) := upper(srcfile); 151 --p_dstdir varchar2(255) := upper(dstdir); 152 --p_dstfile varchar2(255) := upper(dstfile); 153 p_srcdir varchar2(255) := srcdir; 154 p_srcfile varchar2(255) := srcfile; 155 p_dstdir varchar2(255) := dstdir; 156 p_dstfile varchar2(255) := dstfile; 157 file_copied boolean := false; 158 retries pls_integer := 0; 159 begin 160 if dstdir is null then 161 p_dstdir := p_srcdir; 162 end if; 163 if dstfile is null then 164 p_dstfile := p_srcfile || '$'; 165 dstfile := p_dstfile; 166 end if; 167 while not file_copied loop 168 begin 169 trace('[copy_file] begin copy file: ' || p_srcdir || '\' || 170 p_srcfile || ' => ' || p_dstdir || '\' || p_dstfile); --' 171 DBMS_FILE_TRANSFER.copy_file(p_srcdir, 172 p_srcfile, 173 p_dstdir, 174 p_dstfile); 175 trace('[copy_file] completed.'); 176 file_copied := true; 177 exception 178 when others then 179 -- file already exists 180 if sqlcode = -19504 and 181 instr(dbms_utility.format_error_stack, 'ORA-27038') > 0 and 182 retries < 10 then 183 trace('[copy_file] file ' || p_dstdir || '\' || p_dstfile || 184 ' exists, rename to ' || dstfile || retries); 185 retries := retries + 1; 186 p_dstfile := dstfile || retries; 187 file_copied := false; 188 else 189 --log(dbms_utility.format_error_backtrace); 190 file_copied := true; 191 raise; 192 end if; 193 end; 194 end loop; 195 dstfile := p_dstfile; 196 end; 197 198 /************************************************************************ 199 ** Remove file 200 ** 201 ** dir: Directory of the File; 202 ** file: File to be removed; 203 ************************************************************************/ 204 procedure remove_file(dir varchar2, file varchar2) as 205 begin 206 trace('[remove_file] Begin to remove file ' || dir || '/' || file); 207 utl_file.fremove(dir, file); 208 trace('[remove_file] ' || dir || '/' || file || ' has been removed.'); 209 end; 210 211 function gen_table_name(tgttable varchar2, 212 plus varchar2 default '', 213 genowner varchar2 default user) return varchar2 as 214 gentab varchar2(30); 215 begin 216 select upper(tgttable || plus || surfix) 217 into gentab 218 from (select surfix 219 from (select null surfix 220 from dual 221 union all 222 select level surfix 223 from dual 224 connect by level <= 255) 225 where not exists 226 (select 1 227 from dba_tables 228 where owner = genowner 229 and table_name = upper(tgttable || plus || surfix)) 230 order by surfix nulls first) 231 where rownum <= 1; 232 return gentab; 233 end; 234 235 function gen_file_name(tgtfile varchar2, plus varchar2 default '') 236 return varchar2 as 237 genfile varchar2(30); 238 slash char(1); 239 begin 240 select decode(instr(platform_name, 'Windows'), 0, '/', '\') 241 into slash 242 from v_$database 243 where rownum <= 1; 244 select tgtfile || plus || surfix || '.DAT' 245 into genfile 246 from (select surfix 247 from (select null surfix 248 from dual 249 union all 250 select level surfix 251 from dual 252 connect by level <= 255) 253 where not exists (select 1 254 from dba_data_files 255 where file_name like '%' || slash || tgtfile || plus || 256 surfix || '.DAT') 257 order by surfix nulls first) 258 where rownum <= 1; --' 259 return genfile; 260 end; 261 262 function gen_ts_name(tgtts varchar2, plus varchar2 default '') 263 return varchar2 as 264 gents varchar2(30); 265 begin 266 select tgtts || plus || surfix 267 into gents 268 from (select surfix 269 from (select null surfix 270 from dual 271 union all 272 select level surfix 273 from dual 274 connect by level <= 255) 275 where not exists 276 (select 1 277 from dba_tablespaces 278 where tablespace_name = tgtts || plus || surfix) 279 order by surfix nulls first) 280 where rownum <= 1; 281 return gents; 282 end; 283 284 procedure create_directory(path varchar2, dir in out varchar2) as 285 exists_path pls_integer; 286 exists_dir varchar2(256); 287 slash char(1); 288 begin 289 select decode(instr(platform_name, 'Windows'), 0, '/', '\') 290 into slash 291 from v_$database 292 where rownum <= 1; 293 -- windows 294 if slash = '\' then 295 select count(1), max(directory_name) 296 into exists_path, exists_dir 297 from dba_directories 298 where owner = user 299 and upper(directory_path) || 300 decode(substr(directory_path, length(directory_path)), 301 slash, 302 '', 303 slash) = 304 upper(path) || 305 decode(substr(path, length(path)), slash, '', slash); 306 else 307 -- linux/unix 308 select count(1), max(directory_name) 309 into exists_path, exists_dir 310 from dba_directories 311 where owner = user 312 and directory_path || 313 decode(substr(directory_path, length(directory_path)), 314 slash, 315 '', 316 slash) = 317 path || decode(substr(path, length(path)), slash, '', slash); 318 end if; 319 trace('[create_directory] Exists directory number ' || exists_path); 320 if exists_path = 0 then 321 select dir || surfix 322 into dir 323 from (select surfix 324 from (select null surfix 325 from dual 326 union all 327 select level surfix 328 from dual 329 connect by level <= 255) 330 where not exists 331 (select 1 332 from dba_directories 333 where directory_name = dir || surfix) 334 order by surfix nulls first) 335 where rownum <= 1; 336 log('New Directory Name: ' || dir); 337 execute immediate 'create directory ' || dir || ' as ''' || path || ''''; 338 else 339 dir := exists_dir; 340 log('Use existing Directory Name: ' || dir); 341 end if; 342 end; 343 344 procedure replace_segmeta_in_file(tmpdir varchar2, 345 tmpcopyf varchar2, 346 dstdir varchar2, 347 dstfile in out varchar2, 348 dstisfilesystem boolean, 349 tgtobjid number, 350 newobjid number, 351 dtail raw, 352 addpos number, 353 addinfo raw, 354 blksz number default 8192, 355 endianess number default 1) as 356 bfr utl_file.file_type; 357 bfw utl_file.file_type; 358 hsz number := 24; 359 objr raw(4); 360 objn number; 361 dhead raw(32); 362 dbody raw(32767); 363 nbody raw(32767); 364 p_tmpdir varchar2(255) := tmpdir; 365 p_tmpcopyf varchar2(255) := tmpcopyf; 366 p_dstdir varchar2(255) := dstdir; 367 p_tmpdstfile varchar2(255); 368 p_finaldstdir varchar2(255); 369 begin 370 if p_dstdir is null then 371 p_dstdir := p_tmpdir; 372 end if; 373 trace('[replace_objid_in_file] replace object id in ' || tmpdir || '\' || 374 tmpcopyf || ' [' || tgtobjid || ' => ' || newobjid || ']'); --' 375 if not dstisfilesystem then 376 p_tmpdstfile := gen_file_name(dstfile, ''); 377 copy_file(dstdir, dstfile, p_tmpdir, p_tmpdstfile); 378 p_finaldstdir := p_tmpdir; 379 else 380 p_tmpdstfile := dstfile; 381 p_finaldstdir := p_dstdir; 382 end if; 383 bfr := utl_file.fopen(p_tmpdir, p_tmpcopyf, 'RB'); 384 bfw := utl_file.fopen(p_finaldstdir, p_tmpdstfile, 'WB'); 385 while true loop 386 begin 387 nbody := ''; 388 utl_file.get_raw(bfr, dhead, hsz); 389 exit when dhead is null; 390 utl_file.get_raw(bfr, dbody, blksz - hsz); 391 --objr := hextoraw(substrb(rawtohex(dbody), 1, 8)); 392 objr := utl_raw.substr(dbody, 1, 4); 393 if endianess > 0 then 394 objn := to_number(rawtohex(utl_raw.reverse(objr)), 'XXXXXXXX'); 395 else 396 objn := to_number(rawtohex(objr), 'XXXXXXXX'); 397 end if; 398 -- replace data object id with the recover object id 399 --if objn = tgtobjid and substrb(rawtohex(dhead), 1, 2) = '06' then 400 if objn = tgtobjid then 401 if addpos <= hsz then 402 --utl_file.put_raw(bfw, utl_raw.concat(utl_raw.substr(dhead, 1, addpos-1), addinfo, utl_raw.substr(dhead, addpos+utl_raw.length(addinfo)))); 403 nbody := utl_raw.concat(nbody, 404 utl_raw.substr(dhead, 1, addpos - 1), 405 addinfo, 406 utl_raw.substr(dhead, 407 addpos + 408 utl_raw.length(addinfo))); 409 else 410 --utl_file.put_raw(bfw, dhead); 411 nbody := utl_raw.concat(nbody, dhead); 412 end if; 413 --utl_file.put_raw(bfw, utl_raw.concat(utl_raw.substr(dhead, 1, 8), addinfo, utl_raw.substr(dhead, 9+utl_raw.length(addinfo)))); 414 --nbody := utl_raw.concat(nbody, utl_raw.substr(dhead, 1, 8), addinfo, utl_raw.substr(dhead, 9+utl_raw.length(addinfo))); 415 --trace('[replace_objid_in_file] old id in raw: '||rawtohex(objr)); 416 if endianess > 0 then 417 --trace('[replace_objid_in_file] new id in raw: '||utl_raw.reverse(d2r(newobjid, 8))); 418 --utl_file.put_raw(bfw, utl_raw.reverse(d2r(newobjid, 8))); 419 nbody := utl_raw.concat(nbody, 420 utl_raw.reverse(d2r(newobjid, 8))); 421 else 422 --trace('[replace_objid_in_file] new id in raw: '||(d2r(newobjid, 8))); 423 --utl_file.put_raw(bfw, d2r(newobjid, 8)); 424 nbody := utl_raw.concat(nbody, d2r(newobjid, 8)); 425 end if; 426 -- skip objid 427 if addpos > hsz + 5 and addinfo is not null then 428 trace('[replace_objid_in_file] old body len: ' || 429 utl_raw.length(dbody) || ' new = 4 + ' || 430 utl_raw.length(utl_raw.substr(dbody, 5, addpos - hsz - 5)) || 431 ' + ' || utl_raw.length(addinfo) || ' + ' || 432 utl_raw.length(utl_raw.substr(dbody, 433 addpos - hsz - 4 + 434 utl_raw.length(addinfo), 435 blksz - (addpos - 1) - 436 utl_raw.length(dtail) - 437 utl_raw.length(addinfo))) || 438 ' + 4'); 439 --utl_file.put_raw(bfw, utl_raw.concat(utl_raw.substr(dbody, 5, addpos-hsz-5), addinfo, utl_raw.substr(dbody, addpos-hsz, blksz-(addpos-1)-utl_raw.length(dtail)-utl_raw.length(addinfo)))); 440 nbody := utl_raw.concat(nbody, 441 utl_raw.substr(dbody, 442 5, 443 addpos - hsz - 5), 444 addinfo, 445 utl_raw.substr(dbody, 446 addpos - hsz - 4 + 447 utl_raw.length(addinfo), 448 blksz - (addpos - 1) - 449 utl_raw.length(dtail) - 450 utl_raw.length(addinfo))); 451 --trace('[replace_objid_in_file] new body len: '||utl_raw.length(nbody)); 452 elsif addpos = hsz + 5 and addinfo is not null then 453 --utl_file.put_raw(bfw, utl_raw.concat(addinfo, utl_raw.substr(dbody, addpos-hsz, blksz-(addpos-1)-utl_raw.length(dtail)-utl_raw.length(addinfo)))); 454 nbody := utl_raw.concat(nbody, 455 addinfo, 456 utl_raw.substr(dbody, 457 addpos - hsz, 458 blksz - (addpos - 1) - 459 utl_raw.length(dtail) - 460 utl_raw.length(addinfo))); 461 else 462 --utl_file.put_raw(bfw, utl_raw.substr(dbody, 5, blksz-hsz-4-utl_raw.length(dtail))); 463 nbody := utl_raw.concat(nbody, 464 utl_raw.substr(dbody, 465 5, 466 blksz - hsz - 4 - 467 utl_raw.length(dtail))); 468 end if; 469 --trace('[replace_objid_in_file] tail in raw: '||dtail||'('||utl_raw.length(dtail)||')'); 470 --utl_file.put_raw(bfw, dtail); 471 nbody := utl_raw.concat(nbody, dtail); 472 trace('[replace_objid_in_file] new body length: ' || 473 utl_raw.length(nbody)); 474 else 475 --utl_file.put_raw(bfw, dhead); 476 --utl_file.put_raw(bfw, dbody); 477 nbody := utl_raw.concat(nbody, dhead, dbody); 478 end if; 479 --if utl_raw.length(nbody) != blksz then 480 -- trace('[replace_objid_in_file] new body length: '||utl_raw.length(nbody)); 481 --end if; 482 utl_file.put_raw(bfw, nbody); 483 484 utl_file.fflush(bfw); 485 exception 486 when no_data_found then 487 exit; 488 when others then 489 trace('[replace_objid_in_file] ' || SQLERRM); 490 trace('[replace_objid_in_file] ' || 491 dbms_utility.format_error_backtrace); 492 exit; 493 end; 494 end loop; 495 utl_file.fclose(bfw); 496 utl_file.fclose(bfr); 497 if not dstisfilesystem then 498 copy_file(p_tmpdir, p_tmpdstfile, dstdir, dstfile); 499 remove_file(p_tmpdir, p_tmpdstfile); 500 end if; 501 trace('[replace_objid_in_file] completed.'); 502 end; 503 504 function get_cols_no_lob(recowner varchar2, rectab varchar2) 505 return varchar2 as 506 cols varchar2(32767); 507 colno number := 0; 508 begin 509 cols := ''; 510 for col_rec in (select column_name, data_type, nullable 511 from dba_tab_cols 512 where owner = recowner 513 and table_name = rectab) loop 514 if col_rec.data_type NOT LIKE '%LOB' then 515 if colno > 0 then 516 cols := cols || ','; 517 end if; 518 cols := cols || col_rec.column_name; 519 colno := colno + 1; 520 end if; 521 end loop; 522 return cols; 523 end; 524 525 function restore_table_row_no_lob(recowner varchar2, 526 rectab varchar2, 527 rstowner varchar2, 528 rsttab varchar2, 529 cols varchar2, 530 rid rowid) return number as 531 recnum number := 0; 532 begin 533 begin 534 execute immediate 'insert /*+*/ into ' || rstowner || '.' || rsttab || '(' || cols || 535 ') select ' || cols || ' from ' || recowner || '.' || 536 rectab || ' where rowid = :rid' 537 using rid; 538 recnum := recnum + SQL%ROWCOUNT; 539 exception 540 when others then 541 trace('[restore_table_row_no_lob] ' || SQLERRM); 542 trace('[restore_table_row_no_lob] ' || 543 dbms_utility.format_error_backtrace); 544 null; 545 end; 546 return recnum; 547 end; 548 549 function restore_table_in_rows(recowner varchar2, 550 rectab varchar2, 551 rstowner varchar2, 552 rsttab varchar2) return number as 553 recnum number := 0; 554 blk_cur r_cursor; 555 objid number; 556 fid number; 557 blkno number; 558 rnum number; 559 gnum number; 560 cols varchar2(32767); 561 begin 562 begin 563 --trace('[restore_table_in_rows] '||'select dbms_rowid.rowid_object(rowid) objid, dbms_rowid.rowid_relative_fno(rowid) fid, dbms_rowid.rowid_block_number(rowid) blkno, count(1) rnum from '||recowner||'.'||rectab||' group by dbms_rowid.rowid_object(rowid), dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)'); 564 open blk_cur for 'select dbms_rowid.rowid_object(rowid) objid, dbms_rowid.rowid_relative_fno(rowid) fid, dbms_rowid.rowid_block_number(rowid) blkno, count(1) rnum from ' || recowner || '.' || rectab || ' group by dbms_rowid.rowid_object(rowid), dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)'; 565 loop 566 fetch blk_cur 567 into objid, fid, blkno, rnum; 568 exit when blk_cur%NOTFOUND; 569 trace('[restore_table_in_rows] expected rows: ' || rnum); 570 gnum := 0; 571 --trace('[restore_table_in_rows] block: '||blkno); 572 for i in 1 .. rnum loop 573 begin 574 --trace('[restore_table_in_rows] row: '||i); 575 --execute immediate 'insert /*+*/into '||rstowner||'.'||rsttab||' select * from '||recowner||'.'||rectab||' where rowid = dbms_rowid.rowid_create(1, :objid, :fid, :blkno, :i)' using objid, fid, blkno, i-1; 576 execute immediate 'insert /*+*/into ' || rstowner || '.' || 577 rsttab || ' select * from ' || recowner || '.' || 578 rectab || 579 ' where dbms_rowid.rowid_relative_fno(ROWID)=:fid and dbms_rowid.rowid_block_number(ROWID)=:blkno and dbms_rowid.rowid_row_number(ROWID)=:i' 580 using fid, blkno, i - 1; 581 recnum := recnum + SQL%ROWCOUNT; 582 gnum := gnum + SQL%ROWCOUNT; 583 exception 584 when others then 585 if sqlcode = -22922 then 586 -- trace('[restore_table_in_rows] Warning: Unrecoverable Lob found!'); 587 if cols is null then 588 cols := get_cols_no_lob(recowner, rectab); 589 end if; 590 recnum := recnum + 591 restore_table_row_no_lob(recowner, 592 rectab, 593 rstowner, 594 rsttab, 595 cols, 596 dbms_rowid.rowid_create(1, 597 objid, 598 fid, 599 blkno, 600 i - 1)); 601 else 602 trace('[restore_table_in_rows] ' || SQLERRM); 603 trace('[restore_table_in_rows] ' || 604 dbms_utility.format_error_backtrace); 605 end if; 606 null; 607 end; 608 end loop; 609 if gnum != rnum then 610 log('Warning: ' || (rnum - gnum) || ' records lost!'); 611 end if; 612 end loop; 613 exception 614 when others then 615 trace('[restore_table_in_rows] ' || sqlerrm); 616 trace('[restore_table_in_rows] ' || 617 dbms_utility.format_error_backtrace); 618 null; 619 end; 620 return recnum; 621 end; 622 623 function restore_table_in_rows_remote(recowner varchar2, 624 rectab varchar2, 625 rstowner varchar2, 626 rsttab varchar2, 627 dblink varchar2) return number as 628 recnum number := 0; 629 blk_cur r_cursor; 630 objid number; 631 fid number; 632 blkno number; 633 rnum number; 634 cols varchar2(32767); 635 begin 636 begin 637 --rollback; 638 open blk_cur for 'select dbms_rowid.rowid_object(rowid) objid, dbms_rowid.rowid_relative_fno(rowid) fid, dbms_rowid.rowid_block_number(rowid) blkno, count(1) rnum from ' || recowner || '.' || rectab || ' group by dbms_rowid.rowid_object(rowid), dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)'; 639 loop 640 fetch blk_cur 641 into objid, fid, blkno, rnum; 642 exit when blk_cur%NOTFOUND; 643 trace('[restore_table_in_rows_remote] expected rows: ' || rnum); 644 for i in 1 .. rnum loop 645 begin 646 --execute immediate 'insert /*+no_append*/into '||rstowner||'.'||rsttab||' select * from '||recowner||'.'||rectab||'@'||dblink||' where rowid = dbms_rowid.rowid_create(1, :objid, :fid, :blkno, :i)' using objid, fid, blkno, i-1; 647 execute immediate 'insert /*+*/into ' || rstowner || '.' || 648 rsttab || ' select * from ' || recowner || '.' || 649 rectab || '@' || dblink || 650 ' where dbms_rowid.rowid_relative_fno(ROWID)=:fid and dbms_rowid.rowid_block_number(ROWID)=:blkno and dbms_rowid.rowid_row_number(ROWID)=:i' 651 using fid, blkno, i - 1; 652 recnum := recnum + SQL%ROWCOUNT; 653 --commit; 654 exception 655 when others then 656 if sqlcode = -22922 then 657 if cols is null then 658 cols := get_cols_no_lob(recowner, rectab); 659 end if; 660 recnum := recnum + 661 restore_table_row_no_lob(recowner, 662 rectab, 663 rstowner, 664 rsttab, 665 cols, 666 dbms_rowid.rowid_create(1, 667 objid, 668 fid, 669 blkno, 670 i - 1)); 671 else 672 trace('[restore_table_in_rows_remote] ' || SQLERRM); 673 trace('[restore_table_in_rows_remote] ' || 674 dbms_utility.format_error_backtrace); 675 --commit; 676 end if; 677 null; 678 end; 679 end loop; 680 end loop; 681 end; 682 return recnum; 683 end; 684 685 function restore_table_ctas(recowner varchar2, 686 rectab varchar2, 687 rstowner varchar2, 688 rsttab varchar2) return number as 689 recnum number := 0; 690 tmptab varchar2(30); 691 begin 692 tmptab := gen_table_name(rsttab, '', rstowner); 693 begin 694 execute immediate 'create table ' || rstowner || '.' || tmptab || 695 ' as select /*+full(t)*/* from ' || recowner || '.' || 696 rectab || ' t'; 697 execute immediate 'insert /*+append*/ into ' || rstowner || '.' || 698 rsttab || ' select /*+full(t)*/* from ' || recowner || '.' || 699 tmptab || ' t'; 700 recnum := SQL%ROWCOUNT; 701 execute immediate 'drop table ' || rstowner || '.' || tmptab; 702 exception 703 when others then 704 --trace('[restore_table_ctas] '||SQLERRM); 705 --trace('[restore_table_ctas] '||dbms_utility.format_error_backtrace); 706 null; 707 end; 708 return recnum; 709 end; 710 711 function restore_table_no_lob(recowner varchar2, 712 rectab varchar2, 713 rstowner varchar2, 714 rsttab varchar2) return number as 715 recnum number := 0; 716 cols varchar2(32767); 717 begin 718 cols := get_cols_no_lob(recowner, rectab); 719 720 begin 721 --execute immediate 'alter system flush buffer_cache'; 722 execute immediate 'insert /*+append*/ into ' || rstowner || '.' || 723 rsttab || '(' || cols || ') select /*+full(t)*/' || cols || 724 ' from ' || recowner || '.' || rectab || ' t'; 725 726 recnum := recnum + SQL%ROWCOUNT; 727 exception 728 when others then 729 --raise; 730 if sqlcode = -22922 then 731 null; 732 else 733 recnum := recnum + restore_table_in_rows(recowner, 734 rectab, 735 rstowner, 736 rsttab); 737 end if; 738 end; 739 trace('[restore_table_no_lob] ' || recnum || ' records recovered'); 740 return recnum; 741 end; 742 743 function restore_table(recowner varchar2, 744 rectab varchar2, 745 rstowner varchar2, 746 rsttab varchar2, 747 selflink varchar2 default '') return number as 748 recnum number := 0; 749 expnum number := 0; 750 begin 751 begin 752 trace('[restore_table] Trying to restore data to ' || rstowner || '.' || 753 rsttab); 754 execute immediate 'alter system flush buffer_cache'; 755 if s_tracing then 756 execute immediate 'select /*+full(t)*/count(*) from ' || recowner || '.' || 757 rectab || ' t' 758 into expnum; 759 trace('[restore_table] Expected Records in this round: ' || expnum); 760 end if; 761 execute immediate 'insert /*+append*/ into ' || rstowner || '.' || 762 rsttab || ' select /*+full(t)*/* from ' || recowner || '.' || 763 rectab || ' t'; 764 765 recnum := recnum + SQL%ROWCOUNT; 766 if s_tracing and expnum != SQL%ROWCOUNT then 767 trace('[restore_table] ' || (expnum - SQL%ROWCOUNT) || 768 ' records lost!'); 769 return - 1; -- for test 770 end if; 771 exception 772 when others then 773 --raise; 774 if sqlcode = -22922 then 775 log('Warning: Unrecoverable Lob found!'); 776 recnum := recnum + 777 restore_table_in_rows_remote(recowner, 778 rectab, 779 rstowner, 780 rsttab, 781 selflink); 782 --recnum := recnum + restore_table_no_lob(recowner, rectab, rstowner, rsttab); 783 else 784 trace(SQLERRM); 785 trace('[restore_table] ' || dbms_utility.format_error_backtrace); 786 --recnum := recnum + restore_table_in_rows_remote(recowner, rectab, rstowner, rsttab, selflink); 787 --return -1; -- test 788 recnum := recnum + restore_table_in_rows(recowner, 789 rectab, 790 rstowner, 791 rsttab); 792 end if; 793 end; 794 execute immediate 'alter system flush buffer_cache'; 795 trace('[restore_table] ' || recnum || ' records recovered'); 796 return recnum; 797 end; 798 799 procedure get_seg_meta(segowner varchar2, 800 segname varchar2, 801 tmpdir varchar2, 802 dtail out raw, 803 addinfo out raw, 804 blksz number default 8192) as 805 frw raw(32767); 806 firstblk number; 807 slash char(1); 808 hdfile varchar2(255); 809 hdfpath varchar2(4000); 810 hdfdir varchar2(255) := 'TMP_HF_DIR'; 811 finaldir varchar2(255); 812 bfo utl_file.file_type; 813 i number := 0; 814 begin 815 select decode(instr(platform_name, 'Windows'), 0, '/', '\') 816 into slash 817 from v_$database 818 where rownum <= 1; 819 select header_block + 1 820 into firstblk 821 from dba_segments 822 where owner = segowner 823 and segment_name = segname; 824 select substr(file_name, instr(d.file_name, slash, -1) + 1), 825 substr(file_name, 1, instr(d.file_name, slash, -1)) 826 into hdfile, hdfpath 827 from dba_data_files d, dba_segments s 828 where s.header_file = d.file_id 829 and s.owner = segowner 830 and s.segment_name = segname; --' 831 832 trace('[get_seg_meta] ' || hdfpath || hdfile); 833 create_directory(hdfpath, hdfdir); 834 -- copy ASM file to temp os folder 835 if hdfpath like '+%' or hdfpath like '/dev/%' then 836 copy_file(hdfdir, hdfile, tmpdir, hdfile); 837 finaldir := tmpdir; 838 else 839 finaldir := hdfdir; 840 end if; 841 842 bfo := utl_file.fopen(finaldir, hdfile, 'RB'); 843 844 -- reach to the truncated data blocks 845 i := 0; 846 while true loop 847 begin 848 utl_file.get_raw(bfo, frw, blksz); 849 i := i + 1; 850 851 exit when i = firstblk; 852 exception 853 when others then 854 exit; 855 end; 856 end loop; 857 858 utl_file.get_raw(bfo, frw, blksz); 859 860 dtail := utl_raw.substr(frw, blksz - 3, 4); 861 addinfo := utl_raw.substr(frw, 39, 2); 862 863 utl_file.fclose(bfo); 864 865 if hdfpath like '+%' or hdfpath like '/dev/%' then 866 remove_file(tmpdir, hdfile); 867 end if; 868 -- execute immediate 'drop directory '||hdfdir; 869 end; 870 871 function get_seg_data_id(segowner varchar2, 872 segname varchar2, 873 tmpdir varchar2, 874 blksz number default 8192, 875 endianess number default 1) return number as 876 frw raw(32767); 877 hsz number := 28; 878 firstblk number; 879 slash char(1); 880 hdfile varchar2(255); 881 hdfpath varchar2(4000); 882 hdfdir varchar2(255) := 'TMP_HF_DIR'; 883 finaldir varchar2(255); 884 bfo utl_file.file_type; 885 i number := 0; 886 objr raw(4); 887 objn number; 888 begin 889 select decode(instr(platform_name, 'Windows'), 0, '/', '\') 890 into slash 891 from v_$database 892 where rownum <= 1; 893 select header_block + 1 894 into firstblk 895 from dba_segments 896 where owner = segowner 897 and segment_name = segname; 898 select substr(file_name, instr(d.file_name, slash, -1) + 1), 899 substr(file_name, 1, instr(d.file_name, slash, -1)) 900 into hdfile, hdfpath 901 from dba_data_files d, dba_segments s 902 where s.header_file = d.file_id 903 and s.owner = segowner 904 and s.segment_name = segname; --' 905 906 trace('[get_seg_data_id] ' || hdfpath || hdfile); 907 create_directory(hdfpath, hdfdir); 908 -- copy ASM file to temp os folder 909 if hdfpath like '+%' or hdfpath like '/dev/%' then 910 copy_file(hdfdir, hdfile, tmpdir, hdfile); 911 finaldir := tmpdir; 912 else 913 finaldir := hdfdir; 914 end if; 915 916 bfo := utl_file.fopen(finaldir, hdfile, 'RB'); 917 918 -- reach to the truncated data blocks 919 i := 0; 920 while true loop 921 begin 922 utl_file.get_raw(bfo, frw, blksz); 923 i := i + 1; 924 925 exit when i = firstblk; 926 exception 927 when others then 928 exit; 929 end; 930 end loop; 931 932 utl_file.get_raw(bfo, frw, hsz); 933 934 objr := utl_raw.substr(frw, 25, 4); 935 if endianess > 0 then 936 objn := to_number(rawtohex(utl_raw.reverse(objr)), 'XXXXXXXX'); 937 else 938 objn := to_number(rawtohex(objr), 'XXXXXXXX'); 939 end if; 940 941 utl_file.fclose(bfo); 942 943 if hdfpath like '+%' or hdfpath like '/dev/%' then 944 remove_file(tmpdir, hdfile); 945 end if; 946 -- execute immediate 'drop directory '||hdfdir; 947 return objn; 948 end; 949 950 /************************************************************************ 951 ** Recover Table Data From Special Data File; 952 ** 953 ** oriobjid: Object Id of Table to be Recovered; 954 ** recowner: Owner of Table to be used as recovering dummy table; 955 ** rectable: Name of Table to be used as recovering dummy table; 956 ** rstowner: Owner of Table to store the recovered data; 957 ** rsttable: Name of Table to store the recovered data; 958 ** srcdir: Directory of the Data File to be recovered; 959 ** srcfile: Name of the Data File to be recovered; 960 ** srcisfilesystem: Is the source file located in file system or not; 961 ** tmpdir: Temp Directory to create restore tablespace and other temp files; 962 ** recfile: Name of Data File that rectable is stored; 963 ** coryfile: Name of Copy of Data File that rectable is stored; 964 ** blksz: Block size of the Tablespace Storing the Table to be recovered; 965 ** selflink: database link refer to instance self connect to dba account; 966 ************************************************************************/ 967 procedure recover_table(oriobjid number, 968 recowner varchar2, 969 rectab varchar2, 970 rstowner varchar2, 971 rsttab varchar2, 972 srcdir varchar2, 973 srcfile varchar2, 974 srcisfilesystem boolean, 975 tmpdir varchar2, 976 recfile varchar2, 977 copyfile varchar2, 978 blksz number default 8192, 979 fillblks number default 5, 980 selflink varchar2 default '', 981 endianess number default 1, 982 recnum in out number, 983 truncblks in out number) as 984 p_tmpsrcfile varchar2(30); 985 p_finalsrcdir varchar2(255); 986 -- blk blob; 987 --vrw raw(32767); 988 frw raw(32767); 989 tsz number := 4; 990 hsz number := 28; 991 objr raw(4); 992 objn number; 993 dtail raw(4); 994 dhead raw(32); 995 dbody raw(32767); 996 --bfr bfile; 997 bfo utl_file.file_type; 998 bfr utl_file.file_type; 999 bfw utl_file.file_type;1000 fillednum number := 0;1001 dummyheader number;1002 dummyblks number;1003 blkstofill number := fillblks;1004 i number := 0;1005 j number := 0;1006 rstnum number := 0;1007 begin1008 select header_block + 1, blocks - 31009 into dummyheader, dummyblks1010 from dba_segments1011 where owner = recowner1012 and segment_name = rectab;1013 if blkstofill > dummyblks then1014 blkstofill := dummyblks;1015 end if;10161017 if not srcisfilesystem then1018 p_tmpsrcfile := gen_file_name(srcfile, '');1019 copy_file(srcdir, srcfile, tmpdir, p_tmpsrcfile);1020 p_finalsrcdir := tmpdir;1021 else1022 p_tmpsrcfile := srcfile;1023 p_finalsrcdir := srcdir;1024 end if;10251026 bfo := utl_file.fopen(p_finalsrcdir, p_tmpsrcfile, 'RB');1027 --utl_file.get_raw(bfo, dbody, blksz-hsz-tsz);1028 --utl_file.get_raw(bfo, dtail, tsz);10291030 bfr := utl_file.fopen(tmpdir, copyfile, 'RB');1031 bfw := utl_file.fopen(tmpdir, recfile, 'WB');1032 -- reach to the transaction blocks to be filled1033 i := 0;1034 while true loop1035 begin1036 utl_file.get_raw(bfr, frw, blksz);1037 utl_file.put_raw(bfw, frw);1038 i := i + 1;10391040 exit when i = dummyheader + fillednum;1041 exception1042 when others then1043 --raise;1044 --trace('[recover_table] block NO.: '||i);1045 exit;1046 end;1047 end loop;10481049 -- go through the data file of truncated table1050 while true loop1051 begin1052 --trace('[recover_table] '||j);1053 j := j + 1;1054 --objr := substrb(rawtohex(dhead), 49, 8);1055 utl_file.get_raw(bfo, dhead, hsz);1056 if hsz <= 24 then1057 utl_file.get_raw(bfo, dbody, blksz - tsz - hsz);1058 --objr := substrb(rawtohex(dbody), 49-hsz*2, 8);1059 objr := utl_raw.substr(dbody, 25 - hsz, 4);1060 else1061 --objr := substrb(rawtohex(dhead), 49, 8);1062 objr := utl_raw.substr(dhead, 25, 4);1063 end if;1064 if endianess > 0 then1065 --objn := to_number(utl_raw.reverse(hextoraw(objr)), 'XXXXXXXX');1066 objn := to_number(rawtohex(utl_raw.reverse(objr)), 'XXXXXXXX');1067 else1068 --objn := to_number(hextoraw(objr), 'XXXXXXXX');1069 objn := to_number(rawtohex(objr), 'XXXXXXXX');1070 end if;10711072 -- check if block belongs to truncated table1073 if objn != oriobjid or substrb(rawtohex(dhead), 1, 2) != '06' then1074 if hsz > 24 then1075 utl_file.get_raw(bfo, dbody, blksz - hsz);1076 else1077 utl_file.get_raw(bfo, dtail, tsz);1078 end if;1079 else1080 --trace('[recover_table] Find it.');1081 truncblks := truncblks + 1;1082 if hsz > 24 then1083 utl_file.get_raw(bfo, dbody, blksz - hsz - tsz);1084 end if;1085 utl_file.get_raw(bfo, dtail, tsz);10861087 if not utl_file.is_open(bfr) then1088 bfr := utl_file.fopen(tmpdir, copyfile, 'RB');1089 end if;1090 if not utl_file.is_open(bfw) then1091 bfw := utl_file.fopen(tmpdir, recfile, 'WB');1092 end if;10931094 -- filling the trans block1095 utl_file.get_raw(bfr, dhead, hsz);1096 utl_file.put_raw(bfw, dhead); -- put original header1097 utl_file.put_raw(bfw, dbody); -- replace body1098 utl_file.get_raw(bfr, dbody, blksz - hsz - tsz); -- forward pointer in original file copy1099 utl_file.get_raw(bfr, dtail, tsz); -- get original tail1100 utl_file.put_raw(bfw, dtail); -- put original tail1101 fillednum := fillednum + 1;1102 i := i + 1;1103 -- no trans data block left, copy recovered data to backup table and fill the left blocks1104 if fillednum >= blkstofill then1105 --if fillednum+blkstofill-1 >= dummyblks then1106 begin1107 while true loop1108 begin1109 utl_file.get_raw(bfr, frw, blksz);1110 utl_file.put_raw(bfw, frw);1111 i := i + 1;11121113 exception1114 when others then1115 if utl_file.is_open(bfr) then1116 utl_file.fclose(bfr);1117 end if;1118 if utl_file.is_open(bfw) then1119 utl_file.fclose(bfw);1120 end if;1121 exit;1122 end;1123 end loop;11241125 rstnum := restore_table(recowner,1126 rectab,1127 rstowner,1128 rsttab,1129 selflink);1130 -- for test1131 exit when rstnum < 0;1132 recnum := recnum + rstnum;1133 fillednum := 0;1134 commit;11351136 bfr := utl_file.fopen(tmpdir, copyfile, 'RB');1137 bfw := utl_file.fopen(tmpdir, recfile, 'WB');1138 -- go to the transaction blocks again1139 i := 0;1140 while true loop1141 begin1142 utl_file.get_raw(bfr, frw, blksz);1143 utl_file.put_raw(bfw, frw);1144 i := i + 1;11451146 exit when i = dummyheader + fillednum;1147 exception1148 when others then1149 --raise;1150 --trace('[recover_table] block NO.: '||i);1151 exit;1152 end;1153 end loop;1154 utl_file.fflush(bfw);1155 exception1156 when others then1157 trace('[recover_table 2-1] ' || sqlerrm);1158 trace('[recover_table 2-1] ' ||1159 dbms_utility.format_error_backtrace);1160 null;1161 end;1162 end if;1163 end if;1164 exception1165 when no_data_found then1166 exit;1167 when others then1168 trace('[recover_table 2-2] ' || sqlerrm);1169 trace('[recover_table 2-2] ' ||1170 dbms_utility.format_error_backtrace);1171 exit;1172 end;1173 end loop;11741175 -- last blocks not full filled dummy table1176 --if fillednum+blkstofill-1 < dummyblks then1177 if fillednum < blkstofill and rstnum >= 0 then1178 begin1179 while true loop1180 begin1181 utl_file.get_raw(bfr, frw, blksz);1182 utl_file.put_raw(bfw, frw);1183 i := i + 1;11841185 exception1186 when others then1187 if utl_file.is_open(bfr) then1188 utl_file.fclose(bfr);1189 end if;1190 if utl_file.is_open(bfw) then1191 utl_file.fclose(bfw);1192 end if;1193 exit;1194 end;1195 end loop;1196 recnum := recnum + restore_table(recowner,1197 rectab,1198 rstowner,1199 rsttab,1200 selflink);1201 --fillednum := 0;1202 commit;1203 end;1204 end if;1205 if utl_file.is_open(bfr) then1206 utl_file.fclose(bfr);1207 end if;1208 if utl_file.is_open(bfw) then1209 utl_file.fclose(bfw);1210 end if;1211 if utl_file.is_open(bfo) then1212 utl_file.fclose(bfo);1213 end if;1214 utl_file.fclose_all();12151216 log(truncblks || ' truncated data blocks found. ');1217 log(recnum || ' records recovered in backup table ' || rstowner || '.' ||1218 rsttab);12191220 if not srcisfilesystem then1221 remove_file(tmpdir, p_tmpsrcfile);1222 end if;1223 end;12241225 /************************************************************************1226 ** Recover Table Data From Data Files of Targe Table;1227 **1228 ** tgtowner: Owner of Target Table to be recovered;1229 ** tgttable: Name of Target Table to be recovered;1230 ** recowner: Owner of Table to be used as recovering dummy table;1231 ** rectable: Name of Table to be used as recovering dummy table;1232 ** rstowner: Owner of Table to store the recovered data;1233 ** rsttable: Name of Table to store the recovered data;1234 ** tmpdir: Temp Directory to create restore tablespace and other temp files;1235 ** srcfile: Name of the Data File to be recovered;1236 ** recfile: Name of Data File that rectable is stored;1237 ** copydir: Directory of Copy of Data File that rectable is stored;1238 ** coryfile: Name of Copy of Data File that rectable is stored;1239 ** blksz: Block size of the Tablespace Storing the Table to be recovered;1240 ** selflink: database link refer to instance self connect to dba account;1241 ************************************************************************/1242 procedure recover_table(tgtowner varchar2,1243 tgttable varchar2,1244 recowner varchar2,1245 rectab varchar2,1246 rstowner varchar2,1247 rsttab varchar2,1248 tmpdir varchar2,1249 recfile varchar2,1250 copyfile varchar2,1251 blksz number default 8192,1252 fillblks number default 5,1253 selflink varchar2 default '',1254 offline_files varchar2 default null) as1255 tgtobjid number;1256 recobjid number;1257 endianess number;1258 slash char(1);1259 filedir varchar2(255) := 'TMP_DATA_FILE_DIR';1260 tmpcopyf varchar2(256);1261 tsname varchar2(30);1262 readprop varchar2(30);1263 dtail raw(4);1264 addinfo raw(32);1265 recnum number := 0;1266 truncblks number := 0;1267 trecnum number := 0;1268 ttruncblks number := 0;1269 begin1270 execute immediate 'truncate table ' || rstowner || '.' || rsttab;1271 execute immediate 'alter system set db_block_checking=false scope=memory';1272 execute immediate 'alter system set db_block_checksum=false scope=memory';1273 execute immediate 'alter system set "_db_block_check_objtyp"=false scope=memory';1274 execute immediate 'alter session set events ''10231 trace name context forever, level 10''';1275 execute immediate 'alter session set events ''10233 trace name context forever, level 10''';12761277 select instr(platform_name, 'Windows'),1278 decode(instr(platform_name, 'Windows'), 0, '/', '\')1279 into endianess, slash1280 from v_$database1281 where rownum <= 1;1282 select data_object_id1283 into recobjid1284 from dba_objects1285 where owner = recowner1286 and object_name = rectab1287 and object_type = 'TABLE'1288 and rownum <= 1;1289 log('begin to recover table ' || tgtowner || '.' || tgttable);1290 tgtobjid := get_seg_data_id(tgtowner,1291 tgttable,1292 tmpdir,1293 blksz,1294 endianess);12951296 if offline_files is not null then1297 for file_rec in (with target_string as1298 (select /*+inline*/1299 offline_files str, ';' spliter1300 from dual),1301 files as1302 (select trim(regexp_substr(str,1303 '[^' || spliter || ']+',1304 1,1305 level)) file_name1306 from target_string1307 connect by level <=1308 length(regexp_replace(str,1309 '[^' || spliter || ']+')) + 1)1310 select substr(file_name,1311 instr(file_name, slash, -1) + 1) as filename,1312 substr(file_name,1313 1,1314 instr(file_name, slash, -1)) as filepath1315 from files1316 where file_name is not null) loop1317 --'1318 begin1319 log('Recovering data in datafile ' || file_rec.filepath ||1320 file_rec.filename);1321 recnum := 0;1322 truncblks := 0;1323 create_directory(file_rec.filepath, filedir);1324 recover_table(tgtobjid,1325 recowner,1326 rectab,1327 rstowner,1328 rsttab,1329 filedir,1330 file_rec.filename,1331 (file_rec.filepath not like '+%' and1332 file_rec.filepath not like '/dev/%'),1333 tmpdir,1334 recfile,1335 copyfile,1336 blksz,1337 fillblks,1338 selflink,1339 endianess,1340 recnum,1341 truncblks);1342 -- execute immediate 'drop directory '||filedir;1343 trecnum := trecnum + recnum;1344 ttruncblks := ttruncblks + truncblks;1345 exception1346 when others then1347 trace('[recover_table 1] ' || sqlerrm);1348 trace('[recover_table 1] ' ||1349 dbms_utility.format_error_backtrace);1350 end;1351 end loop;1352 else1353 if s_repobjid then1354 get_seg_meta(recowner, rectab, tmpdir, dtail, addinfo, blksz);1355 select tablespace_name1356 into tsname1357 from dba_tables1358 where owner = tgtowner1359 and table_name = tgttable1360 and rownum <= 1;1361 select STATUS1362 into readprop1363 from dba_tablespaces1364 where tablespace_name = tsname;1365 if readprop != 'READ ONLY' then1366 execute immediate 'alter tablespace ' || tsname || ' read only';1367 execute immediate 'alter system flush buffer_cache';1368 end if;1369 --for file_rec in (select substr(file_name,decode(instr(d.file_name, '\', -1), 0, instr(file_name, '/', -1), instr(file_name, '\', -1))+1) as filename,1370 -- substr(file_name,1,decode(instr(d.file_name, '\', -1), 0, instr(file_name, '/', -1), instr(file_name, '\', -1))) as filepath1371 -- from dba_data_files d, dba_tables t1372 -- where d.tablespace_name = t.tablespace_name and t.owner = tgtowner and t.table_name = tgttable) loop --'1373 for file_rec in (select substr(file_name,1374 instr(d.file_name, slash, -1) + 1) as filename,1375 substr(file_name,1376 1,1377 instr(d.file_name, slash, -1)) as filepath1378 from dba_data_files d, dba_tables t1379 where d.tablespace_name = t.tablespace_name1380 and t.owner = tgtowner1381 and t.table_name = tgttable) loop1382 --'1383 begin1384 log('Recovering data in datafile ' || file_rec.filepath ||1385 file_rec.filename);1386 recnum := 0;1387 truncblks := 0;1388 create_directory(file_rec.filepath, filedir);1389 tmpcopyf := gen_file_name(file_rec.filename, '');1390 copy_file(filedir, file_rec.filename, tmpdir, tmpcopyf);1391 --replace_segmeta_in_file(srcdir, tmpcopyf, srcdir, file_rec.filename, tgtobjid, recobjid, dtail, 39, addinfo, blksz, endianess);1392 replace_segmeta_in_file(tmpdir,1393 tmpcopyf,1394 filedir,1395 file_rec.filename,1396 (file_rec.filepath not like '+%' and1397 file_rec.filepath not like '/dev/%'),1398 tgtobjid,1399 recobjid,1400 dtail,1401 39,1402 '',1403 blksz,1404 endianess);1405 recover_table(recobjid,1406 recowner,1407 rectab,1408 rstowner,1409 rsttab,1410 filedir,1411 file_rec.filename,1412 (file_rec.filepath not like '+%' and1413 file_rec.filepath not like '/dev/%'),1414 tmpdir,1415 recfile,1416 copyfile,1417 blksz,1418 fillblks,1419 selflink,1420 endianess,1421 recnum,1422 truncblks);1423 --recover_table(tgtobjid, recowner, rectab, rstowner, rsttab, srcdir, file_rec.filename, recdir, recfile, copydir, copyfile, blksz, fillblks, selflink, endianess);1424 copy_file(tmpdir, tmpcopyf, filedir, file_rec.filename);1425 remove_file(tmpdir, tmpcopyf);1426 -- execute immediate 'drop directory '||filedir;1427 trecnum := trecnum + recnum;1428 ttruncblks := ttruncblks + truncblks;1429 trace('[recover_table 1] ' || tmpcopyf || ' removed.');1430 exception1431 when others then1432 trace('[recover_table 1] ' || sqlerrm);1433 trace('[recover_table 1] ' ||1434 dbms_utility.format_error_backtrace);1435 end;1436 end loop;1437 if readprop != 'READ ONLY' then1438 execute immediate 'alter tablespace ' || tsname || ' read write';1439 end if;1440 else1441 for file_rec in (select substr(file_name,1442 instr(d.file_name, slash, -1) + 1) as filename,1443 substr(file_name,1444 1,1445 instr(d.file_name, slash, -1)) as filepath1446 from dba_data_files d, dba_tables t1447 where d.tablespace_name = t.tablespace_name1448 and t.owner = tgtowner1449 and t.table_name = tgttable) loop1450 --'1451 begin1452 log('Recovering data in datafile ' || file_rec.filepath ||1453 file_rec.filename);1454 recnum := 0;1455 truncblks := 0;1456 create_directory(file_rec.filepath, filedir);1457 recover_table(tgtobjid,1458 recowner,1459 rectab,1460 rstowner,1461 rsttab,1462 filedir,1463 file_rec.filename,1464 (file_rec.filepath not like '+%' and1465 file_rec.filepath not like '/dev/%'),1466 tmpdir,1467 recfile,1468 copyfile,1469 blksz,1470 fillblks,1471 selflink,1472 endianess,1473 recnum,1474 truncblks);1475 -- execute immediate 'drop directory '||filedir;1476 trecnum := trecnum + recnum;1477 ttruncblks := ttruncblks + truncblks;1478 exception1479 when others then1480 trace('[recover_table 1] ' || sqlerrm);1481 trace('[recover_table 1] ' ||1482 dbms_utility.format_error_backtrace);1483 end;1484 end loop;1485 end if;1486 end if;14871488 log('Total: ' || ttruncblks || ' truncated data blocks found. ');1489 log('Total: ' || trecnum || ' records recovered in backup table ' ||1490 rstowner || '.' || rsttab);14911492 execute immediate 'alter session set events ''10233 trace name context off''';1493 execute immediate 'alter session set events ''10231 trace name context off''';1494 execute immediate 'alter system set "_db_block_check_objtyp"=true scope=memory';1495 execute immediate 'alter system set db_block_checksum=true scope=memory';1496 execute immediate 'alter system set db_block_checking=true scope=memory';14971498 log('Recovery completed.');1499 end;15001501 /************************************************************************1502 ** Prepare the data files to be use during recovering;1503 **1504 ** tgtowner: Owner of Target Table to be recovered;1505 ** tgttable: Name of Target Table to be recovered;1506 ** datapath: Absolute path of Data Files;1507 ** datadir: Directory to be created referring to datapath;1508 ** rects: Tablespace to store the recovering dummy table;1509 ** recfile: Name of Data File to store the recovering dummy table;1510 ** rstts: Tablespace to store table storing the recovered data;1511 ** rstfile: Name of Data File to store restoring table;1512 ** blksz: Block size of the Tablespace Storing the Table to be recovered;1513 ** rectsblks: block number of recovery tablespace1514 ** rectsblks: block number of restore tablespace1515 ************************************************************************/1516 procedure prepare_files(tgtowner varchar2,1517 tgttable varchar2,1518 tmppath varchar2,1519 rects out varchar2,1520 recfile out varchar2,1521 rstts out varchar2,1522 rstfile out varchar2,1523 blksz out varchar2,1524 rectsblks number default 16,1525 rsttsblks number default 2560) as1526 ext_mgmt varchar2(30);1527 ss_mgmt varchar2(30);1528 slash char(1);1529 begin1530 select decode(instr(platform_name, 'Windows'), 0, '/', '\')1531 into slash1532 from v_$database1533 where rownum <= 1;15341535 select block_size, extent_management, segment_space_management1536 into blksz, ext_mgmt, ss_mgmt1537 from dba_tablespaces ts, dba_tables t1538 where t.tablespace_name = ts.tablespace_name1539 and t.owner = upper(tgtowner)1540 and t.table_name = upper(tgttable);15411542 --select 'FY_REC_DATA'||surfix into rects from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tablespaces where tablespace_name = 'FY_REC_DATA'||surfix) order by surfix nulls first) where rownum<=1;1543 --select 'FY_REC_DATA'||surfix||'.DAT' into recfile from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_data_files where file_name like '%\FY_REC_DATA'||surfix||'.DAT') order by surfix nulls first) where rownum<=1;1544 rects := gen_ts_name('FY_REC_DATA', '');1545 recfile := gen_file_name('FY_REC_DATA', '');1546 log('Recover Tablespace: ' || rects || '; Data File: ' || recfile);1547 execute immediate 'create tablespace ' || rects || ' datafile ''' ||1548 rtrim(tmppath, slash) || slash || recfile ||1549 ''' size ' || to_char(blksz * rectsblks / 1024) ||1550 'K autoextend off extent management ' || ext_mgmt ||1551 ' SEGMENT SPACE MANAGEMENT ' || ss_mgmt; --'15521553 --select 'FY_RST_DATA'||surfix into rstts from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tablespaces where tablespace_name = 'FY_REST_DATA'||surfix) order by surfix nulls first) where rownum<=1;1554 --select 'FY_RST_DATA'||surfix||'.DAT' into rstfile from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_data_files where file_name like '%\FY_REST_DATA'||surfix||'.DAT') order by surfix nulls first) where rownum<=1;1555 rstts := gen_ts_name('FY_RST_DATA', '');1556 rstfile := gen_file_name('FY_RST_DATA', '');1557 log('Restore Tablespace: ' || rstts || '; Data File: ' || rstfile);1558 execute immediate 'create tablespace ' || rstts || ' datafile ''' ||1559 rtrim(tmppath, slash) || slash || rstfile ||1560 ''' size ' || to_char(blksz * rsttsblks / 1024) ||1561 'K autoextend on extent management ' || ext_mgmt ||1562 ' SEGMENT SPACE MANAGEMENT ' || ss_mgmt; --'1563 end;15641565 /************************************************************************1566 ** Clean up existing Recover and Restore Tablespace. Drop tables in the tablespaces1567 **1568 ** rects: Recover tablespace name1569 ** rects: Restore tablespace name, default NULL, will not do cleaning up;1570 ************************************************************************/1571 procedure clean_up_ts(rects varchar2, rstts varchar2 default null) as1572 readprop varchar2(30);1573 begin1574 select STATUS1575 into readprop1576 from dba_tablespaces1577 where tablespace_name = rects;1578 if readprop = 'READ ONLY' then1579 execute immediate 'alter tablespace ' || rects || ' read write';1580 end if;1581 for tab_rec in (select owner, table_name1582 from dba_tables1583 where tablespace_name = rects) loop1584 execute immediate 'drop table ' || tab_rec.owner || '.' ||1585 tab_rec.table_name;1586 end loop;1587 if rstts is not null then1588 for tab_rec in (select owner, table_name1589 from dba_tables1590 where tablespace_name = rstts) loop1591 execute immediate 'drop table ' || tab_rec.owner || '.' ||1592 tab_rec.table_name;1593 end loop;1594 end if;1595 end;15961597 /************************************************************************1598 ** Fill Blocks of Recovering Table, to format the blocks;1599 **1600 ** tgtowner: Owner of Target Table to be recovered;1601 ** tgttable: Name of Target Table to be recovered;1602 ** tmpdir: Temp Directory to be used to create the restore tablespace and files;1603 ** rects: Tablespace to store the recovering dummy table;1604 ** recfile: Name of Data File to store the recovering dummy table;1605 ** rstts: Tablespace to store table storing the recovered data;1606 ** blks: Number blocks in Initial Extent of the recovering dummy table;1607 ** recowner: Owner of Table to be used as recovering dummy table;1608 ** rstowner: Owner of Table to store the recovered data;1609 ** rectable: Name of Table to be used as recovering dummy table;1610 ** rsttable: Name of Table to store the recovered data;1611 ** coryfile: Name of Copy of Data File that rectable is stored;1612 ************************************************************************/1613 procedure fill_blocks(tgtowner varchar2,1614 tgttable varchar2,1615 tmpdir varchar2,1616 rects varchar2,1617 recfile varchar2,1618 rstts varchar2,1619 blks number default 8,1620 recowner varchar2 default user,1621 rstowner varchar2 default user,1622 rectab in out varchar2,1623 rsttab in out varchar2,1624 copyfile out varchar2) as1625 blksz number;1626 blkno number;1627 cols varchar2(32767);1628 vals varchar2(32767);1629 colno number := 0;1630 begin1631 if rectab is null then1632 select block_size1633 into blksz1634 from dba_tablespaces ts, dba_tables t1635 where t.tablespace_name = ts.tablespace_name1636 and t.owner = upper(tgtowner)1637 and t.table_name = upper(tgttable);1638 -- select upper(tgttable||'$'||surfix) into rectab from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tables where owner = recowner and table_name = upper(tgttable||'$'||surfix)) order by surfix nulls first) where rownum<=1;1639 rectab := gen_table_name(tgttable, '$', recowner);1640 log('Recover Table: ' || recowner || '.' || rectab);1641 --trace('[fill_blocks] create table '||recowner||'.'||rectab||' tablespace '||rects||' storage(initial '||to_char(blks*blksz/1024)||'K) as select * from '||tgtowner||'.'||tgttable||' where 1=2');1642 execute immediate 'create table ' || recowner || '.' || rectab ||1643 ' tablespace ' || rects || ' storage(initial ' ||1644 to_char(blks * blksz / 1024) ||1645 'K) as select * from ' || tgtowner || '.' ||1646 tgttable || ' where 1=2';1647 else1648 --execute immediate 'truncate table '||recowner||'.'||rectab;1649 execute immediate 'delete from ' || recowner || '.' || rectab;1650 commit;1651 end if;16521653 cols := '';1654 vals := '';1655 for col_rec in (select column_name, data_type, nullable1656 from dba_tab_cols1657 where owner = recowner1658 and table_name = rectab) loop1659 if col_rec.nullable = 'N' then1660 execute immediate 'alter table ' || recowner || '.' || rectab ||1661 ' modify ' || col_rec.column_name || ' null';1662 end if;1663 if colno < 6 then1664 if col_rec.data_type like '%CHAR%' or1665 col_rec.data_type like '%RAW%' then1666 if colno > 0 then1667 cols := cols || ',';1668 vals := vals || ',';1669 end if;1670 cols := cols || col_rec.column_name;1671 vals := vals || '''A''';1672 colno := colno + 1;1673 elsif col_rec.data_type like '%NUMBER%' or1674 col_rec.data_type = 'FLOAT' then1675 if colno > 0 then1676 cols := cols || ',';1677 vals := vals || ',';1678 end if;1679 cols := cols || col_rec.column_name;1680 vals := vals || '0';1681 colno := colno + 1;1682 elsif col_rec.data_type LIKE '%TIMESTAMP%' or1683 col_rec.data_type = 'DATE' then1684 if colno > 0 then1685 cols := cols || ',';1686 vals := vals || ',';1687 end if;1688 cols := cols || col_rec.column_name;1689 vals := vals || 'sysdate';1690 colno := colno + 1;1691 end if;1692 end if;1693 end loop;16941695 --select upper(tgttable||'$$'||surfix) into rsttab from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tables where owner = rstowner and table_name = upper(tgttable||'$$'||surfix)) order by surfix nulls first) where rownum<=1;1696 if rsttab is null then1697 rsttab := gen_table_name(tgttable, '$$', rstowner);1698 log('Restore Table: ' || rstowner || '.' || rsttab);1699 execute immediate 'create table ' || rstowner || '.' || rsttab ||1700 ' tablespace ' || rstts || ' as select * from ' ||1701 recowner || '.' || rectab || ' where 1=2';1702 else1703 execute immediate 'truncate table ' || rstowner || '.' || rsttab;1704 end if;17051706 --trace('[fill_blocks] insert into '||recowner||'.'||rectab||'('||cols||') values ('||vals||')');1707 while true loop1708 execute immediate 'insert into ' || recowner || '.' || rectab || '(' || cols ||1709 ') values (' || vals || ')';1710 execute immediate 'select count(unique(dbms_rowid.rowid_block_number( rowid ))) from ' ||1711 recowner || '.' || rectab1712 into blkno;1713 exit when blkno >= blks - 3;1714 end loop;1715 commit;1716 execute immediate 'alter system flush buffer_cache';1717 execute immediate 'delete from ' || recowner || '.' || rectab;1718 commit;1719 execute immediate 'alter system flush buffer_cache';1720 trace('[fill_blocks] Data Blocks formatted.');17211722 execute immediate 'alter tablespace ' || rects || ' read only';17231724 select 'FY_REC_DATA_COPY' || surfix || '.DAT'1725 into copyfile1726 from (select surfix1727 from (select null surfix1728 from dual1729 union all1730 select level surfix1731 from dual1732 connect by level <= 255)1733 where not exists1734 (select 11735 from dba_data_files1736 where file_name like1737 '%FY_REC_DATA_COPY' || surfix || '.DAT')1738 order by surfix nulls first)1739 where rownum <= 1;1740 copy_file(tmpdir, recfile, tmpdir, copyfile);1741 log('Copy file of Recover Tablespace: ' || copyfile);1742 end;17431744 /************************************************************************1745 ** Create recovery table on new file of truncated table's tablespace;1746 **1747 ** tgtowner: Owner of Target Table to be recovered;1748 ** tgttable: Name of Target Table to be recovered;1749 ** datadir: Directory to be created referring to datapath;1750 ** rects: Tablespace to store the recovering dummy table;1751 ** recfid: ID of Data File to store the recovering dummy table;1752 ** recfile: Name of Data File to store the recovering dummy table;1753 ** rectable: Name of Table to be used as recovering dummy table;1754 ** blks: Number blocks in Initial Extent of the recovering dummy table;1755 ** rectsblks: block number of recovery tablespace1756 ************************************************************************/1757 procedure create_rectab_on_tgttab_ts(tgtowner varchar2,1758 tgttable varchar2,1759 datadir varchar2,1760 recowner varchar2 default user,1761 rects out varchar2,1762 recfid in out number,1763 recfile out varchar2,1764 rectab out varchar2,1765 blks number default 8,1766 rectsblks number default 16) as1767 blksz number;1768 tsid number;1769 tsonline number;1770 datapath varchar2(32767);1771 r_files r_cursor;1772 filelist t_fileprops;1773 tn number;1774 begin1775 select ts.ts#, ts.name, ts.online$, blocksize1776 into tsid, rects, tsonline, blksz1777 from sys.ts$ ts, dba_tables t1778 where t.tablespace_name = ts.name1779 and t.owner = upper(tgtowner)1780 and t.table_name = upper(tgttable);1781 if tsonline = 4 then1782 execute immediate 'alter tablespace ' || rects || ' read write';1783 end if;17841785 if recfid is null then1786 open r_files for1787 select file#, status$ bulk from sys.file$ where ts# = tsid;1788 fetch r_files bulk collect1789 into filelist;1790 recfile := gen_file_name('FY_REC_DATA', '');1791 select rtrim(directory_path, '\') || '\'1792 into datapath1793 from dba_directories1794 where directory_name = datadir; --'1795 execute immediate 'alter tablespace ' || rects || ' add datafile ''' ||1796 datapath || recfile || ''' size ' ||1797 to_char(rectsblks * blksz / 1024) || 'K';1798 select file_id1799 into recfid1800 from dba_data_files1801 where tablespace_name = rects1802 and (file_name like '%\' || recfile or1803 file_name like '%/' || recfile); --'1804 else1805 open r_files for1806 select file#, status$ bulk1807 from sys.file$1808 where ts# = tsid1809 and file# != recfid;1810 fetch r_files bulk collect1811 into filelist;1812 trace('[create_rectab_on_tgttab_ts] file id: ' || recfid);1813 select decode(instr(file_name, '/', -1),1814 0,1815 substr(file_name, instr(file_name, '\', -1) + 1),1816 substr(file_name, instr(file_name, '/', -1) + 1))1817 into recfile1818 from dba_data_files1819 where file_id = recfid; --'1820 end if;1821 log('Recover Tablespace: ' || recfile || '(' || recfid || ')');1822 for i in 1 .. filelist.count loop1823 update sys.file$ f1824 set status$ = 11825 where ts# = tsid1826 and file# = filelist(i).file#;1827 end loop;1828 commit;1829 execute immediate 'alter system flush buffer_cache';1830 --select file# into tn from sys.file$ where ts#=tsid and status$=2;1831 --trace('[create_rectab_on_tgttab_ts] inactive files: '||filelist.count);1832 --trace('[create_rectab_on_tgttab_ts] active file id: '||tn);1833 rectab := gen_table_name(tgttable, '$', recowner);1834 log('Recover Table: ' || recowner || '.' || rectab);1835 --dbms_lock.sleep(3);1836 execute immediate 'create table ' || recowner || '.' || rectab ||1837 ' tablespace ' || rects || ' storage(initial ' ||1838 to_char(blks * blksz / 1024) ||1839 'K) as select * from ' || tgtowner || '.' || tgttable ||1840 ' where 1=2';1841 select header_file1842 into tn1843 from dba_segments1844 where owner = recowner1845 and segment_name = rectab;1846 trace('[create_rectab_on_tgttab_ts] header file: ' || filelist.count);1847 for i in 1 .. filelist.count loop1848 update sys.file$ f1849 set status$ = filelist(i).status$1850 where ts# = tsid1851 and file# = filelist(i).file#;1852 end loop;1853 commit;1854 execute immediate 'alter system flush buffer_cache';1855 end;18561857 procedure replace_segraw_in_file(srcdir varchar2,1858 srcfile varchar2,1859 dstdir varchar2,1860 dstfile varchar2,1861 blknum number,1862 startpos number,1863 repcont raw,1864 blksz number default 8192) as1865 bfr utl_file.file_type;1866 bfw utl_file.file_type;1867 dbody raw(32767);1868 i number;1869 p_srcdir varchar2(255) := srcdir;1870 p_srcfile varchar2(255) := srcfile;1871 p_dstdir varchar2(255) := dstdir;1872 p_dstfile varchar2(255) := dstfile;1873 begin1874 if p_dstdir is null then1875 p_dstdir := p_srcdir;1876 end if;1877 trace('[replace_segraw_in_file] replace block id: ' || blknum ||1878 ' start: ' || startpos || ' content: ' || repcont);1879 bfr := utl_file.fopen(p_srcdir, p_srcfile, 'RB');1880 bfw := utl_file.fopen(p_dstdir, p_dstfile, 'WB');18811882 i := 0;1883 while true loop1884 begin1885 utl_file.get_raw(bfr, dbody, blksz);1886 utl_file.put_raw(bfw, dbody);1887 i := i + 1;18881889 exit when i = blknum - 1;1890 exception1891 when others then1892 exit;1893 end;1894 end loop;18951896 utl_file.get_raw(bfr, dbody, blksz);1897 if startpos <= 1 then1898 utl_file.put_raw(bfw,1899 utl_raw.concat(repcont,1900 utl_raw.substr(dbody,1901 1 +1902 utl_raw.length(repcont))));1903 else1904 utl_file.put_raw(bfw,1905 utl_raw.concat(utl_raw.substr(dbody, 1, startpos - 1),1906 repcont,1907 utl_raw.substr(dbody,1908 startpos +1909 utl_raw.length(repcont))));1910 end if;19111912 while true loop1913 begin1914 utl_file.get_raw(bfr, dbody, blksz);1915 utl_file.put_raw(bfw, dbody);19161917 exception1918 when no_data_found then1919 exit;1920 when others then1921 trace('[replace_segraw_in_file] ' || SQLERRM);1922 trace('[replace_segraw_in_file] ' ||1923 dbms_utility.format_error_backtrace);1924 exit;1925 end;1926 end loop;19271928 utl_file.fclose(bfw);1929 utl_file.fclose(bfr);1930 trace('[replace_segraw_in_file] completed.');1931 end;19321933 procedure dump_seg_block_raw(hdfile varchar2,1934 srcdir varchar2,1935 blknb number,1936 blksz number default 8192) as1937 frw raw(32767);1938 bfo utl_file.file_type;1939 bfw utl_file.file_type;1940 i number := 0;1941 begin1942 bfo := utl_file.fopen(srcdir, hdfile, 'RB');1943 bfw := utl_file.fopen(srcdir, hdfile || '_' || blknb || '.BLK', 'WB');19441945 -- reach to the truncated data blocks1946 i := 0;1947 while true loop1948 begin1949 utl_file.get_raw(bfo, frw, blksz);1950 i := i + 1;19511952 exit when i = blknb;1953 exception1954 when others then1955 exit;1956 end;1957 end loop;19581959 utl_file.get_raw(bfo, frw, blksz);1960 utl_file.put_raw(bfw, frw);19611962 utl_file.fclose(bfo);1963 utl_file.fclose(bfw);1964 end;1965 /*------------------------------------------------------------------------------------------1966 procedure test_chain(filename varchar2,1967 blknum number,1968 startpos number,1969 repcont raw,1970 srcdir varchar2 default 'FY_DATA_DIR')1971 as1972 tmpcopyf varchar2(256);1973 begin1974 execute immediate 'alter system set db_block_checking=false scope=memory';1975 execute immediate 'alter system set db_block_checksum=false scope=memory';1976 execute immediate 'alter system set "_db_block_check_objtyp"=false scope=memory';1977 execute immediate 'alter session set events ''10231 trace name context forever, level 10''';1978 execute immediate 'alter session set events ''10233 trace name context forever, level 10''';1979 begin1980 tmpcopyf := gen_file_name(filename, '$');1981 trace('[test_chain] bakcup file '||tmpcopyf);1982 copy_file(srcdir, filename, srcdir, tmpcopyf);1983 replace_segraw_in_file(srcdir, tmpcopyf, srcdir, filename, blknum, startpos, repcont, 8192);1984 execute immediate 'alter system flush buffer_cache';1985 for rec in (select * from t_chain where rowid='AABFUoAAHAAAABFAAA') loop1986 null;1987 end loop;1988 trace('[test_chain] table query completed');1989 exception when others then1990 trace('[test_chain] '||sqlerrm);1991 trace('[test_chain] '||dbms_utility.format_error_backtrace);1992 end;1993 copy_file(srcdir, tmpcopyf, srcdir, filename);1994 remove_file(srcdir, tmpcopyf);1995 trace('[test_chain] '||tmpcopyf||' removed.');1996 execute immediate 'alter system set db_block_checking=true scope=memory';1997 execute immediate 'alter system set db_block_checksum=true scope=memory';1998 execute immediate 'alter system set "_db_block_check_objtyp"=true scope=memory';1999 execute immediate 'alter session set events ''10231 trace name context off''';2000 execute immediate 'alter session set events ''10233 trace name context off''';2001 exception when others then2002 trace('[test_chain] '||sqlerrm);2003 trace('[test_chain] '||dbms_utility.format_error_backtrace);2004 end;2005 ------------------------------------------------------------------------------------------*/2006 procedure test_rec1(tow varchar2 default 'DEMO',2007 ttb varchar2 default 'T_CHAIN',2008 fbks number default 1,2009 tmppath varchar2 default '/tmp/') --'2010 as2011 tgtowner varchar2(30) := upper(tow);2012 tgttable varchar2(30) := upper(ttb);2013 tmpdir varchar2(30);2014 rects varchar2(30);2015 recfile varchar2(30);2016 rstts varchar2(30);2017 rstfile varchar2(30);2018 blksz number;2019 rectab varchar2(30);2020 rsttab varchar2(30);2021 copyfile varchar2(30);2022 begin2023 tmpdir := 'FY_DATA_DIR';2024 create_directory(tmppath, tmpdir);2025 prepare_files(tgtowner,2026 tgttable,2027 tmpdir,2028 rects,2029 recfile,2030 rstts,2031 rstfile,2032 blksz);2033 rects := 'FY_REC_DATA';2034 rstts := 'FY_RST_DATA';2035 recfile := 'FY_REC_DATA.DAT';2036 fill_blocks(tgtowner,2037 tgttable,2038 tmpdir,2039 rects,2040 recfile,2041 rstts,2042 8,2043 tgtowner,2044 tgtowner,2045 rectab,2046 rsttab,2047 copyfile);2048 recover_table(tgtowner,2049 tgttable,2050 tgtowner,2051 rectab,2052 tgtowner,2053 rsttab,2054 tmpdir,2055 recfile,2056 copyfile,2057 blksz,2058 fbks,2059 'myself');2060 -- execute immediate 'drop directory '||tmpdir;2061 end;20622063 procedure test_rec2(tow varchar2 default 'DEMO',2064 ttb varchar2 default 'T_CHAIN',2065 fbks number default 1) as2066 tgtowner varchar2(30) := upper(tow);2067 tgttable varchar2(30) := upper(ttb);2068 tmpdir varchar2(30);2069 rects varchar2(30);2070 recfile varchar2(30);2071 rstts varchar2(30);2072 blksz number;2073 rectab varchar2(30);2074 rsttab varchar2(30);2075 copyfile varchar2(30);2076 begin2077 tmpdir := 'FY_DATA_DIR';2078 rects := 'FY_REC_DATA';2079 rstts := 'FY_RST_DATA';2080 recfile := 'FY_REC_DATA.DAT';2081 clean_up_ts(rects, rstts);2082 select block_size2083 into blksz2084 from dba_tablespaces ts, dba_tables t2085 where ts.tablespace_name = t.tablespace_name2086 and t.owner = tgtowner2087 and t.table_name = tgttable;2088 fill_blocks(tgtowner,2089 tgttable,2090 tmpdir,2091 rects,2092 recfile,2093 rstts,2094 8,2095 tgtowner,2096 tgtowner,2097 rectab,2098 rsttab,2099 copyfile);2100 recover_table(tgtowner,2101 tgttable,2102 tgtowner,2103 rectab,2104 tgtowner,2105 rsttab,2106 tmpdir,2107 recfile,2108 copyfile,2109 blksz,2110 fbks,2111 'myself');2112 -- execute immediate 'drop directory '||tmpdir;2113 end;21142115 procedure test_rec3(tow varchar2 default 'DEMO',2116 ttb varchar2 default 'T_CHAIN',2117 fbks number default 1,2118 fid number default null) as2119 tgtowner varchar2(30) := upper(tow);2120 tgttable varchar2(30) := upper(ttb);2121 tmpdir varchar2(30);2122 rects varchar2(30);2123 recfile varchar2(30);2124 rstts varchar2(30);2125 blksz number;2126 rectab varchar2(30);2127 rsttab varchar2(30);2128 copyfile varchar2(30);2129 recfid number := fid;2130 begin2131 tmpdir := 'FY_DATA_DIR';2132 rstts := 'FY_RST_DATA';2133 --begin2134 -- execute immediate 'drop table '||tgtowner||'.'||tgttable||'$';2135 -- execute immediate 'drop table '||tgtowner||'.'||tgttable||'$$';2136 --exception when others then2137 -- null;2138 --end;2139 create_rectab_on_tgttab_ts(tgtowner,2140 tgttable,2141 tmpdir,2142 tgtowner,2143 rects,2144 recfid,2145 recfile,2146 rectab,2147 8,2148 16);2149 select block_size2150 into blksz2151 from dba_tablespaces ts, dba_tables t2152 where ts.tablespace_name = t.tablespace_name2153 and t.owner = tgtowner2154 and t.table_name = tgttable;2155 fill_blocks(tgtowner,2156 tgttable,2157 tmpdir,2158 rects,2159 recfile,2160 rstts,2161 8,2162 tgtowner,2163 tgtowner,2164 rectab,2165 rsttab,2166 copyfile);2167 recover_table(tgtowner,2168 tgttable,2169 tgtowner,2170 rectab,2171 tgtowner,2172 rsttab,2173 tmpdir,2174 recfile,2175 copyfile,2176 blksz,2177 fbks,2178 'myself');2179 -- execute immediate 'drop directory '||tmpdir;2180 begin2181 --execute immediate 'drop table '||tgtowner||'.'||rectab;2182 --execute immediate 'alter tablespace '||rects||' drop datafile '||recfid;2183 --exception when others then2184 null;2185 end;2186 end;21872188 procedure recover_truncated_table(tow varchar2,2189 ttb varchar2,2190 fbks number default 1,2191 tmppath varchar2 default null,2192 offline_files varchar2 default null) --'2193 as2194 tgtowner varchar2(30) := upper(tow);2195 tgttable varchar2(30) := upper(ttb);2196 tmpdir varchar2(30);2197 rects varchar2(30);2198 recfile varchar2(30);2199 rstts varchar2(30);2200 rstfile varchar2(30);2201 blksz number;2202 rectab varchar2(30);2203 rsttab varchar2(30);2204 copyfile varchar2(30);2205 fy_ts_cnt number := 0;2206 endianess pls_integer;2207 temppath varchar2(32767) := tmppath;2208 begin2209 select instr(platform_name, 'Windows')2210 into endianess2211 from v_$database2212 where rownum <= 1;2213 if temppath is null then2214 if endianess > 0 then2215 temppath := 'c:\temp\';2216 else2217 temppath := '/tmp/';2218 end if;2219 end if;2220 dbms_output.enable(999999);2221 tmpdir := 'FY_DATA_DIR';2222 rects := 'FY_REC_DATA';2223 rstts := 'FY_RST_DATA';2224 recfile := 'FY_REC_DATA.DAT';2225 create_directory(temppath, tmpdir);2226 select count(*)2227 into fy_ts_cnt2228 from dba_tablespaces2229 where tablespace_name in (rects, rstts);2230 if fy_ts_cnt = 2 then2231 clean_up_ts(rects, rstts);2232 select substr(file_name,2233 decode(instr(file_name, '\', -1),2234 0,2235 instr(file_name, '/', -1),2236 instr(file_name, '\', -1)) + 1)2237 into recfile2238 from dba_data_files2239 where tablespace_name = rects2240 and rownum <= 1;2241 else2242 prepare_files(tgtowner,2243 tgttable,2244 temppath,2245 rects,2246 recfile,2247 rstts,2248 rstfile,2249 blksz);2250 end if;2251 select block_size2252 into blksz2253 from dba_tablespaces ts, dba_tables t2254 where ts.tablespace_name = t.tablespace_name2255 and t.owner = tgtowner2256 and t.table_name = tgttable;2257 fill_blocks(tgtowner,2258 tgttable,2259 tmpdir,2260 rects,2261 recfile,2262 rstts,2263 8,2264 tgtowner,2265 tgtowner,2266 rectab,2267 rsttab,2268 copyfile);2269 recover_table(tgtowner,2270 tgttable,2271 tgtowner,2272 rectab,2273 tgtowner,2274 rsttab,2275 tmpdir,2276 recfile,2277 copyfile,2278 blksz,2279 fbks,2280 'myself',2281 offline_files);2282 log('Data has been recovered to ' || tgtowner || '.' || rsttab);2283 --execute immediate 'DROP TABLESPACE '||rects||' INCLUDING CONTENTS AND DATAFILES ';2284 --execute immediate 'DROP TABLESPACE '||rstts||' INCLUDING CONTENTS AND DATAFILES ';2285 --remove_file(tmpdir,recfile);2286 --remove_file(tmpdir,rstfile);2287 remove_file(tmpdir, copyfile);2288 -- execute immediate 'DROP DIRECTORY '||tmpdir;2289 end;22902291begin2292 null;2293end FY_Recover_Data;2294/2295
本文结束。
• 个人博客地址：www.xmmup.com
• 微信公众号：DB宝，作者：小麦苗，作者微信：db_bao
• 作者博客地址：http://blog.itpub.net/26736162/
• 作者QQ：646634621，QQ群：230161599、618766405
• 提供Oracle OCP、OCM、高可用（rac+dg+ogg）、MySQL DBA和PostgreSQL DBA培训
• 版权所有，欢迎分享本文，转载请保留出处• 若有侵权请联系小麦苗删除
★DB宝分享的IT资料：https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag★DB宝笔试面试详解：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
长按下图识别二维码，关注小麦苗的微信公众号：DB宝，学习最实用的数据库技术。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
