ITPub博客

首页 > 数据库 > Oracle > [20160222]测试gdul.txt

[20160222]测试gdul.txt

原创 Oracle 作者:lfree 时间:2016-02-22 10:52:23 0 删除 编辑

[20160222]测试gdul.txt

--链接 http://www.itpub.net/thread-2053167-1-1.html,自己昨天看了一下,做一个简单测试:

--下载gdul3.0.1.4.tar.7z并解压,我的linux没有7z解压软件,可以在windows先解压再上传。

1.目录如下:

# ls -lR gdul/
gdul/:
total 9996
drwxr-xr-x 2 oracle oinstall     4096 2016-02-22 08:43:15 bin_file
-rw-r--r-- 1 root   root     10206320 2016-02-21 17:37:54 gdul3.0.1.4.tar.7z
-rw-r--r-- 1 oracle oinstall     4732 2016-02-18 14:38:22 setup

gdul/bin_file:
total 29992
-rw-r--r-- 1 oracle oinstall 6136112 2016-02-19 18:42:26 gdul_aix52
-rw-r--r-- 1 oracle oinstall 9757096 2016-02-19 18:42:24 gdul_hpia11.23
-rw-r--r-- 1 oracle oinstall 6858856 2016-02-19 18:42:24 gdul_hppa11.23
-rw-r--r-- 1 oracle oinstall 2920264 2016-02-19 18:20:46 gdul_linux64
-rw-r--r-- 1 oracle oinstall 2989128 2016-02-19 18:42:22 gdul_solaris8
-rw-r--r-- 1 oracle oinstall 1985024 2016-02-19 18:19:32 gdul_win64.exe

$ ln -s  ./bin_file/gdul_linux64 gdul
$ chmod 755 gdul

2.执行setup脚本,会建立相关目录,如果在windows下使用估计给手工建立以及相关脚本。

$ . setup
GDUL setup program, version 3.0.0.1
------------------------------------------------------------------------
ORACLE_HOME:    /u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_SID:     book
ORACLE_VERSION: 11.2.0.4.0
------------------------------------------------------------------------
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 22 08:54:31 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Please enter password for SYS:
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
setup has completed sucessfully.

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 tablespace sugar as select * from dba_objects ;
Table created.

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

SCOTT@book> truncate table tx ;
Table truncated.

SCOTT@book> alter system checkpoint;
System altered.

4.开始恢复:
$ gdul
*********************************************************************
  GDUL for ORACLE DB.
  Version 3.0.1.4, build date: 2016.02.19.
  Copyright (c) 2007, 2016. Andy Geng.  ALL RIGHTS RESERVED.
  Email   : gengyonghui@aliyun.com
  QQ group: 235019291, WeChat Official Accounts: dbtool
*********************************************************************
Need to run 'bootstrap' to init dictionary.
GDUL> bootstrap
Bootstrap finish.
--如果要使用方向键,可以使用rlwrap gdul方式执行。

GDUL> info
FILE#      TS#        RFILE#     BIGFILE    SIZE(GB)   NAME
1          0          1          FALSE      0.74       /mnt/ramdisk/book/system01.dbf
2          1          2          FALSE      1.36       /mnt/ramdisk/book/sysaux01.dbf
3          2          3          FALSE      0.18       /mnt/ramdisk/book/undotbs01.dbf
4          4          4          FALSE      1.10       /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

GDUL> unload table scott.tx
2016-02-22 09:01:01...unloading table TX 0 rows unloaded.

--我执行了truncate,没有数据。

GDUL> scan tablespace 7
start scan tablespace 7...
scan tablespace completed.

GDUL> untrunc table scott.tx
2016-02-22 09:05:23...untruncating table TX 87016 rows unloaded.

--与前面的恢复一致。取出内容放在dump目录下。注意这里的命令是untrunc,命令最后不要输入分号。导入看看。

--恢复看看。我这里是测试环境(损坏问题不大),如果生产环境,最好选择另外的机器导入测试看看。
$ cp SCOTT_TX.dmp /u01/app/oracle/admin/book/dpdump/

$ impdp scott/book dumpfile=SCOTT_TX.dmp REMAP_TABLE=SCOTT.TX:TY
Import: Release 11.2.0.4.0 - Production on Mon Feb 22 09:17:38 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
ORA-39002: invalid operation
ORA-39068: invalid master table data in row with PROCESS_ORDER=-13
ORA-01403: no data found

--我这里测试失败。
--修改gdul.ini换成exp格式。

<!--export to SQLLDR, EXP, EXPDP-->
<export_format>EXP</export_format>

--退出gdul,重新进入:
GDUL> untrunc table scott.tx
2016-02-22 09:19:18...untruncating table TX 87016 rows unloaded.

$ imp scott/book tables=TX file=SCOTT_TX.dmp ignore=y
Import: Release 11.2.0.4.0 - Production on Mon Feb 22 09:20:21 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                           "TX"      87016 rows imported
Import terminated successfully without warnings.

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

--继续测试:

