对于已经存在的exp导出文件或者expdp的导出文件,如何获取导出文件头的信息呢?

联系:QQ(5163721)

标题:对于已经存在的exp导出文件或者expdp的导出文件,如何获取导出文件头的信息呢?

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

对于已经存在的exp导出文件或者expdp的导出文件,如何获取导出文件头的信息呢?
我们来测试下,我的expdp导出文件是:

[oracle@lunar datapump]$ pwd
/u01/test/datapump
[oracle@lunar datapump]$ ll
total 452
-rw-r----- 1 oracle asmadmin 458752 Aug 30 14:10 sp2014.dmp
[oracle@lunar datapump]$ 

方法1:利用dbms_datapump.get_dumpfile_info我们可以得到dump文件头的信息,具体脚本参考

抽取exp/expdp导出文件头的信息

[oracle@lunar test]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 30 18:55:43 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SYS@lunarbb>exec show_dumpfile_info(p_dir=> 'lunar_dir', p_file=> 'sp2014.dmp')  
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile.        Version: 18-DEC-2013
Required.: RDBMS version: 10.2.0.1.0 or higher
.          Export dumpfile version: 7.3.4.0.0 or higher
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: sp2014.dmp
Directory: lunar_dir
Disk Path: /u01/test/datapump
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.01.00.00.00
...Internal Dump File Version....: 4.1 (Oracle12c Release 1: 12.1.0.x)
...Creation Date.................: Fri Aug 30 14:10:32 2013
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.: 1
...Master Piece Number in file...: 1
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: lunarbb
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "LUNAR"."SYS_EXPORT_TABLE_01"
...GUID (unique job identifier)..: E5250CB5A94A67ECE0430100007F08B5
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 23
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27
SYS@lunarbb>

方法2: 使用string来看:

[oracle@lunar datapump]$ cat sp2014.dmp | head | strings |more
"LUNAR"."SYS_EXPORT_TABLE_01"  ----job名称
x86_64/Linux 2.4.xx   -------操作系统版本
lunarbb   ----------导出文件的数据库名字
AL32UTF8     -------导出文件的字符集
12.01.00.00.00     -----导出文件的版本信息
001:001:000001:000001
。。。

方法3:
impdp lunar/lunar DIRECTORY=lunar_dir DUMPFILE=lunartest.dmp NOLOGFILE=y SQLFILE=lunartest_impdp.sql TABLES=lunar_par_test TRACE=100300

这里TRACE=100300是生成data pump进程trace信息。其他trace信息还有很多,后面陆续会介绍其他的data pump的相关trace。
我们知道data pump进程启动的时候,会有两类进程,即:Datapump Master (DM) 和 Worker (DW) processes
他们生成的trace文件产生在BACKGROUND_DUMP_DEST目录里面,命名格式如下:
— Master Process trace file: _dm_.trc
— Worker Process trace file: _dw_.trc

[oracle@lunar trace]$ ls -lrt *dw*
-rw-r----- 1 oracle oinstall  352 Mar  4 04:58 travel_dw00_3097.trm
-rw-r----- 1 oracle oinstall 9091 Mar  4 04:58 travel_dw00_3097.trc
[oracle@lunar trace]$ ls -lrt *dm00*
-rw-r----- 1 oracle oinstall  421 Mar  4 04:58 travel_dm00_3095.trm
-rw-r----- 1 oracle oinstall 7057 Mar  4 04:58 travel_dm00_3095.trc
[oracle@lunar trace]$ 

我们具体看一下3个文件的内容:
1,SQLFILE=lunartest_impdp.sql
2,travel_dw00_3097.trc
3,travel_dm00_3095.trc

首先,看下lunartest_impdp.sql,这里面SQLFILE类似于imp工具的INDEXFILE参数,即生成dump文件的DDL信息:

[oracle@lunar datapump]$ impdp lunar/lunar DIRECTORY=lunar_dir DUMPFILE=lunartest.dmp NOLOGFILE=y SQLFILE=lunartest_impdp.sql TABLES=lunar_par_test TRACE=100300

