如何从逻辑备份的dumpfile文件里获取头部详细信息?

在上一篇日志里,我们了解了 如何从逻辑备份的dumpfile文件里获取DDL脚本? 在本篇里,我们参照 MetaLink文档ID 462488.1】来了解如何从dumpfile里获取更为详细的头部信息。

所有的dumpfile文件都是包含大小为4KB的头部数据区,该区域记录dumpfile文件的详细信息。从Oracle 10.2.0.1.0开始,可以调用系统包DBMS_DATAPUMP.GET_DUMPFILE_INFO来获取dumpfile的头部信息。

1   参照 MetaLink文档ID 462488.1】我们可以创建一个SHOW_DUMPFILE_INFO存储过程,通过调用该过程可以获取dumpfile的详细信息,该过程的源代码如下:

CONNECT sys/manager as sysdba;

CREATE PROCEDURE show_dumpfile_info(
p_dir  VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir         = directory object where dumpfile can be found
-- p_file        = simple filename of export dumpfile (case-sensitive)
v_separator   VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path        all_directories.directory_path%type := '?';
v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp
v_fileversion VARCHAR2(15);           -- 0.1=10gR1 1.1=10gR2 2.1=11g
v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info
type valtype  IS VARRAY(22) OF VARCHAR2(2048);
var_values    valtype := valtype();
no_file_found EXCEPTION;
PRAGMA        exception_init(no_file_found, -39211);

BEGIN

-- Show generic info:
-- ==================

dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile.        Version: 19-MAR-2008');
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(22);
FOR i in 1 .. 22 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;

-- Show dumpfile 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_ENCPWD_MODE         CONSTANT NUMBER := 22;

-- 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;

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

IF v_filetype = 1 OR v_filetype = 2 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.: ' || var_values(5));
dbms_output.put_line('...Direct Path.....: ' || var_values(13));
dbms_output.put_line('...Export Version..: ' || var_values(15));
ELSIF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
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)',
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 (Dual)',
'5', '5 (Transparent)',
var_values(22)) INTO var_values(22) FROM dual;

dbms_output.put_line('...File Version....: ' || var_values(1));
dbms_output.put_line('...Master Present..: ' || var_values(2));
dbms_output.put_line('...GUID............: ' || var_values(3));
dbms_output.put_line('...File Number.....: ' || var_values(4));
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Creation Date...: ' || var_values(6));
dbms_output.put_line('...Flags...........: ' || var_values(7));
dbms_output.put_line('...Job Name........: ' || var_values(8));
dbms_output.put_line('...Platform........: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance........: ' || var_values(10));
END IF;
dbms_output.put_line('...Language........: ' || var_values(11));
dbms_output.put_line('...Block size......: ' || var_values(12));
dbms_output.put_line('...Metadata Compres: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed.: ' || var_values(18));
dbms_output.put_line('...Metadata Encrypt: ' || var_values(19));
dbms_output.put_line('...Data Encrypted..: ' || var_values(20));
dbms_output.put_line('...Column Encrypted: ' || var_values(21));
dbms_output.put_line('...Encrypt.pwd. mod: ' || var_values(22));
IF v_fileversion = '2.1' or v_fileversion = '3.1' THEN
dbms_output.put_line('...Master Piece Cnt: ' || var_values(16));
dbms_output.put_line('...Master Piece Num: ' || var_values(17));
END IF;
END IF;
IF v_fileversion >= '1.1' THEN
dbms_output.put_line('...Job Version.....: ' || var_values(15));
END IF;
dbms_output.put_line('...Max Items Code..: ' ||
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;
/

2  接下来,我们还以上篇博文里提到的使用EXPDP导出的dumpfile为例,其具体调用方法如下:

SQL> show user;
USER is "SYS"
SQL> select * from dba_directories where directory_name like 'MIG%';
OWNE DIRECTORY_NAME  DIRECTORY_PATH
---- --------------- --------------------------------------------------
SYS  MIG_DIR         /u01/app/oracle/mig_dir
SQL> !ls -l /u01/app/oracle/mig_dir
total 24360
-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp
-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log
-rw-r--r-- 1 oracle asmadmin   103892 Nov 21 17:23 hr_ddl.sql
-rw-r--r-- 1 oracle asmadmin     1672 Nov 21 17:23 impdp_hr.log
SQL> exec show_dumpfile_info(p_dir=>'mig_dir',p_file=>'expdp_hr.dmp');
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile.        Version: 19-MAR-2008
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.: expdp_hr.dmp
Directory: mig_dir
Disk Path: /u01/app/oracle/mig_dir
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...File Version....: 3.1 (Oracle11g Release 2: 11.2.0.x)
...Master Present..: 1 (Yes)
...GUID............: B23C2582426C247EE04010ACBF00579A
...File Number.....: 1
...Characterset ID.: 873 (AL32UTF8)
...Creation Date...: Mon Nov 21 17:19:48 2011
...Flags...........: 2
...Job Name........: "HR"."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx
...Instance........: rac1.localdomain:devdb1
...Language........: AL32UTF8
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Column Encrypted: 0 (No)
...Encrypt.pwd. mod: 2 (None)
...Master Piece Cnt: 1
...Master Piece Num: 1
...Job Version.....: 11.02.00.00.00
...Max Items Code..: 22
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL>

可以看到较为详细的信息,其中的Block size 4096表示dumpfile的header为4K。

3   如果,想把该过程的执行权限给普通用户:

GRANT execute ON show_dumpfile_info TO username;

4   如果,想要删除改过程:

DROP PROCEDURE show_dumpfile_info;

5  对于UNIX平台,我们还可以借助操作系统的命令来简单获取一些简单的信息:

rac1-> pwd
/home/oracle
rac1-> ls -l
total 23688
drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321
-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp
-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp
-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log
drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle
-rw-r--r--  1 oracle oinstall    28766 Nov 21 17:03 show_ddl.log
rac1-> cat exp_hr.dmp |head|strings
iEXPORT:V11.02.00
RUSERS
8192
Mon Nov 21 16:50:25 2011exp_hr.dmp
#G#G
rac1->

结合,上一篇,我们算是对如何从dumpfile里获取DDL脚本以及如何获取dumpfile的header deatail information有了一个初步认识。希望这两篇文章可以对网友在学习Oracle数据库逻辑备份带去帮助!

如何从逻辑备份的dumpfile文件里获取DDL脚本?

在数据库的管理工作中,难免会遇到使用Export/Import或者是EXPort Data Pump/IMPort Data Pump工具来执行逻辑备份、恢复的场景。在有些时候,我们可能会对产生的dumpfile的内容感兴趣。本文就这两种工具产生的逻辑备份文件分别描述如何获取数据定义语句(Data Definition Language)的脚本?

1 先来看,如何从传统的Export工具产生的dumpfile里获取DDL语句?

首先,导出HR用户的所有对象:

rac1-> id

uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)

