Home » RDBMS Server » Server Utilities » IMPDP got hanged (windows 2012, oracle 12c)
IMPDP got hanged [message #639116] Wed, 01 July 2015 23:37 Go to next message
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 #639117 is a reply to message #639116] Wed, 01 July 2015 23:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
What is the size of the dump file? How long did you actually wait patiently before deciding that the process has hanged?
Re: IMPDP got hanged [message #639119 is a reply to message #639117] Thu, 02 July 2015 00:13 Go to previous messageGo to next message
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 #639121 is a reply to message #639116] Thu, 02 July 2015 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The output for the CONSISTENT_GETS column getting increased.


So import is not hanging, it is working.

Post the result of:
Select s.inst_id||':'||s.sid Sid, dp.job_name, s.seconds_in_wait, s.event
from dba_datapump_sessions dp, gv$session s
where s.saddr = dp.saddr
  and s.inst_id = dp.inst_id
  and dp.owner_name = ...
  and dp.job_name = ...
order by 1
/

Re: IMPDP got hanged [message #639123 is a reply to message #639119] Thu, 02 July 2015 00:19 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Lalit,

And the issue started at the initial stage only.
That is while importing GRANT.

Regards
Muktha
Re: IMPDP got hanged [message #639125 is a reply to message #639123] Thu, 02 July 2015 00:23 Go to previous messageGo to next message
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 #639127 is a reply to message #639125] Thu, 02 July 2015 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

CB lock s CBAC master lock internally used during synchronization of CBAC (Code Based Access Control) roles that is roles granted to procedures.
This can happen in several cases, we have to know what is executing the waiting session:
select q.sql_text 
from gv$session s, gv$sql q
where ( q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id )
  and s.inst_id = 1 and s.sid = 16
/


Re: IMPDP got hanged [message #639128 is a reply to message #639123] Thu, 02 July 2015 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

muktha_22 wrote on Thu, 02 July 2015 07:19
Hi 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 Go to previous messageGo to next message
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 #639131 is a reply to message #639130] Thu, 02 July 2015 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have you still the same output from the first query?
Check which procedures are associated to a role (ALL_CODE_ROLE_PRIVS) both in source (for schema A,B,C) and target (all schemas) databases.
I have no 12c so I will not be able to help further, maybe someone else will.

Re: IMPDP got hanged [message #639132 is a reply to message #639130] Thu, 02 July 2015 01:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #639144 is a reply to message #639134] Thu, 02 July 2015 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use DBA view, is this the result for both databases?

Re: IMPDP got hanged [message #639145 is a reply to message #639144] Thu, 02 July 2015 03:20 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Michel,

What Both DB?

In the source DB export made successfully.

Problem is only in IMPORT.

Regards
Muktha
Re: IMPDP got hanged [message #639147 is a reply to message #639145] Thu, 02 July 2015 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes but information is needed from both databases: source and target, the data and dump file are from source, aren't they?

Re: IMPDP got hanged [message #639150 is a reply to message #639147] Thu, 02 July 2015 04:31 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Michel,

Yes, in both the source and target DB are showing blank output for "ALL_CODE_ROLE_PRIVS".

Regards
Muktha
Re: IMPDP got hanged [message #639151 is a reply to message #639150] Thu, 02 July 2015 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again use DBA view.

Re: IMPDP got hanged [message #639153 is a reply to message #639151] Thu, 02 July 2015 05:28 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Michel,

Yes I tested with "DBA_CODE_ROLE_PRIVS".
But the result is same.

Regards
Binoj
Re: IMPDP got hanged [message #639155 is a reply to message #639153] Thu, 02 July 2015 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Execute impdp with sqlfile and post the file.

Re: IMPDP got hanged [message #639324 is a reply to message #639155] Mon, 06 July 2015 04:55 Go to previous message
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
Previous Topic: Expdb with parallel parameter writes files one at a time
Next Topic: Datapump Import Parallel not effect in Index Creation
Goto Forum:
  


Current Time: Thu Mar 28 08:55:43 CDT 2024