文档章节

dul恢复drop表测试

我就叫你女王吧
 我就叫你女王吧
发布于 2016/05/24 16:34
字数 1167
阅读 27
收藏 0

dul对被drop对象进行恢复,需要提供两个信息

1.被删除表所属表空间(非必须)
2.被删除表结构(必须)
模拟删除表

--创建测试表

SQL> create table t_dul_drop tablespace czum

  2  as

  3  select * from dba_tables;

 

Table created.

 

--备份被删除表数据,便于比较和提供测试表结构

SQL> create table t_dul_drop_bak tablespace users

  2  as select * from t_dul_drop;

 

Table created.

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> select count(*) from t_dul_drop;

 

  COUNT(*)

----------

      1785

 

SQL> drop table chf.t_dul_drop purge;

 

Table dropped.

 

SQL> alter system checkpoint;

 

System altered.

使用logminer找到data_object_id

delete from "SYS"."OBJ$" where "OBJ#" = '68474' and "DATAOBJ#" ='68474'

and "OWNER#" = '61' and "NAME" = 'T_DUL_DROP' and "NAMESPACE" = '1' and

"SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('04-FEB-13','DD-MON-RR')

and "MTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') and "STIME" = TO_DATE('04-FEB-13', 'DD-MON-RR')

and "STATUS" ='1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0'

and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" ='61' and

"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID ='AAAAASAABAAAPzCAAV';

这里可以知道,被删除表的data_object_id为68474

DUL恢复被删除表

--dul版本

E:\dul10>dul.exe

 

Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013

with 64-bit io functions

 

Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.

 

 Strictly Oracle Internal use Only

 

DUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;

Parameter altered

 

--扫描所属表空间

DUL> scan tablespace 6;

Scanning tablespace 6, data file 6 ...

  13 segment header and 331 data blocks

  tablespace 6, data file 6: 1279 blocks scanned

Reading EXT.dat 13 entries loaded and sorted 13 entries

Reading SEG.dat 13 entries loaded

Reading COMPATSEG.dat 0 entries loaded

Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries

 

--scan tables得到需求表(可以核对数据样例)

DUL> scan tables;

UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)

        , COL004 VARCHAR2(20), COL005 VARCHAR2(30), COL006 VARCHAR2(5), COL007 NUMBER

        , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER

        , COL012 NUMBER, COL013 NUMBER, COL014 NUMBER, COL015 CHAR

        , COL016 NUMBER, COL017 NUMBER, COL018 VARCHAR2(3), COL019 VARCHAR2(1)

        , COL020 NUMBER, COL021 NUMBER, COL022 NUMBER, COL023 NUMBER

        , COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER

        , COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)

        , COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)

        , COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)

        , COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)

        , COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 VARCHAR2(8), COL047 VARCHAR2(3)

        , COL048 VARCHAR2(3), COL049 VARCHAR2(8), COL050 VARCHAR2(8), COL051 VARCHAR2(5)

        , COL052 VARCHAR2(2), COL053 VARCHAR2(2), COL054 VARCHAR2(3), COL055 VARCHAR2(7) )

    STORAGE( DATAOBJNO 68474 );

 

--恢复删除表(业务提供表结构)