rac1-> pwd

/home/oracle

rac1-> ls -l

total 24

drwxrwxr-x 17 root   root      4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall 15626 Aug 31 14:28 db.rsp

drwxr-x---  3 oracle oinstall  4096 Aug 31 15:03 oradiag_oracle

rac1-> exp hr/hr file=exp_hr.dmp log=exp_hr.log owner=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 16:50:22 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user HR

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user HR

About to export HR's objects ...

. exporting database links

. exporting sequence numbers

.....

.....

. exporting statistics

Export terminated successfully without warnings.

rac1-> ls -l

total 23656

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

rac1->

我们已经顺利导出了一份exp_hr.dmp的dumpfile,那么该如何从该文件里获取DDL语句呢?

我们可以利用传统的导入工具Import附带SHOW=y选项来获取:

rac1-> imp hr/hr file=exp_hr.dmp log=show_ddl.log  show=y

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:03:26 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

...

...

Import terminated successfully without warnings.

rac1->

rac1-> ls -l

total 23688

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

-rw-r--r--  1 oracle oinstall    28766 Nov 21 17:03 show_ddl.log

rac1-> head -30 show_ddl.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"

"CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41155490');"

"COMMIT; END;"

"CREATE TYPE "MY_TEST_TYPE" TIMESTAMP '2011-10-12:09:59:12' OID 'AF115DDE620"

"59E4AE04010ACBF000672'   as object(id number,name varchar2(10));"

"CREATE SEQUENCE "LOCATIONS_SEQ" MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 S"

"TART WITH 3300 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "DEPARTMENTS_SEQ" MINVALUE 1 MAXVALUE 9990 INCREMENT BY 10 "

"START WITH 280 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "EMPLOYEES_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999"

"99999 INCREMENT BY 1 START WITH 207 NOCACHE NOORDER NOCYCLE"

"CREATE CLUSTER "DEPT_EMP_CLUSTER" ("DEPARTMENT_ID" NUMBER(4, 0))  PCTFREE 1"

"0 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 262144 NEXT 1048576 MI"

"NEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"

"SERS""

"CREATE INDEX "IDX_DEPT_EMP_CLUSTER" ON CLUSTER "DEPT_EMP_CLUSTER" PCTFREE 1"

"0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 F"

"REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS""

