Monday, April 24, 2017

Domain index creation hangs during datapump import

The impdp hangs while creating Domain index and never completes. User waited for 3 days but no luck. All the objects got imported except one domain index.


$ tail -f output
…..
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
When I query data_datapump_jobs , I can see that import is still running.

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SYS SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA EXECUTING 32 2 35
SAPCLD SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0 0 0
When I query v$session_longops, I can see that 48915 out of 48916 MB done and job hangs

select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, message
from v$session_longops order by start_time desc) …..

SYS_IMPORT_SCHEMA_01 48915 48916 MB 81659 SYS_IMPORT_SCHEMA_01: IMPORT : 48915 out of 48916 MB done
Solution: After research I found that there is a bug in 11.2.0.4 and applying below patch resolved the issue.
Patch 23521888: MERGE REQUEST ON TOP OF 11.2.0.4.0 FOR BUGS 20503463 16683112


Reference: Please find couple of datapump performance Bugs in 11.2.0.4
============================================================
For 11.2.0.4
EXPDP Performance Bugs:
MLR Patch 21443197 released on top of 11.2.0.4 contains the fixes for the bugs: 18082965 18469379 18793246 20236523 19674521 20532904 20548904

IMPDP Performance Bugs:
- Bug 19520061 - IMPDP: EXTREMELY SLOW IMPORT FOR A PARTITIONED TABLE
- Bug 13609098 - IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW >>>>> This patch is already in lsinventory

Bug 21128593 : UPDATING THE MASTER TABLE AT THE END OF DP JOB IS SLOW STARTING WITH 12.1.0.2>> But patch is already in lsinventory
Posted by at
Labels: , , ,

11 comments:

  1. UnknownMay 19, 2017 at 12:24 AM

    Thanks for publishing such useful information. madalin stunt cars 2

    ReplyDelete
  2. UnknownSeptember 8, 2017 at 11:55 PM

    I really like following your blog as the articles are so simple to read and follow. survival games Excellent. Please keep up the good work. Thanks.

    ReplyDelete
  3. balexarOctober 1, 2018 at 3:13 AM

    A great website with interesting material

    Tina

    ReplyDelete
  4. adam cleanFebruary 16, 2020 at 11:00 PM

    من اجود وارخص الشركات التي توجد في منطقة مكة المكرمة والتي تعمل في مجال نقل العفش وتقدم خدمات جيدة وهي افضل شركة نقل العفش بجده التي تختص بنقل العفش من بيت الى بيت آخر في مدينة جدة وما جاورها من مناطق تابعة لها وقد نضطر قبل نقل العفش الى تنظيف المنزل الجديد قبل النقل من الداخل ومن الخارج وذلك بالتواصل مع افضل شركة تنظيف منازل بجدة تختص بأعمال التنظيف للمنازل الجديدة والمفروشة من طرف شركة تنظيف كنب بجدة لكي يتم تعقيم المنزل ومن الأفضل ان تقوم بعمل مكافحة للحشرات بالتواصل مع افضل واقوى شركات مكافحة الحشرات بجدة التي تتعامل في مكافحة الحشرات وتستخدم مبيدات آمنة ومضمونة ونحتاج ايضا الى تنظيف الخزان وذلك بالتعرف على اكبر وافضل واقوى شركات تنظيف خزانات بجدة التي تقدم افضل الخدمات الجيدة في تنظيف وتعقيم الخزانات لكي تحافظ على الماء نظيفا ومعقما اطول فترة زمنية ممكنة لكي تكون مياهك نظيفة فان شركات تنظيف خزانات المياه مستعدة لاستقبال مكالمتكم في أي وقت وطلب الخدمة

    ReplyDelete
  5. مايكروسيستمNovember 15, 2020 at 11:54 AM

    - شركة نقل عفش بالاحساء
    - شركة نقل عفش بالدمام
    - شركة نقل عفش بالقطيف

    ReplyDelete
  6. Kevin HorganAugust 19, 2021 at 9:54 AM

    I am jovial you take pride in what you write. It makes you stand way out from many other writers that can not push high-quality content like you. creative business names

    ReplyDelete
  7. thanosAugust 24, 2021 at 12:15 AM

    There comes when most web clients need to get their very own domain name, and they need to discover precisely how to purchase domain names. https://onohosting.com/

    ReplyDelete
  8. thanosAugust 24, 2021 at 12:15 AM

    Similarly as with pretty much every other item or administration in the data age, domain names can be purchased on the web, as long as you have a Visa or a PayPal account. https://onohosting.com/

    ReplyDelete
  9. TabatSeptember 21, 2021 at 8:50 AM

    I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you! creative business names

    ReplyDelete
  10. Jason RoyFebruary 9, 2022 at 3:34 AM

    Provider (ISP), the association and the sort of association. Moreover, many administrations likewise incorporate a different area with geo-area data. Your IP shows the country you interface from with the most exactness and the area/city are likewise shown.https://onohosting.com/

    ReplyDelete
  11. berryhenryFebruary 15, 2022 at 2:02 AM


    Searching for more executioner site design improvement tips? Searching for more SEO tools to run your SEO tasks all the more effectively. https://onohosting.com/

    ReplyDelete

Subscribe to: Post Comments (Atom)