DUL> unload table t_dul_drop(

  2  OWNER                              VARCHAR2(30),

  3  TABLE_NAME                         VARCHAR2(30),

  4  TABLESPACE_NAME                                    VARCHAR2(30),

  5  CLUSTER_NAME                                       VARCHAR2(30),

  6  IOT_NAME                                           VARCHAR2(30),

  7  STATUS                                             VARCHAR2(8) ,

  8  PCT_FREE                                           NUMBER      ,

  9  PCT_USED                                           NUMBER      ,

 10  INI_TRANS                                          NUMBER      ,

 11  MAX_TRANS                                          NUMBER      ,

 12  INITIAL_EXTENT                                     NUMBER      ,

 13  NEXT_EXTENT                                        NUMBER      ,

 14  MIN_EXTENTS                                        NUMBER      ,

 15  MAX_EXTENTS                                        NUMBER      ,

 16  PCT_INCREASE                                       NUMBER      ,

 17  FREELISTS                                          NUMBER      ,

 18  FREELIST_GROUPS                                    NUMBER      ,

 19  LOGGING                                            VARCHAR2(3) ,

 20  BACKED_UP                                          VARCHAR2(1) ,

 21  NUM_ROWS                                           NUMBER      ,

 22  BLOCKS                                             NUMBER      ,

 23  EMPTY_BLOCKS                                       NUMBER      ,

 24  AVG_SPACE                                          NUMBER      ,

 25  CHAIN_CNT                                          NUMBER      ,

 26  AVG_ROW_LEN                                        NUMBER      ,

 27  AVG_SPACE_FREELIST_BLOCKS                          NUMBER      ,

 28  NUM_FREELIST_BLOCKS                                NUMBER      ,

 29  DEGREE                                             VARCHAR2(20),

 30  INSTANCES                                          VARCHAR2(20),

 31  CACHE                                              VARCHAR2(10),

 32  TABLE_LOCK                                         VARCHAR2(8) ,

 33  SAMPLE_SIZE                                        NUMBER      ,

 34  LAST_ANALYZED                                      DATE        ,

 35  PARTITIONED                                        VARCHAR2(3) ,

 36  IOT_TYPE                                           VARCHAR2(12),

 37  TEMPORARY                                          VARCHAR2(1) ,

 38  SECONDARY                                          VARCHAR2(1) ,

 39  NESTED                                             VARCHAR2(3) ,

 40  BUFFER_POOL                                        VARCHAR2(7) ,

 41  FLASH_CACHE                                        VARCHAR2(7) ,

 42  CELL_FLASH_CACHE                                   VARCHAR2(7) ,

 43  ROW_MOVEMENT                                       VARCHAR2(8) ,

 44  GLOBAL_STATS                                       VARCHAR2(3) ,

 45  USER_STATS                                         VARCHAR2(3) ,

 46  DURATION                                           VARCHAR2(15),

 47  SKIP_CORRUPT                                       VARCHAR2(8) ,

 48  MONITORING                                         VARCHAR2(3) ,

 49  CLUSTER_OWNER                                      VARCHAR2(30),

 50  DEPENDENCIES                                       VARCHAR2(8) ,

 51  COMPRESSION                                        VARCHAR2(8) ,

 52  COMPRESS_FOR                                       VARCHAR2(12),

 53  DROPPED                                            VARCHAR2(3) ,

 54  READ_ONLY                                          VARCHAR2(3) ,

 55  SEGMENT_CREATED                                    VARCHAR2(3) ,

 56  RESULT_CACHE                                       VARCHAR2(7))

 57   STORAGE( DATAOBJNO 68474 );

. unloading table                T_DUL_DROP

DUL: Warning: Recreating file "T_DUL_DROP.ctl"

    1785 rows unloaded

模拟业务规则提供,创建表

SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0;

 

Table created.

导入数据

e:\dul10>sqlldr chf/xifenfei control=T_DUL_DROP.ctl

 

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Feb 4 23:35:57 2013

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 64

Commit point reached - logical record count 128

Commit point reached - logical record count 192

Commit point reached - logical record count 256

Commit point reached - logical record count 320

Commit point reached - logical record count 384

Commit point reached - logical record count 448

Commit point reached - logical record count 512

Commit point reached - logical record count 576

Commit point reached - logical record count 640

Commit point reached - logical record count 704

Commit point reached - logical record count 768

Commit point reached - logical record count 832

Commit point reached - logical record count 896

Commit point reached - logical record count 960

Commit point reached - logical record count 1024

Commit point reached - logical record count 1088

Commit point reached - logical record count 1152

Commit point reached - logical record count 1216

Commit point reached - logical record count 1280

Commit point reached - logical record count 1344

Commit point reached - logical record count 1408

Commit point reached - logical record count 1472

Commit point reached - logical record count 1536

Commit point reached - logical record count 1600

Commit point reached - logical record count 1664

Commit point reached - logical record count 1728

Commit point reached - logical record count 1785

恢复数据结果

SQL> select count(*) from t_dul_drop;

 

  COUNT(*)

----------

      1785

 

SQL> select owner,table_name from t_dul_drop where rownum<10;

 

OWNER                          TABLE_NAME

------------------------------ ------------------------------

