Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » 备份恢复

如何查看expdp/exp dmp文件信息

每个dump文件头部都包含文件头信息,通常块的大小为4k,头部包含了数据库名称,导出时间,导出的平台,数据库的版本,字符集等主要信息。从Oracle10g Release 2 (10.2.0.1.0)开始,可以通过包dbms_datapump.get_dumpfile_info来获取这些信息,当然也可以通过aodu获取。本文的测试是在12c和11g下做的测试。


获取头部信息的procedure show_dumpfile_info

CREATE OR REPLACE PROCEDURE show_dumpfile_info(
  p_dir  VARCHAR2 DEFAULT 'dpump',
  p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir        = directory object where dump file can be found
-- p_file       = simple filename of export dump file (case-sensitive)
  v_separator   VARCHAR2(80) := '--------------------------------------' ||
                                '--------------------------------------';
  v_path        all_directories.directory_path%type := '?';
  v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp 3=ext
  v_fileversion VARCHAR2(15);           -- 0.1=10gR1 1.1=10gR2 (etc.)
  v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info
  type valtype  IS VARRAY(23) OF VARCHAR2(2048);
  var_values    valtype := valtype();
  no_file_found EXCEPTION;
  PRAGMA        exception_init(no_file_found, -39211);
BEGIN
-- Dump file details:
-- ==================
-- For Oracle10g Release 2 and higher:
--    dbms_datapump.KU$_DFHDR_FILE_VERSION        CONSTANT NUMBER := 1;
--    dbms_datapump.KU$_DFHDR_MASTER_PRESENT      CONSTANT NUMBER := 2;
--    dbms_datapump.KU$_DFHDR_GUID                CONSTANT NUMBER := 3;
--    dbms_datapump.KU$_DFHDR_FILE_NUMBER         CONSTANT NUMBER := 4;
--    dbms_datapump.KU$_DFHDR_CHARSET_ID          CONSTANT NUMBER := 5;
--    dbms_datapump.KU$_DFHDR_CREATION_DATE       CONSTANT NUMBER := 6;
--    dbms_datapump.KU$_DFHDR_FLAGS               CONSTANT NUMBER := 7;
--    dbms_datapump.KU$_DFHDR_JOB_NAME            CONSTANT NUMBER := 8;
--    dbms_datapump.KU$_DFHDR_PLATFORM            CONSTANT NUMBER := 9;
--    dbms_datapump.KU$_DFHDR_INSTANCE            CONSTANT NUMBER := 10;
--    dbms_datapump.KU$_DFHDR_LANGUAGE            CONSTANT NUMBER := 11;
--    dbms_datapump.KU$_DFHDR_BLOCKSIZE           CONSTANT NUMBER := 12;
--    dbms_datapump.KU$_DFHDR_DIRPATH             CONSTANT NUMBER := 13;
--    dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
--    dbms_datapump.KU$_DFHDR_DB_VERSION          CONSTANT NUMBER := 15;
-- For Oracle11gR1:
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT  CONSTANT NUMBER := 16;
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
--    dbms_datapump.KU$_DFHDR_DATA_COMPRESSED     CONSTANT NUMBER := 18;
--    dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED  CONSTANT NUMBER := 19;
--    dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED      CONSTANT NUMBER := 20;
-- For Oracle11gR2:
--    dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED   CONSTANT NUMBER := 21;
--    dbms_datapump.KU$_DFHDR_ENCRIPTION_MODE     CONSTANT NUMBER := 22;
-- For Oracle12cR1:
--    dbms_datapump.KU$_DFHDR_COMPRESSION_ALG     CONSTANT NUMBER := 23;


-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 22;
-- For Oracle12cR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 23;


-- Show header output info:
-- ========================


  dbms_output.put_line(v_separator);
  dbms_output.put_line('Purpose..: Obtain details about export ' ||
        'dumpfile.        Version: 18-DEC-2013');
  dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
  dbms_output.put_line('.          ' ||
        'Export dumpfile version: 7.3.4.0.0 or higher');
  dbms_output.put_line('.          ' ||
        'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
  dbms_output.put_line('Usage....: ' ||
        'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
  dbms_output.put_line('Example..: ' ||
        'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
  dbms_output.put_line(v_separator);
  dbms_output.put_line('Filename.: ' || p_file);
  dbms_output.put_line('Directory: ' || p_dir);


-- Retrieve Export dumpfile details:
-- =================================


  SELECT directory_path INTO v_path FROM all_directories
   WHERE directory_name = p_dir
      OR directory_name = UPPER(p_dir);


  dbms_datapump.get_dumpfile_info(
           filename   => p_file,       directory => UPPER(p_dir),
           info_table => v_info_table, filetype  => v_filetype);


  var_values.EXTEND(23);
  FOR i in 1 .. 23 LOOP
    BEGIN
      SELECT value INTO var_values(i) FROM TABLE(v_info_table)
       WHERE item_code = i;
    EXCEPTION WHEN OTHERS THEN var_values(i) := '';
    END;
  END LOOP;


  dbms_output.put_line('Disk Path: ' || v_path);


  IF v_filetype >= 1 THEN
    -- Get characterset name:
    BEGIN
      SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
        ')' INTO var_values(5) FROM dual;
    EXCEPTION WHEN OTHERS THEN null;
    END;
    IF v_filetype = 2 THEN
      dbms_output.put_line(
         'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
      dbms_output.put_line(v_separator);
      SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
        '1', '1 (Direct Path)', var_values(13))
        INTO var_values(13) FROM dual;
      dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
      dbms_output.put_line('...Direct Path Export Mode.......: ' || var_values(13));
      dbms_output.put_line('...Export Version................: ' || var_values(15));
    ELSIF v_filetype = 1 OR v_filetype = 3 THEN
      SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
      SELECT DECODE(var_values(1),
                    '0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
                    '1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
                    '2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
                    '3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
                    '4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
        var_values(1)) INTO var_values(1) FROM dual;
      SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
        var_values(2)) INTO var_values(2) FROM dual;
      SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
        var_values(14)) INTO var_values(14) FROM dual;
      SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
        var_values(18)) INTO var_values(18) FROM dual;
      SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
        var_values(19)) INTO var_values(19) FROM dual;
      SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
        var_values(20)) INTO var_values(20) FROM dual;
      SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
        var_values(21)) INTO var_values(21) FROM dual;
      SELECT DECODE(var_values(22),
                    '1', '1 (Unknown)',
                    '2', '2 (None)',
                    '3', '3 (Password)',
                    '4', '4 (Password and Wallet)',
                    '5', '5 (Wallet)',
        var_values(22)) INTO var_values(22) FROM dual;
      SELECT DECODE(var_values(23),
                    '2', '2 (None)',
                    '3', '3 (Basic)',
                    '4', '4 (Low)',
                    '5', '5 (Medium)',
                    '6', '6 (High)',
        var_values(23)) INTO var_values(23) FROM dual;
      IF v_filetype = 1 THEN
        dbms_output.put_line(
           'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
        dbms_output.put_line(v_separator);
        dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
        dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
        dbms_output.put_line('...Creation Date.................: ' || var_values(6));
        dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
        dbms_output.put_line('...Master Present in dump file...: ' || var_values(2));
        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN
          dbms_output.put_line('...Master in how many dump files.: ' || var_values(16));
          dbms_output.put_line('...Master Piece Number in file...: ' || var_values(17));
        END IF;
        dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
        IF v_fileversion >= '2.1' THEN
          dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
        END IF;
        dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
        dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
        dbms_output.put_line('...Job Name......................: ' || var_values(8));
        dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
        dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
        dbms_output.put_line('...Metadata Compressed...........: ' || var_values(14));
        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
          dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
          IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
            dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
          END IF;
          dbms_output.put_line('...Metadata Encrypted............: ' || var_values(19));
          dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
          dbms_output.put_line('...Column Data Encrypted.........: ' || var_values(21));
          dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
        END IF;
      ELSE
        dbms_output.put_line(
           'Filetype.: ' || v_filetype || ' (External Table dumpfile)');
        dbms_output.put_line(v_separator);
        dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
        dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
        dbms_output.put_line('...Creation Date.................: ' || var_values(6));
        dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
        dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
        IF v_fileversion >= '2.1' THEN
          dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
        END IF;
        dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
        dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
        dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
        dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
          dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
          IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
            dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
          END IF;
          dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
          dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
        END IF;
      END IF;
      dbms_output.put_line('...Internal Flag Values..........: ' || var_values(7));
      dbms_output.put_line('...Max Items Code (Info Items)...: ' ||
                  dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
    END IF;
  ELSE
    dbms_output.put_line('Filetype.: ' || v_filetype);
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: Not an export dumpfile.');
  END IF;
  dbms_output.put_line(v_separator);
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('Disk Path: ?');
    dbms_output.put_line('Filetype.: ?');
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: Directory Object does not exist.');
    dbms_output.put_line(v_separator);
  WHEN no_file_found THEN
    dbms_output.put_line('Disk Path: ' || v_path);
    dbms_output.put_line('Filetype.: ?');
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: File does not exist.');
    dbms_output.put_line(v_separator);
END;
/

12c dmp文件头部信息
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB11                          READ WRITE NO
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/cdb1/undotbs01.dbf
/oradata/cdb1/pdb11/system01.dbf
/oradata/cdb1/pdb11/sysaux01.dbf
/oradata/cdb1/pdb11/pdb11_users01.dbf
SQL> create tablespace ohs_tbs datafile '/oradata/cdb1/pdb11/ohs_tbs01.dbf' size 200M;
Tablespace created.
SQL> create user ohsdba identified by ohsdba default tablespace ohs_tbs;
User created.
SQL> grant dba to ohsdba;
Grant succeeded.
SQL>
SQL> conn ohsdba/ohsdba@localhost:/pdb11
Connected.
SQL>
SQL> create table ohs_objects as select * from dba_objects;
Table created.
SQL> insert into ohs_objects select * from ohs_objects;
90926 rows created.
SQL> /
181852 rows created.
SQL> /
363704 rows created.

SQL> commit;
Commit complete.
SQL>
SQL> create directory dpump as '/pgold/dpump';
Directory created.
SQL>



[oracle@db1 ~]$ expdp ohsdba/ohsdba@localhost:/pdb11 directory=dpump dumpfile=pgold_pdb11_%U.dmp logfile=expdp_20160612.log filesize=5M parallel=5
Export: Release 12.1.0.2.0 - Production on Sun Jun 12 10:51:58 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "OHSDBA"."SYS_EXPORT_SCHEMA_01":  ohsdba/********@localhost:/pdb11 directory=dpump dumpfile=pgold_pdb11_%U.dmp logfile=expdp_20160612.log filesize=5M parallel=5
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 104 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
. . exported "OHSDBA"."OHS_OBJECTS"                      82.86 MB  727408 rows
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OHSDBA.SYS_EXPORT_SCHEMA_01 is:
  /pgold/dpump/pgold_pdb11_01.dmp
  /pgold/dpump/pgold_pdb11_02.dmp
  /pgold/dpump/pgold_pdb11_03.dmp
  /pgold/dpump/pgold_pdb11_04.dmp
  /pgold/dpump/pgold_pdb11_05.dmp
  /pgold/dpump/pgold_pdb11_06.dmp
  /pgold/dpump/pgold_pdb11_07.dmp
  /pgold/dpump/pgold_pdb11_08.dmp
  /pgold/dpump/pgold_pdb11_09.dmp
  /pgold/dpump/pgold_pdb11_10.dmp
  /pgold/dpump/pgold_pdb11_11.dmp
  /pgold/dpump/pgold_pdb11_12.dmp
  /pgold/dpump/pgold_pdb11_13.dmp
  /pgold/dpump/pgold_pdb11_14.dmp
  /pgold/dpump/pgold_pdb11_15.dmp
  /pgold/dpump/pgold_pdb11_16.dmp
  /pgold/dpump/pgold_pdb11_17.dmp
  /pgold/dpump/pgold_pdb11_18.dmp
Job "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jun 12 10:52:39 2016 elapsed 0 00:00:39
[oracle@db1 ~]$

SQL> set serveroutput on
SQL> exec show_dumpfile_info(p_file=>'pdb11_01.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.: pdb11_01.dmp
Directory: dpump
Disk Path: /pgold/dpump
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.01.00.02.00
...Internal Dump File Version....: 4.1 (Oracle12c Release 1: 12.1.0.x)
...Creation Date.................: Sun Jun 12 10:50:28 2016
...File Number (in dump file set): 1
...Master Present in dump file...: 0 (No)
...Master in how many dump files.:
...Master Piece Number in file...:
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: cdb1
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: 350CD52F3B8F10A3E0537800000A550F
...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.
SQL>


[oracle@db1 ~]$ ./aodu
AT Oracle Database Utility,Release 1.1.0 on Sun Jun 12 11:07:57 2016
Copyright (c) 2014, 2015, Robin.Han.  All rights reserved.
http://ohsdba.cn
E-Mail:375349564@qq.com
AODU> help
Available Commands:
      help      - Display this help
       dul      - oracle data unload,currently under developing
    unpump      - extract data from dmp files,currently under developing
    asmcmd      - asmcmd utility,currently under developing
      bbed      - bbed utility,currently under developing
      exit      - Exits
   asmdisk      - Check asm disks(e.g. asmdisk /dev/sdb1)
    unwrap      - Unwrap PL/SQL,e.g. unwrap prvtstat.plb prvtstat.sql
   dmpinfo      - Get dmpfile(exp/expdp) info,e.g. dmpinfo ohsdba.dmp
       asm      - ASM utility,internal use only
       rac      - RAC utility,internal use only
     optim      - Optimizer utility,internal use only
    ora600      - ORA-600 searching
  oradebug      - Oradebug utility,oradebug usage all
      host      - Enter OS terminal
     rowid      - Decode rowid,e.g. rowid AAAAECAABAAAAgiAAA
       scn      - Decode scn,e.g. scn 0x0000.00233d44
      rdba      - Convert RDBA into rfile# and block#,e.g. rdba 0x004062ff
      time      - Convert the number to checkpoint time,e.g. time 123456789
      drux      - Decode rba/xid/uba,e.g. drux rba 0x000024.000011bd.0010
     odlog      - Get archived/redo logfile info. odlog <filename>
AODU> dmpinfo /pgold/dpump/pgold_pdb11_18.dmp
        Dmp Filetype..................: Datapump dumpfile
        Source Database Name..........:
        Database Job Version..........:
        Internal Dump File Version....: 4.1
        Creation Date.................: 2016-06-12 10:52:39
        Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
        GUID (unique job identifier)..: 350CDB00668A10D6E0537800A49C8
        Block size dump file (bytes)..: 4096
        Language Name of characterset.:
        Characterset ID of source db..: 873
        Operating System of source db.:
AODU>


11g dmp文件头部信息

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL>
SQL> select name from v$database;
NAME
---------
UPGR
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/upgr/system01.dbf
/oradata/upgr/sysaux01.dbf
/oradata/upgr/undotbs01.dbf
/oradata/upgr/users01.dbf
SQL> create tablespace ohs_tbs11g datafile '/oradata/upgr/ohs_tbs11g.dbf' size 200M;
Tablespace created.
SQL> create user ohsdba identified by ohsdba default tablespace ohs_tbs11g;
User created.
SQL> grant dba to ohsdba;
Grant succeeded.
SQL> conn ohsdba/ohsdba
Connected.
SQL> create table ohs_objects as select * from dba_objects;
Table created.
SQL> insert into ohs_objects select * from ohs_objects;
86348 rows created.
SQL> /
172696 rows created.
SQL> /
345392 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create directory dpump as '/oradata';
Directory created.
SQL>

[oracle@db1 ~]$ expdp ohsdba/ohsdba directory=dpump dumpfile=ohsdba_11g.dmp logfile=ohsdba.log
Export: Release 11.2.0.4.0 - Production on Sun Jun 12 11:18:35 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, Data Mining and Real Application Testing options
Starting "OHSDBA"."SYS_EXPORT_SCHEMA_01":  ohsdba/******** directory=dpump dumpfile=ohsdba_11g.dmp logfile=ohsdba.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "OHSDBA"."OHS_OBJECTS"                      66.91 MB  690784 rows
Master table "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OHSDBA.SYS_EXPORT_SCHEMA_01 is:
  /oradata/ohsdba_11g.dmp
Job "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jun 12 11:19:10 2016 elapsed 0 00:00:29

[oracle@db1 ~]$ 

SQL> exec show_dumpfile_info(p_file=>'ohsdba_11g.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.: ohsdba_11g.dmp
Directory: dpump
Disk Path: /oradata
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 11.02.00.04.00
...Internal Dump File Version....: 3.1 (Oracle11g Release 2: 11.2.0.x)
...Creation Date.................: Sun Jun 12 11:19:10 2016
...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....: upgr
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: 350D3A38AF2312D7E0537800000A9C09
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 2
...Max Items Code (Info Items)...: 22
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.

SQL>

[oracle@db1 ~]$ ./aodu
AT Oracle Database Utility,Release 1.1.0 on Sun Jun 12 11:19:44 2016
Copyright (c) 2014, 2015, Robin.Han.  All rights reserved.
http://ohsdba.cn
E-Mail:375349564@qq.com
AODU> dmpinfo /oradata/ohsdba_11g.dmp
        Dmp Filetype..................: Datapump dumpfile
        Source Database Name..........: upgr
        Database Job Version..........: 11.02.00.04.00
        Internal Dump File Version....: 3.1
        Creation Date.................: 2016-06-12 11:19:10
        Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
        GUID (unique job identifier)..: 350D3A38AF2312D7E0537800A9C09
        Block size dump file (bytes)..: 4096
        Language Name of characterset.: AL32UTF8
        Characterset ID of source db..: 873
        Operating System of source db.: x86_64/Linux 2.4.xx
AODU>

11g客户端导出12c数据库

在11g上创建dblink,导出12c数据库

SQL> conn ohsdba/ohsdba
Connected.
SQL> create database link T12c connect to ohsdba identified by ohsdba using '(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1) (PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=pdb11)))';
Database link created.
SQL> select * from dual@T12c;
D
-
X
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

[oracle@db1 ~]$ expdp ohsdba/ohsdba directory=dpump dumpfile=pdb11_12c.dmp logfile=12c.log network_link=T12c

Export: Release 11.2.0.4.0 - Production on Sun Jun 12 12:34:30 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, Data Mining and Real Application Testing options
Starting "OHSDBA"."SYS_EXPORT_SCHEMA_01":  ohsdba/******** directory=dpump dumpfile=pdb11_12c.dmp logfile=12c.log network_link=T12c
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 104 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "OHSDBA"."OHS_OBJECTS"                      82.86 MB  727408 rows
Master table "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OHSDBA.SYS_EXPORT_SCHEMA_01 is:
  /oradata/pdb11_12c.dmp
Job "OHSDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jun 12 12:34:49 2016 elapsed 0 00:00:18


[oracle@db1 ~]$ ./aodu

AT Oracle Database Utility,Release 1.1.0 on Sun Jun 12 12:35:07 2016
Copyright (c) 2014, 2015, Robin.Han.  All rights reserved.
http://ohsdba.cn
E-Mail:375349564@qq.com
AODU> dmpinfo /oradata/pdb11_12c.dmp
        Dmp Filetype..................: Datapump dumpfile
        Source Database Name..........: upgr
        Database Job Version..........: 11.02.00.04.00
        Internal Dump File Version....: 3.1
        Creation Date.................: 2016-06-12 12:34:49
        Job Name......................: "OHSDBA"."SYS_EXPORT_SCHEMA_01"
        GUID (unique job identifier)..: 350E49AEAC812221E0537800A00F9
        Block size dump file (bytes)..: 4096
        Language Name of characterset.: AL32UTF8
        Characterset ID of source db..: 873
        Operating System of source db.: x86_64/Linux 2.4.xx
AODU>

注意:用数据泵expdp导出时,不需要设置字符集(即使你设置了,也不管用),因为Oracle会查询数据库获取字符集的信息,然后再做导出。用network_link导出时,你看到的dump文件头部信息为你当前数据库的信息,比如数据库的名字,数据库Job版本,操作系统平台信息,字符集信息,可以参考以上用11g客户端导出12c数据库的例子。


Reference

How to Gather the Header Information and the Content of an Export Dumpfile ? (Doc ID 462488.1)

关键词:dmp expdp 

相关文章

aodu(At Oracle Database Utility)之dmpinfo
如何查看expdp/exp dmp文件信息
Unix/Windows平台数据泵备份常用脚本
Oracle DataPump

联系我们

Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com