FY_Recover_Data 普通表及RAC环境TRUNCATE表恢复测试

原创布衣&凡尘 2022-08-18

1212

1、数据准备：

-- 创建测试用户：
create tablespace two_dat datafile '/u01/oracle/oradata/two_dat.dbf' size 1024m;
create user two identified by two default tablespace two_dat quota unlimited on two_dat;
grant connect,resource to two;
SQL> conn two/two
Connected.
-- 创建测试表
SQL> DROP TABLE  t1 PURGE;
Table dropped
SQL> create table t1
 (
 id number,
 name_id number,
 create_date date,
 sex varchar2(1),
 remark varchar2(100)
 );
Table created
SQL> --创建自增序列
SQL> DROP SEQUENCE t1_seq;
Sequence dropped
SQL> CREATE SEQUENCE t1_seq
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 0
  CYCLE
  CACHE 10
  ORDER;
Sequence created
SQL> --创建随机数据插入存储过程,其中col1列单调递增
SQL> create or replace procedure p_insert_t1(insert_num NUMBER DEFAULT 1000) as
  v_col1 NUMBER;
  BEGIN
  FOR i IN 1..insert_num LOOP
  select t1_seq.nextval INTO v_col1 from dual;
  insert into t1(id,name_id,create_date,sex,remark)
  values
  (v_col1,
  (select round(dbms_random.value(10000, 100000000)) from dual),
  sysdate,
  (select round(dbms_random.value(0, 1)) from dual),
  (select dbms_random.string('a', 100) from dual));
  END LOOP;
  commit;
  end p_insert_t1;
/
Procedure created

2、包导入：

包下载：https://www.modb.pro/download/806794

SQL> @/home/oracle/FY_Recover_Data.pck 
Package created.
Package body created.

3、离线文件测试恢复：

当你不小心truncate了一张表数据后，可以立即将其所在表空间的数据文件拷贝出来。然后利用Fy_Recover_Data的离线数据文件恢复功能恢复数据。这样可以使数据损失降到最低（记住，当表被truncate后，其数据块立即被系统收回，并可能随时被分配给其他对象）。

SQL> exec p_insert_t1(10000);
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
  COUNT(*)
----------
     10000
-- truncated t1 数据
SQL> truncate table t1 ;
Table truncated.

SQL> select count(*) from t1;
  COUNT(*)
----------
         0
 -- 切换sys 用户
SQL> conn / as sysdba
Connected. 
-- 查看表空间对应文件 ：
SQL>  select tablespace_name ,file_name from dba_data_files;
TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------
TWO_DAT                        /u01/oracle/oradata/two_dat.dbf
-- 复制数据文件 ：
SQL> ! cp /u01/oracle/oradata/two_dat.dbf /home/oracle/
SQL> set serveroutput on format wrapped ;
------------------------------------------------------------------
-- TWO:用户名
-- T1:表名
-- 1 ： block number to be filled in recovery table
-- /u01/oracle/oradata/phytest1/ ： FY_RST_DATA、FY_REC_DATA 表空间文件目录
-- /home/oracle/two_dat.dbf ： 离线文件
-------------------------------------------------------------------
SQL> exec fy_recover_data.recover_truncated_table('TWO','T1',1,'/u01/oracle/oradata/phytest1/','/home/oracle/two_dat.dbf');
14:36:34: Use existing Directory Name: TMP_HF_DIR
14:36:34: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
14:36:34: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
14:36:35: Recover Table: TWO.T1$
14:36:35: Restore Table: TWO.T1$$
14:36:40: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
14:36:40: begin to recover table TWO.T1
14:36:40: Use existing Directory Name: TMP_HF_DIR1
14:36:40: Recovering data in datafile /home/oracle/two_dat.dbf
14:36:40: New Directory Name: TMP_DATA_FILE_DIR
14:36:52: 182 truncated data blocks found.
14:36:52: 10000 records recovered in backup table TWO.T1$$
14:36:52: Total: 182 truncated data blocks found.
14:36:52: Total: 10000 records recovered in backup table TWO.T1$$
14:36:52: Recovery completed.
14:36:52: Data has been recovered to TWO.T1$$
PL/SQL procedure successfully completed.
SQL> select count(*) from two.t1$$;
  COUNT(*)
----------
     10000
SQL> insert into two.t1 select * from two.t1$$;
10000 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from two.t1;
  COUNT(*)
----------
     10000
