How expdp/impdp uses the version parameter to migrate data across versions 03/29 Update SLTechnology News&Howtos

How expdp/impdp uses the version parameter to migrate data across versions

2025-03-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you how expdp/impdp uses version parameters for cross-version data migration, I believe most people do not know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it together.

First, problem description and analysis:

There is a problem, the requirement is to export the data of Oracle 11g version with expdp data pump, and then import the data into Oracle 10 version, is it possible? It's a good question. You can't talk nonsense until you've done it. First of all, let's guess, I think it is possible for the low version to import the high version, and not necessarily for the high version to import the low version, but let's think about oracle's ability to import the contents of 11g libraries into the lower version. The answer is in the experiment, and then we will do this experiment immediately to verify it.

2. Experiment

Low version-> high version environment: low version 10g (10.2.0.1.0) high version 11g (11.2.0.1.0) guess: can be concluded: yes

(1) create a test user sam in 10g environment and grant permissions

[oracle@test ~] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Tue Jan 5 19:06:25 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

SYS@OCM10G > select username from dba_users where username='SAM'

No rows selected

SYS@OCM10G > create user sam identified by sam

User created.

SYS@OCM10G > grant connect,resource to sam

Grant succeeded.

(2) create test table test and data

SYS@OCM10G > conn sam/sam

Connected.

SAM@OCM10G > create table test (id int,name varchar2 (10))

Table created.

SAM@OCM10G > insert into test values (1)

1 row created.

SAM@OCM10G > commit

Commit complete.

SAM@OCM10G > select * from test

ID NAME

1 sam

(3) create the directory used when exporting data and assign (read / write) permission

SAM@OCM10G > conn / as sysdba

Connected.

SYS@OCM10G > create directory test as'/ home/oracle'

Directory created.

SYS@OCM10G > grant write,read on directory test to sam

Grant succeeded.

(4) expdp exports sam user data in 10g database

[oracle@test ~] $ls-l test.dmp

Ls: cannot access test.dmp: No such file or directory

[oracle@test ~] $expdp sam/sam@ocm10g directory=test dumpfile=test.dmp

