Troubleshooting of EXPDP stuck in AIX environment and expdp stuck in aix Environment
Problem: A recent user is preparing to build a test environment. Because the official database of this user is an AIX midrange computer and the test environment is an X86 Platform, data can only be exported through expdp, however, during export, it was found that EXPDP was stuck in the following position and remained unchanged for several hours:
Export: Release 11.2.0.3.0-Production on Fri Dec 5 13:06:21 2014
Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing optionsStarting "SYSTEM ". "SYS_EXPORT_FULL_01": system/******** dumpfile = dump: full20141204b _ % U_db.dmp logfile = dump: full = y exclude = PACKAGE, FUNCTION, PROCEDURE, INDEX, TABLE: "IN (select table_name from dba_tables where table_name in ('medical record graphics ', 'test image result', 'test report image') and owner = 'zlhes ') "cluster = n TRACE = 480300 Estimate in progress using BLOCKS method... processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATATotal estimation using BLOCKS method: 125.3 GBProcessing object type conflict/jsonobject type DATABASE_EXPORT/PROFILEProcessing object type DATABASE_EXPORT/SYS_USER/USERProcessing object type DATABASE_EXPORT/SCHEMA/USERProcessing object type conflict/jsonobject type conflict/GRANT/SYSTEM_GRANT/revoke object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/jsonobject type DATABASE_EXPORT//jsonobject type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCEProcessing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/jsonobject type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/CROSS_SCHEMA /jsonobject type export/DIRECTORY/DIRECTORYProcessing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/jsonobject type DATABASE_EXPORT/CONTEXTProcessing object type DATABASE_EXPORT/SCHEMA/export/jsonobject type DATABASE_EXPORT/SCHEMA/ jsonobject type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPECProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/jsonobject type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/CROSS_SCHEMA/jsonobject type DATABASE_EXPORT/ response/response/jsonobject type DATABASE_EXPORT/response/POST_SYSTEM_ACTIONS/jsonobject type DATABASE_EXPORT/SCHEMA/TABLE/TABLEProcessing object type DATABASE_EXPORT /SCHEMA/TABLE/jsonobject type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/ COMMENTProcessing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEWProcessing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/jsonobject type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/jsonobject type DATABASE_EXPORT/ SCHEMA/VIEW/COMMENTProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
View wait events
Based on the wait event, view the SQL statement executed by the session as follows:
Here we can see the statistical information of a table. However, when you execute this SQL statement, you cannot find the query result. On metalink, find the following document:
Expdp hangs on aix when exclude is used with query clause (Document 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-2012) 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.
We can see that EXCLUDE = TABLE: "IN (select *) is used in expdp. The solution provided IN this document is to EXCLUDE statistics and try to modify the Export Statement, EXPDP is successfully completed by excluding statistics.
