目录
一、简介二、实验环境介绍三、 实验代码四、实验过程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;
13
14
15-- 数据已经被truncate掉了，下边我们来恢复
16@/tmp/FY_Recover_Data.pck
17exec 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;
24
25
26-- 清理数据
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.
9
10
11Connected to:
12Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
13
14SYS@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;
18
19BANNER BANNER_FULL BANNER_LEGACY CON_ID
20-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
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 0
22 Version 19.3.0.0.0
23
24
25Elapsed: 00:00:00.15
26
27
28SYS@192.168.66.35:11521/lhrsdb> create user lhr identified by lhr;
29
30User created.
31
32Elapsed: 00:00:00.26
33SYS@192.168.66.35:11521/lhrsdb> grant dba to lhr;
34
35Grant succeeded.
36
37Elapsed: 00:00:00.12
38SYS@192.168.66.35:11521/lhrsdb>
39SYS@192.168.66.35:11521/lhrsdb> create table lhr.LHRTB_210702 as SELECT * FROM dba_objects;
40
41Table created.
42
43Elapsed: 00:00:01.83
44SYS@192.168.66.35:11521/lhrsdb>
45SYS@192.168.66.35:11521/lhrsdb> INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;
46
4772690 rows created.
48
49Elapsed: 00:00:00.67
50SYS@192.168.66.35:11521/lhrsdb>
51SYS@192.168.66.35:11521/lhrsdb> INSERT INTO lhr.LHRTB_210702 SELECT * FROM lhr.LHRTB_210702;
52
53145380 rows created.
54
55Elapsed: 00:00:00.91
56SYS@192.168.66.35:11521/lhrsdb> commit;
57
58Commit complete.
59
60Elapsed: 00:00:00.11
61SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702;
62
63 COUNT(1)
64----------
65 290760
66
67Elapsed: 00:00:00.19
68SYS@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';
70
71D.BYTES/1024/1024
72-----------------
73 45
74
75Elapsed: 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 0
12
13Elapsed: 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.67
10SYS@192.168.66.35:11521/lhrsdb> select * from v$tablespace;
11
12 TS# NAME INC BIG FLA ENC CON_ID
13---------- ------------------------------ --- --- --- --- ----------
14 1 SYSAUX YES NO YES 0
15 0 SYSTEM YES NO YES 0
16 2 UNDOTBS1 YES NO YES 0
17 4 USERS YES NO YES 0
18 3 TEMP NO NO YES 0
19
20Elapsed: 00:00:00.16
21SYS@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_DIR
2416:03:26: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
2516:03:26: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
2616: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.DAT
2916:03:49: begin to recover table LHR.LHRTB_210702
3016:03:50: New Directory Name: TMP_HF_DIR
3116:03:52: Recovering data in datafile /opt/oracle/oradata/LHRSDB/users01.dbf
3216:03:52: Use existing Directory Name: TMP_HF_DIR
3316: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$$
39
40PL/SQL procedure successfully completed.
41
42Elapsed: 00:02:59.27
43SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702$$;
44
45 COUNT(1)
46----------
47 290760
48
49Elapsed: 00:00:00.52
50SYS@192.168.66.35:11521/lhrsdb>
51SYS@192.168.66.35:11521/lhrsdb> alter table lhr.LHRTB_210702 nologging;
52
53Table altered.
54
55Elapsed: 00:00:00.25
56SYS@192.168.66.35:11521/lhrsdb>
57SYS@192.168.66.35:11521/lhrsdb> insert /*+append*/ into lhr.LHRTB_210702 select * from lhr.LHRTB_210702$$;
58
59290760 rows created.
60
61Elapsed: 00:00:01.99
62SYS@192.168.66.35:11521/lhrsdb> commit;
63
64Commit complete.
65
66Elapsed: 00:00:00.91
67SYS@192.168.66.35:11521/lhrsdb> alter table lhr.LHRTB_210702 logging;
68
69Table altered.
70
71Elapsed: 00:00:00.11
72SYS@192.168.66.35:11521/lhrsdb>
73SYS@192.168.66.35:11521/lhrsdb> SELECT COUNT(1) FROM lhr.LHRTB_210702;
74
75 COUNT(1)
76----------
77 290760
78
79Elapsed: 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_COUNT
100---------- -------------------- ---------- ----------- ----------- ---------- ---------- ------------ ---------- ---------- -----------
101 0 SYSTEM 910 4 906 99.54 32 2.764 8192 LOGGING 1
102 1 SYSAUX 670 30 640 95.476 32 1.952 8192 LOGGING 1
103 2 UNDOTBS1 345 271 74 21.413 32 .225 8192 LOGGING 1
104 3 TEMP 33 27 6 18.182 32 .018 8192 NOLOGGING 1
105 4 USERS 344 2 341 99.291 32 1.042 8192 LOGGING 1
106 6 FY_REC_DATA 0 0 0 100 0 100 8192 LOGGING 1
107 7 FY_RST_DATA 57 3 54 95.154 32 .165 8192 LOGGING 1
108 ALL TS: 2358.625 338 2021 85.678 192
109
1108 rows selected.
111
112Elapsed: 00:00:00.59
113SYS@192.168.66.35:11521/lhrsdb>
114SYS@192.168.66.35:11521/lhrsdb> drop tablespace FY_REC_DATA including contents and datafiles;
115
116Tablespace dropped.
117
118Elapsed: 00:00:12.01
119SYS@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;
122
123Tablespace dropped.
124
125Elapsed: 00:00:11.43
126SYS@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 exist
131
132
133Elapsed: 00:00:00.15
134SYS@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 begin
1008 select header_block + 1, blocks - 3
1009 into dummyheader, dummyblks
1010 from dba_segments
1011 where owner = recowner
1012 and segment_name = rectab;
1013 if blkstofill > dummyblks then
1014 blkstofill := dummyblks;
1015 end if;
1016
1017 if not srcisfilesystem then
1018 p_tmpsrcfile := gen_file_name(srcfile, '');
1019 copy_file(srcdir, srcfile, tmpdir, p_tmpsrcfile);
1020 p_finalsrcdir := tmpdir;
1021 else
1022 p_tmpsrcfile := srcfile;
1023 p_finalsrcdir := srcdir;
1024 end if;
1025
1026 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);
1029
1030 bfr := utl_file.fopen(tmpdir, copyfile, 'RB');
1031 bfw := utl_file.fopen(tmpdir, recfile, 'WB');
1032 -- reach to the transaction blocks to be filled
1033 i := 0;
1034 while true loop
1035 begin
1036 utl_file.get_raw(bfr, frw, blksz);
1037 utl_file.put_raw(bfw, frw);
1038 i := i + 1;
1039
1040 exit when i = dummyheader + fillednum;
1041 exception
1042 when others then
1043 --raise;
1044 --trace('[recover_table] block NO.: '||i);
1045 exit;
1046 end;
1047 end loop;
1048
1049 -- go through the data file of truncated table
1050 while true loop
1051 begin
1052 --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 then
1057 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 else
1061 --objr := substrb(rawtohex(dhead), 49, 8);
1062 objr := utl_raw.substr(dhead, 25, 4);
1063 end if;
1064 if endianess > 0 then
1065 --objn := to_number(utl_raw.reverse(hextoraw(objr)), 'XXXXXXXX');
1066 objn := to_number(rawtohex(utl_raw.reverse(objr)), 'XXXXXXXX');
1067 else
1068 --objn := to_number(hextoraw(objr), 'XXXXXXXX');
1069 objn := to_number(rawtohex(objr), 'XXXXXXXX');
1070 end if;
1071
1072 -- check if block belongs to truncated table
1073 if objn != oriobjid or substrb(rawtohex(dhead), 1, 2) != '06' then
1074 if hsz > 24 then
1075 utl_file.get_raw(bfo, dbody, blksz - hsz);
1076 else
1077 utl_file.get_raw(bfo, dtail, tsz);
1078 end if;
1079 else
1080 --trace('[recover_table] Find it.');
1081 truncblks := truncblks + 1;
1082 if hsz > 24 then
1083 utl_file.get_raw(bfo, dbody, blksz - hsz - tsz);
1084 end if;
1085 utl_file.get_raw(bfo, dtail, tsz);
1086
1087 if not utl_file.is_open(bfr) then
1088 bfr := utl_file.fopen(tmpdir, copyfile, 'RB');
1089 end if;
1090 if not utl_file.is_open(bfw) then
1091 bfw := utl_file.fopen(tmpdir, recfile, 'WB');
1092 end if;
1093
1094 -- filling the trans block
1095 utl_file.get_raw(bfr, dhead, hsz);
1096 utl_file.put_raw(bfw, dhead); -- put original header
1097 utl_file.put_raw(bfw, dbody); -- replace body
1098 utl_file.get_raw(bfr, dbody, blksz - hsz - tsz); -- forward pointer in original file copy
1099 utl_file.get_raw(bfr, dtail, tsz); -- get original tail
1100 utl_file.put_raw(bfw, dtail); -- put original tail
1101 fillednum := fillednum + 1;
1102 i := i + 1;
1103 -- no trans data block left, copy recovered data to backup table and fill the left blocks
1104 if fillednum >= blkstofill then
1105 --if fillednum+blkstofill-1 >= dummyblks then
1106 begin
1107 while true loop
1108 begin
1109 utl_file.get_raw(bfr, frw, blksz);
1110 utl_file.put_raw(bfw, frw);
1111 i := i + 1;
1112
1113 exception
1114 when others then
1115 if utl_file.is_open(bfr) then
1116 utl_file.fclose(bfr);
1117 end if;
1118 if utl_file.is_open(bfw) then
1119 utl_file.fclose(bfw);
1120 end if;
1121 exit;
1122 end;
1123 end loop;
1124
1125 rstnum := restore_table(recowner,
1126 rectab,
1127 rstowner,
1128 rsttab,
1129 selflink);
1130 -- for test
1131 exit when rstnum < 0;
1132 recnum := recnum + rstnum;
1133 fillednum := 0;
1134 commit;
1135
1136 bfr := utl_file.fopen(tmpdir, copyfile, 'RB');
1137 bfw := utl_file.fopen(tmpdir, recfile, 'WB');
1138 -- go to the transaction blocks again
1139 i := 0;
1140 while true loop
1141 begin
1142 utl_file.get_raw(bfr, frw, blksz);
1143 utl_file.put_raw(bfw, frw);
1144 i := i + 1;
1145
1146 exit when i = dummyheader + fillednum;
1147 exception
1148 when others then
1149 --raise;
1150 --trace('[recover_table] block NO.: '||i);
1151 exit;
1152 end;
1153 end loop;
1154 utl_file.fflush(bfw);
1155 exception
1156 when others then
1157 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 exception
1165 when no_data_found then
1166 exit;
1167 when others then
1168 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;
1174
1175 -- last blocks not full filled dummy table
1176 --if fillednum+blkstofill-1 < dummyblks then
1177 if fillednum < blkstofill and rstnum >= 0 then
1178 begin
1179 while true loop
1180 begin
1181 utl_file.get_raw(bfr, frw, blksz);
1182 utl_file.put_raw(bfw, frw);
1183 i := i + 1;
1184
1185 exception
1186 when others then
1187 if utl_file.is_open(bfr) then
1188 utl_file.fclose(bfr);
1189 end if;
1190 if utl_file.is_open(bfw) then
1191 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) then
1206 utl_file.fclose(bfr);
1207 end if;
1208 if utl_file.is_open(bfw) then
1209 utl_file.fclose(bfw);
1210 end if;
1211 if utl_file.is_open(bfo) then
1212 utl_file.fclose(bfo);
1213 end if;
1214 utl_file.fclose_all();
1215
1216 log(truncblks || ' truncated data blocks found. ');
1217 log(recnum || ' records recovered in backup table ' || rstowner || '.' ||
1218 rsttab);
1219
1220 if not srcisfilesystem then
1221 remove_file(tmpdir, p_tmpsrcfile);
1222 end if;
1223 end;
1224
1225 /************************************************************************
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) as
1255 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 begin
1270 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''';
1276
1277 select instr(platform_name, 'Windows'),
1278 decode(instr(platform_name, 'Windows'), 0, '/', '\')
1279 into endianess, slash
1280 from v_$database
1281 where rownum <= 1;
1282 select data_object_id
1283 into recobjid
1284 from dba_objects
1285 where owner = recowner
1286 and object_name = rectab
1287 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);
1295
1296 if offline_files is not null then
1297 for file_rec in (with target_string as
1298 (select /*+inline*/
1299 offline_files str, ';' spliter
1300 from dual),
1301 files as
1302 (select trim(regexp_substr(str,
1303 '[^' || spliter || ']+',
1304 1,
1305 level)) file_name
1306 from target_string
1307 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 filepath
1315 from files
1316 where file_name is not null) loop
1317 --'
1318 begin
1319 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 '+%' and
1332 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 exception
1346 when others then
1347 trace('[recover_table 1] ' || sqlerrm);
1348 trace('[recover_table 1] ' ||
1349 dbms_utility.format_error_backtrace);
1350 end;
1351 end loop;
1352 else
1353 if s_repobjid then
1354 get_seg_meta(recowner, rectab, tmpdir, dtail, addinfo, blksz);
1355 select tablespace_name
1356 into tsname
1357 from dba_tables
1358 where owner = tgtowner
1359 and table_name = tgttable
1360 and rownum <= 1;
1361 select STATUS
1362 into readprop
1363 from dba_tablespaces
1364 where tablespace_name = tsname;
1365 if readprop != 'READ ONLY' then
1366 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 filepath
1371 -- from dba_data_files d, dba_tables t
1372 -- 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 filepath
1378 from dba_data_files d, dba_tables t
1379 where d.tablespace_name = t.tablespace_name
1380 and t.owner = tgtowner
1381 and t.table_name = tgttable) loop
1382 --'
1383 begin
1384 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 '+%' and
1397 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 '+%' and
1413 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 exception
1431 when others then
1432 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' then
1438 execute immediate 'alter tablespace ' || tsname || ' read write';
1439 end if;
1440 else
1441 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 filepath
1446 from dba_data_files d, dba_tables t
1447 where d.tablespace_name = t.tablespace_name
1448 and t.owner = tgtowner
1449 and t.table_name = tgttable) loop
1450 --'
1451 begin
1452 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 '+%' and
1465 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 exception
1479 when others then
1480 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;
1487
1488 log('Total: ' || ttruncblks || ' truncated data blocks found. ');
1489 log('Total: ' || trecnum || ' records recovered in backup table ' ||
1490 rstowner || '.' || rsttab);
1491
1492 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';
1497
1498 log('Recovery completed.');
1499 end;
1500
1501 /************************************************************************
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 tablespace
1514 ** rectsblks: block number of restore tablespace
1515 ************************************************************************/
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) as
1526 ext_mgmt varchar2(30);
1527 ss_mgmt varchar2(30);
1528 slash char(1);
1529 begin
1530 select decode(instr(platform_name, 'Windows'), 0, '/', '\')
1531 into slash
1532 from v_$database
1533 where rownum <= 1;
1534
1535 select block_size, extent_management, segment_space_management
1536 into blksz, ext_mgmt, ss_mgmt
1537 from dba_tablespaces ts, dba_tables t
1538 where t.tablespace_name = ts.tablespace_name
1539 and t.owner = upper(tgtowner)
1540 and t.table_name = upper(tgttable);
1541
1542 --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; --'
1552
1553 --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;
1564
1565 /************************************************************************
1566 ** Clean up existing Recover and Restore Tablespace. Drop tables in the tablespaces
1567 **
1568 ** rects: Recover tablespace name
1569 ** rects: Restore tablespace name, default NULL, will not do cleaning up;
1570 ************************************************************************/
1571 procedure clean_up_ts(rects varchar2, rstts varchar2 default null) as
1572 readprop varchar2(30);
1573 begin
1574 select STATUS
1575 into readprop
1576 from dba_tablespaces
1577 where tablespace_name = rects;
1578 if readprop = 'READ ONLY' then
1579 execute immediate 'alter tablespace ' || rects || ' read write';
1580 end if;
1581 for tab_rec in (select owner, table_name
1582 from dba_tables
1583 where tablespace_name = rects) loop
1584 execute immediate 'drop table ' || tab_rec.owner || '.' ||
1585 tab_rec.table_name;
1586 end loop;
1587 if rstts is not null then
1588 for tab_rec in (select owner, table_name
1589 from dba_tables
1590 where tablespace_name = rstts) loop
1591 execute immediate 'drop table ' || tab_rec.owner || '.' ||
1592 tab_rec.table_name;
1593 end loop;
1594 end if;
1595 end;
1596
1597 /************************************************************************
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) as
1625 blksz number;
1626 blkno number;
1627 cols varchar2(32767);
1628 vals varchar2(32767);
1629 colno number := 0;
1630 begin
1631 if rectab is null then
1632 select block_size
1633 into blksz
1634 from dba_tablespaces ts, dba_tables t
1635 where t.tablespace_name = ts.tablespace_name
1636 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 else
1648 --execute immediate 'truncate table '||recowner||'.'||rectab;
1649 execute immediate 'delete from ' || recowner || '.' || rectab;
1650 commit;
1651 end if;
1652
1653 cols := '';
1654 vals := '';
1655 for col_rec in (select column_name, data_type, nullable
1656 from dba_tab_cols
1657 where owner = recowner
1658 and table_name = rectab) loop
1659 if col_rec.nullable = 'N' then
1660 execute immediate 'alter table ' || recowner || '.' || rectab ||
1661 ' modify ' || col_rec.column_name || ' null';
1662 end if;
1663 if colno < 6 then
1664 if col_rec.data_type like '%CHAR%' or
1665 col_rec.data_type like '%RAW%' then
1666 if colno > 0 then
1667 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%' or
1674 col_rec.data_type = 'FLOAT' then
1675 if colno > 0 then
1676 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%' or
1683 col_rec.data_type = 'DATE' then
1684 if colno > 0 then
1685 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;
1694
1695 --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 then
1697 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 else
1703 execute immediate 'truncate table ' || rstowner || '.' || rsttab;
1704 end if;
1705
1706 --trace('[fill_blocks] insert into '||recowner||'.'||rectab||'('||cols||') values ('||vals||')');
1707 while true loop
1708 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 || '.' || rectab
1712 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.');
1721
1722 execute immediate 'alter tablespace ' || rects || ' read only';
1723
1724 select 'FY_REC_DATA_COPY' || surfix || '.DAT'
1725 into copyfile
1726 from (select surfix
1727 from (select null surfix
1728 from dual
1729 union all
1730 select level surfix
1731 from dual
1732 connect by level <= 255)
1733 where not exists
1734 (select 1
1735 from dba_data_files
1736 where file_name like
1737 '%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;
1743
1744 /************************************************************************
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 tablespace
1756 ************************************************************************/
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) as
1767 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 begin
1775 select ts.ts#, ts.name, ts.online$, blocksize
1776 into tsid, rects, tsonline, blksz
1777 from sys.ts$ ts, dba_tables t
1778 where t.tablespace_name = ts.name
1779 and t.owner = upper(tgtowner)
1780 and t.table_name = upper(tgttable);
1781 if tsonline = 4 then
1782 execute immediate 'alter tablespace ' || rects || ' read write';
1783 end if;
1784
1785 if recfid is null then
1786 open r_files for
1787 select file#, status$ bulk from sys.file$ where ts# = tsid;
1788 fetch r_files bulk collect
1789 into filelist;
1790 recfile := gen_file_name('FY_REC_DATA', '');
1791 select rtrim(directory_path, '\') || '\'
1792 into datapath
1793 from dba_directories
1794 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_id
1799 into recfid
1800 from dba_data_files
1801 where tablespace_name = rects
1802 and (file_name like '%\' || recfile or
1803 file_name like '%/' || recfile); --'
1804 else
1805 open r_files for
1806 select file#, status$ bulk
1807 from sys.file$
1808 where ts# = tsid
1809 and file# != recfid;
1810 fetch r_files bulk collect
1811 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 recfile
1818 from dba_data_files
1819 where file_id = recfid; --'
1820 end if;
1821 log('Recover Tablespace: ' || recfile || '(' || recfid || ')');
1822 for i in 1 .. filelist.count loop
1823 update sys.file$ f
1824 set status$ = 1
1825 where ts# = tsid
1826 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_file
1842 into tn
1843 from dba_segments
1844 where owner = recowner
1845 and segment_name = rectab;
1846 trace('[create_rectab_on_tgttab_ts] header file: ' || filelist.count);
1847 for i in 1 .. filelist.count loop
1848 update sys.file$ f
1849 set status$ = filelist(i).status$
1850 where ts# = tsid
1851 and file# = filelist(i).file#;
1852 end loop;
1853 commit;
1854 execute immediate 'alter system flush buffer_cache';
1855 end;
1856
1857 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) as
1865 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 begin
1874 if p_dstdir is null then
1875 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');
1881
1882 i := 0;
1883 while true loop
1884 begin
1885 utl_file.get_raw(bfr, dbody, blksz);
1886 utl_file.put_raw(bfw, dbody);
1887 i := i + 1;
1888
1889 exit when i = blknum - 1;
1890 exception
1891 when others then
1892 exit;
1893 end;
1894 end loop;
1895
1896 utl_file.get_raw(bfr, dbody, blksz);
1897 if startpos <= 1 then
1898 utl_file.put_raw(bfw,
1899 utl_raw.concat(repcont,
1900 utl_raw.substr(dbody,
1901 1 +
1902 utl_raw.length(repcont))));
1903 else
1904 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;
1911
1912 while true loop
1913 begin
1914 utl_file.get_raw(bfr, dbody, blksz);
1915 utl_file.put_raw(bfw, dbody);
1916
1917 exception
1918 when no_data_found then
1919 exit;
1920 when others then
1921 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;
1927
1928 utl_file.fclose(bfw);
1929 utl_file.fclose(bfr);
1930 trace('[replace_segraw_in_file] completed.');
1931 end;
1932
1933 procedure dump_seg_block_raw(hdfile varchar2,
1934 srcdir varchar2,
1935 blknb number,
1936 blksz number default 8192) as
1937 frw raw(32767);
1938 bfo utl_file.file_type;
1939 bfw utl_file.file_type;
1940 i number := 0;
1941 begin
1942 bfo := utl_file.fopen(srcdir, hdfile, 'RB');
1943 bfw := utl_file.fopen(srcdir, hdfile || '_' || blknb || '.BLK', 'WB');
1944
1945 -- reach to the truncated data blocks
1946 i := 0;
1947 while true loop
1948 begin
1949 utl_file.get_raw(bfo, frw, blksz);
1950 i := i + 1;
1951
1952 exit when i = blknb;
1953 exception
1954 when others then
1955 exit;
1956 end;
1957 end loop;
1958
1959 utl_file.get_raw(bfo, frw, blksz);
1960 utl_file.put_raw(bfw, frw);
1961
1962 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 as
1972 tmpcopyf varchar2(256);
1973 begin
1974 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 begin
1980 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') loop
1986 null;
1987 end loop;
1988 trace('[test_chain] table query completed');
1989 exception when others then
1990 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 then
2002 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 as
2011 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 begin
2023 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;
2062
2063 procedure test_rec2(tow varchar2 default 'DEMO',
2064 ttb varchar2 default 'T_CHAIN',
2065 fbks number default 1) as
2066 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 begin
2077 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_size
2083 into blksz
2084 from dba_tablespaces ts, dba_tables t
2085 where ts.tablespace_name = t.tablespace_name
2086 and t.owner = tgtowner
2087 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;
2114
2115 procedure test_rec3(tow varchar2 default 'DEMO',
2116 ttb varchar2 default 'T_CHAIN',
2117 fbks number default 1,
2118 fid number default null) as
2119 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 begin
2131 tmpdir := 'FY_DATA_DIR';
2132 rstts := 'FY_RST_DATA';
2133 --begin
2134 -- execute immediate 'drop table '||tgtowner||'.'||tgttable||'$';
2135 -- execute immediate 'drop table '||tgtowner||'.'||tgttable||'$$';
2136 --exception when others then
2137 -- 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_size
2150 into blksz
2151 from dba_tablespaces ts, dba_tables t
2152 where ts.tablespace_name = t.tablespace_name
2153 and t.owner = tgtowner
2154 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 begin
2181 --execute immediate 'drop table '||tgtowner||'.'||rectab;
2182 --execute immediate 'alter tablespace '||rects||' drop datafile '||recfid;
2183 --exception when others then
2184 null;
2185 end;
2186 end;
2187
2188 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 as
2194 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 begin
2209 select instr(platform_name, 'Windows')
2210 into endianess
2211 from v_$database
2212 where rownum <= 1;
2213 if temppath is null then
2214 if endianess > 0 then
2215 temppath := 'c:\temp\';
2216 else
2217 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_cnt
2228 from dba_tablespaces
2229 where tablespace_name in (rects, rstts);
2230 if fy_ts_cnt = 2 then
2231 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 recfile
2238 from dba_data_files
2239 where tablespace_name = rects
2240 and rownum <= 1;
2241 else
2242 prepare_files(tgtowner,
2243 tgttable,
2244 temppath,
2245 rects,
2246 recfile,
2247 rstts,
2248 rstfile,
2249 blksz);
2250 end if;
2251 select block_size
2252 into blksz
2253 from dba_tablespaces ts, dba_tables t
2254 where ts.tablespace_name = t.tablespace_name
2255 and t.owner = tgtowner
2256 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;
2290
2291begin
2292 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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
