前言
本文主要介紹了關(guān)于expdp任務(wù)異常處理的相關(guān)內(nèi)容，分享出來供大家參考學習，下面話不多說了，來一起看看詳細的介紹吧
環(huán)境：AIX 6.1 + Oracle 10.2.0.4
現(xiàn)象：在XTTS遷移測試階段，遇到執(zhí)行幾個expdp的導出任務(wù)，遲遲沒有返回任何信息，對應(yīng)日志無任何輸出，查看任務(wù)狀態(tài)：
SQL>
set lines 300
col OWNER_NAME for a10
col OPERATION for a15
col JOB_MODE for a20
col STATE for a15
select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ --------------- -------------------- --------------- ---------- ----------------- -----------------
SYS SYS_EXPORT_TRANSPORTABLE_01 EXPORT TRANSPORTABLE DEFINING 1 0 1
SYS SYS_EXPORT_TRANSPORTABLE_02 EXPORT TRANSPORTABLE DEFINING 1 1 2
SYS SYS_EXPORT_TRANSPORTABLE_03 EXPORT TRANSPORTABLE DEFINING 1 1 2
SYS SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA DEFINING 1 1 2
SYS SYS_EXPORT_TRANSPORTABLE_04 EXPORT TRANSPORTABLE DEFINING 1 1 2
SYS SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA DEFINING 1 1 2
6 rows selected.
可以看到所有的expdp導出任務(wù)的STATE都停留在DEFINING狀態(tài)。
1.牛刀小試清異常
先強制殺掉后臺執(zhí)行的所有expdp任務(wù)：
ps -ef|grep expdp|grep -v grep|awk '{print $2}'|xargs kill -9
然后嘗試刪除這些表（其實應(yīng)該在not running狀態(tài)下刪除）
select 'drop table '||OWNER_NAME||'.'||JOB_NAME||' purge;' from dba_datapump_jobs where STATE='NOT RUNNING';
drop table sys.SYS_EXPORT_TRANSPORTABLE_01 purge;
..
可這樣是沒有作用的，查詢結(jié)果不變。
甚至嘗試正常shutdown immediate停止數(shù)據(jù)庫，也無法成功，告警日志看到有活動調(diào)用：
Thu Nov 1 15:14:24 2018
Active call for process 4522064 user 'oracle' program 'oracle@localhost (DM00)'
Active call for process 4456536 user 'oracle' program 'oracle@localhost (DM01)'
Active call for process 10027180 user 'oracle' program 'oracle@localhost (DM02)'
Active call for process 7340140 user 'oracle' program 'oracle@localhost (DM03)'
Active call for process 6291888 user 'oracle' program 'oracle@localhost (DM04)'
Active call for process 8126596 user 'oracle' program 'oracle@localhost (DM05)'
SHUTDOWN: waiting for active calls to complete.
發(fā)現(xiàn)這些進程的id都對應(yīng)了ora_dm的進程：
$ ps -ef|grep ora_dm
oracle 4456536 1 0 17:00:09 - 0:00 ora_dm01_xxxxdb
oracle 4522064 1 0 16:50:57 - 0:00 ora_dm00_xxxxdb
oracle 7340140 1 0 14:06:07 - 0:00 ora_dm03_xxxxdb
oracle 8126596 1 0 14:35:03 - 0:00 ora_dm05_xxxxdb
oracle 10027180 1 0 13:55:08 - 0:00 ora_dm02_xxxxdb
oracle 6291888 1 0 14:31:17 - 0:00 ora_dm04_xxxxdb
oracle 7340432 8388786 0 15:22:59 pts/4 0:00 grep ora_dm
實際上，這就是expdp任務(wù)的相關(guān)進程，強制殺掉這些進程：
ps -ef|grep ora_dm|grep -v grep|awk '{print $2}'|xargs kill -9
之后數(shù)據(jù)庫關(guān)閉成功：
Thu Nov 1 15:24:37 2018
All dispatchers and shared servers shutdown
Thu Nov 1 15:24:37 2018
ALTER DATABASE CLOSE NORMAL
啟動數(shù)據(jù)庫后，再次查詢發(fā)現(xiàn)已經(jīng)成功清理：
SQL>
set lines 300
col OWNER_NAME for a10
col OPERATION for a15
col JOB_MODE for a20
col STATE for a15
select * from dba_datapump_jobs;
no rows selected
小結(jié)：數(shù)據(jù)泵任務(wù)與ora_dm進程相關(guān)；如果數(shù)據(jù)泵任務(wù)發(fā)生異常，但任務(wù)并沒有退出的情況，需要同時殺掉這類進程（殺掉后狀態(tài)就會變?yōu)镹OT RUNNING）。關(guān)庫不是必須的，只是演示此時正常關(guān)閉被阻塞的場景。這也能說明為什么要保證在NOT RUNNING狀態(tài)下才可以清理。
2.追本溯源查MOS
上面的步驟只是清理了異常的數(shù)據(jù)泵任務(wù)，但沒有解決問題，再次后臺執(zhí)行備份任務(wù)依然會重現(xiàn)故障：
nohup sh expdp_xtts.sh
$ ps -ef|grep expdp
oracle 6684914 8061208 0 15:30:07 pts/2 0:00 grep expdp
oracle 7143482 8061208 0 15:30:03 pts/2 0:00 sh expdp_xtts.sh
oracle 6685096 7143482 0 15:30:03 pts/2 0:00 expdp '/ as sysdba' parfile=expdp_xtts.par
$ ps -ef|grep ora_dm
oracle 7602308 8061208 0 15:30:10 pts/2 0:00 grep ora_dm
oracle 3997964 1 1 15:30:05 - 0:00 ora_dm00_xxxxdb
$
此時查詢dba_datapump_jobs,state依然一直是defining狀態(tài)：
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ --------------- ------------------------------ ------------------------------ ---------- ----------------- -----------------
SYS SYS_EXPORT_TRANSPORTABLE_01 EXPORT TRANSPORTABLE DEFINING 1 1 2
其他的導出任務(wù)都一樣，不再贅述。
為了方便測試，寫一個簡單的單表expdp導出，現(xiàn)象也一樣。
expdp \'/ as sysdba' directory=XTTS tables=query.test dumpfile=query_test.dmp logfile=query_test.log
根據(jù)故障現(xiàn)象，用如下關(guān)鍵字在MOS中搜索: expdp state DEFINING，匹配到文檔：
DataPump Export/Import Hangs With "DEFINING" Status When Using A Directory On NFS Filesystem (文檔 ID 2262196.1)
正好這次測試是在NFS文件系統(tǒng)上，MOS建議移動到本地文件系統(tǒng)導出。
這次再將expdp進程全部殺掉：
ps -ef|grep ora_dm|grep -v grep|awk '{print $2}'|xargs kill -9
ps -ef|grep expdp|grep -v grep|awk '{print $2}'|xargs kill -9
此時查詢dba_datapump_jobs：
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ --------------- ------------------------------ --------------- ---------- ----------------- -----------------
SYS SYS_EXPORT_TABLE_04 EXPORT TABLE NOT RUNNING 0 0 0
SYS SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0 0 0
SYS SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0 0 0
SYS SYS_EXPORT_TABLE_05 EXPORT TABLE NOT RUNNING 0 0 0
SYS SYS_EXPORT_TABLE_03 EXPORT TABLE NOT RUNNING 0 0 0
SYS SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0 0 0
SYS SYS_EXPORT_TRANSPORTABLE_01 EXPORT TRANSPORTABLE NOT RUNNING 0 0 0
7 rows selected.
清理NOT RUNNING的master表：
select 'drop table '||OWNER_NAME||'.'||JOB_NAME||' purge;' from dba_datapump_jobs where STATE='NOT RUNNING';
--執(zhí)行結(jié)果用來執(zhí)行，再次查看結(jié)果為空：
SQL> select * from dba_datapump_jobs;
no rows selected
按MOS建議，將導出任務(wù)移動到本地文件系統(tǒng)：
AIX源端導出XTTS源數(shù)據(jù)至源端/hxbak/xtts_exp目錄中，而后copy至nfs共享存儲/xtts/dmp中:
mkdir /hxbak/xtts_exp
chown oracle:dba /hxbak/xtts_exp
ls -ld /hxbak/xtts_exp
select * from dba_directories;
create or replace directory XTTS as '/hxbak/xtts_exp';
此時測試expdp任務(wù)可正常運行：
$ expdp \'/ as sysdba' directory=XTTS tables=query.test dumpfile=query_test.dmp logfile=query_test.log
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 01 November, 2018 16:03:21
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' directory=XTTS tables=query.test dumpfile=query_test.dmp logfile=query_test.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "QUERY"."TEST" 6.743 MB 72593 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/hxbak/xtts_exp/query_test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:03:57
SQL> select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ --------------- ------------------------------ --------------- ---------- ----------------- -----------------
SYS SYS_EXPORT_TABLE_01 EXPORT TABLE EXECUTING 1 1 3
再次導出其他元數(shù)據(jù)：
#expdp_xtts.sh (about 5min)
nohup sh expdp_xtts.sh
#expdp_xtts_other.sh(about 5min)
nohup sh expdp_xtts_other.sh
#expdp_tmp_table
nohup sh expdp_tmp_table01.sh
nohup sh expdp_tmp_table02.sh
nohup sh expdp_tmp_table03.sh
nohup sh expdp_tmp_table04.sh
最后將這些導出文件再移動到/xtts/dmp/下，供后續(xù)xtts測試目標端導入使用：
$ pwd
/hxbak/xtts_exp
$ cp -rp * /xtts/dmp/
目標端導入時只需要有讀這些文件的權(quán)限，即可，實際測試恢復(fù)OK。
小結(jié)：
在自己的linux環(huán)境測試過是可以直接expdp到nfs文件系統(tǒng)的，AIX看來有區(qū)別，MOS的建議也只是一個workaround，但也能滿足需求，畢竟元數(shù)據(jù)導出文件沒多大。
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- 基于oracle expdp命令的使用詳解
- expdp 中ORA-39002、ORA-39070錯誤詳解及解決辦法
