Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to use version parameters to solve the differences in the version of Oracle data pump. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

When we review the development of Oracle product tools, we can't help but recognize the success of Exp/Imp. From the early version to the present 11g Magi Expax Imp is the most commonly used and easiest database backup tool for developers.

However, with the continuous development of Oracle products and the quantification of data in the real environment, Exp/Imp can no longer meet the actual needs. Oracle officially launched the data pump (Data Pump), that is, the Expdp/Impdp product, in the 10g version. In many ways, Data Pump addresses or alleviates some of the shortcomings of Exp/Imp.

1. Version trap

Friends who often use Exp/Imp may have the experience that when exporting data to a local machine and then importing it to a new server, they often report a version error. Different database server versions and different client versions will affect us to export data dump files and affect the success of our import operation.

There are two essential reasons for this: one is that Exp/Imp is a client-side tool, and the tool program resides on the client-side. Different versions of the client will inevitably bring different Exp/Imp operations, and there must be some differences in the Dump files written. This tests the compatibility of client programs.

Another reason is the version compatibility of the database server. With the new features and properties introduced by Oracle in each version, it is difficult for us to require a lower version of the server to support it. Oracle can be backward compatible, but it cannot "predict the future".

Therefore, in the process of using Exp/Imp, we often discuss two problems, one is character set matching, and the other is version export strategy.

In the process of using Data Pump, there will also be version differences. However, the architectural characteristics of the Data Pump version determine that the version differences will be resolved to a certain extent.

Unlike the Exp/Imp tool, Data Pump is a server-side tool that runs only on the database server side. This makes Data Pump unaffected by the client version. At the same time, Oracle took into account the impact of version differences, but also made a lot of additional configuration for Data Pump. Among them, the version parameter is the solution to the problem.

2. Introduction of experimental environment.

Data Pump is launched from 10g, so the difference between the largest versions of Oracle is between 10g and 11g. The author chooses two environments for testing.

The 10g environment is the Oracle Windows version that creates Directory objects.

SQL > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

SQL > create directory verdmp as'd:\ verdmp'

Directory created

The 11g environment uses the Linux version.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

-- create Directory object

[root@oracle11g /] # cd / verdmp/

[root@oracle11g verdmp] # pwd

/ verdmp

SQL > create directory verdmp as'/ verdmp'

Directory created.

3. From 10g to 11g

First, let's test the situation from 10g to 11g. If we export the Dump file from 10g and import it into 11g, is there any problem?

Let's first export the dmp file on 10g.

C:\ Documents and Settings\ Administrator > expdp\ "/ as sysdba\" directory=verdmp dumpfile=scottdmp.dmp schemas=scott

Export: Release 10.2.0.1.0-Production on Saturday, 24 November, 2012 16:08:43

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Start "SYS". "SYS_EXPORT_SCHEMA_01": "/ * AS SYSDBA" directory=verdmp dumpf

Ile=scottdmp.dmp schemas=scott

Estimation is being made using the BLOCKS method.

(for reasons of space, there are omissions. )

. . The line "SCOTT". "BONUS" 0 KB 0 is exported

The primary table "SYS". "SYS_EXPORT_SCHEMA_01" has been successfully loaded / unloaded

*

The dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

D:\ VERDMP\ SCOTTDMP.DMP

Assignment "SYS". "SYS_EXPORT_SCHEMA_01" completed successfully at 16:20:03

The exported file exists in the directory.

D:\ verdmp directory

2012-11-24 16:08

2012-11-24 16:08..

2012-11-24 16:20 2360 export.log

2012-11-24 16:20 69062656 SCOTTDMP.DMP

2 files 69065016 bytes

2 directories 36759314432 available bytes

Exported successfully and transferred to Linux 11g environment using ftp.

[root@oracle11g verdmp] # ls-l

Total 67520

-rw-r--r-- 1 root root 2360 Nov 24 08:14 export.log

-rw-r--r-- 1 root root 69062656 Nov 24 08:14 SCOTTDMP.DMP

[oracle@oracle11g ~] $impdp userid=\'/ as sysdba\ 'directory=verdmp dumpfile=SCOTTDMP.DMP remap_schema=scott:test