"CREATE TABLE "BIGFILE_TAB" ("ID" NUMBER, "NAME" VARCHAR2(10))  PCTFREE 10 P"

"CTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXT"

"ENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BIG_T"

rac1->

从上,我们可以看到,我们在IMPORT命令行上带上SHOW=Y的选项时,IMPORT工具并没有真正的执行数据导入,而是生成了一份含有DDL语句的日志文件,我们可以直接编辑该日志文件,以获取我们所需的脚本。

2 那么,对于EXPDP产生的dumpfile,又该如何获取DDL语句呢?

先以EXPDP导出dumpfile:

rac1-> pwd

/u01/app/oracle/mig_dir

rac1-> ls -l

total 0

rac1-> expdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=expdp_hr.log schemas=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 17:16:45 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

...

Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:19:48

rac1-> ls -l

total 24248

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

rac1->

该如何从expdp_hr.dmp文件里获取DDL语句呢?IMPDP工具给我们提供了SQLFILE的命令行选项,同样是只获取DDL语句,并未真正的执行数据导入。

rac1-> impdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=impdp_hr.log schemas=hr sqlfile=hr_ddl.sql

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:23:23 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

...

...

Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 17:23:47

rac1-> ls -l

total 24360

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

-rw-r--r-- 1 oracle asmadmin   103892 Nov 21 17:23 hr_ddl.sql

-rw-r--r-- 1 oracle asmadmin     1672 Nov 21 17:23 impdp_hr.log

rac1-> head -30 hr_ddl.sql

-- CONNECT HR

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: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41161309');

COMMIT;

END;

/

-- new object type path: SCHEMA_EXPORT/TYPE/TYPE_SPEC

CREATE TYPE "HR"."MY_TEST_TYPE"

OID 'AF115DDE62059E4AE04010ACBF000672' as object(id number,name varchar2(10));

/

ALTER TYPE "HR"."MY_TEST_TYPE"

COMPILE SPECIFICATION

PLSQL_OPTIMIZE_LEVEL=  2

PLSQL_CODE_TYPE=  INTERPRETED

PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

/

-- new object type path: SCHEMA_EXPORT/SEQUENCE/SEQUENCE

CREATE SEQUENCE  "HR"."LOCATIONS_SEQ"  MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 START WITH 3300 NOCACHE  NOORDER  NOCYCLE ;

rac1->

最后,简单一句话总结:

对于使用传统工具EXPORT导出的dumpfile,我们可以使用IMPORT加上SHOW=Y的选项获取DDL

对于使用数据泵EXPDP导出的dumpfile,我们可以使用IMPDP加上SQLFILE的选项获取DDL

ORA-39142 IMPDP跨版本导入数据报错解决

前几天,公司某项目组搭建一套演示应用系统,需要将后台的Oracle数据从一台服务器迁移到另外一套系统。在执行IMPDP导入的过程遭遇下述错误,后经查找Metalink给出解决方法。

-bash-3.2$ impdp directory=mig_dir dumpfile=dms_expdp.dmp logfile=impdp.log schemas=dms
Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 10 November, 2011 3:12:56
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: sys/oracle as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/mig_dir/dms_expdp.dmp"
[oracle@OEL511gR2 mig_dir]$ oerr ora 39142
39142, 00000, "incompatible version number %s in dump file \"%s\""
// *Cause:  A dump file was specified for an import operation whose version
//          number is incompatible with the dump file version of the
//          Data Pump product currently running on the system. Usually this
//          message indicates that the dump file was produced by a newer
//          version of the Data Pump export utility.
// *Action: Import this dump file using the Data Pump import utility with
//          the same version as the export which created the file.
[oracle@OEL511gR2 mig_dir]$

ORA-39142的错误是指要导入的dump file中记录的数据库版本与要导入的数据库版本不一致。果不其然,这份导出的dump file是其他同事从一套11.1.0.6.0数据库上EXPDP 导出的,而这套要导入演示系统的数据库版本是10.2.0.5.0。将11g数据库中导出的dump file导入到10g数据库的过程中,就报这个错误了,Metalink ID 553337.1给出了详细的解决方法。

我这里的解决方法是在源库,即11g的数据库上重新以EXPDP加上version=10.2.0.5.0参数重新执行导出,然后在目标库上重新执行上述导入命令,OK!

需要注意的是,经过测试:直接在目标库(10.2.0.5.0)上以IMPDP附带version=11.1.0.6.0参数导入第一份dump file文件是行不通的,依旧会报上述ORA-39142的错!
那么,dump file里包含的数据库版本信息及其它详细信息应该怎么获取呢?我们将在下一篇介绍。