SQL> drop table two.t1$$ purge;
Table dropped.
SQL> select tablespace_name ,file_name from dba_data_files;
TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
FY_REC_DATA                    /u01/oracle/oradata/two/FY_REC_DATA.DAT
FY_RST_DATA                    /u01/oracle/oradata/two/FY_RST_DATA.DAT
TWO_DAT                        /u01/oracle/oradata/two_dat.dbf
-- 删除FY_RST_DATA、FY_REC_DATA 表空间
SQL> drop tablespace FY_RST_DATA including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace FY_REC_DATA including contents and datafiles;
Tablespace dropped.

### 4、truncate 普通表测试
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select count(*) from t1;
  COUNT(*)
----------
     76785
SQL> truncate table t1;
Table truncated.
SQL> select count(*) from t1;
  COUNT(*)
----------
         0
SQL> set serveroutput on format wrapped 
SQL>  exec fy_recover_data.recover_truncated_table('SYS','T1');
11:42:28: New Directory Name: FY_DATA_DIR
11:42:28: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
11:42:28: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
11:42:28: Recover Table: SYS.T1$
11:42:28: Restore Table: SYS.T1$$
11:42:36: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
11:42:36: begin to recover table SYS.T1
11:42:36: New Directory Name: TMP_HF_DIR
11:42:38: Recovering data in datafile /u01/oracle/oradata/phytest1/system01.dbf
11:42:38: Use existing Directory Name: TMP_HF_DIR
11:43:13: 1096 truncated data blocks found.
11:43:13: 76785 records recovered in backup table SYS.T1$$
11:43:13: Total: 1096 truncated data blocks found.
11:43:13: Total: 76785 records recovered in backup table SYS.T1$$
11:43:13: Recovery completed.
11:43:13: Data has been recovered to SYS.T1$$
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
  COUNT(*)
----------
         0
SQL> select count(*) from SYS.T1$$;
  COUNT(*)
----------
     76785
-- 生产2个表空间： 默认表空间文件存放在：/tmp 目录
SQL> select tablespace_name ,file_name from dba_data_files;
TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------
FY_REC_DATA                    /tmp/FY_REC_DATA.DAT
FY_RST_DATA                    /tmp/FY_RST_DATA.DAT
-- 删除表空间：
SQL> drop tablespace FY_REC_DATA including contents and datafiles;
SQL> drop tablespace FY_RST_DATA including contents and datafiles;

5、测试RAC环境：

– 将FY_RST_DATA、FY_REC_DATA 创建到本地目录：

SQL> exec fy_recover_data.recover_truncated_table('TWO','T1',1,'/home/oracle');
10:23:22: Use existing Directory Name: FY_DATA_DIR2
10:23:23: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
10:23:23: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
10:23:23: Recover Table: TWO.T1$
10:23:23: Restore Table: TWO.T1$$
10:23:27: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
10:23:27: begin to recover table TWO.T1
10:23:27: Use existing Directory Name: TMP_HF_DIR1
10:23:32: Recovering data in datafile +DATA/two/datafile/two_dat.287.1113040749
10:23:32: Use existing Directory Name: TMP_HF_DIR1
10:25:33: 182 truncated data blocks found.
10:25:33: 10000 records recovered in backup table TWO.T1$$
10:25:33: Total: 182 truncated data blocks found.
10:25:33: Total: 10000 records recovered in backup table TWO.T1$$
10:25:33: Recovery completed.
10:25:33: Data has been recovered to TWO.T1$$

PL/SQL procedure successfully completed.

10:25:33 SQL> select count(*) from TWO.T1$$;

  COUNT(*)
----------
     10000
-- 后续操作同上，此处省略。。。

其它节点因为表空间建在本地磁盘会报错：

ORA-01186: file 63 failed verification tests
ORA-01157: cannot identify/lock data file 63 - see DBWR trace file
ORA-01110: data file 63: '/home/oracle/FY_RST_DATA.DAT'
File 63 not verified due to error ORA-01157

– 不支持：将FY_RST_DATA、FY_REC_DATA 创建到 ASM
– 报错：ORA-15046

SQL> set serveroutput on format wrapped ; 
SQL>exec fy_recover_data.recover_truncated_table('TWO','T1',1,'+DATA');
10:21:53: Use existing Directory Name: FY_DATA_DIR1
10:21:53: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
10:21:54: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
10:21:54: Recover Table: TWO.T1$
10:21:54: Restore Table: TWO.T1$$
10:21:58: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
10:21:58: begin to recover table TWO.T1
10:21:58: Use existing Directory Name: TMP_HF_DIR1
BEGIN fy_recover_data.recover_truncated_table('TWO','T1',1,'+DATA'); END;

