Oracle-資料泵實操____Oracle

概述 資料泵的作用： 1.實現邏輯備份和邏輯恢複 2.在資料庫使用者之間移動對象 3.在資料庫之間移動對象 4.實現資料表空間轉移 資料泵的特點與傳統匯出匯入的區別 1.EXP和IMP是客戶段工具程式， EXPDP和IMPDP是服務端的工具程式 2.EXP和IMP效率比較低. EXPDP和IMPDP效率高 3.資料泵功能強大並行、過濾、轉換、壓縮、加密、互動等等 4.資料泵不支援9i以前版本， EXP/IMP短期內還是比較適用 5.同exp/imp資料泵匯出包括匯出表,匯出方案,匯出資料表空間,匯出資料庫4種方式.

Oracle官方指導文檔 需求描述

198測試環境的cc使用者下的全量資料，需要同步到到准生產環境197一份 方案分析

因資料庫版本為11.2（大於Oracle10g）初步確定使用資料泵的方式從198匯出後匯入197資料庫 操作步驟 expdp

SQL 視窗下執行以下操作 1.建立邏輯目錄

建立邏輯目錄

SQL>  create directory dir_dp as '/oracle/oracle11';

2.查看目錄

同時查看作業系統是否存在，如果不存在，mkdir -p 建立目錄 否則出錯

SELECT privilege, directory_name, DIRECTORY_PATH  FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;

或者 可以查看

select * from dba_directories ;

DATA_PUMP_DIR就是直接可以用的DUMP目錄，匯出檔案和匯入檔案就放在上面的路徑下，如果沒有，則需要手動建立並賦給讀/寫入權限.

如果沒有查詢到的話，重新整理一下

SQL> host ls /oracle/oracle11

3.給cc使用者賦予在指定目錄的操作許可權

以dba使用者等管理員賦予

SQL>  Grant read,write on directory dir_dp to cc;

4. 匯出資料

使用主機的Oracle使用者

按使用者導/匯出指定schema：

oracle@entel2:[/oracle]$expdp cc/password@//10.45.7.198:1521/cc schemas=cc directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;...........Dump file set for CC.SYS_EXPORT_SCHEMA_01 is:  /oracle/oracle11/expdp_test1.dmpJob "CC"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Aug 20 12:02:13 2016 elapsed 0 00:01:33

參數解析： schemas：資料庫使用者別名 而非執行個體名 cc/password@//10.45.7.198:1521/cc port後面指定的是執行個體名 ，最前面的是使用者名稱和密碼 scp dmp 到目標主機

[root@entel2 ~]# scp expdp_198.dmp oracle@10.45.7.197:/oracle/oracle11

impdp 刪除使用者

SQL> DROP USER cc CASCADE;

建立使用者

SQL>CREATE USER cc IDENTIFIED BY passwordDEFAULT TABLESPACE TAB_CC;

賦予許可權

SQL>GRANT CONNECT ,RESOURCE TO cc ;SQL>GRANT ALL PRIVILEGES TO cc ;SQL>GRANT DBA TO cc ;

使用Oracle使用者匯入 相同的schema

oracle@entel1:[/oracle]$impdp cc/password@//10.45.7.197:1521/cc schemas=cc directory=dir_dp_198 dumpfile =expdp_198.dmp logfile=expdp_198.log;

不同的schema

oracle@entel1:[/oracle]$impdp entelcc_st4/password@//10.45.7.197:1521/cc schemas=cc remap_schema=cc:entelcc_st4 directory=dir_dp_198 dumpfile =expdp_198.dmp logfile=expdp_198.log;

說明： 目標主機上 directory也需要建立 ，並賦予讀寫權限,如果不指定，則使用預設目錄 預設Directory為DATA_PUMP_DIR

--查看預設目錄select * from dba_directories where directory_name='DATA_PUMP_DIR';

DATA_PUMP_DIR這個目錄，是系統預設目錄，如不單獨指定目錄，dmp檔案會在這裡，但預設這個目錄其他使用者是沒有許可權的。 dmp檔案需要存放到對應的directory目錄下，否者匯入報錯

如果目標庫的schema和源庫的schema一致，按以上指令碼即可，不一致的情況需要指定，文法如下：schemas=old_schema remap_schema=old_schema:new_schema

否者報錯：

ORA-39002: invalid operationORA-39166: Object ENTELCC_ST4 was not found.

REMAP_SCHEMA、REMAP_TABLESPACE、REMAP_DATAFILE

除了REMAP_SCHEMA，還有REMAP_TABLESPACE、REMAP_DATAFILE 等