SCOTT@book> truncate table tx ;
Table truncated.

--插入少量数据。
SCOTT@book> insert into  tx  select * from dba_objects where rownum<=10;
10 rows created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> alter system checkpoint;
System altered.

GDUL> scan tablespace 7
start scan tablespace 7...
scan tablespace completed.
GDUL> untrunc table scott.tx
2016-02-22 09:23:09...untruncating table TX 86608 rows unloaded.

--恢复数据比原来少,估计丢失1块的数据。87016-86608=408

$ imp scott/book tables=TX file=SCOTT_TX.dmp ignore=y
Import: Release 11.2.0.4.0 - Production on Mon Feb 22 09:24:46 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                           "TX"      86608 rows imported
Import terminated successfully without warnings.

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

5.继续测试drop table的情况:

SCOTT@book> drop table tx purge ;
Table dropped.

SCOTT@book> alter system checkpoint;
System altered.

GDUL> scan tablespace 7
start scan tablespace 7...
scan tablespace completed.

GDUL> list user

ID         NAME                 TABLE_CNT
---------- -------------------- -----------
0          SYS                  1039
5          SYSTEM               167
9          OUTLN                3
14         DIP                  0
21         ORACLE_OCM           0
30         DBSNMP               20
31         APPQOSSYS            4
32         WMSYS                46
42         EXFSYS               47
43         CTXSYS               50
45         XDB                  80
46         ANONYMOUS            0
53         ORDSYS               5
54         ORDDATA              73
55         ORDPLUGINS           0
56         SI_INFORMTN_SCHEMA   0
57         MDSYS                151
60         OLAPSYS              126
64         MDDATA               0
66         SPATIAL_WFS_ADMIN_USR 0
69         SPATIAL_CSW_ADMIN_USR 0
71         SYSMAN               729
73         MGMT_VIEW            0
74         FLOWS_FILES          1
75         APEX_PUBLIC_USER     0
77         APEX_030200          360
78         OWBSYS               1
79         OWBSYS_AUDIT         0
83         SCOTT                14
84         HR                   7
85         OE                   14
86         IX                   17
87         SH                   17
88         PM                   3
89         BI                   0
2147483638 XS$NULL              0

GDUL> set user scott
GDUL> list table
ID         NAME
87106      DEPT
87108      EMP
87110      BONUS
87111      SALGRADE
89246      B1
89247      B2
89528      CH9_STATS
90051      BIN$KkbxLB9md9HgU05kqMDnGw==$0
90083      SAMPLE_PAYMENTS
90084      PAYMENTS
90570      T1X
90581      TP
90610      T
90700      TX

--奇怪什么还能看到TX,估计没有重新建立字段的原因。

GDUL> bootstrap
Bootstrap finish.
GDUL> scan tablespace 7
start scan tablespace 7...
scan tablespace completed.
GDUL> set user scott
GDUL> list table
ID         NAME
87106      DEPT
87108      EMP
87110      BONUS
87111      SALGRADE
89246      B1
89247      B2
89528      CH9_STATS
90051      BIN$KkbxLB9md9HgU05kqMDnGw==$0
90083      SAMPLE_PAYMENTS
90084      PAYMENTS
90570      T1X
90581      TP
90610      T

--换一句话将要执行TX的data_object_id才有可能恢复。
$ cat scan_segment.dat
90886, 7, 6, 130

$ cat scan_extent.dat
7, 6, 90886, 130, 14
7, 6, 90886, 145, 15
7, 6, 90886, 161, 15
7, 6, 90886, 177, 15
7, 6, 90886, 193, 15
7, 6, 90886, 209, 15
7, 6, 90886, 225, 15
7, 6, 90886, 241, 15
7, 6, 90886, 258, 126
7, 6, 90886, 386, 126
7, 6, 90886, 514, 126
7, 6, 90886, 642, 126
7, 6, 90886, 770, 126
7, 6, 90886, 898, 126
7, 6, 90886, 1026, 126
7, 6, 90886, 1154, 126
7, 6, 90886, 1282, 126
7, 6, 89890, 1410, 126
7, 6, 89890, 1538, 126

--估计是90886.

GDUL> help

[INIT DATA DICTIONARY]
  info                                                   -- list datafiles.
  bootstrap                                              -- init dictionary, which MUST be run at first.
  scan database [object <data_object_id>]                -- scan whole database for segments and extents.
  scan tablespace <ts#> [object <data_object_id>]        -- scan whole tablespace for segments and extents.
  scan datafile <ts#> <rfile#> [object <data_object_id>] -- scan datafile for segments and extents.

[QUERY DICTIONARY]
  show charset                                           -- show database&national charset.
  show user                                              -- show current user.
  set user <username>                                    -- set current user.
  list user                                              -- list users of database.
  list table <table_substr>                              -- list tables of current user.
  desc <tablename>                                       -- show table structure.

[UNLOAD TABLES]
  unload user <username>                                 -- unload all tables belong to the user.
  unload table [owner.]<tablename>[:partition_name]      -- unload table rows.
  untrunc table [owner.]<tablename>[:partition_name]     -- recover truncated table rows.

