Oracle Export/Import DB(含Oracle Data Pump)

Oracle Export/Import DB(含Oracle Data Pump)

標籤: 

Export/Import DB

1.        確認空間大小 
2.        export tablespace,並確認是否要更改路徑 
3.        create user/grant resource 
4.        export data 
5.        import data 

範列二: 
Oracle Data Pump 

範列一:export production spc data to test spcdb 
一、確認空間大小,空間約23+14+14+15+4.4G=70.4G 
 

二、export tablespace 
 
確認要匯出的Tablespace, 可以透過滑鼠右鍵,Create Script的方式產生 
 
Copy 到Clipboard,並貼到記事本上做編輯 

 
修改需要存放的tablespace路徑 
PS.存放路徑的最後面, maxsize 32000M後沒有",",從 toad出來的資料有",",請把它拿掉 

三、Create User & Grant 
 
把我們所需要的權限,copy 到test db 

四、export data 
方法一、 

[root@C5TSPCDB1 oracle]# cat exp.sh 
exp spc/spc@c5pspc parfile=exp.par log=impspc.log compress=n
[root@C5TSPCDB1 oracle]# cat exp.par 
file=/home/oracle/spc.dmp 
statistics=none 
tables= 

R3_ARRAY_PDS_SAMPLE_ATTREX, 
PDSPRE_PROCESSSTEP_TEST, 
CHART_DEF_FORALARMCONFIG, 
CHART_RULE_FORALARMCONFIG, 
C5_ARRAY_FILE_CHART_CRIT, 
C5_ARRAY_FILE_SAMPLE_ATTR, 
SYSTEM_CONFIG, 
FAB_DEF 

方法二: 
exp sim/sim file=sim.dmp rows=y owner=sim log=sim.log compress=n

若沒有加compress=n(Default=Yes),建立Table的initial_extent值即會等於匯出資料量的大小
影響:之後若要把oracle上的datafile縮小,無法縮小此datafile

compress此參數與壓縮完全無關…

五、Import Data 
imp spc/spc file= spc.dmp full=y 

備註: 
Create User 操作 

1. 新增帳號: CREATE USER 使用者名稱  IDENTIFIED BY 密碼;
ex: CREATE USER WFE3_SPC  IDENTIFIED BY wfe3_spc  DEFAULT TABLESPACE CONFIG;

2. 權限設定: GRANT 權限 ON 資料庫物件 TO 使用者名稱; 
                      GRANT 角色 TO 使用者名稱 
3. 取消權限: REVOKE 權限 ON 資料庫物件 FROM 使用者名稱; 
4. 新增角色: CREATE ROLE 角色名稱 [ NOT IDENTIFIED | IDENTIFIED { BY 密碼| USING [ 鋼要 .] PACKAGE | EXTERNALLY | GLOBALLY } ] ; 
5. 取消角色: DROP ROLE 角色名稱 
6. 更改密碼: ALTER USER  使用者名稱 IDENTIFIED BY 密碼; 
7. 強制變更密碼: ALTER USER 使用者名稱 PASSWORD EXPIRE; 
8. 鎖住現有的使用者: ALTER USER 使用者名稱 ACCOUNT LOCK; 
9. 解鎖現有的使用者: ALTER USER 使用者名稱 ACCOUNT UNLOCK; 
10. 刪除現有的使用者: DROP USER 使用者名稱; 

高速的資料匯出/匯入:Oracle Data Pump 

1.確認備份的User是否有CREATE ANY DIRECTORY的權限 

SQL> select * from user_sys_privs;

 

 
假設沒有權限: 
grant create any directory to scott; 

2.建立Directory,在os的權限下,要選擇oracle權限的目錄(使用ORACLE DBA權限) 
Create or replace directory dir_name as '/oradata1';   
create or replace directory test  as '/oradata1'; 
查詢Directory: 
SELECT * FROM dba_directories; 

3.設定讀取權限 
grant read, write on directory TEST to scott; 
grant read, write on directory TEST to system; 

4.資料Import/Export(同一帳號import/export)
Table Exports/Imports 
若不同Schema,需要再加個remap_schema=src_account:dst_account
若不同的Tablespace(多個對應到不同的TableSpace),需要加
REMAP_TABLESPACE=src1:dst1 REMAP_TABLESPACE=src2:dst2

expdp scott/tiger@ora10g tables=SALES directory=TEST dumpfile=DP_SALES.dmp logfile=expdpDP_SALES.log 

impdp scott/tiger@ora10g tables=SALES directory=TEST dumpfile=DP_SALES.dmp logfile=impdpDP_SALES.log 

Schema Exports/Imports 

expdp scott/tiger@ora10g schemas=scott directory=TEST dumpfile=SH.dmp logfile=expdpSH.log

impdp scott/tiger@ora10g schemas=scott directory=TEST dumpfile=SH.dmp logfile=impdpSH.log 

不同帳號的話:
impdp scott/tiger@ora10g schemas=scott directory=TEST dumpfile=SH.dmp logfile=impdpSH.log remap_schema=ea:scott remap_tablespace=USERS:scott;

 

Database Exports/Imports 

expdp system/oracle@ora10g full=Y directory=TEST dumpfile=DB10G.dmp logfile=expdpDB10G.log 

impdp system/oracle@ora10g full=Y directory=TEST dumpfile=DB10G.dmp logfile=impdpDB10G.log 

5.將dump出來的資料轉換成sql file 
impdp scott/tiger directory=test dumpfile=t_user.dmp sqlfile=t_user.sql 

6.        Drop directory 
DROP DIRECTORY <directory_name>; 

7.砍掉 datapump jobs

以前用 exp 做 export 時, 如果臨時要 cancel 的話, 就把 shell script kill 掉, 然後去 v$session 看看有沒有 session 還在做 exp, 如果有的話就 alter system kill session ….. 就 OK 了, 現在用了 datapump 做 export 的話就得這樣做

SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name ;

這樣可以找出目前還在執行的 datapump, 要看的是 OWNER.OBJECT 這個欄位, 會長的像 USER.SYS_EXPORT_FULL_01

這時要終結這個 job 的話, 要下

drop table USER.SYS_EXPORT_FULL_01 purge;

這樣才能把 datapump jobs 給砍掉

參考資料: 
http://tw.myblog.yahoo.com/stevencheng-blog/article?mid=228&prev=235&next=222 

http://psoug.org/reference/directories.html

http://blog.xuite.net/misgarlic/weblogic/43337153

備註:
imp/exp 若使用不同版本,會出現錯誤,通常是高的版本匯到低的版本
IMP-00010: not a valid export file, header failed verification

IMP-00000: Import terminated

DataPump也同樣會有這個問題:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification

ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/hr.dmp"