12c 資料泵常用功能總結

snowdba發表於2015-02-27
一. 保持伺服器端環境變數和資料庫字符集一致
SYS@cdb >select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

[oracle@snow ~]$ echo $LANG
en_US.UTF-8

如果需要調整環境變數可以使用下面的命令
[oracle@snow ~]$ export LANG=AMERICAN_AMERICA.AL32UTF8

二. 建立作業的準備工作
1, 目錄
儘量不要使用ORACLE_HOME掛載點，防止匯出檔案過大導致掛載點空間不足使資料庫掛起。
create or replace dp_dir as ‘/oradump’;

select owner,directory_name,directory_path from dba_directories;

grant read,write on directory dp_dir to dp

2, pdb所需環境
1，登入cdb，檢視pdb。如果此時pdb1是mount狀態可以切換到pdb1下執行alter database open命令和普通資料庫一樣。或者使用alter pluggable database all open開啟所有的pdb。

SYS@cdb >show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO

2，切換到pdb1
SYS@cdb >alter session set container=pdb1;

3，單獨建立一個dba許可權的資料泵使用者
SYS@cdb >grant dba to dp identified by dp;

4，建立一個資料泵目錄dp_dir，路徑為oracle家目錄
SYS@cdb >create or replace directory dp_dir as '/home/oracle';

5，設定tnsnames.ora，增加pdb1的連結。HOST按照自己主機的地址新增，SERVICE_NAME為pdb的示例名，這裡為pdb1
[oracle@snow ~]$ cd $ORACLE_HOME/network/admin
[oracle@snow admin]$ cat tnsnames.ora
pdb1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)

呼叫方法為：expdp dp/dp@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1.log schemas=hr

三. 引數檔案的使用
某些資料泵命令含有引號，比如ogg表級別同步時經常用到的FLASHBACK_TIME或者使用sys使用者的/as sysdba字據。當命令中含有引號時，最好使用引數檔案。

[oracle@snow ~]$ vi hrexp.par
userid="/ as sysdba"
directory=dp_dir
dumpfile=hr.dmp
logfile=hr.log
schemas=hr

$ expdp parfile=hrexp.par

四. OGG表級初始化需要注意的一致性問題

1, 取得當前的scn
SYS@ora12c >select current_scn from v$database;

CURRENT_SCN
-----------
2096937