Import: Release 11.2.0.1.0-Production on Sat Nov 24 08:17:15 2012

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS". "SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS". "SYS_IMPORT_FULL_01": userid= "/ * AS SYSDBA" directory=verdmp dumpfile=SCOTTDMP.DMP remap_schema=scott:test

(for reasons of space, there are omissions. )

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS". "SYS_IMPORT_FULL_01" completed with 2 error (s) at 08:17:39

Import succeeded. We have come to the conclusion that when using Data Pump, dmp files exported from a lower version can be imported to a higher version of the database server. No additional configuration is required for this process.

4. 11g to 10g

Let's try to move from 11g to 10g.

[oracle@oracle11g ~] $expdp userid=\'/ as sysdba\ 'directory=verdmp dumpfile=testdmp.dmp schemas=test reuse_dumpfiles=y

Export: Release 11.2.0.1.0-Production on Sat Nov 24 08:24:01 2012

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS". "SYS_EXPORT_SCHEMA_01": userid= "/ * AS SYSDBA" directory=verdmp dumpfile=testdmp.dmp schemas=test reuse_dumpfiles=y

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 85.25 MB

(for reasons of space, there are omissions. )

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

/ verdmp/testdmp.dmp

Job "SYS". "SYS_EXPORT_SCHEMA_01" successfully completed at 08:24:43

Export the file successfully, transfer the dmp file to 10g environment to import.

D:\ verdmp > dir

The volume in drive D has no label.

The serial number of the volume is 2294-1384

D:\ verdmp directory

2012-11-24 17:00

2012-11-24 17:00..

2012-11-24 16:20 2360 export.log

2012-11-24 16:20 69062656 SCOTTDMP.DMP

2012-11-24 17:00 69103616 testdmp.dmp

3 files 138168632 bytes

2 directories 36689879040 available bytes

-- Import Fil

D:\ verdmp > impdp\ "/ as sysdba\" directory=verdmp dumpfile=testdmp.dmp

Import: Release 10.2.0.1.0-Production on Saturday, 24 November, 2012 17:02:19

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

ORA-39001: invalid parameter value

ORA-39000: dump file description error

ORA-39142: version number 3.1 (in the dump file "D:\ verdmp\ testdmp.dmp") is not compatible

An error is reported, which is obviously a version error. The solution to the problem is to use the version parameter to restrict the format of the dmp file when exporting expdp. In the official documentation, the version parameters are as follows:

VERSION

Version of objects to export.

Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

We re-export the file.

[oracle@oracle11g ~] $expdp userid=\'/ as sysdba\ 'directory=verdmp dumpfile=testdmp_10g.dmp schemas=test reuse_dumpfiles=y version=10.2.0.1.0

Export: Release 11.2.0.1.0-Production on Sat Nov 24 08:32:34 2012

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS". "SYS_EXPORT_SCHEMA_01": userid= "/ * AS SYSDBA" directory=verdmp dumpfile=testdmp_10g.dmp schemas=test reuse_dumpfiles=y version=10.2.0.1.0

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 85.25 MB

(for reasons of space, there are omissions. )

*

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

/ verdmp/testdmp_10g.dmp

Job "SYS". "SYS_EXPORT_SCHEMA_01" successfully completed at 08:33:10

Re-import the file.

D:\ verdmp > dir

The volume in drive D has no label.

The serial number of the volume is 2294-1384

D:\ verdmp directory

2012-11-24 17:13

2012-11-24 17:13..

2012-11-24 16:20 2360 export.log

2012-11-24 17:02 378 import.log

2012-11-24 16:20 69062656 SCOTTDMP.DMP

2012-11-24 17:00 69103616 testdmp.dmp

2012-11-24 17:13 69087232 testdmp_10g.dmp

5 files 207256242 bytes

2 directories 36620615680 available bytes

-re-import

D:\ verdmp > impdp\ "/ as sysdba\" directory=verdmp dumpfile=testdmp_10g.dmp remap_tablespace=test:tptest