*
ERROR at line 1:
ORA-19504: failed to create file "+DATA/two_dat.287.1113040749"
ORA-17502: ksfdcre:4 Failed to create file +DATA/two_dat.287.1113040749
ORA-15046: ASM file name '+DATA/two_dat.287.1113040749' is not in single-file creation form
ORA-06512: at "SYS.FY_RECOVER_DATA", line 97
ORA-06512: at "SYS.FY_RECOVER_DATA", line 639
ORA-06512: at "SYS.FY_RECOVER_DATA", line 990
ORA-06512: at "SYS.FY_RECOVER_DATA", line 1627
ORA-06512: at line 1

6、注意事项：

1）此操作会频刷：buffer cache
alert 日志：

Wed Aug 17 15:46:48 2022
create tablespace FY_REC_DATA datafile '/home/oracle/FY_REC_DATA.DAT' size 128K autoextend off extent management LOCAL SEGMENT SPACE MANAGEMENT AUTO
Completed: create tablespace FY_REC_DATA datafile '/home/oracle/FY_REC_DATA.DAT' size 128K autoextend off extent management LOCAL SEGMENT SPACE MANAGEMENT AUTO
create tablespace FY_RST_DATA datafile '/home/oracle/FY_RST_DATA.DAT' size 20480K autoextend on extent management LOCAL SEGMENT SPACE MANAGEMENT AUTO
Completed: create tablespace FY_RST_DATA datafile '/home/oracle/FY_RST_DATA.DAT' size 20480K autoextend on extent management LOCAL SEGMENT SPACE MANAGEMENT AUTO
ALTER SYSTEM: Flushing buffer cache
ALTER SYSTEM: Flushing buffer cache
alter tablespace FY_REC_DATA read only
 Converting block 0 to version 10 format
Completed: alter tablespace FY_REC_DATA read only
ALTER SYSTEM SET db_block_checking='FALSE' SCOPE=MEMORY;
ALTER SYSTEM SET db_block_checksum='FALSE' SCOPE=MEMORY;
ALTER SYSTEM SET _db_block_check_objtyp=FALSE SCOPE=MEMORY;
Wed Aug 17 15:55:25 2022
ALTER SYSTEM: Flushing buffer cache
ALTER SYSTEM: Flushing buffer cache
Wed Aug 17 15:57:51 2022
ALTER SYSTEM: Flushing buffer cache
ALTER SYSTEM: Flushing buffer cache
Wed Aug 17 16:00:18 2022
ALTER SYSTEM: Flushing buffer cache
ALTER SYSTEM: Flushing buffer cache
Wed Aug 17 16:02:53 2022
ALTER SYSTEM: Flushing buffer cache
ALTER SYSTEM: Flushing buffer cache

2）truncate之后，需要保证没有新的数据进入表中，否则无法还原；
3）存放该表的数据文件块不能被覆盖，否则无法完整还原数据。

在发生故障后，可以迅速使用：生产环境谨慎操作
SQL> alter tablespace users read only;
SQL> alter tablespace users read write;
来关闭/开启表空间的写功能，这样可以保证数据文件不会被覆写。

4）对于大的表空间，谨慎操作，它会扫描整个表空间的数据文件,所以操作的时间长短与表空间大小有很大的关系，我600G的表空间，恢复了5个小时左右（供参考）。
5）对于RAC集群，不支持在ASM创建恢复表空间，如将FY_RST_DATA、FY_REC_DATA 创建到本地目录时，考虑到另一个节点会报错：

Wed Aug 17 16:23:52 2022
Errors in file /u01/oracle/diag/rdbms/two/two2/trace/two2_m001_13521.trc:
ORA-01157: cannot identify/lock data file 62 - see DBWR trace file
ORA-01110: data file 62: '/home/oracle/FY_RST_DATA.DAT'

6）生产环境建议：当生产操作truncate 失误后，通过闪回功能，在备库进行恢复。
6、建议对表空间对库的数据文件拷贝出来，做离线文件恢复，比较靠谱。

文档下载

《FY_Recover_Data.dbf》https://www.modb.pro/doc/74682
《Oracle RAC 集群迁移文件操作.pdf》文档下载：https://www.modb.pro/doc/72985
《Oracle Date 字段索引使用测试.dbf》文档下载https://www.modb.pro/doc/72521
《PL/Java.pdf》文档下载：https://www.modb.pro/doc/70867
《GP的资源队列.pdf》文档下载：https://www.modb.pro/doc/67644
《Greenplum psql客户端免交互执行SQL.pdf》https://www.modb.pro/doc/69806
《Oracle 自动收集统计信息机制》：https://www.modb.pro/db/403670
《Oracle_索引重建—优化索引碎片》：https://www.modb.pro/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略测试》https://www.modb.pro/db/414692

欢迎赞赏支持或留言指正

最后修改时间：2022-08-19 10:22:52

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者