Oracle的exp/imp詳解

轉自：http://czmmiao.iteye.com/blog/1522278

Oracle的exp/imp詳解(原創)

備份概述
邏輯備份：
備份可分為兩類，物理備份和邏輯備份

物理備份：該方法實現資料庫的完整恢復，但需要極大的外部儲存裝置，例如磁帶庫，具體包括冷備份和熱備份。冷備份和熱備份(熱備份要求資料庫執行在歸檔模式下)都是物理備份，它涉及到組成資料庫的檔案，但不考慮邏輯內容。
邏輯備份： 使用軟體技術從資料庫中匯出資料並寫入一個輸出檔案，該檔案的格式一般與原資料庫的檔案格式不同，只是 原資料庫中資料內容的一個映像。因此，邏輯備份檔案只能用來對資料庫進行邏輯恢復，即資料匯入，而不能按資料庫原來的儲存特徵進行物理恢復。邏輯備份一般 用於增量備份，即備份那些在上次備份以後改變的資料。
在進行邏輯備份時，首先要確認資料庫的字符集和作業系統的字符集是否一致，如果不一致則會報錯ora-00091，具體設定方法如下：
SQL> select * from v$nls_parameters where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
PARAMETER            VALUE
-------------------- --------------------
NLS_LANGUAGE         AMERICAN
NLS_TERRITORY        AMERICA
NLS_CHARACTERSET     WE8ISO8859P1
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1

exp

可以透過exp help=y或者imp help=y檢視exp或imp的詳細引數，下面以exp為例解釋引數意義
USERID：使用者名稱/口令
FULL：匯出整個資料庫，只有擁有exp_full_database角色的使用者或者特權使用者如sys，system等才能進行全庫匯出。 示例如下
exp "'/ as sysdba'" full=y
BUFFER：制定資料緩衝區大小，主要用於提高exp/imp速度，該單位為位元組，不能寫成buffer=1m的形式，應寫成位元組為單位的引數，如buffer=1048576
exp hr/hr file=t_b.dmp buffer= 1048576 tables=T
OWNER：需要匯出的使用者，示例如下
exp "'/ as sysdba'" owner=\(hr,scott\) file=hr_scott.dmp
上例中由於是在linux平臺進行測試的，需要對 owner=\(hr,scott\)使用\進行轉義
FILE：輸出檔案
TABLES：需要匯出的表
COMPRESS：匯入到一個區 (Y) 。主要目的是為了消除儲存碎片，以保證某張表的所有記錄都儲存在連續的空間裡。 但是負面效應很明顯， 如果該引數值為y，則會將高水位線以下的所有extent匯入到一個區中， 因此在匯入時很有可能出現，明明表中資料很少，但是卻花了很多時間在建立的extent上。 且自oracle9i開始，使用了本地管理的表空間，儲存碎片的問題應該比低版本好多了，筆者個人建議將compress設為n。可參加如下實驗過程
SQL> create table t as select * from dba_objects;
Table created.
SQL> select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T';
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T                 6        768         21          65536
採用delete的方式刪除表中資料，不降低HWM
SQL> delete from t;
50625 rows deleted.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T';
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T                 6        768         21          65536
將compress設為n和y分別對t表進行兩次匯出
exp hr/hr tables=t file=t_n.dmp compress=n
exp hr/hr tables=t file=t_y.dmp compress=y
刪除t表
SQL> drop table t purge;
Table dropped.
匯入compress值為y的dmp檔案
imp hr/hr file=t_y.dmp fromuser=hr touser=hr;
查詢t表初始extent大小
SQL> select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T'
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T                 6        768          6        6291456
檢視建表語句
SQL> select dbms_metadata.get_ddl('TABLE','T','HR') FROM DUAL;
  CREATE TABLE "HR"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 6291456 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
再次刪除t表
SQL> drop table t purge;
Table dropped.
匯入compress值為n的dmp檔案
imp hr/hr file=t_n.dmp fromuser=hr touser=hr;
查詢t表初始extent大小
SQL>select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T'
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T             .0625          8          1          65536
查詢建表語句
SQL>select dbms_metadata.get_ddl('TABLE','T','HR') FROM DUAL;
  CREATE TABLE "HR"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
