Home » RDBMS Server » Server Utilities » impdp takes huge time in oracle11gR2 !
impdp takes huge time in oracle11gR2 ! [message #543062] Mon, 13 February 2012 01:13 Go to next message
shipon_97
Messages: 8
Registered: March 2008
Junior Member
1) My database dump size near about 4GB , which is provided by the vendor .

2) In the dump , total objects are 364949 , where

Table : 121316
LOB object : 121315
(Normal+LOB) indexes : 122317

3) Now when I run the import using system or another user , it hangs on the below stage for 70+ hours ..

impdp ntest/ntest directory=test_dir dumpfile=JBLLIVE.31Jan2012.11.50AM.dmp remap_schema=JBLLIVE:NTEST logfile=ntest_10feb.log

Import: Release 11.2.0.1.0 - Production on Fri Feb 10 09:49:50 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "NTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "NTEST"."SYS_IMPORT_FULL_01": ntest/******** directory=test_dir dumpfile=JBLLIVE.31Jan2012.11.50AM.dmp remap_schema=JBLLIVE:NTEST logfile=ntest_10feb.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NTEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
----

In this situation I observed the worker status and see that some table and some LOB objects including LOB indexes are imported .
Worker process do it in background but it does not show in the front import log file (I dont understand why it not shows in the import logfile)
it imports one table,one LOB , one LOB index ..then again one table,one LOB , one LOB index ... in this way .
And my observation first it inserts data into the LOB tables and then it inserts into normal table . And
when it is starting to insert data to the normal table then this table's log are shown in the import logfile.


an example of our data type :

Objects :
===================================================

LOB_FD17_RGS_TSTCD2 LOB

FD17_RGS_VERSION TABLE

(here i see one table has one LOB segment, in this way 121316 table has 121316 LOB)

SQL> desc FD17_RGS_VERSION
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID VARCHAR2(255)
XMLRECORD BLOB

Our observation perhaps inserting blob mainly occures the slowness . Is there any patch or is there any bug regarding BLOB/LOB objects in oracle-11gR2

please give your expert opinions ... ....

We already contact with oracle support, but still we don't get any satisfactory result from last 7 days .
Re: impdp takes huge time in oracle11gR2 ! [message #543066 is a reply to message #543062] Mon, 13 February 2012 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 60017
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enable trace on your import session(s); see Pete Finnigan, How to set trace for others sessions, for your own session and at instance level

Quote:
Is there any patch or is there any bug regarding BLOB/LOB objects in oracle-11gR2


Search on MOS.

Quote:
but still we don't get any satisfactory result from last 7 days.


Which means?
Tell us all information you gave to Oracle and what Oracle told you.

Regards
Michel

[Updated on: Mon, 13 February 2012 01:20]

Report message to a moderator

Re: impdp takes huge time in oracle11gR2 ! [message #543077 is a reply to message #543066] Mon, 13 February 2012 01:39 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

As Michel has already mentioned, you need to enable trace or debug the export session. Check the following link:
http://www.dbi-services.com/index.php/blog/entry/how-to-debug-a-data-pump-error
Re: impdp takes huge time in oracle11gR2 ! [message #543080 is a reply to message #543066] Mon, 13 February 2012 01:46 Go to previous messageGo to next message
shipon_97
Messages: 8
Registered: March 2008
Junior Member
Thx michel ,

I already give trace files 2 times to oracle support . then again I attach here the trace files after few moments.

Oracle support is working still now , and not get proper feedback yet . But we need immediate response , so I use the ORAFAQ forum . If I know is there any mechanism to handle LOB segment/object in oralce 11gR2, then perhaps we get the solution .

The total objects are below mentioned in our prod DB :


121,316 INDEX
121,316 TABLE
3 FUNCTION
997 VIEW
1 PACKAGE BODY
121,315 LOB
1 PACKAGE


Our import takes near about 96 to 100 hours .
Re: impdp takes huge time in oracle11gR2 ! [message #543089 is a reply to message #543080] Mon, 13 February 2012 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 60017
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Tell us all information you gave to Oracle and what Oracle told you.


If Oracle can't answer with ALL information how do you expect we can answer WITHOUT them?
When I said "told us", I meant post us these information.

Regards
Michel

[Updated on: Mon, 13 February 2012 02:04]

Report message to a moderator

Re: impdp takes huge time in oracle11gR2 ! [message #543191 is a reply to message #543080] Mon, 13 February 2012 06:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10630
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
See if Metalink Note 803095.1 is applicable.

Re: impdp takes huge time in oracle11gR2 ! [message #543192 is a reply to message #543191] Mon, 13 February 2012 06:12 Go to previous message
shipon_97
Messages: 8
Registered: March 2008
Junior Member
Thx .. I will try ...
Previous Topic: Parallel EXPORT & IMPORT
Next Topic: which client do i need to use Data Pump Tools?
Goto Forum:
  


Current Time: Mon Dec 22 10:29:17 CST 2014

Total time taken to generate the page: 0.05389 seconds