Export: Release 10.2.0.1.0-64bit Production on Friday, 08 January, 2016 10:32:37

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "SAM". "SYS_EXPORT_SCHEMA_01": sam/*@ocm10g directory=test dumpfile=test.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/COMMENT

. . Exported "SAM". "TEST" 5.234 KB 1 rows

Master table "SAM". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Dump file set for SAM.SYS_EXPORT_SCHEMA_01 is:

/ home/oracle/test.dmp

Job "SAM". "SYS_EXPORT_SCHEMA_01" successfully completed at 10:32:44

[oracle@test ~] $ls-l test.dmp

-rw-r- 1 oracle oinstall 143360 Jan 8 10:32 test.dmp

(5) create 11g environment, import data using directory, and create SAM users

[oracle@test ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 10:34:48 2016

Connected to:

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

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

SYS@OCM11G > select username from dba_users where username='SAM'

No rows selected

SYS@OCM11G > create user sam identified by sam

User created.

SYS@OCM11G > grant connect,resource to sam

Grant succeeded.

(6) Import test.dmp file into 11g database

[oracle@test ~] $impdp system/oracle@ocm11g directory=test dumpfile=test.dmp

Import: Release 11.2.0.1.0-Production on Fri Jan 8 11:03:37 2016

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

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

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

Starting "SYSTEM". "SYS_IMPORT_FULL_01": system/*@ocm11g directory=test dumpfile=test.dmp

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "SAM". "TEST" 5.234 KB 1 rows

Job "SYSTEM". "SYS_IMPORT_FULL_01" successfully completed at 11:03:39

(7) check users and import data

[oracle@test ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 11:03:54 2016

Connected to:

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

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

SYS@OCM11G >

SYS@OCM11G > conn sam/sam

Connected.

SAM@OCM11G > select * from test

ID NAME

1 sam

High version-> low version environment: high version 11g (11.2.0.1.0) low version 10g (10.2.0.1.0) guess: inconclusive: yes, use the version parameter

(1) create a test user suzzy in the 10g library and grant permissions

[oracle@test ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 11:14:37 2016

Connected to:

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

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

SYS@OCM11G > select username from dba_users where username='SUZZY'

No rows selected

SYS@OCM11G > create user suzzy identified by suzzy

User created.

SYS@OCM11G > grant connect,resource to suzzy

Grant succeeded.

(2) create test table test1 and data

SYS@OCM11G > conn suzzy/suzzy

Connected.

SUZZY@OCM11G > create table test1 (id int,name varchar2 (10))

Table created.

SUZZY@OCM11G > insert into test1 values (1)

1 row created.

SUZZY@OCM11G > commit

Commit complete.

SUZZY@OCM11G > select * from test1

ID NAME

1 suzzy

(3) Grant user suzzy read and write permissions to the exported directory test

SYS@OCM11G > grant write,read on directory test to suzzy

Grant succeeded.

(4) Export suzzy users and data in 11g database

[oracle@test ~] $expdp suzzy/suzzy@ocm11g directory=test dumpfile=test1.dmp

Export: Release 11.2.0.1.0-Production on Fri Jan 8 11:29:14 2016

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

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

Starting "SUZZY". "SYS_EXPORT_SCHEMA_01": suzzy/*@ocm11g directory=test dumpfile=test1.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/COMMENT

. . Exported "SUZZY". "TEST1" 5.429 KB 1 rows

Master table "SUZZY". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Dump file set for SUZZY.SYS_EXPORT_SCHEMA_01 is:

/ home/oracle/test1.dmp

Job "SUZZY". "SYS_EXPORT_SCHEMA_01" successfully completed at 11:29:29

(5) create users in 10g database and grant permissions, including directory used during import

SYS@OCM10G > select username from dba_users where username='SUZZY'

No rows selected

SYS@OCM10G > create user suzzy identified by suzzy

User created.

SYS@OCM10G > grant connect,resource to suzzy

Grant succeeded.

SYS@OCM10G > grant read,write on directory test to suzzy

Grant succeeded.

(6) when the test1.dmp file is imported into 10g database, an error will be reported, because the version does not meet the requirements, so you need to use the version parameter in expdp. For introduction to the parameters, please see the article (3)

[oracle@test ~] $impdp suzzy/suzzy@ocm10g directory=test dumpfile=test1.dmp

Import: Release 10.2.0.1.0-64bit Production on Friday, 08 January, 2016 11:35:06

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39142: incompatible version number 3.1 in dump file "/ home/oracle/test1.dmp"

Translation:

ORA-39001: invalid parameter value

ORA-39000: dump file description error

ORA-39142: version number 3.1 (in dump file "/ home/oracle/test1.dmp") is not compatible

(7) add version parameter and re-export dump file from 11g library.

[oracle@test ~] $expdp suzzy/suzzy@ocm11g directory=test dumpfile=test2.dmp version=10.2.0.1.0

Export: Release 11.2.0.1.0-Production on Fri Jan 8 14:01:34 2016

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

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

Starting "SUZZY". "SYS_EXPORT_SCHEMA_01": suzzy/*@ocm11g directory=test dumpfile=test2.dmp 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: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/COMMENT

. . Exported "SUZZY". "TEST1" 5.304 KB 1 rows

Master table "SUZZY". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Dump file set for SUZZY.SYS_EXPORT_SCHEMA_01 is:

/ home/oracle/test2.dmp

Job "SUZZY". "SYS_EXPORT_SCHEMA_01" successfully completed at 14:01:50

(8) Import the test2.dmp file exported with parameter version into 10g database

[oracle@test ~] $impdp suzzy/suzzy@ocm10g directory=test dumpfile=test2.dmp

Import: Release 10.2.0.1.0-64bit Production on Friday, 08 January, 2016 14:03:47

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

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

Starting "SUZZY". "SYS_IMPORT_FULL_01": suzzy/*@ocm10g directory=test dumpfile=test2.dmp

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "SUZZY". "TEST1" 5.304 KB 1 rows

Job "SUZZY". "SYS_IMPORT_FULL_01" successfully completed at 14:03:48

(9) validate users and data

[oracle@test ~] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Fri Jan 8 14:04:27 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

SYS@OCM10G > conn suzzy/suzzy

Connected.

SUZZY@OCM10G > select * from test1

ID NAME

1 suzzy

3. Description of version parameters

(1) official documents:

VERSION

Default: COMPATIBLE

Purpose

Specifies the version of database objects to be exported (that is, only database objects and attributes that are compatible with the specified release will be exported). This can be used to create a dump file set that is compatible with a previous release of Oracle Database. Note that this does not mean that Data Pump Export can be used with releases of Oracle Database prior to Oracle Database 10g release 1. Data Pump Export only works with Oracle Database 10g release 1,10.1 or later. The VERSION parameter simply allows you to identify the version of the objects being exported.

Syntax and Description

VERSION= [COMPATIBLE | LATEST | version_string]

The legal values for the VERSION parameter are as follows:

COMPATIBLE-This is the default value. The version of the metadata corresponds to the database compatibility level. Database compatibility must be set to 9.2 or higher.

LATEST-The version of the metadata corresponds to the database release.

Version_string-A specific database release (for example, 11.2.0). In Oracle Database 11g, this value cannot be lower than 9.2.

Database objects or attributes that are incompatible with the specified release will not be exported. For example, tables containing new datatypes that are not supported in the specified release will not be exported.

Restrictions

Exporting a table with archived LOBs to a database release earlier than 11.2 is not allowed.

If the Data Pump Export VERSION parameter is specified along with the TRANSPORT_TABLESPACES parameter, then the value must be equal to or greater than the Oracle Database COMPATIBLE initialization parameter.

(2) description

According to the official documents, we can see that there are three options for version parameters, which are compatible, latest and version_string. The default option is compatible. This option is that the exported metadata is consistent with the version compatibility of the database. For example, if the database compatibility I exported is 11.2.0.0.0, then it is 11.2.0.0.0 when exported, which is easy to understand. The latest option is the highest version that is consistent with the database version, which is rarely used. Version_string is a string that specifies a specific database version. This is very common. When you know your database version, you can directly specify the export compatible version. Note that in the 11g version, the specified version cannot be less than 9.2.

In addition, database objects and properties should also be supported for this version, otherwise they will not be exported, for example, tables containing new data types will not be exported.

Restrictions:

Prior to version 11.2, it is not allowed to export archived LOBs tables (literally, I did some consultation in the group, and there was no final result, but there were still some gains. Tables containing securefile lob exported in 11g will be lost when imported into 10g, see 4)

If the data pump version parameter is specified with the TRANSPORT_TABLESPACES, then the version value must be equal to or greater than the Oracle database initialization parameter compatible.

IV. About securefile lob field export and import test

Create a test table

11g environment:

[oracle@test ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 13 18:58:37 2016

Connected to:

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

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

SYS@OCM11G > show parameter db_securefile

NAME TYPE VALUE

Db_securefile string PERMITTED

SYS@OCM11G > conn sam/sam

Connected.

SAM@OCM11G > create table T1 (a clob) lob (a) store as securefile (compress low cache nologging)

Table created.

SAM@OCM11G > insert into T1 select rpad ('axiaojimiyun') str from dual connect by rownumcommit

Commit complete.

Export data

[oracle@test ~] $expdp sam/sam@ocm11g directory=test dumpfile=test_lob8.dmp version=10.2.0.1.0

Export: Release 11.2.0.1.0-Production on Wed Jan 13 19:10:42 2016

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

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

Starting "SAM". "SYS_EXPORT_SCHEMA_01": sam/*@ocm11g directory=test dumpfile=test_lob8.dmp 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: 576 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . Exported "SAM". "T1" 83.51 KB 11 rows

. . Exported "SAM". "T_CLOB" 83.54 KB 10 rows

. . Exported "SAM". "TEST_LOB" 5.382 KB 1 rows

. . Exported "SAM". "TEST" 5.296 KB 1 rows

Master table "SAM". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Dump file set for SAM.SYS_EXPORT_SCHEMA_01 is:

/ home/oracle/test_lob8.dmp

Job "SAM". "SYS_EXPORT_SCHEMA_01" successfully completed at 19:10:58

Import 10g environment

[oracle@test ~] $impdp sam/sam@ocm10g directory=test dumpfile=test_lob8.dmp

Import: Release 10.2.0.1.0-64bit Production on Wednesday, 13 January, 2016 19:13:15

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

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

Starting "SAM". "SYS_IMPORT_FULL_01": sam/*@ocm10g directory=test dumpfile=test_lob8.dmp

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "SAM". "T1" 83.51 KB 11 rows

. . Imported "SAM". "T_CLOB" 83.54 KB 10 rows

. . Imported "SAM". "TEST_LOB" 5.382 KB 1 rows

. . Imported "SAM". "TEST" 5.296 KB 1 rows

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SAM". "SYS_IMPORT_FULL_01" successfully completed at 19:13:16

Comparison of the structure of T1 table in 10g par 11g library

(11g)

SYS@OCM11G > select dbms_metadata.get_ddl ('TABLE','T1','SAM') from dual

DBMS_METADATA.GET_DDL ('TABLE','T1','SAM')

CREATE TABLE "SAM". "T1"

("A" CLOB

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

LOB ("A") STORE AS SECUREFILE (

TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192

CACHE NOLOGGING COMPRESS LOW KEEP_DUPLICATES

STORAGE (INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

))

(10g)

SYS@OCM10G > select dbms_metadata.get_ddl ('TABLE','T1','SAM') from dual

DBMS_METADATA.GET_DDL ('TABLE','T1','SAM')

CREATE TABLE "SAM". "T1"

("A" CLOB

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "USERS"

LOB ("A") STORE AS (

TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0

CACHE

STORAGE (INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

These are all the contents of the article "how expdp/impdp uses version parameters to migrate data across versions". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.