Home » RDBMS Server » Server Utilities » I'm New to Oracle and need help with DataPump in Oracle 10.2.3 (Oracle 10.2.3)
icon5.gif  I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438688] Mon, 11 January 2010 17:20 Go to next message
123tech
Messages: 3
Registered: January 2010
Junior Member
I had setup datapump and it worked for about a month and then failed. I received the message 'can not create table system.export_smhca. Already in use.'

I looked on the Oracle Meta Link and it suggested to grant access to the system user.

I did and tried to export again. I received the same message. I then renamed the table using the following command.

ALTER TABLE system.export_smhca RENAME TO export_smhca1

Now when I try to run datapump, I wee the following messages.
ORA-31626: job does not exist
ORA-31637: cannot create job EXPORT_SMHCA for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20100111174418" and "KUPC$S_1_20100111174418
" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1580
ORA-04031: unable to allocate 32 bytes of shared memory ("streams pool","unknown object",
"streams pool","fixed allocation callback")

I looked up 'ORA-31637' on Meta Link.

An article suggested that I run the following command:

alter system set aq_tm_processes = 5 scope = both;

I'm not sure what aq_tm_processes are so I searched this forum and found reference that someone had problems with this value set to zero. What complications may I run into if I run the command suggested in the article and do you agree with this approach to fixing the issue. Thanks.
Re: I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438689 is a reply to message #438688] Mon, 11 January 2010 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>alter system set aq_tm_processes = 5 scope = both;

What is the current value of AQ_TM_PROCESSES?
Re: I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438690 is a reply to message #438689] Mon, 11 January 2010 17:42 Go to previous messageGo to next message
123tech
Messages: 3
Registered: January 2010
Junior Member
Hi,

When I run the following statement

select NAME, TYPE, VALUE, DISPLAY_VALUE from V$parameter WHERE NAME='aq_tm_processes'

I get the results.

aq_tm_processes,3,0,0

So It appreast the value is '0'
Re: I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438691 is a reply to message #438690] Mon, 11 January 2010 17:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I had setup datapump and it worked for about a month and then failed

What changed?
Re: I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438692 is a reply to message #438691] Mon, 11 January 2010 17:53 Go to previous messageGo to next message
123tech
Messages: 3
Registered: January 2010
Junior Member
Nothing that I know of.


The last time that it worked, it generated the following log.


;;;
Export: Release 10.2.0.3.0 - Production on Thursday, 10 December, 2009 21:00:01

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production
Starting "SYSTEM"."EXPORT_SMHCA": system/********@smhca parfile=D:\oracle\product\10.2.0\flash_recovery_area\SMHCA\DATA_PUMP_TEMP\EXPORT_PRAM.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 87.15 GB
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "AAITA"."MASTERJOBAUDIO" 23.03 GB 88321 rows
. . exported "AAITA"."DOCVERSION" 4.565 GB 159133 rows
. . exported "AAITA"."MASTERJOBFILE" 3.258 GB 88375 rows
. . exported "AAITA"."TAPLUS_ASR_JOB" 12.91 GB 10975 rows
ORA-39065: unexpected master process exception in RECEIVE
ORA-39078: unable to dequeue message for agent MCP from queue "KUPC$C_1_20091210210003"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 558
ORA-25205: the QUEUE SYS.KUPC$C_1_20091210210003 does not exist
Job "SYSTEM"."EXPORT_SMHCA" stopped due to fatal error at 00:10:59
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-39079: unable to enqueue message DG,KUPC$C_1_20091210210003,KUPC$A_1_20091210210007,MCP,29,Y
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 913
ORA-04031: unable to allocate 32 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
ORA-06512: at "SYS.KUPW$WORKER", line 1342
ORA-06512: at line 2



One error shows that it prematurely exited. While researching this, Meta Link suggest that it may be a bug. But instead of running a patch, which I haven't done before, I was hopeing to find a quick workaround to get this working again.
Re: I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438719 is a reply to message #438692] Mon, 11 January 2010 23:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
any PARALLEL specified in your Par file ?

See this...

sriram Smile
Re: I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438755 is a reply to message #438692] Tue, 12 January 2010 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
unable to allocate 32 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

Enlarge streams pool (streams_pool_size parameter) but do not set it to 0.

Regards
Michel

[Edit: add missing import word: "not"]

[Updated on: Tue, 12 January 2010 11:47]

Report message to a moderator

Re: I'm New to Oracle and need help with DataPump in Oracle 10.2.3 [message #438855 is a reply to message #438755] Tue, 12 January 2010 11:10 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Check oracle document 457724.1
Previous Topic: Policy - function_schema is not exp/imp properly
Next Topic: expdp
Goto Forum:
  


Current Time: Fri Dec 09 03:36:29 CST 2016

Total time taken to generate the page: 0.13498 seconds