Import: Release 11.2.0.3.0 - Production on Tue Mar 4 04:57:58 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options
Master table "LUNAR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "LUNAR"."SYS_SQL_FILE_TABLE_01":  lunar/******** DIRECTORY=lunar_dir DUMPFILE=lunartest.dmp NOLOGFILE=y SQLFILE=lunartest_impdp.sql TABLES=lunar_par_test TRACE=100300 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "LUNAR"."SYS_SQL_FILE_TABLE_01" successfully completed at 04:58:00

[oracle@lunar datapump]$ 

lunartest_impdp.sql中包含了dump文件中的DDL信息,但是相比以前的exp的indexfile参数生成的DDL文本信息的可读性,好了不是一星半点,O(∩_∩)O哈哈~:

[oracle@lunar datapump]$ cat lunartest_impdp.sql
-- CONNECT LUNAR
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "LUNAR"."LUNAR_PAR_TEST" 
   (    "NAME" VARCHAR2(4000 BYTE) NOT NULL ENABLE, 
        "AAAAA" NUMBER NOT NULL ENABLE, 
        "BBBBB" VARCHAR2(180 BYTE) NOT NULL ENABLE, 
        "CCCCC" VARCHAR2(4000 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  PARTITION BY RANGE ("AAAAA") INTERVAL (1) TRANSITION ("PART_INIT") 
  SUBPARTITION BY RANGE ("BBBBB") 
  SUBPARTITION TEMPLATE ( 
    SUBPARTITION "SP_2008" VALUES LESS THAN ( '2009' ), 
    SUBPARTITION "SP_2009" VALUES LESS THAN ( '2010' ), 
    SUBPARTITION "SP_2010" VALUES LESS THAN ( '2011' ), 
    SUBPARTITION "SP_2011" VALUES LESS THAN ( '2012' ), 
    SUBPARTITION "SP_2012" VALUES LESS THAN ( '2013' ), 
    SUBPARTITION "SP_2013" VALUES LESS THAN ( '2014' ), 
    SUBPARTITION "SP_2014" VALUES LESS THAN ( '2015' ), 
........
省略部分内容
........
 NOCOMPRESS , 
  SUBPARTITION "SYS_SUBP77"  VALUES LESS THAN ('2025') 
  TABLESPACE "USERS" 
 NOCOMPRESS , 
  SUBPARTITION "SYS_SUBP78"  VALUES LESS THAN ('2026') 
  TABLESPACE "USERS" 
 NOCOMPRESS , 
  SUBPARTITION "SYS_SUBP79"  VALUES LESS THAN (MAXVALUE) 
  TABLESPACE "USERS" 
 NOCOMPRESS ) )  ENABLE ROW MOVEMENT ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "LUNAR"."PK_TEST_COM_PARTITION_1" ON "LUNAR"."LUNAR_PAR_TEST" ("NAME") 
  PCTFREE 10 INITRANS 2 MAXTRANS 167 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "LUNAR"."PK_TEST_COM_PARTITION_1" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "LUNAR"."LUNAR_PAR_TEST" ADD CONSTRAINT "PK_TEST_COM_PARTITION_1" PRIMARY KEY ("NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 167 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
[oracle@lunar datapump]$     

2,travel_dw00_3097.trc
从下面的trace可以看到,该文件主要是Data Pump Worker进程的详细操作过程:

KUPP:04:57:59.427: Current trace/debug flags: 00100300 = 1049344
*** MODULE NAME:(Data Pump Worker) 2014-03-04 04:57:59.436
*** ACTION NAME:(SYS_SQL_FILE_TABLE_01) 2014-03-04 04:57:59.436
 
KUPC:04:57:59.436: Setting remote flag for this process to FALSE
prvtaqis - Enter 
prvtaqis subtab_name upd 
prvtaqis sys table upd 
KUPF:04:57:59.489: In INIT_CB
kwqberlst !retval block 
kwqberlst rqan->lagno_kwqiia  4 
kwqberlst rqan->lascn_kwqiia > 0 block 
kwqberlst rqan->lascn_kwqiia  4 
kwqberlst ascn 511695 lascn 22 
kwqberlst !retval block 
kwqberlst rqan->lagno_kwqiia  4 
KUPF:04:57:59.499: Retrieved FILE_LIST, Count = 1
KUPF:04:57:59.499: dump file block size:   4096
KUPF:04:57:59.499: metadata buffer size:   131072
KUPF:04:57:59.499: table data buffer size: 262144
KUPF:04:57:59.499: min phy block size:     512
KUPF:04:57:59.499: max phy block size:     32768
KUPF:04:57:59.499: metadata compression:   1
KUPF:04:57:59.499: tabldata compression:   0
KUPF:04:57:59.499: metadata encryption:    0
KUPF:04:57:59.499: tabldata encryption:    0
KUPF:04:57:59.499: column encryption:      0
KUPF:04:57:59.499: job version:            11.02.00.03.00
KUPF:04:57:59.499: (kupfxInit) File Manager has been initialized...
.......
省略部分
.........
*** 2014-03-04 04:58:00.251
kwqberlst !retval block 
kwqberlst rqan->lagno_kwqiia  4 
kwqberlst rqan->lascn_kwqiia > 0 block 
kwqberlst rqan->lascn_kwqiia  4 
kwqberlst ascn 511695 lascn 22 
kwqberlst !retval block 
kwqberlst rqan->lagno_kwqiia  4 
KUPF:04:58:00.255: MD: filePiece(1).fid: 1
KUPF:04:58:00.255: MD: filePiece(1).bno: 2
KUPF:04:58:00.255: MD: filePiece(1).len: 1075
KUPF:04:58:00.255: MD: filePiece(1).alc: .1075
KUPF:04:58:00.255: MD: filePiece(1).off: 0
KUPF:04:58:00.255: MD: filePiece(1).nam: /home/oracle/test/datapump/lunartest.dmp
KUPF:04:58:00.255: In kupfxReadLob...
KUPF:04:58:00.255: In kupfiReadLob...
KUPF:04:58:00.255: In kupfiSetupMDFilePiece...
KUPF:04:58:00.255: In kupfuAllocFilePiece...
KUPF:04:58:00.255: In kupfioOpenForRead...
KUPF:04:58:00.255: In kupfioOpenForRead: file /home/oracle/test/datapump/lunartest.dmp has 63 block(s)
KUPF:04:58:00.255: In kupfTransformData...
.........
省略部分
.........
KUPF:04:58:00.314: In kupfuDecompress...
KUPF:04:58:00.314: ...processing input piece number: 1
KUPF:04:58:00.314: ...available in: 1421
KUPF:04:58:00.314: ...available out: 131072
KUPF:04:58:00.316: ...decompressor encountered EOS.
KUPF:04:58:00.316: ...writing 8400 bytes to CLOB
KUPF:04:58:00.316: ...wrote 4200 chars to CLOB at offset 1
KUPF:04:58:00.316: In kupfuiTrxCleanup...4
KUPF:04:58:00.316: MD: XML read complete...
KUPF:04:58:00.316: MD: Input: 1421 bytes Output: 4200 bytes
KUPF:04:58:00.379: In kupfxCloseCtx...
KUPF:04:58:00.379: In kupfiCloseCtx...
KUPF:04:58:00.379: In kupfioCloseFile...
KUPF:04:58:00.379: File number 1 has been closed
KUPF:04:58:00.379: Leaving kupfiCloseCtx...
kwqberlst !retval block 
kwqberlst rqan->lagno_kwqiia  4 
kwqberlst rqan->lascn_kwqiia > 0 block 
kwqberlst rqan->lascn_kwqiia  4 
kwqberlst ascn 511695 lascn 22 
kwqberlst !retval block 
kwqberlst rqan->lagno_kwqiia  4 
KUPF:04:58:00.385: In kupfxTerm...
[oracle@lunar trace]$ 

3,travel_dm00_3095.trc
从下面的trace可以看到,该文件主要是Data Pump Master进程的详细操作过程:

KUPP:04:57:59.157: Current trace/debug flags: 00100300 = 1049344
*** MODULE NAME:(Data Pump Master) 2014-03-04 04:57:59.163
*** ACTION NAME:(SYS_SQL_FILE_TABLE_01) 2014-03-04 04:57:59.163
 
KUPC:04:57:59.163: Setting remote flag for this process to FALSE
prvtaqis - Enter 
prvtaqis subtab_name upd 
prvtaqis sys table upd 
KUPP:04:57:59.201: Initialization complete for master process DM00
KUPF:04:57:59.302: In INIT_CB
KUPF:04:57:59.303: dump file block size:   4096
KUPF:04:57:59.303: metadata buffer size:   131072
KUPF:04:57:59.303: table data buffer size: 262144
KUPF:04:57:59.303: min phy block size:     512
KUPF:04:57:59.303: max phy block size:     32768
KUPF:04:57:59.303: metadata compression:   0
KUPF:04:57:59.303: tabldata compression:   0
KUPF:04:57:59.303: metadata encryption:    0
KUPF:04:57:59.303: tabldata encryption:    0
KUPF:04:57:59.303: column encryption:      0
KUPF:04:57:59.303: job version:            11.02.00.03.00
KUPF:04:57:59.303: (kupfxInit) File Manager has been initialized...
.........
省略部分
.........
kwqberlst ascn 511682 lascn 22 
KUPF:04:57:59.378: is_dba    = TRUE
KUPF:04:57:59.378: read_only = TRUE
KUPF:04:57:59.378: fileName  = lunartest.dmp          ------------导出文件名称
KUPF:04:57:59.379: directory = LUNAR_DIR              ------------directory的名称
KUPF:04:57:59.379: In kupfxParseFileName...
KUPF:04:57:59.379: directory = LUNAR_DIR
KUPF:04:57:59.379: In kupfxGetDefFileName...
KUPF:04:57:59.380: In kupfxExmDmpFile...
KUPF:04:57:59.380: In kupfuExmDmpFile...
KUPF:04:57:59.380: In kupfioReadHeader...
KUPF:04:57:59.380: newImpFile: EXAMINE_DUMP_FILE
KUPF:04:57:59.380: ......DB Version = 11.02.00.03.00       ------------这里就是数据库版本信息
KUPF:04:57:59.380: File Version Str = 3.1
KUPF:04:57:59.380: File Version Num = 769
KUPF:04:57:59.380: Version CapBits1 = 98559
KUPF:04:57:59.380: ......Has Master = 1
KUPF:04:57:59.380: ........Job Guid = F3C86AFC53A40BEFE0434238A8C03446
KUPF:04:57:59.380: Master Table Pos = 8
KUPF:04:57:59.380: Master Table Len = 226992
KUPF:04:57:59.380: Master Table Fsi = 001:001:000001:000001
KUPF:04:57:59.380: .....File Number = 1
KUPF:04:57:59.380: ......Charset ID = 873            ------------这里就是字符集ID
KUPF:04:57:59.381: ...Creation date = Tue Mar 04 04:55:39 2014
KUPF:04:57:59.381: ...........Flags = 2
KUPF:04:57:59.381: ......Media Type = 0
KUPF:04:57:59.381: ........Job Name = "LUNAR"."SYS_EXPORT_TABLE_01"   ------------这里就是job信息
KUPF:04:57:59.381: ........Platform = x86_64/Linux 2.4.xx
KUPF:04:57:59.381: ........Instance = travel
KUPF:04:57:59.381: ........Language = AL32UTF8   ------------这里就是字符集信息
KUPF:04:57:59.381: .......Blocksize = 4096
KUPF:04:57:59.386: Added FILE row; PO: -22; FID: 1; NAM: lunartest.dmp
KUPF:04:57:59.386: newImpFile: file; /home/oracle/test/datapump/lunartest.dmp, FID; 1
kwqberlst !retval block 
kwqberlst rqan->lagno_kwqiia  4 
kwqberlst rqan->lascn_kwqiia > 0 block 
kwqberlst rqan->lascn_kwqiia  4 
kwqberlst ascn 511682 lascn 22 
KUPF:04:57:59.391: In kupfxGetDefFileName...
KUPF:04:57:59.391: is_dba    = TRUE
KUPF:04:57:59.391: read_only = FALSE
KUPF:04:57:59.391: fileName  = lunartest_impdp.sql
KUPF:04:57:59.391: directory = LUNAR_DIR
KUPF:04:57:59.391: In kupfxParseFileName...
KUPF:04:57:59.392: directory = LUNAR_DIR
.........
省略部分
.........
此条目发表在 expdp/impdp, FAQ, Scripts 分类目录,贴了 , 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注