[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
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
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 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.