| impdp takes huge time in oracle11gR2 ! [message #543062] |
Mon, 13 February 2012 01:13  |
shipon_97
Messages: 17
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 #543080 is a reply to message #543066] |
Mon, 13 February 2012 01:46  |
shipon_97
Messages: 17
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 .
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
