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

This article introduces the relevant knowledge of "what is the method of exporting and importing Oracle data pump Data Pump". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Oracle data pump Data Pump export import

Exp/Imp is an early data logical backup and restore tool introduced by Oracle, which is easy to use and powerful. However, Exp/Imp does not support some new features of Oracle very well, and the backup and restore speed of large amounts of data still can not meet the requirements. So starting from 10g, Oracle launched the data pump (Data Pump) as an upgraded alternative version of Exp/Imp.

TTS metadata can also be exported using Data Pump. Next we will do the experiment again using Data Pump. Note: the tablespace Read Only configuration and related check steps are skipped.

Data Pump is a server-side tool, and you need to define directory objects if you use it.

SQL > create or replace directory TTSDMP

2 as'/ transtts'

Directory created

SQL > grant read, write on directory ttsdmp to public

Grant succeeded

Export the metadata dmp file.

[oracle@bsplinux transtts] $expdp userid=\'/ as sysdba\ 'transport_tablespaces=ttsind,ttstbl dumpfile=ttsdmp.dmp directory=ttsdmp transport_full_check=y

Export: Release 11.2.0.1.0-Production on Mon Nov 19 20:06:33 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_TRANSPORTABLE_01": userid= "/ * AS SYSDBA" transport_tablespaces=ttsind,ttstbl dumpfile=ttsdmp.dmp directory=ttsdmp transport_full_check=y

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

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

*

Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:

/ transtts/ttsdmp.dmp

*

Datafiles required for transportable tablespace TTSIND:

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf

Datafiles required for transportable tablespace TTSTBL:

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf

Job "SYS". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:08:02

Copy the data file.

[oracle@bsplinux datafile] $cp / u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf / transtts

Cp / u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf / transtts

