?Oracle數(shù)據(jù)泵Data Pump導(dǎo)出導(dǎo)入的方法是什么

本篇內(nèi)容介紹了“Oracle數(shù)據(jù)泵Data Pump導(dǎo)出導(dǎo)入的方法是什么”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

Oracle數(shù)據(jù)泵Data Pump導(dǎo)出導(dǎo)入 

Exp/Imp是Oracle早期推出的數(shù)據(jù)邏輯備份還原工具，使用簡(jiǎn)單、功能強(qiáng)大。但是Exp/Imp對(duì)一些Oracle新特性支持不是很好，而且對(duì)于海量數(shù)據(jù)備份還原速度還是不能滿足要求。于是從10g開(kāi)始，Oracle推出了數(shù)據(jù)泵（Data Pump）作為Exp/Imp的升級(jí)替代版本。 

使用Data Pump也是可以進(jìn)行TTS元數(shù)據(jù)的導(dǎo)出。下面我們將實(shí)驗(yàn)使用Data Pump重新做一次。注意：表空間Read Only配置和相關(guān)的檢查步驟略過(guò)。

Data Pump是一個(gè)服務(wù)端工具，使用的話需要定義directory對(duì)象。

SQL> create or replace directory TTSDMP

  2    as '/transtts';

Directory created

SQL> grant read, write on directory ttsdmp to public;

Grant succeeded

導(dǎo)出元數(shù)據(jù)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

拷貝數(shù)據(jù)文件。

[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

標(biāo)紅部分的文件為進(jìn)行TTS的必要內(nèi)容。

說(shuō)明：由于我們?cè)谙嗤沫h(huán)境下進(jìn)行測(cè)試，所以將表空間刪除。

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

-------------------------------------------------------------------------------- ------------------------------

表空間刪除成功。下面進(jìn)行實(shí)際還原過(guò)程。

[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工具導(dǎo)入元數(shù)據(jù)信息。

[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

此時(shí)，表空間已經(jīng)導(dǎo)回，對(duì)應(yīng)的數(shù)據(jù)表也能夠還原回去。

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

注意：無(wú)論是使用exp/imp還是Data Pump，導(dǎo)入對(duì)象對(duì)應(yīng)的owner必須存在并且名稱一致。如果不存在owner對(duì)象，即使使用Data Pump也不會(huì)自動(dòng)將用戶重新創(chuàng)建。

7、異平臺(tái)TTS移植

相同平臺(tái)類(lèi)型移植，一直是TTS使用的一個(gè)前提條件，也是一個(gè)很難逾越的屏蔽。這給TTS的使用帶來(lái)一些限制。比如，我們不能實(shí)現(xiàn)從AIX平臺(tái)到開(kāi)放Linux平臺(tái)的遷移。于是，在10g以上的版本中，我們可以突破這個(gè)限制。

下面我們嘗試將一個(gè)表空間從AIX平臺(tái)遷移到Linux平臺(tái)。

在AIX平臺(tái)上，我們將其作為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個(gè)對(duì)象，約286M數(shù)據(jù)。下面設(shè)置表空間Read Only和平臺(tái)檢查。

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類(lèi)型。而Target目標(biāo)Linux為Little類(lèi)型。所以，我們?cè)谶M(jìn)行跨平臺(tái)遷移。

注意：跨平臺(tái)遷移也需要滿足字符集相同的要求。

元數(shù)據(jù)導(dǎo)出我們計(jì)劃使用expdp，所以配置directory對(duì)象。

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導(dǎo)出表空間對(duì)象元數(shù)據(jù)信息。

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

注意，下面是遷移的關(guān)鍵步驟，我們需要將對(duì)應(yīng)的遷移數(shù)據(jù)文件Convert到目標(biāo)平臺(tái)格式。Oracle推薦使用Rman進(jìn)行這個(gè)工作。

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)" –目標(biāo)平臺(tái)格式（可以查詢目標(biāo)數(shù)據(jù)庫(kù)的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相當(dāng)于將指定的數(shù)據(jù)文件進(jìn)行轉(zhuǎn)換，拷貝轉(zhuǎn)換過(guò)的版本到一個(gè)Stage目錄上。這樣，目錄/ftptemp/trans中就已經(jīng)包括了所有的遷移內(nèi)容了。

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

轉(zhuǎn)換到Target環(huán)境，是一臺(tái)Linux服務(wù)器。

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將傳輸文件傳輸?shù)侥繕?biāo)平臺(tái)，我們使用Linux自帶的FTP命令行客戶端完成。

[oracle@bsplinux transtts]$ ls -l

total 0

[oracle@bsplinux transtts]$ pwd

/transtts

[oracle@bsplinux transtts]$

--調(diào)用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]$

將拷貝的數(shù)據(jù)復(fù)制到數(shù)據(jù)文件目錄，進(jìn)行導(dǎo)入元數(shù)據(jù)步驟。注意，在Target環(huán)境中，要首先創(chuàng)建好用戶和權(quán)限信息。

SQL> create user nbspoc identified by nbspoc;

User created

SQL> grant resource, connect to nbspoc;

Grant succeeded

導(dǎo)入表空間信息。

[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

設(shè)置收尾信息和驗(yàn)證。

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

遷移成功。那么，還有很多更復(fù)雜的情況，比如ASM的引入。在ASM環(huán)境下，我們是不能夠直接訪問(wèn)到數(shù)據(jù)文件，拷貝數(shù)據(jù)文件的（Oracle 11gR2之前）。

“Oracle數(shù)據(jù)泵Data Pump導(dǎo)出導(dǎo)入的方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！