Import: Release 10.2.0.1.0-Production on Saturday, 24 November, 2012 17:16:52

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

The primary table "SYS". "SYS_IMPORT_FULL_02" has been successfully loaded / unloaded

Start "SYS". "SYS_IMPORT_FULL_02": "/ * AS SYSDBA" directory=verdmp dumpfil

E=testdmp_10g.dmp remap_tablespace=test:tptest

Dealing with object type SCHEMA_EXPORT/USER

Dealing with object type SCHEMA_EXPORT/SYSTEM_GRANT

Dealing with object type SCHEMA_EXPORT/ROLE_GRANT

Dealing with object type SCHEMA_EXPORT/DEFAULT_ROLE

Dealing with object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Dealing with object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

ORA-39083: creation of object type TYPE failed with an error:

ORA-02304: invalid object identifier text

The failed sql is:

CREATE TYPE "TEST". "T_INFO" OID '5F88C5F6877C43468A5C2CE5F53CEA48' is object (

Id number)

ORA-39083: creation of object type TYPE failed with an error:

ORA-02304: invalid object identifier text

The failed sql is:

CREATE TYPE "TEST". "T_INFOLIST" OID '49E54F26C4CC4134B65C15C1D91026FC' is tabl

E of t_info

Dealing with object type SCHEMA_EXPORT/DB_LINK

Dealing with object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

(for reasons of space, there are omissions. )

Dealing with object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ORA-39082: object type ALTER_PROCEDURE: "TEST". "P_TEST_NC" has been created with compilation warnings

Dealing with object type SCHEMA_EXPORT/VIEW/VIEW

ORA-39082: object type VIEW: "TEST". "V_T1" has been created with compilation warnings

Dealing with object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

The assignment "SYS". "SYS_IMPORT_FULL_02" has been completed with 4 errors (completed at 17:18:11)

There are errors, but the problem with the imported version no longer exists. The author has solved this problem before, see (http://space.itpub.net/17203031/viewspace-732501) for details.

We use the transform parameter to solve this problem.

D:\ verdmp > impdp\ "/ as sysdba\" directory=verdmp dumpfile=testdmp_10g.dmp remap_tablespace=test:tptest transform=oid:n

Import: Release 10.2.0.1.0-Production on Saturday, 24 November, 2012 17:20:30

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

The primary table "SYS". "SYS_IMPORT_FULL_02" has been successfully loaded / unloaded

Start "SYS". "SYS_IMPORT_FULL_02": "/ * AS SYSDBA" directory=verdmp dumpfile=testdmp_10g.dmp remap_tablespace=test:tptest transform=oid:n

(for reasons of space, there are omissions. )

Dealing with object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

The assignment "SYS". "SYS_IMPORT_FULL_02" has completed with 2 errors (completed at 17:20:41)

Conclusion: there will be version conflicts when exporting from a high-version database server and trying to import to a lower-version database server. The solution in the process of exporting expdp, use the version parameter to specify the version number.

Note that the impdp program also has a version parameter. But it does not work to resolve the conflict. Impdp's version is used to generate compatibility SQL statements.

VERSION

Version of objects to import.

Valid keywords are: [COMPATIBLE], LATEST or any valid database version.

Only valid for NETWORK_LINK and SQLFILE.

Even if we use version in impdp, it will not be compatible.

D:\ verdmp > impdp\ "/ as sysdba\" directory=verdmp dumpfile=testdmp.dmp remap_tablespace=test:tptest transform=oid:n version=10.2.0.1.0

Import: Release 10.2.0.1.0-Production on Saturday, 24 November, 2012 17:24:33

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

ORA-39001: invalid parameter value

ORA-39000: dump file description error

ORA-39142: version number 3.1 (in the dump file "D:\ verdmp\ testdmp.dmp") is not compatible

Version issues are much easier for Oracle Data Pump than for exp/imp. At present, the versions of Data Pump are only 10g and 11g. It is believed that with the continuous evolution of the version, there will be more and more scenarios of data pump migration across versions.

After reading the above, do you have any further understanding of how to use the version parameter to resolve the version difference of the Oracle data pump? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.