[UNLOAD DATA SEGMENT]
  sample segment <data_object_id>|ALL                    -- guess column types of specified data segment, ALL means all segments.
  unload segment <data_object_id>|ALL                    -- unload specified data segment, used by recover dropped table rows, etc

[UTILITY]
  rowid <rowid>                                          -- explain raw rowid.
  rdba <0xnnnnnnnn>                                      -- explain raw dba.
  oradump file <file#> block <block#>                    -- dump block in oracle format.
  dump file <file#> block <block#>                       -- dump block in binary format.

[HELP]
  help                                                   -- show all commands.
  version                                                -- show software version.
  exit|quit                                              -- exit GDUL program.

--猜测unload segment <data_object_id>|ALL应该可以测试看看。

GDUL> unload segment 90886
Unload segment error:
Failed to get sampled segment type file, please run "sample segment 90886" first.
detail :Can't open file, file_name: "sample/seg_00090886.dict", OS Error: No such file or directory

GDUL> sample segment 90886
Segmentation fault

GDUL> sample segment 89890
Segmentation fault
--不行报错。

--重新测试:

SCOTT@book> create table tx tablespace sugar as select * from dba_objects ;
Table created.

SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='TX';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
     90887          90887

SCOTT@book> alter system checkpoint;
System altered.

SCOTT@book> drop table tx purge ;
Table dropped.

SCOTT@book> alter system checkpoint;
System altered.


--依旧不行,取样时直接退出。
GDUL> sample segment 90887
rlwrap: warning: gdul crashed, killed by SIGSEGV.
rlwrap itself has not crashed, but for transparency,
it will now kill itself (without dumping core) with the same signal
warnings can be silenced by the --no-warnings (-n) option
Segmentation fault

SCOTT@book> create table ty tablespace users as select * from dba_objects where rownum=1;
Table created.

SCOTT@book> alter system checkpoint;
System altered.

SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='TY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
     90888          90888

--采用欺骗的方法是否可以恢复。
GDUL> bootstrap
Bootstrap finish.
GDUL> scan tablespace 7
start scan tablespace 7...
scan tablespace completed.

--修改tab.dat文件,找到obj#=90888那行。替换里面的一些内容,修改如下:
<row> <OBJ#>90888</OBJ#><DATAOBJ#>90887</DATAOBJ#><TS#>7</TS#><FILE#>6</FILE#><BLOCK#>130</BLOCK#><BOBJ#></BOBJ#><TAB#></TAB#><COLS>15</COLS><CLUCOLS></CLUCOLS><PCTFREE$>10</PCTFREE$><PCTUSED$>40</PCTUSED$><INITRANS>1</INITRANS><MAXTRANS>255</MAXTRANS><FLAGS>1073741825</FLAGS><AUDIT$>--------------------------------------</AUDIT$><ROWCNT></ROWCNT><BLKCNT></BLKCNT><EMPCNT></EMPCNT><AVGSPC></AVGSPC><CHNCNT></CHNCNT><AVGRLN></AVGRLN><AVGSPC_FLB></AVGSPC_FLB><FLBCNT></FLBCNT><ANALYZETIME></ANALYZETIME><SAMPLESIZE></SAMPLESIZE><DEGREE></DEGREE><INSTANCES></INSTANCES><INTCOLS>15</INTCOLS><KERNELCOLS>15</KERNELCOLS><PROPERTY>536870912</PROPERTY><TRIGFLAG>0</TRIGFLAG><SPARE1>736</SPARE1><SPARE2></SPARE2><SPARE3></SPARE3><SPARE4></SPARE4><SPARE5></SPARE5><SPARE6>2016-02-22 01:57:55</SPARE6> </row>
--主要是DATAOBJ#,TS#,FILE#,BLOCK#符合需要。

--修改obj.dat文件,找到obj#=90888那行。替换里面的一些内容,修改如下:
<row> <OBJ#>90888</OBJ#><DATAOBJ#>90887</DATAOBJ#><OWNER#>83</OWNER#><NAME>TY</NAME><NAMESPACE>1</NAMESPACE><SUBNAME></SUBNAME><TYPE#>2</TYPE#><CTIME>2016-02-22 09:57:55</CTIME><MTIME>2016-02-22 09:57:55</MTIME><STIME>2016-02-22 09:57:55</STIME><STATUS>1</STATUS><REMOTEOWNER></REMOTEOWNER><LINKNAME></LINKNAME><FLAGS>0</FLAGS><OID$></OID$><SPARE1>6</SPARE1><SPARE2>1</SPARE2><SPARE3>83</SPARE3><SPARE4></SPARE4><SPARE5></SPARE5><SPARE6></SPARE6> </row>
--主要是DATAOBJ#符合需要。

GDUL> untrunc table scott.ty
2016-02-22 10:36:49...untruncating table TY 87016 rows unloaded.

--ok取出成功!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1992354/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292307