Oracle 11g資料泵詳解

Oracle 11g資料泵詳解

Oracle Database 10g引入了最新的資料泵(DataDump)技術，資料泵匯出匯入(EXPDP和IMPDP)的作用

1，實現邏輯備份和邏輯恢複

2，在資料庫使用者之間移動對象

3，在資料庫之間移動對象

4，實現資料表空間搬移

資料泵使用EXPDP和IMPDP時應該注意的事項：

EXP和IMP是用戶端工具程式，它們既可以在用戶端使用，也可以在服務端使用。

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

IMP只適用於EXP匯出的檔案，不適用於EXPDP匯出檔案；IMPDP只適用於EXPDP匯出的檔案，而不適用於EXP匯出檔案。

一：可以通過dblink來使用資料泵，這樣省掉了匯出的步驟，操作簡單了。如下所示的例子：

impdp  system/manager123 network_link=dblink_to_myself  remap_tablespace=DIC_DATA:DICTEST remap_schema=DICTEST_ZH_CN:DICTEST_ZH_CN  tables=td_pay_bank, td_common_content  TABLE_EXISTS_ACTION=replace;

二：匯出的內容

1)按使用者導

expdpscott/tiger@orclschemas=scott dumpfile=expdp.dmp DIRECTORY=dir logfile=expdp.log

2)並行進程parallel

expdpscott/tiger@orcldirectory=dir dumpfile=scott3.dmp parallel=40  job_name=scott3

3)按表名導

expdpscott/tiger@orclTABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dir;

4)按查詢條件導

expdpscott/tiger@orcldirectory=dir dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

5)按資料表空間導

expdp system/manager DIRECTORY=dir DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

6)導整個資料庫

expdp system/manager  DIRECTORY=dir  DUMPFILE=full.dmp  FULL=y;

三：匯出內容：

CONTENT該選項用於指定要匯出的內容.預設值為ALL
 CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
當設定CONTENT為ALL時,將匯出對象定義及其所有資料.為DATA_ONLY時,只匯出對象資料,為METADATA_ONLY時,只匯出對象定義
例子：只匯出表結構

expdp  system/"manager(*)0202"  directory=bak  dumpfile=TEST_NAV_ZH_CN20150506.dmp  schemas=test_nav_zh_tw,  test_nav_zh_cn test_nav_en_us, test_nav_ar_sa  CONTENT=METADATA_ONLY 

四：directory: 

 查看已經建好的目錄；如果沒有使用directory參數那麼會使用目錄DATA_PUMP_DIR

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
 ----- ---------------------- ----------------------------------------------------------

SYS SUBDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/product_media/
SYS XMLDIR /u01/app/oracle/product/11.2.0/db/rdbms/xml
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/sales_history/
SYS DATA_PUMP_DIR /u01/app/oracle/admin/tj01/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db/ccr/state
 

注意：通過查詢我們看到，所有的目錄都屬於SYS使用者，而不管是哪個使用者建立的，在資料庫裡已經提前建好了這個目錄對象DATA_PUMP_DIR。如果在使用expdp匯出時，不指定目錄對象參數，Oracle會使用資料庫預設的目錄DATA_PUMP_DIR，不過如果想使用這個目錄的話，使用者需要具有exp_full_database的許可權才行
 

五：不常用的一些參數;
1.EXTIMATE_ONLY

指定是否只估算匯出作業所佔用的磁碟空間,預設值為N

2.EXTIMATE_ONLY={Y | N}

設定為Y時,匯出作用只估算對象所佔用的磁碟空間,而不會執行匯出作業,為N時,不僅估算對象所佔用的磁碟空間,還會執行匯出操作.

Expdp scott/tiger ESTIMATE_ONLY=yNOLOGFILE=y

3.EXCLUDE

該選項用於指定執行操作時釋放要排除物件類型或相關對象

EXCLUDE=object_type[:name_clause] [,….]

Object_type用於指定要排除的物件類型,name_clause用於指定要排除的具體對象.EXCLUDE和INCLUDE不能同時使用

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dup EXCLUDE=VIEW

4.FILESIZE

指定匯出檔案的最大尺寸,預設為0,(表示檔案尺寸沒有限制)

5. FLASHBACK_SCN

指定匯出特定SCN時刻的表資料

FLASHBACK_SCN=scn_value

Scn_value用於標識SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同時使用

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dmp

FLASHBACK_SCN=358523

10）FLASHBACK_TIME

指定匯出特定時間點的表資料

FLASHBACK_TIME=”TO_TIMESTAMP(time_value)”

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dmp FLASHBACK_TIME=

“TO_TIMESTAMP(’25-08-200414:35:00’,’DD-MM-YYYYHH24:MI:SS’)”
 