2，使用FLASHBACK_SCN引數一致性匯出資料
由於語句比較長，在employees 後面採用了換行符號\，注意employee與\之間有空格。下一行的>是回車後自動生成的，表示後面的內容和前面的是一行的。
[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=20150226.dmp tables=hr.employees \
> flashback_scn=2096937

3，使用FLASHBACK_TIME引數一致性匯出資料
在這裡建議使用引數檔案的方式，這樣會省去很多轉移符

[oracle@snow ~]$ vi flashback.par
userid=dp/dp
directory=dp_dir
dumpfile=20150226_1.dmp
logfile=20150226_1.log
tables=hr.employees
flashback_time="to_timestamp('2015-02-09 16:00:00','yyyy-mm-dd hh24:mi:ss')"

[oracle@snow ~]$ expdp parfile=flashback.par

五. 匯出資料庫後設資料
[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=tbs.dmp full=y include=tablespace content=metadata_only

[oracle@snow ~]$ impdp dp/dp@pdb1 directory=dp_dir dumpfile=tbs.dmp sqlfile=tbs.sql content=metadata_only

如果expdp中include=table，在到處作業時資料泵會匯出所有相關依存物件，比如與表相關的index，constrain,triggerexpdp dp/dp@pdb1 directory=dp_dir dumpfile=tbs.dmp full=y include=table content=metadata_only

看看生成的sql語句全貌
[oracle@snow ~]$ more tbsp.sql

單個物件的提取DDL可以使用dbms_metadata.get_ddl
SYS@cdb >alter session set container=pdb1;
SYS@cdb >set long 9999
SYS@cdb >set pages 100
SYS@cdb >select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') from dual;

六. include/exclude
include: 我們只需要的內容
include=tablespace：只對表空間作業，其它的表、索引、統計資訊等等都不關心。

exclude: 我們不想要的內容
exclude=index: 作業中不包含index，其它的都保留。

七. remap異構複製
有時候需要將使用者及其物件和資料移動到其他資料庫時候，可能會需要重新命名。這時候可以採用資料泵中remap_schema引數來處理。

匯出schema為HR的所有內容
[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=hr.dmp logfile=hr.log schemas=hr

匯入schema為HR的所有內容，並且重名為hr_new。 如果此時資料庫中沒有hr_new使用者，資料泵會自動建立。
[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new

如果只需要後設資料，並不需要資料可以使用content=metadata_only引數來實現
[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new_2 content=metadata_only

八. 估算作業大小
使用estimate_only=y來計算匯出檔案的大小。這裡可以估算全庫full=y，可以估算schemas，也可以估算某些表
[oracle@snow ~]$ expdp dp/dp estimate_only=y full=y logfile=n

九. 匯出檔案已經存在、匯入物件已經存在
[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=full_pdb1.dmp logfile=full_pdb1.log full=y reuse_dumpfiles=y

[oracle@snow ~]$ vi tables_imp.par
userid=dp/dp
directory=dp_dir
dumpfile=tables.dmp
logfile=tables.log
tables=
HR.REGIONS,
HR.LOCATIONS,
SH.PROMOTIONS,
SH.CUSTOMERS,
SH.COUNTRIES
table_exists_action=replace

十. 關於效能最佳化的幾個用法
1. 並行
[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=full_%U.dmp parallel=2 logfile=full.log full=y reuse_dumpfiles=y

Oracle建議並行度不要大於伺服器上CPU數量的兩倍，這一點和rman的通道不同，rman的每一個通道基本上會耗盡一顆cpu（啟用壓縮）。

2. 壓縮
[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=hr_cb.dmp logfile=hr.log schemas=hr reuse_dumpfiles=y compression=all compression_algorithm=basic

壓縮比例分為：BASIC , LOW , MEDIUM, HIGH
下面的測試分別將各種壓縮引數執行了一遍，對比一下結果發現即使最小壓縮low也能節省將近50%的空間！
[oracle@snow ~]$ du -sh hr*
196K hr_ch.dmp  
220K hr_cm.dmp  
240K hr_cb.dmp  
240K hr_c.dmp   
280K hr_cl.dmp  
568K hr.dmp     

十一. 空間不足的救命稻草
如果在expdp匯出時候目錄空間不夠了可以進入互動模式，向其它目錄空間追加資料泵檔案
[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=full_%U.dmp parallel=2 logfile=full.log full=y reuse_dumpfiles=y
...
...
Ctrl+c進入互動模式
Export> add_file=:data.dmp

十二. 12c新特性
1. 將檢視匯出為表
$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=vm.dmp views_as_tables=sales_rockies

$ impdp dp/dp@pdb1 directory=dp_dir dumpfile=vm.dmp tables=sales_rockies

2. 匯入資料時開啟transform=disable_archive_logging:Y 避免生成redo日誌
[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=hrc.dmp \
> transform=disable_archive_logging:Y


十三. 監控資料泵執行狀態
開啟兩個並行通道匯出全庫資料
[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=full_%U.dmp parallel=2 logfile=full.log full=y reuse_dumpfiles=y

1. 在sqlplus中透過檢視監控
[oracle@snow ~]$ sqlplus / as sysdba

col owner_name for a10
col opration for a10
col job_name for a20
col state for a10
col operation for a20

正在執行的job名稱
SYS@cdb >select owner_name,operation,job_name,state from dba_datapump_jobs;

OWNER_NAME OPERATION JOB_NAME STATE
---------- -------------------- -------------------- ----------
DP EXPORT SYS_EXPORT_FULL_03 EXECUTING

col usernmae for a10
col process for a10
set line 120

正在執行的job sid，serial#
SYS@cdb >select sid,serial#,username,process,program from v$session s,dba_datapump_sessions d where s.saddr=d.saddr;

SID SERIAL# USERNAME PROCESS PROGRAM
---------- ---------- ------------------------------ ---------- ------------------------------------------------
272 1079 DP 85198 ude@snow.oracle.com (TNS V1-V3)
254 2395 DP 85204 oracle@snow.oracle.com (DM00)
30 339 DP 85206 oracle@snow.oracle.com (DW00)
21 1657 DP 85208 oracle@snow.oracle.com (DW01)

2. OS程式，ora_dmNN為主程式，ora_dwNN為工程式，多個並行就會有相對應的工程式
[oracle@snow ~]$ ps -ef | egrep 'ora_dm|ora_dw' | grep -v egrep
oracle 6326 1 7 01:09 ? 00:00:01 ora_dm00_cdb
oracle 6328 1 89 01:09 ? 00:00:22 ora_dw00_cdb
oracle 6330 1 93 01:09 ? 00:00:17 ora_dw01_cdb

3. 追蹤警告日誌
[oracle@snow trace]$ tail -200f alert_cdb.log
…
Mon Feb 09 20:09:17 2015
DM00 started with pid=53, OS id=85204, job DP.SYS_EXPORT_FULL_03
Mon Feb 09 20:09:18 2015

DW00 started with pid=56, OS id=85206, wid=1, job DP.SYS_EXPORT_FULL_03
Mon Feb 09 20:09:21 2015
Thread 1 cannot allocate new log, sequence 28
Private strand flush not complete
Current log# 3 seq# 27 mem# 0: /u01/app/oracle/oradata/cdb/redo03.log
Mon Feb 09 20:09:23 2015

DW01 started with pid=64, OS id=85208, wid=2, job DP.SYS_EXPORT_FULL_03
Mon Feb 09 20:09:24 2015
Thread 1 advanced to log sequence 28 (LGWR switch)
Current log# 1 seq# 28 mem# 0: /u01/app/oracle/oradata/cdb/redo01.log
Mon Feb 09 20:09:24 2015
Archived Log entry 16 added for thread 1 sequence 27 ID 0x767a0947 dest 1:

十四. 後臺執行安全第一
資料泵作業、rman作業都是比較耗時的工作，而且依賴執行命令的視窗。如果視窗被關閉，作業很可能就掛掉了，所以最好使用後臺執行方式，並且透過tail日誌來跟蹤輸出。當然這樣做就不能使用互動方式來中斷資料泵了。

後臺執行方式
nohup expdp \'/ as sysdba\' directory=dp_dir dumpfile=para_%u.dmp parallel=5 schemas=scott; &

資料泵是DBA經常使用的一個工具，總結一下記錄下來以備不時之需。

全文完！










來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1443034/，如需轉載，請註明出處，否則將追究法律責任。

相關文章