SYS                            IDL_CHAR$

SYS                            IDL_UB2$

SYS                            IDL_SB4$

SYS                            ERROR$

SYS                            SETTINGS$

SYS                            NCOMP_DLL$

SYS                            PROCEDUREJAVA$

SYS                            PROCEDUREC$

SYS                            PROCEDUREPLSQL$

 

rows selected.

· 完美解决dul处理clob字段乱码问题

· dul支持ORACLE 12C CDB数据库恢复

· dul恢复truncate表测试

· exp dmp文件损坏恢复

· 跳过obj$坏块方法

· goldengate 异常处理

· 通过修改基表(link$)让非public dblink变为public

· sqlplus 使用小技巧

· table()函数用法

· long查询结果转换为varchar2类型

· oracle 建立分区表

· 表在线重定义(无主键)

 

更多Oracle学习:http://www.wyzc.com/ocp/?tg=3006123630

© 著作权归作者所有

我就叫你女王吧
粉丝 3
博文 34
码字总数 25430
作品 0
海淀
私信 提问
DUL 恢复简单表测试

--1. 构造测试数据 SQL> alter database datafile '/oracle/app/oracle/oradata/test/users01.dbf' resize 100m;SQL> create table ducw.test_dul tablespace users 2 as 3 select * from d......

sky_heaven
2018/01/31
0
0
Oracle 数据库勒索病毒 RushQL 处理办法

Oracle 数据库勒索病毒 RushQL 处理办法 办法来自Oracle 官方: https://blogs.oracle.com/cnsupport_news/%E5%AF%B9%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E2%80%9C%E6%AF%94%E7%89%B9%E5%......

rootliu
2018/11/21
376
0
无备份情况下恢复MySQL drop table

无备份情况下恢复MySQL drop table love wife & love life --Roger2017-08-0125 阅读 dropMySQLtable 本站文章除注明转载外,均为本站原创: 转载自 love wife & love life —Roger的Oracle&...

love wife & love life --Roger
2017/08/01
0
0
无备份情况下恢复MySQL truncate table

无备份情况下恢复MySQL truncate table love wife & love life --Roger2017-07-3119 阅读 deleteMySQL 本站文章除注明转载外,均为本站原创: 转载自 love wife & love life —Roger的Oracl...

love wife & love life --Roger
2017/07/31
0
0
TSPITR方式数据库找回误操作丢失的数据

一、TSPITR介绍 TSPITR全称是Tablespace Point In Time Recover(表空间基于时间点的不完全恢复)。原理是通过辅助实例基于时间还原出误操作前的数据通过DataPump将数据导入到目标数据库。T...

持续高温
2018/07/30
0
0

没有更多内容

加载失败,请刷新页面

加载更多

面试题必备-web页面基础

html标签是由<>包围的关键词 html标签是成对出现的 有部分标签是没有结束标签的,叫单标签, 页面中所有的内容,都是要放在HTML标签中的 HTML标签分三部分: 标签名称 标签内容 标签属性 HT...

达达前端小酒馆
39分钟前
10
0
OSChina 周二乱弹 —— 女装大佬被拆穿是妹子假扮

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 小小编辑推荐:《All of the Stars》- Ed Sheeran 《All of the Stars》- Ed Sheeran 手机党少年们想听歌,请使劲儿戳(这里) @Leon_swool ...

小小编辑
48分钟前
219
6
3. 彤哥说netty系列之Java BIO NIO AIO进化史

你好,我是彤哥,本篇是netty系列的第三篇。 欢迎来我的公从号彤哥读源码系统地学习源码&架构的知识。 简介 上一章我们介绍了IO的五种模型,实际上Java只支持其中的三种,即BIO/NIO/AIO。 本...

彤哥读源码
52分钟前
20
0
02.日志系统:一条SQL更新语句是如何执行的?

我们还是从一个表的一条更新语句说起,我们创建下面一张表: create table T(ID int primary key, c int); 如果要将ID=2这一行c的值加1,SQL可以这么写: update T set c=c+1 where ID=2; 前...

scgaopan
今天
10
0
【五分钟系列】掌握vscode调试技巧

调试前端js 准备一个前端项目 index.html <!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1......

aoping
今天
11
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部