六：當用一個schema去覆蓋另一個schemas時，如下：

[oracle@rac1 ~]$ impdp system/manager123 network_link=dblink_to_myself remap_tablespace=users:users remap_schema=liuwenhe:liuhe schemas=liuwenhe;

如果liuhe這使用者存在，那麼該操作完成之後，liuhe這個使用者權限，密碼什麼的不會變化。

如果liuhe這個使用者不存在，那麼該操作完成之後，就會建立一個liuhe的使用者，並且沒有任何許可權，值得注意的是密碼和liuwenhe的密碼相同。

七：當匯入schemas的時候，會把屬於這個使用者的所有對象，包括SEQUENCE，FUNCTION，PROCEDURE，主鍵，索引，都一併過去。

[oracle@rac1 ~]$ impdp system/manager123  network_link=dblink_to_myself remap_tablespace=users:users remap_schema=liuwenhe:liuhe  tables=liuwenhe.liuwenhe table_exists_action=replace; 

liuwenhe.liuwenhe這個表的主鍵和索引，也會過去。並且名字和liuwenhe.liuwenhe的對應名字一樣。

八：該選項用於指定當表已經存在時匯入作業要執行的操作,預設為SKIP
TABBLE_EXISTS_ACTION={SKIP | APPEND |TRUNCATE | FRPLACE }

當設定該選項為SKIP時,匯入作業會跳過已存在表處理下一個對象;當設定為APPEND時,會追加資料,為TRUNCATE時,匯入作業會截斷表,然後為其追加新資料;當設定為REPLACE時,匯入作業會刪除已存在表,重建表並追加資料,注意,TRUNCATE選項不適用與簇表和NETWORK_LINK選項
 
九：注意：普通使用者做全庫匯出，需要有這個許可權：
SQL> grant exp_full_database to scott;

十：REMAP_TABLE參數 

 將源表資料對應到不同的目標表中
 eg:impdp orcldev/oracle DIRECTORY=backup_path dumpfile=oracldev.dmp remap_table=TAB_TEST:TEST_TB
  資料匯入到TEST_TB表中，但是該表的索引等資訊並沒有相應的建立，需要手工初始化
 
十一：REMAP_DATAFILE參數

　　文法：REMAP_DATAFILE=source_datafile:target_datafile
　　 Oracle_Online:
　　Remapping datafiles is useful when you move databases between platforms that have different file naming conventions. The source_datafile and target_datafile names should be exactly as you want them to appear in the SQL statements where they are referenced. Oracle recommends that you enclose datafile names in quotation marks to eliminate ambiguity on platforms for which a colon is a valid file specification character

十三：下面看一個實驗 ,驗證的是只匯出資料後，可以恢複，就算是表結構已經變化了，他也能把相應的列恢複，

1)SQL> desc liuwenhe.liuwenhe;

 Name                                      Null?    Type

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

 X                                        NOT NULL NUMBER(38)

 Y                                                  NUMBER(38)

2)SQL> select * from liuwenhe.liuwenhe;

        X          Y

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

        1          3

        3          3

        2          4

        4          5

        5          8

        6          9

6 rows selected.

3)[oracle@rac1 expdp]$ expdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe;

 Export: Release 11.2.0.3.0 - Production on Mon Jul 6 11:52:56 2015

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

 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, Real Application Clusters, OLAP, Data Mining
 and Real Application Testing options
 Starting "SYSTEM"."SYS_EXPORT_TABLE_03":  system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe
 Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 64 KB
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 . . exported "LIUWENHE"."LIUWENHE"                      5.492 KB      6 rows
 Master table "SYSTEM"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
  /backup/expdp/hhhf.dmp
 Job "SYSTEM"."SYS_EXPORT_TABLE_03" successfully completed at 11:53:10

4)SQL> alter table liuwenhe.liuwenhe  drop column y;

Table altered.

5)SQL> truncate table liuwenhe.liuwenhe;

Table truncated.

6)[oracle@rac1 expdp]$ impdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe  content=data_only;

Import: Release 11.2.0.3.0 - Production on Mon Jul 6 11:55:07 2015

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

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

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

Starting "SYSTEM"."SYS_IMPORT_TABLE_04":  system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe content=data_only 

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "LIUWENHE"."LIUWENHE"                      5.492 KB      6 rows

Job "SYSTEM"."SYS_IMPORT_TABLE_04" successfully completed at 11:55:13
7)SQL> select * from liuwenhe.liuwenhe;

        X

----------

        1

        2

        3

        4

        5

        6

6 rows selected.

總結：Oracle資料泵，邏輯備份和恢複工具，他直接在邏輯從面操作，不能想象成單純的insert ， 而且只匯出資料，然後恢複資料時候，可以不用寫remap_tablespace這個參數 。