您當(dāng)前位置：首頁(yè) > 數(shù)據(jù)庫(kù) > 數(shù)據(jù)庫(kù)應(yīng)用 > AIX環(huán)境下EXPDP卡住問(wèn)題處理

AIX環(huán)境下EXPDP卡住問(wèn)題處理

來(lái)源：程序員人生發(fā)布時(shí)間：2014-12-08 08:52:06 閱讀次數(shù)：6937次

問(wèn)題現(xiàn)象：

最近1用戶準(zhǔn)備搭建測(cè)試環(huán)境，由于該用戶正式庫(kù)是AIX小機(jī)，測(cè)試環(huán)境是X86平臺(tái)，因此只能通過(guò)expdp進(jìn)行數(shù)據(jù)導(dǎo)出，但是在導(dǎo)出的時(shí)候，發(fā)現(xiàn)EXPDP1直卡在以下位置,幾個(gè)小時(shí)都不動(dòng)彈：

Export: Release 11.2.0.3.0 - Production on Fri Dec 5 13:06:21 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=dump:full20141204b_%U_db.dmp logfile=dump:full_expdp1204b_db.log full=y exclude=PACKAGE,FUNCTION,PROCEDURE,INDEX,TABLE:"IN (select table_name from

dba_tables where table_name in ('電子病歷圖形','檢驗(yàn)圖象結(jié)果','檢驗(yàn)報(bào)告圖象') and owner='ZLHIS')" cluster=n TRACE=480300

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 125.3 GB

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/ROLE

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/CROSS_SCHEMA/OBJECT_GRANT

Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/CONTEXT

Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/CROSS_SCHEMA/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY

查看等待事件

根據(jù)等待事件，查看該會(huì)話履行的SQL語(yǔ)句以下：

這里可以看到有個(gè)表統(tǒng)計(jì)信息的信息，但是履行該SQL語(yǔ)句，發(fā)現(xiàn)沒法查詢出結(jié)果，上metalink上，找到1篇以下文檔：

EXPDP HANGS ON AIX WHEN EXCLUDE IS USED WITH QUERY CLAUSE (文檔 ID 1513238.1)

YMPTOMS

This article is specifically written for EXPDP/HANG on AIX platforms. If the HANG is seen on WIN/Linux then this article is not likely to assist

and if seen on any other platform it is worth looking at the symptoms to see if they match, if they do then there is no adverse impact

in applying the advised solution to see if it resolves.

The following symptoms are relevant :-

a) EXPDP at FULL or SCHEMA level gets beyond the 'Total estimation using BLOCKS method' phase and then seems to hang

b) The INCLUDE/EXCLUDE option is being used with a QUERY clause e.g.

INCLUDE=TABLE:"not in (select do.object_name from dba_objects do where do.object_name = 'DUAL')"

EXCLUDE=TABLE:"IN (select table_name from all_tables where ((table_name like 'XN_%' ) or (table_name like 'TR_%' ))) "

c) If in addition to the EXLUDE in (B) we also use EXCLUDE=STATISTICS the EXPDP no longer hangs and runs to completion.

SOLUTION

To implement a solution for unpublished Bug:14095143, please execute any of the below alternative solutions:

Upgrade to 12.1 when it will become available

OR

Apply patchset release 11.2.0.4 when it becomes available (not available as of time of publishing this article: DEC⑵012) in which Bug:14095143 is fixed.

OR

Download and apply interim Patch:14095143, if available for your platform and RDBMS release. To check for conflicting patches, please use the MOS Patch Planner Tool. If no patch is available, file a Service Request through My Oracle Support for your specific Oracle version and platform. An AIX oneoff fix is made for 11203

OR
Use the workaround of specifying the EXCLUDE=STATISTICS command line option for EXPDP.

可以看到，我們確切EXPDP中使用了EXCLUDE=TABLE:"IN (select * ) 這類類似的排除語(yǔ)句，文檔給出的解決方法是排除統(tǒng)計(jì)信息，嘗試修改導(dǎo)出語(yǔ)句，排除統(tǒng)計(jì)信息，EXPDP順利完成。

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
程序員人生