1、REMAP_SCHEMA
該選項用於將源方案的所有對象裝載到目標方案中：REMAP_SCHEMA=source_schema:target_schema
2、REMAP_TABLESPACE
將源資料表空間的所有對象匯入到目標資料表空間中：REMAP_TABLESPACE=source_tablespace:target:tablespace
3、REMAP_DATAFILE 該選項用於將來源資料檔案名稱轉變為目標資料檔案名,在不同平台之間搬移資料表空間時可能需要該選項.
REMAP_DATAFIEL=source_datafie:target_datafile 不同路徑下的dump檔案同時匯入

需要建立多個directory

指定 dumpfile=dir1:1.dmp,dir2:2.dm 查看指令碼

查看各個SCHEMA佔用空間的大小

select owner,round(sum(bytes/1024/1024),2)||' MB' as schema_size from dba_segments group by owner order by SCHEMA_SIZE desc;  

查詢己經匯入的對象個數

select owner,count(*) from dba_objects where owner like 'ENTELCC_ST4%' group by owner order by owner;  

這裡不同的schema，之前沒有注意到，導致一直報錯，看到了yfleng2002的博文Oracle schema 層級的資料移轉，贊一個。 schema解釋

A schema is a collection of database objects (used by a user.).
Schema objects are the logical structures that directly refer to the database’s data.
A user is a name defined in the database that can connect to and access objects.
Schemas and users help database administrators manage database security.

一個使用者一般對應一個schema,該使用者的schema名等於使用者名稱，並作為該使用者預設schema。

Oracle資料庫中不能新建立一個schema，要想建立一個schema，只能通過建立一個使用者的方法解決(Oracle中雖然有create schema語句，但是它並不是用來建立一個schema的)，在建立一個使用者的同時為這個使用者建立一個與使用者名稱同名的schem並作為該使用者的預設shcema。

即schema的個數同user的個數相同，而且schema名字同user名字一一 對應並且相同，所有我們可以稱schema為user的別名，雖然這樣說並不準確，但是更容易理解一些。

一個使用者有一個預設的schema，其schema名就等於使用者名稱，當然一個使用者還可以使用其他的schema。如果我們訪問一個表時，沒有指明該表屬於哪一個schema中的，系統就會自動給我們在表上加上預設的sheman名。比如我們在訪問資料庫時，訪問scott使用者下的emp表，通過select * from emp; 其實，這sql語句的完整寫法為select * from scott.emp。在資料庫中一個對象的完整名稱為schema.object，而不屬user.object。類似如果我們在建立對象時不指定該對象的schema，在該對象的schema為使用者的預設schema。這就像一個使用者有一個預設的資料表空間，但是該使用者還可以使用其他的資料表空間，如果我們在建立對象時不指定資料表空間，則Object Storage Service在預設資料表空間中，要想讓Object Storage Service在其他資料表空間中，我們需要在建立對象時指定該對象的資料表空間。

以上schema解釋參考oracle中schema指的是什麼。，感謝分享 注意事項

EXPDP和IMPDP是服務端的工具程式，只能在ORACLE服務端使用，不能在用戶端使用

低版本是不能匯入高版本dmp，需要在高版本的EXPDP匯出時指定版本號碼匯出。低版本IMPDP無需指定版本。
例如：11.2.0.4匯入到10.2.0.5

expdp username/password directory=dump_dir dumpfile=test.dmp version=10.2.0.5.0

匯出匯入全庫 全庫匯出

oracle@entel2:[/oracle]$expdp \'/ as sysdba\' directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2;【開始執行........上述命令也可以增加logfile等 自行決定】Export: Release 11.2.0.4.0 - Production on Mon Oct 24 18:47:01 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2 Estimate in progress using BLOCKS method...Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATATotal estimation using BLOCKS method: 2.294 GBProcessing object type DATABASE_EXPORT/TABLESPACE. . exported "ZMC"."NM_ALARM_EVENT"                      317.5 MB  467108 rowsProcessing object type DATABASE_EXPORT/PROFILEProcessing object type DATABASE_EXPORT/SYS_USER/USERProcessing object type DATABASE_EXPORT/SCHEMA/USERProcessing object type DATABASE_EXPORT/ROLEProcessing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLEProcessing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA..............

PARALLEL 更改當前作業的活動 worker 的數目。PARALLEL=<worker 的數目>。指定執行匯出操作的並行進程個數,預設值為1

FULL 匯出整個資料庫 預設為N。 全庫匯入

$ impdp \'/ as sysdba\' directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2 table_exists_action=replace;

更多詳情參考