您好,登錄后才能下訂單哦!
這篇文章主要講解了“expdpnf導出問題分析”,文中的講解內(nèi)容簡單清晰,易于學習與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學習“expdpnf導出問題分析”吧!
中止僵死的expdp任務(wù)
--dba_datapump_jobs查找數(shù)據(jù)泵作業(yè)
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT EXPDP_20051121 EXPORT SCHEMA EXECUTING 1
SCOTT SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0
SCOTT SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0
--查找數(shù)據(jù)泵的master表
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
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID 85283 TABLE SCOTT.EXPDP_20051121
VALID 85215 TABLE SCOTT.SYS_EXPORT_TABLE_02
VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01
--刪除master表
DROP TABLE scott.sys_export_table_02;
--對于啟用了recyclebin的系統(tǒng),需要額外運行
purge dba_recyclebin;
-------------正常中止expdp作業(yè)---------------------------
--查到j(luò)ob_name
select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------------------------------------ ------------------------------ ----------
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 1 3
--attach該任務(wù)
expdp system/sys attach=SYS_EXPORT_FULL_01
Export> stop_job
Export> KILL_JOB
修改正在執(zhí)行導入操作的impdp并行度,提高導入效率
impdp uname/password attach=SYS_IMPORT_SCHEMA_01 ---回車,可以查看該任務(wù)的詳細情況
rac expdp導出時報錯:ORA-31693、ORA-31617、ORA-19505、ORA-27037
ac expdp導出時報錯:
ORA-31693: Table data object "scott"."dept" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/home/oraclerac/dump_dir/oravs_expdp_05.dmp" for write
ORA-19505: failed to identify file "/home/oraclerac/dump_dir/oravs_expdp_05.dmp"
ORA-27037: unable to obtain file status
錯誤原因:
在RAC環(huán)境下如果PARALLEL參數(shù)設(shè)置大于1,進程會在RAC的各個節(jié)點上進行導出。如果導出目錄不共享的話就會報錯
解決方法:
1、設(shè)置為共享的directory;
2、導出語句添加cluster=n選項,指定只在本地導出;
分區(qū)表數(shù)據(jù)泵導入太慢,達不到客戶的遷移要求
導出語句如下:(10G單節(jié)點)
userid='/ as sysdba'
directory=milk_dir
dumpfile=mon_%U.dmp
logfile=0828.log
schemas=mon
parallel=8
導入語句(11G的rac):
userid='/ as sysdba'
directory=milk_dir
dumpfile=mon_%U.dmp
logfile=0828.log
parallel=8
CLUSTER=N
整個過程導出花費了3個小時不到,導入花費了近10個小時。這個過程實在是太慢,,,,, 在整個導入過程中,通過實時刷新日志,發(fā)現(xiàn)一到分區(qū)表的分區(qū)就賊慢,就算是一個分區(qū)只有22條數(shù)據(jù)。也要2分鐘刷新一次,相當于兩分鐘導入了160條數(shù)據(jù),這速度,,,,,,,,,,,不行,,,,不行,,,,,
從新優(yōu)化導入的parfile:
userid='/ as sysdba'
directory=milk_dir
dumpfile=mon_%U.dmp
logfile=0828.log
parallel=8
ACCESS_METHOD=DIRECT_PATH
CLUSTER=N
導入速度直接從花費10小時降為兩小時,符合遷移的效率
ACCESS_METHOD=DIRECT_PATH
官方的解釋:
use ACCESS_METHOD=DIRECT_PATH to import the data, as this access method does not check the table metadata before import
導出含clob字段的表數(shù)據(jù),報快照過舊,無法導出
該問題是因為含clob字段表數(shù)據(jù)過大導致,需要修改數(shù)據(jù)庫閃回參數(shù)解決
查看undo表空間大小是否足夠,undo_retention參數(shù)是否設(shè)置太小,一般跟undo空間無關(guān),只需修改參數(shù)即可
目前解決辦法有兩個,加大retention或者使用pctversion
另外,對于使用MSSM表空間的表,只有pctverion可用,lob retention不可用
1、加大retention
查看undo參數(shù)
SQL> show parameter undo
--修改undo閃回參數(shù)
alter system set undo_retention=7200 scope=both;
通過查詢oracle官網(wǎng),oracle的lob大字段有自己的retention參數(shù),如果只調(diào)整undo_retention,而沒有同步到lob大字段,該參數(shù)還是默認的900s,確認查詢結(jié)果如下:
select table_name,column_name,pctversion,retention from dba_lobs where table_name='T_PATIENT_DOC_COLB';
select table_name,column_name,pctversion,retention from dba_lobs where table_name='T_PATIENTS_DOC_CA';
select table_name,column_name,pctversion,retention from dba_lobs where table_name='T_CARE_DOC';
修改表含有l(wèi)ob大字段的retention設(shè)置
ALTER TABLE T_PATIENT_DOC_COLB MODIFY LOB(CONTENT)(retention);
ALTER TABLE T_PATIENTS_DOC_CA MODIFY LOB(SIGN)(retention);
ALTER TABLE T_PATIENTS_DOC_CA MODIFY LOB(TIMESTAMP)(retention);
ALTER TABLE T_CARE_DOC MODIFY LOB(CARE_DOC)(retention);
2、使用pctversion
從結(jié)果中可以看到,當顯示指定pctversion的時候,retention參數(shù)會失效
SQL> alter table T_PATIENTS_DOC_CA modify lob(SIGN) (pctversion 10);
SQL> select column_name, pctversion, retention from user_lobs where table_name = 'T_PATIENTS_DOC_CA';
COLUMN_NAME PCTVERSION RETENTION
------------------------------ --------- ----------
SIGN 10
TIMESTAMP 7200
感謝各位的閱讀,以上就是“expdpnf導出問題分析”的內(nèi)容了,經(jīng)過本文的學習后,相信大家對expdpnf導出問題分析這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是億速云,小編將為大家推送更多相關(guān)知識點的文章,歡迎關(guān)注!
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。