重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
Oracle数据泵DataPump导出导入的方法是什么
本篇内容介绍了“Oracle数据泵Data Pump导出导入的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
在黔西南州等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都做网站、成都网站建设 网站设计制作定制网站开发,公司网站建设,企业网站建设,成都品牌网站建设,全网整合营销推广,外贸网站制作,黔西南州网站建设费用合理。
Oracle数据泵Data Pump导出导入
Exp/Imp是Oracle早期推出的数据逻辑备份还原工具，使用简单、功能强大。但是Exp/Imp对一些Oracle新特性支持不是很好，而且对于海量数据备份还原速度还是不能满足要求。于是从10g开始，Oracle推出了数据泵（Data Pump）作为Exp/Imp的升级替代版本。
使用Data Pump也是可以进行TTS元数据的导出。下面我们将实验使用Data Pump重新做一次。注意：表空间Read Only配置和相关的检查步骤略过。
Data Pump是一个服务端工具，使用的话需要定义directory对象。
SQL> create or replace directory TTSDMP
2 as '/transtts';
Directory created
SQL> grant read, write on directory ttsdmp to public;
Grant succeeded
导出元数据dmp文件。
[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
拷贝数据文件。
[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
标红部分的文件为进行TTS的必要内容。
说明：由于我们在相同的环境下进行测试，所以将表空间删除。
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
-------------------------------------------------------------------------------- ------------------------------
表空间删除成功。下面进行实际还原过程。
[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
使用impdp工具导入元数据信息。
[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
此时，表空间已经导回，对应的数据表也能够还原回去。
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
--切换回test用户
SQL> select count(*) from test.t;
COUNT(*)
----------
72348
注意：无论是使用exp/imp还是Data Pump，导入对象对应的owner必须存在并且名称一致。如果不存在owner对象，即使使用Data Pump也不会自动将用户重新创建。
7、异平台TTS移植
相同平台类型移植，一直是TTS使用的一个前提条件，也是一个很难逾越的屏蔽。这给TTS的使用带来一些限制。比如，我们不能实现从AIX平台到开放Linux平台的迁移。于是，在10g以上的版本中，我们可以突破这个限制。
下面我们尝试将一个表空间从AIX平台迁移到Linux平台。
在AIX平台上，我们将其作为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/1024 from dba_segments where tablespace_name='NBSPOC';
COUNT(*) SUM(BYTES)/1024/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
NBSPOC表空间中包括174个对象，约286M数据。下面设置表空间Read Only和平台检查。
SQL> alter tablespace nbspoc read only;
Tablespace altered
SQL> select PLATFORM_NAME from v$database;
PLATFORM_NAME
------------------------------
AIX-Based Systems (64-bit)
我们从v$transportable_platform视图中，可以看出AIX-Based Systems(64-bit)属于Big类型。而Target目标Linux为Little类型。所以，我们在进行跨平台迁移。
注意：跨平台迁移也需要满足字符集相同的要求。
元数据导出我们计划使用expdp，所以配置directory对象。
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
检查表空间NBSPOC的依赖性。
SQL> exec dbms_tts.transport_set_check('NBSPOC',true);
PL/SQL procedure successfully completed
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Expdp导出表空间对象元数据信息。
oracle:/ftptemp/trans>=ttspoc.dmp directory=dmpdir transport_full_check=y <
Export: Release 11.2.0.1.0 - Production on Mon Nov 19 14:26:51 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/******** AS SYSDBA" transport_tablespaces=nbspoc dumpfile=ttspoc.dmp directory=dmpdir 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/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
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/ftptemp/trans/ttspoc.dmp
******************************************************************************
Datafiles required for transportable tablespace NBSPOC:
/nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:28:43
oracle:/ftptemp/trans>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 –大约9M
注意，下面是迁移的关键步骤，我们需要将对应的迁移数据文件Convert到目标平台格式。Oracle推荐使用Rman进行这个工作。
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)" –目标平台格式（可以查询目标数据库的v$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
从命令行情况看，Rman相当于将指定的数据文件进行转换，拷贝转换过的版本到一个Stage目录上。这样，目录/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
转换到Target环境，是一台Linux服务器。
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)
下面就是用FTP将传输文件传输到目标平台，我们使用Linux自带的FTP命令行客户端完成。
[oracle@bsplinux transtts]$ ls -l
total 0
[oracle@bsplinux transtts]$ pwd
/transtts
[oracle@bsplinux transtts]$
--调用FTP客户端
[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>
--切换目录
ftp> cd /ftptemp/trans
250 CWD command successful.
ftp> dir
227 Entering Passive Mode (10,1,15,66,134,182)
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,134,206)
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,66,134,211)
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,66,134,228)
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>
--传输完毕
[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]$
将拷贝的数据复制到数据文件目录，进行导入元数据步骤。注意，在Target环境中，要首先创建好用户和权限信息。
SQL> create user nbspoc identified by nbspoc;
User created
SQL> grant resource, connect to nbspoc;
Grant succeeded
导入表空间信息。
[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
设置收尾信息和验证。
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/1024 from dba_segments where tablespace_name='NBSPOC';
COUNT(*) SUM(BYTES)/1024/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
迁移成功。那么，还有很多更复杂的情况，比如ASM的引入。在ASM环境下，我们是不能够直接访问到数据文件，拷贝数据文件的（Oracle 11gR2之前）。
“Oracle数据泵Data Pump导出导入的方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
分享题目：Oracle数据泵DataPump导出导入的方法是什么
网页网址：http://cqcxhl.com/article/peecgg.html