[20160414]重测gdul.txt-白红宇

[20160414]重测gdul.txt

--2月份时做过一个测试,链接http://blog.itpub.net/267265/viewspace-1992354/

--作者最近做了许多更新,重复测试看看.我主要测试drop的恢复.

1.安装略.

2.配置很简单:

执行source ./osetup

3.测试环境:

SCOTT@book> @ &r/ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table tx as select * from dba_objects ;

Table created.

SCOTT@book> select count(*) from tx ;

COUNT(*)

----------

87035

SCOTT@book> create table ty tablespace tea as select * from dba_objects where 1=0;

Table created.

--注意不要使用原来的表空间,避免写入信息覆盖恢复数据.

SCOTT@book> drop table tx purge ;

Table dropped.

SCOTT@book> alter system checkpoint ;

System altered.

4.恢复看看:

GDUL> bootstrap

Bootstrap finish.

GDUL> info

FILE# TS# RFILE# BIGFILE SIZE(GB) NAME

1 0 1 FALSE 0.77 /mnt/ramdisk/book/system01.dbf

2 1 2 FALSE 1.58 /mnt/ramdisk/book/sysaux01.dbf

3 2 3 FALSE 1.40 /mnt/ramdisk/book/undotbs01.dbf

4 4 4 FALSE 0.49 /mnt/ramdisk/book/users01.dbf

5 6 5 FALSE 0.34 /mnt/ramdisk/book/example01.dbf

6 7 6 FALSE 0.10 /mnt/ramdisk/book/sugar01.dbf

7 8 7 FALSE 0.00 /mnt/ramdisk/book/tea01.dbf

GDUL> unload table scott.tx

Table SCOTT.TX not exists

GDUL> set user scott

GDUL> list table

ID NAME

87106 DEPT

87108 EMP

87110 BONUS

87111 SALGRADE

89246 B1

89247 B2

89528 CH9_STATS

90083 SAMPLE_PAYMENTS

91044 DEPT2

91749 EMP2

92086 BIN$LyZXcP+9Ej3gU05kqMA9/g==$0

92095 EMPX

92178 T1

92190 T

92215 TT

92463 TY

--看不到TX表,已经drop了.

GDUL> scan tablespace 4

start scan tablespace 4...

scan tablespace completed.

SCOTT@book> select * from dba_objects where object_name='TY';

OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME

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

SCOTT TY 92463 92463 TABLE 2016-04-14 12:07:05 2016-04-14 12:07:05 2016-04-14:12:07:05 VALID N N N 1

-- TX 的OBJECT_ID 是92463-1=92462.至少data_object_id如何猜测,也可以sample segment all来判断.或者logminer也可以发现.

GDUL> unload table scott.ty object_id 92462;

2016-04-14 12:20:57...unloaded "SCOTT"."TY" 87035 rows

--这种偷梁换柱的方法比较实用.

GDUL> sample segment 92462;

sampled row count: 10000

sampled max column count: 14

SEG_00092462

"COL0001" VARCHAR2(4000)

"COL0002" VARCHAR2(4000)

"COL0003" VARCHAR2(4000)

"COL0004" NUMBER

"COL0005" NUMBER

"COL0006" VARCHAR2(4000)

"COL0007" DATE

"COL0008" DATE

"COL0009" VARCHAR2(2000)

"COL0010" VARCHAR2(2000)

"COL0011" VARCHAR2(2000)

"COL0012" VARCHAR2(2000)

"COL0013" VARCHAR2(2000)

"COL0014" NUMBER

see 'sample/SEG_00092462.dict' for detail.

-- 如果通过取样有可能是错误的,因为如果后面的几列为NULL,oracle是不记录的.这样如果其他块有记录,这样就存在错误.

SCOTT@book> @ &r/desc ty

Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

--实际上我原来的表有15个字段,而取样少了1个,主要最后一个字段都是NULL.

5.导入:

$ imp scott/book file=SCOTT_TY.dmp ignore=y full=y

Import: Release 11.2.0.4.0 - Production on Thu Apr 14 14:54:52 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V08.01.07 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. . importing table "TY" 87035 rows imported

Import terminated successfully without warnings.

6.补充使用logminer发现drop对象的data_object_id:

BEGIN

DBMS_LOGMNR.START_LOGMNR

(

STARTTIME => '2016-04-14 11:00:00'

,ENDTIME => '2016-04-14 13:00:00'

,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG

+ DBMS_LOGMNR.CONTINUOUS_MINE

+ DBMS_LOGMNR.COMMITTED_DATA_ONLY

);

END;

select * from V$LOGMNR_CONTENTS where seg_name='TX' and operation='DDL' and sql_redo like 'drop%';

Record View

As of: 2016/04/14 14:30:45

SCN: 13228352106

START_SCN:

COMMIT_SCN:

TIMESTAMP: 2016/04/14 12:07:18

START_TIMESTAMP:

COMMIT_TIMESTAMP:

XIDUSN: 10

XIDSLT: 4

XIDSQN: 32609

XID: 0A000400617F0000

PXIDUSN: 10

PXIDSLT: 4

PXIDSQN: 32609

PXID: 0A000400617F0000

TX_NAME:

OPERATION: DDL

OPERATION_CODE: 5

ROLLBACK: 0

SEG_OWNER: SCOTT

SEG_NAME: TX

TABLE_NAME: TX

SEG_TYPE: 2

SEG_TYPE_NAME: TABLE

TABLE_SPACE:

ROW_ID: AAAAAAAAAAAAAAAAAB

USERNAME: UNKNOWN

OS_USERNAME: UNKNOWN

MACHINE_NAME: UNKNOWN

AUDIT_SESSIONID: 0

SESSION#: 0

SERIAL#: 0

SESSION_INFO: UNKNOWN

THREAD#: 1

SEQUENCE#: 2

RBASQN: 57

RBABLK: 40384

RBABYTE: 272

UBAFIL: 3

UBABLK: 0

UBAREC: 0

UBASQN: 0

ABS_FILE#: 0

REL_FILE#: 0

DATA_BLK#: 0

DATA_OBJ#: 92462

~~~~~~~~~~~~~~~~~~~~~~~~

DATA_OBJV#: 1

DATA_OBJD#: 0

SQL_REDO: drop table tx purge ;

SQL_UNDO:

RS_ID: 0x000039.00009dc0.0110

SSN: 0

CSF: 0

INFO: USER DDL (PlSql=0 RecDep=0)

STATUS: 0

REDO_VALUE: 137502

UNDO_VALUE: 137503

SAFE_RESUME_SCN:

CSCN:

OBJECT_ID:

EDITION_NAME:

CLIENT_ID:

-- 注意看~ ,就是对象的data_object_id.