[oracle@yjz69_.dbf / transttscp / u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bm

[oracle@bsplinux transtts] $pwd

/ transtts

[oracle@bsplinux transtts] $ls-l

Total 30888

-rw-r--r-- 1 oracle oinstall 1402 Nov 19 20:08 export.log

-rw-r- 1 oracle oinstall 10493952 Nov 19 20:09 o1_mf_ttsind_8bmyjz69_.dbf

-rw-r- 1 oracle oinstall 20979712 Nov 19 20:09 o1_mf_ttstbl_8bmyjf3w_.dbf

-rw-r- 1 oracle oinstall 106496 Nov 19 20:08 ttsdmp.dmp

The documents in the red section are necessary for TTS.

Description: because we are testing in the same environment, we delete the tablespace.

SQL > drop tablespace ttstbl including contents and datafiles

Tablespace dropped

SQL > drop tablespace ttsind including contents and datafiles

Tablespace dropped

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name like 'TTS%'

FILE_NAME TABLESPACE_NAME

-

Tablespace deleted successfully. Let's do the actual restore process.

[oracle@bsplinux transtts] $cp o1_mf_ttsind_8bmyjz69_.dbf / u01/app/oradata/ORA11G/datafile/

[oracle@bsplinux transtts] $cp o1_mf_ttstbl_8bmyjf3w_.dbf / u01/app/oradata/ORA11G/datafile/

[oracle@bsplinux datafile] $ls-l | grep tts

-rw-r- 1 oracle oinstall 10493952 Nov 19 20:15 o1_mf_ttsind_8bmyjz69_.dbf

-rw-r- 1 oracle oinstall 20979712 Nov 19 20:15 o1_mf_ttstbl_8bmyjf3w_.dbf

Use the impdp tool to import metadata information.

[oracle@bsplinux transtts] $impdp userid=\'/ as sysdba\ 'directory=ttsdmp dumpfile=ttsdmp.dmp transport_datafiles='/u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf','/u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf'

Import: Release 11.2.0.1.0-Production on Mon Nov 19 20:18:41 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

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

Starting "SYS". "SYS_IMPORT_TRANSPORTABLE_01": userid= "/ * AS SYSDBA" directory=ttsdmp dumpfile=ttsdmp.dmp transport_datafiles=/u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf,/u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 20:18:56

At this point, the tablespace has been imported back, and the corresponding data table can be restored.

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name like 'TTS%'

FILE_NAME TABLESPACE_NAME

-

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf TTSIND

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf TTSTBL

-- switch back to test user

SQL > select count (*) from test.t

COUNT (*)

-

72348

Note: whether using exp/imp or Data Pump, the owner corresponding to the imported object must exist and have the same name. If no owner object exists, the user is not automatically recreated even if Data Pump is used.

7. Transplant TTS on different platforms

Porting the same platform type has always been a prerequisite for the use of TTS, and it is also an insurmountable shield. This puts some restrictions on the use of TTS. For example, we cannot migrate from an AIX platform to an open Linux platform. So, in versions above 10g, we can break this limit.

Let's try to migrate a tablespace from the AIX platform to the Linux platform.

On the AIX platform, we use it as a source database.

SQL > select * from v$version

BANNER

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

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

SQL > select count (*), sum (bytes) / 1024 from dba_segments where tablespace_name='NBSPOC'

COUNT (*) SUM (BYTES) / 1024 take 1024

--

174 286

SQL > select file_name, tablespace_name from dba_data_files where tablespace_name='NBSPOC'

FILE_NAME TABLESPACE_NAME

-

/ nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf NBSPOC

The NBSPOC tablespace includes 174 objects, about 286m of data. Let's set up the tablespace Read Only and platform check.

SQL > alter tablespace nbspoc read only

Tablespace altered

SQL > select PLATFORM_NAME from v$database

PLATFORM_NAME

-

AIX-Based Systems (64-bit)

From the v$transportable_platform view, we can see that AIX-Based Systems (64-bit) is of type Big. The Target target Linux is of type Little. So, we are doing cross-platform migration.

Note: cross-platform migration also needs to meet the same requirements of the character set.

We plan to use expdp for metadata export, so configure the directory object.

Oracle:/ftptemp > cd trans

Oracle:/ftptemp/trans > ls-l

Total 0

Oracle:/ftptemp/trans > pwd

/ ftptemp/trans

Oracle:/ftptemp/trans >

SQL > create directory dmpdir as'/ ftptemp/trans'

Directory created

Check the dependency of the tablespace NBSPOC.

SQL > exec dbms_tts.transport_set_check ('NBSPOC',true)

PL/SQL procedure successfully completed

SQL > select * from transport_set_violations

VIOLATIONS

Expdp exports tablespace object metadata information.

Oracle:/ftptemp/trans > = ttspoc.dmp directory=dmpdir transport_full_check=y ls-l

Total 19224

-rw-r--r-- 1 oracle dba 1529 Nov 19 14:28 export.log

-rw-r- 1 oracle dba 9838592 Nov 19 14:28 ttspoc.dmp-about 9m

Note that the following are the key steps of the migration, and we need to Convert the corresponding migration data files to the target platform format. Oracle recommends using Rman to do this.

Oracle:/ftptemp/trans > rman nocatalog

Recovery Manager: Release 11.2.0.1.0-Production on Mon Nov 19 14:36:47 2012

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

RMAN > connect target /

Connected to target database: NBSTEST (DBID=2351142467)

Using target database control file instead of recovery catalog

RMAN > convert tablespace 'NBSPOC'

2 > to platform= "Linux IA (32-bit)"-Target platform format (you can query the v$database view of the target database)

3 > db_file_name_convert='/nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf','/ftptemp/trans/NBSPOCTBL01.dbf'

Starting conversion at source at 19-NOV-12

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=355 device type=DISK

Channel ORA_DISK_1: starting datafile conversion

Input datafile file number=00014 name=/nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf

Converted datafile=/ftptemp/trans/NBSPOCTBL01.dbf

Channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55

Finished conversion at source at 19-NOV-12

From the command line point of view, Rman is equivalent to converting the specified data file and copying the converted version to a Stage directory. In this way, all the migration content is already included in the directory / ftptemp/trans.

Oracle:/ftptemp/trans > ls-l

Total 2067240

-rw-r- 1 oracle dba 1048584192 Nov 19 14:41 NBSPOCTBL01.dbf

-rw-r--r-- 1 oracle dba 1529 Nov 19 14:28 export.log

-rw-r- 1 oracle dba 9838592 Nov 19 14:28 ttspoc.dmp

Switch to a Target environment, which is a Linux server.

SQL > conn sys/oracle@ora11gp as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as SYS

SQL > select PLATFORM_NAME from v$database

PLATFORM_NAME

-

Linux IA (32-bit)

The following is to transfer the transfer file to the target platform using FTP, which is done using the FTP command line client that comes with Linux.

[oracle@bsplinux transtts] $ls-l

Total 0

[oracle@bsplinux transtts] $pwd

/ transtts

[oracle@bsplinux transtts] $

-- call the FTP client

[oracle@bsplinux transtts] $ftp

Ftp > open 10.1.15.66

Connected to 10.1.15.66.

220 P550_05_LC FTP server (Version 4.2 Wed Dec 23 11:06:15 CST 2009) ready.

502 authentication type cannot be set to GSSAPI

502 authentication type cannot be set to KERBEROS_V4

KERBEROS_V4 rejected as an authentication type

Name (10.1.15.66:oracle): oracle

331 Password required for oracle.

Password:

230-Last unsuccessful login: Wed Nov 14 15:35:34 GMT+08:00 2012 on / dev/pts/1 from 10.1.39.109

230-Last login: Mon Nov 19 14:24:58 GMT+08:00 2012 on / dev/pts/2 from 10.1.39.62

230 User oracle logged in.

Remote system type is UNIX.

Using binary mode to transfer files.

Ftp >

-- switch directories

Ftp > cd / ftptemp/trans

250 CWD command successful.

Ftp > dir

227 Entering Passive Mode (10, 1, 15, 6, 134182)

150 Opening data connection for / bin/ls.

Total 2067240

-rw-r- 1 oracle dba 1048584192 Nov 19 14:41 NBSPOCTBL01.dbf

-rw-r--r-- 1 oracle dba 1529 Nov 19 14:28 export.log

-rw-r- 1 oracle dba 9838592 Nov 19 14:28 ttspoc.dmp

226 Transfer complete.

Ftp > get ttspoc.dmp

Local: ttspoc.dmp remote: ttspoc.dmp

227 Entering Passive Mode (10, 1, 15, 66, 134206)

150 Opening data connection for ttspoc.dmp (9838592 bytes).

226 Transfer complete.

9838592 bytes received in 0.85 seconds (1.1e+04 Kbytes/s)

Ftp > get export.log

Local: export.log remote: export.log

227 Entering Passive Mode (10, 1, 15, 6, 134211)

150 Opening data connection for export.log (1529 bytes).

226 Transfer complete.

1529 bytes received in 0.17 seconds (9 Kbytes/s)

Ftp > get NBSPOCTBL01.dbf

Local: NBSPOCTBL01.dbf remote: NBSPOCTBL01.dbf

227 Entering Passive Mode (10, 1, 15, 6, 134228)

150 Opening data connection for NBSPOCTBL01.dbf (1048584192 bytes).

226 Transfer complete.

1048584192 bytes received in 3.6e+02 seconds (2.9e+03 Kbytes/s)

Ftp >

-- Transmission completed

[oracle@bsplinux transtts] $ls-l

Total 1034640

-rw-r--r-- 1 oracle oinstall 1529 Nov 19 21:51 export.log

-rw-r--r-- 1 oracle oinstall 1048584192 Nov 19 21:58 NBSPOCTBL01.dbf

-rw-r--r-- 1 oracle oinstall 9838592 Nov 19 21:51 ttspoc.dmp

[oracle@bsplinux transtts] $

Copy the copied data to the data file directory and perform the metadata import step. Note that in the Target environment, you need to first create user and permission information.

SQL > create user nbspoc identified by nbspoc

User created

SQL > grant resource, connect to nbspoc

Grant succeeded

Import tablespace information.

[oracle@bsplinux transtts] $impdp userid=\'/ as sysdba\ 'directory=ttsdmp dumpfile=ttspoc.dmp transport_datafiles='/u01/app/oradata/ORA11G/datafile/NBSPOCTBL01.dbf'

Import: Release 11.2.0.1.0-Production on Mon Nov 19 22:13:42 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

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

Starting "SYS". "SYS_IMPORT_TRANSPORTABLE_01": userid= "/ * AS SYSDBA" directory=ttsdmp dumpfile=ttspoc.dmp transport_datafiles=/u01/app/oradata/ORA11G/datafile/NBSPOCTBL01.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:16:29

Set the closing information and validation.

SQL > select file_name, tablespace_name from dba_data_files where tablespace_name like 'NBS%'

FILE_NAME TABLESPACE_NAME

-

/ u01/app/oradata/ORA11G/datafile/NBSPOCTBL01.dbf NBSPOC

SQL > select count (*), sum (bytes) / 1024 from dba_segments where tablespace_name='NBSPOC'

COUNT (*) SUM (BYTES) / 1024 take 1024

--

174 286

SQL > select tablespace_name, status from dba_tablespaces where tablespace_name like 'NBS%'

TABLESPACE_NAME STATUS

NBSPOC READ ONLY

SQL > alter tablespace nbspoc read write

Tablespace altered

The migration was successful. Well, there are many more complex situations, such as the introduction of ASM. In the ASM environment, we cannot directly access the data file and copy the data file (before Oracle 11gR2).

This is the end of the introduction of "what is the method of Oracle data pump Data Pump export and import". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.