可 以看到當compress為y時，exp將資料匯入到一個extent中，而在imp匯入的時候則體現為改變原有表的儲存引數，將初始化extent增大 到了6291456，個人建議通常情況下將compress設定為n，避免由於初始extent過大，給降低HWM帶來的不必要麻煩。
RECORDLENGTH：IO記錄的長度
DIRECT：直接路徑 (N)。
傳統模式匯出和直接路徑匯出的原理
傳 統模式匯出相當於使用select語句從表中取出資料，資料從磁碟上先讀到buffer cache中，記錄被轉移到一個評估檢測的緩衝區中，資料經過語法檢測後沒有問題，將資料傳給PGA，最後寫入匯出的檔案中。如果使用Direct Path模式匯出，資料直接從磁碟上讀取到匯出的PGA中：記錄直接被轉換匯出會話的私有buffer中。這也就是意味著SQL語句處理層被忽略掉了，因 為資料已經是符合匯出的格式了，不需要其他的轉換處理了。資料直接被傳送給匯出的客戶端，最後寫入匯出檔案。過程可概況如下
direct=n datafile---->sga----->pga----->dump
direct=y datafile---->pga----->dump
傳統模式匯出和直接路徑匯出的差異
Direct Path匯出模式速度上明顯快於Conventional Path匯出模式，因為Direct Path匯出模式忽略了SQL語句處理這一層。
當使用Direct Path匯出模式的時候，可以增大引數RECORDLENGTH的值來提高匯出的效能。匯出的效能主要取決以下的因素：DB_BLOCK_SIZE、匯出 表上列的型別、匯出檔案的I/O層（主要是指匯出檔案儘量要和資料庫的資料檔案在不同的磁碟上，避免I/O上的競爭）。一般來說，引數 RECORDLENGTH設定為作業系統I/O的block size或者是DB_BLOCK_SIZE的整數倍，例如65535。
使用哪種模式匯出資料都不會影響匯入資料，也就是說匯入資料的時間是一樣的。
傳統模式匯出和直接路徑匯出的限制
Direct Path匯出模式只能使用命令列或者引數檔案的方式來匯出，不能使用互動式的方式匯出資料，只有Conventional Path匯出模式可以使用互動式的方式。
Direct Path匯出模式不能用於匯出傳輸表空間，即設定引數TRANSPORT_TABLESPACES=Y，其他的FULL、USER、TABLE模式均可以使用Direct Path匯出模式。
在Oracle8i以前的版本里面，如果表裡面存在LOB的物件，是不能使用Direct Path匯出模式匯出表的，如果使用Direct Path匯出模式匯出表，那些存在LOB物件的記錄是不會被匯出的。自從Oracle8i之後，這種限制就被取消了。對於Oracle8i之後的版本，如 果使用Direct Path匯出模式匯出表，那些存在LOB物件的記錄是會自動以Conventional Path匯出模式來匯出。但是如果你用低於Oracle8i的客戶端的exp工具的Direct Path匯出模式匯出Oracle8i以上的資料庫存在LOB物件的表，那些包含LOB的記錄還是同樣不會被匯出。
Exp工具中的QUERY引數只能用於Conventional Path匯出模式，QUERY引數允許匯出一個表的滿足一定條件的部分記錄。
Exp工具中的BUFFER引數只能用於傳統模式匯出，BUFFER引數設定了用於fetch記錄的快取的大小，以位元組為單位，即在array中最大數量的記錄。
引數RECORDLENGTH指定檔案記錄的最大長度，以位元組為單位，即匯出I/O的buffer，最大為65535。這個引數決定了在沒寫入匯出檔案中快取中堆積資料的多少。如果沒有設定這個引數，取決於作業系統平臺，在大多數平臺的預設值是1024位元組。
只有當環境變數中的NLS_LANG設定成跟匯出資料庫中的字符集一致的時候才能使用Direct Path匯出模式匯出資料。如果環境變數中的NLS_LANG和資料庫的字符集不一致的時候，匯出就會報類似下面的錯誤：
EXP-41 "Export done in server's UTF8, different from user's character set WE8ISO8859P1"     
EXP-0 "Export terminated unsuccessfully".
這種限制只對於Oracle8i及其更低版本的Oracle有效，Oracle8i以上的版本不會出現此類錯誤。
DIRECT=Y 引數定義了 使用直接路徑方式匯出 ，RECORDLENGTH引數定義了Export I/O緩衝的大小，作用類似於常規路徑匯出使用的BUFFER引數。建議設定RECORDLENGTH引數為最大I/O緩衝，即65535(64kb)。其用法如下
exp userid=system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log
GRANTS：匯出許可權 (Y)
INCTYPE：增量匯出型別，已廢除
INDEXES：匯出索引 (Y)
RECORD：跟蹤增量匯出 (Y) ，已廢除
TRIGGERS：匯出觸發器 (Y)
LOG：螢幕輸出的日誌檔案
STATISTICS：在匯出檔案中保留物件的統計資訊，預設值ESTIMATE，還可以為compute或者none。如果匯出時出現
EXP-00091: Exporting questionable statistics
可以考慮將 STATISTICS設定為NONE
ROWS：確定表中的資料行是否匯出，預設為Y，匯出
QUERY：用於匯出表的子集的select子句，示例如下
exp hr/hr file=emp_q.dmp tables=employees query=\"where hire_date \>to_date\(\'1999-01-01\'\,\'yyyy-mm-dd\'\)\"
PARFILE：引數檔名，可以用如下方式匯出
exp hr/hr parfile=parfile
$ cat parfile
file=t_p.dmp
compress=y
rows=y
tables=(t,empl%s)
使用parfile引數可以對頻繁進行的匯出操作進行反覆呼叫，同時也可以避免不同作業系統之間需要對特定字元進行轉義的煩惱，如下例
exp hr/hr parfile=parfile
$cat parfile
file=t_p.dmp
compress=y
rows=y
tables=employees
statistics=none
query="where hire_date>to_date('1999-01-01','yyyy-mm-dd')"
CONSISTENT：在匯出時，將影響正在匯出的表的事務設為只讀，主要作用於巢狀表和分割槽表，預設為N。
CONSTRAINTS：匯出的約束條件 (Y)
OBJECT_CONSISTENT:只在物件匯出期間設定為只讀的事務處理 (N) 
FEEDBACK:每 x 行顯示進度，預設為0
FILESIZE：每個匯出檔案的最大大小
FLASHBACK_SCN：用於將會話快照設定回以前狀態的SCN
FLASHBACK_TIME：用於獲取最接近指定時間的SCN的時間
RESUMABLE：遇到空間不足時的錯誤時掛起，預設為N，需與 RESUMABLE_NAME和 RESUMABLE_TIMEOUT一起使用
RESUMABLE_NAME：用於標示哪個會話需要使用 RESUMABLE選項，格式為 User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID
RESUMABLE_TIMEOUT：RESUMABLE的等待時間，預設為7200s，如果在指定時間內未解決問題，則操作中斷
TTS_FULL_CHECK：對TTS執行完整或部分相關性檢查
TABLESPACES：要匯出的表空間列表，示例如下
exp "'/ as sysdba'" file=t_ts.dmp tablespaces=(users,example)
TRANSPORT_TABLESPACE 匯出可傳輸的表空間後設資料 (N)
直接備份到磁帶上
exp icdmain/icd rows=y indexes=n compress=n buffer=65536 feedback=100000 file=/dev/rmt0 log=exp.log tables=(tab1,tab2,tab3)
注：在磁碟空間允許的情況下，應先備份到本地伺服器，然後再複製到磁帶。出於速度方面的考慮，儘量不要直接備份到磁帶裝置
imp
imp的引數和exp的大致相同，下面是常用引數的解釋，與exp相同的這就不再贅述
ignore：Oracle在恢復資料的過程中，當匯入某個表時，該表已經存在，就要根據ignore引數的設定來決定如何操作。若 ignore=y，Oracle不執行CREATE TABLE語句，直接將資料插入到表中，如果插入的記錄違背了約束條件，比如主鍵約束，唯一索引等，則出錯的記錄不會插入，但合法的記錄會新增到表中。若 ignore=n，Oracle不執行CREATE TABLE語句，同時也不會將資料插入到表中，而是忽略該表的錯誤，繼續匯入下一個表。 ?
注意：如果表中的欄位並沒有唯一性約束，那麼在使用ignore=y的情況下很有可能插入重複資料。
indexes：在恢復資料的過程中，若indexes=n，則表上的索引不會被恢復，但是對 LOB 索引, OID索引和 主鍵索引等系統自動生成的索引將無條件恢復。
indexfile：不進行匯入操作而是將建立物件的文字儲存到檔案中，可以透過編輯使用該文字檔案建立資料庫物件。
fromuser,touser:這兩個引數可以組合使用，也可以分開使用。他們可以實現將源使用者的物件資料，匯入到目標使用者schema底下的功能。這裡要注意，匯入時的使用者需要有imp_full_database角色，示例如下

