IMPDP got hanged [message #639116] |
Wed, 01 July 2015 23:37 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi ALL,
One of my IMPDP process got hanged for many hours,
Kindly guide me.
impdp ****/*** SCHEMAS=('A','B','C') DIRECTORY=*** DUMPFILE=EXPORT_30-06-2015.DMP LOGFILE=30-Jun-2015_imp.log REMAP_SCHEMA=A:A1,B:B1,C:C1
I have checked the below queries:
select * from v$sess_io where sid = (select sid from v$session where program like 'impdp%');
The output for the CONSISTENT_GETS column getting increased.
select space_used/1024/1024/1024, space_limit/1024/1024/1024 from v$recovery_file_dest;
The column for space_used is zero and space_limit column is 6GB.
Thanks and Regards
Muktha
|
|
|
|
Re: IMPDP got hanged [message #639119 is a reply to message #639117] |
Thu, 02 July 2015 00:13 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi Lalit,
It was FULL DB EXPORT and the size was 140GB.
I am trying to Import only 4 schemas.
While leaving office yesterday, I started IMPORT, today morning I realized the issue.
Thanks and Regards
Muktha
|
|
|
|
|
Re: IMPDP got hanged [message #639125 is a reply to message #639123] |
Thu, 02 July 2015 00:23 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
The output is below.
SID JOB_NAME SECONDS_IN_WAIT EVENT
------ -------------------- --------------- -------------------------------------------
1:16 SYS_IMPORT_SCHEMA_05 58227 enq: CB - role operation
1:1911 SYS_IMPORT_SCHEMA_05 2 wait for unread message on broadcast channel
1:3326 SYS_IMPORT_SCHEMA_05 1 wait for unread message on broadcast channel
|
|
|
|
Re: IMPDP got hanged [message #639128 is a reply to message #639123] |
Thu, 02 July 2015 01:07 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
muktha_22 wrote on Thu, 02 July 2015 07:19Hi Lalit,
And the issue started at the initial stage only.
That is while importing GRANT.
Regards
Muktha
Ah! I didn't see this post.
So it is most likely a role that is granted to procedures/functions/packages and this later is currently somewhere.
The previous query will give you which role, from this you will get which procedures are involved and then you can check who used it.
Are you in RAC?
[Updated on: Thu, 02 July 2015 01:14] Report message to a moderator
|
|
|
Re: IMPDP got hanged [message #639130 is a reply to message #639128] |
Thu, 02 July 2015 01:23 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi,
We are not using RAC,
The output for the query is below.
SQL_TEXT
---------
SELECT script FROM sys.metaxsl$ WHERE xmltag=:1 AND transform=:2 AND model=:3
SELECT script FROM sys.metaxsl$ WHERE xmltag=:1 AND transform=:2 AND model=:3
Regards
Muktha
|
|
|
|
Re: IMPDP got hanged [message #639132 is a reply to message #639130] |
Thu, 02 July 2015 01:33 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
At times I have seen import slower with high CPU usage and less memory. Could you have a look at CPU usage and memory? If it's too high, you could stop few services for the time being until import completes.
|
|
|
Re: IMPDP got hanged [message #639134 is a reply to message #639132] |
Thu, 02 July 2015 01:49 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi Lalit,
The CPU usage graph is till 4%
And the Memory usage graph is till 38% only.
Hi Michel,
The output for the query "ALL_CODE_ROLE_PRIVS" is blank.
And the output for the first query is same as previous. Only the column SECONDS_IN_WAIT get changed.
Regards
Muktha
|
|
|
|
|
|
|
|
|
|
Re: IMPDP got hanged [message #639324 is a reply to message #639155] |
Mon, 06 July 2015 04:55 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
Sorry, we are not allowed to upload any files.
And it was in an UAT server, once we restarted the DB, the problem got resolved.
Thanks and Regards
Muktha
|
|
|