每个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数据库的例子。
How to Gather the Header Information and the Content of an Export Dumpfile ? (Doc ID 462488.1)