匯入一個或一組指定使用者所屬的全部物件
$imp system/manager file=full_all.dmp log=seapark  fromuser=hr
$imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)

將一個或一組指定使用者所屬的全部物件匯入到另一個使用者下
$imp hr/hr fromuser=hr touser=czm file=hr_all.dmp
$imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)
commit：預設值為 COMMIT=N，及在沒插入玩一個物件後提交。 當COMMIT=Y時候是根據你BUFFER的大小決定每次提交的數量。對於包含了LONG、RAW、 DATE等型別的表，不論BUFFER設定多大，都是每插入一行進行提交。設定commit=y可以防止減少回滾段的壓力，但由於頻繁提交，會帶來效能 上的影響，推薦使用COMMIT=N。
不同版本的EXP/IMP問題
一般來說，從低版本匯入到高版本問題不大，麻煩的是將高版本的資料匯入到低版本中，在Oracle9i之前，不同版本Oracle之間的EXP/IMP可以透過下面的方法來解決：
1、在高版本資料庫上執行低版本的catexp.sql；
2、使用低版本的EXP來匯出高版本的資料；
3、使用低版本的IMP將資料庫匯入到底版本資料庫中；
4、在高版本資料庫上重新執行高版本的catexp.sql指令碼。
但在9i中，上面的方法並不能解決問題。如果直接使用底版本EXP/IMP會出現如下錯誤：
EXP-00008: ORACLE error %lu encountered
ORA-00904: invalid column name
這已經是一個公佈的BUG，需要等到Oracle10.0才能解決，BUG號為2261，你可以到METALINK上去檢視有關此BUG的詳細資訊。
BUG歸BUG，我們的工作還是要做，在沒有Oracle的支援之前，我們就自己解決。在Oracle9i中執行下面的SQL重建exu81rls檢視即可。
CREATE OR REPLACE view exu81rls
 (objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)
 AS select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
 decode(bitand(r.stmt_type,1), 0,'', 'SELECT,')
 || decode(bitand(r.stmt_type,2), 0,'', 'INSERT,')
 || decode(bitand(r.stmt_type,4), 0,'', 'UPDATE,')
 || decode(bitand(r.stmt_type,8), 0,'', 'DELETE,'),
 r.check_opt, r.enable_flag,
 DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)
 from user$ u, obj$ o, rls$ r
 where u.user# = o.owner#
 and r.obj# = o.obj#
 and (uid = 0 or
 uid = o.owner# or
 exists ( select * from session_roles where role='SELECT_CATALOG_ROLE')
 )
 /
 grant select on sys.exu81rls to public;
 /
常見問題及解決方法
資料庫物件已經存在
一 般情況, 匯入資料前應該徹底刪除目標資料下的表, 序列, 函式/過程,觸發器等；資料庫物件已經存在, 按預設的imp引數, 則會匯入失敗。如果用了引數ignore=y, 會把exp檔案內的資料內容匯入，如果表有唯一關鍵字的約束條件, 不合條件將不被匯入如果表沒有唯一關鍵字的約束條件, 將引起記錄重複
字符集轉換
資料庫物件有主外來鍵約束
不符合主外來鍵約束時, 資料會匯入失敗,
解決辦法:
1、先匯入主表, 再匯入依存表
2、disable目標匯入物件的主外來鍵約束, 匯入資料後, 再enable它們
匯入大表時儲存分配失敗
預設的EXP時, compress = Y, 也就是把所有的資料壓縮在一個資料塊上。匯入時, 如果不存在連續一個大資料塊, 則會匯入失敗. 匯出80M以上的大表時, 記得compress= N, 則不會引起這種錯誤.
imp和exp使用的字符集不同
如果字符集不同, 匯入會失敗, 可以改變unix環境變數或者NT登錄檔裡NLS_LANG相關資訊。匯入完成後再改回來.