Home » RDBMS Server » Server Utilities » Not able to run expdp (Enterprise 11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not able to run expdp [message #689874]

Sun, 16 June 2024 12:41

orHooha
Messages: 19
Registered: April 2015

Junior Member

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Export: Release 11.2.0.3.0 - Production

Hi,

We've been having a problem generating an export for a database DOCT1, on a RAC. We get errors about job resource synchronization.

There seems also to be an indication that there's 2 other jobs running, but when we run a DATAPUMP query, it mentions SYS_EXPORT_FULL_01, but says nothing is running.

We tried increasing the STREAMS_POOL_SIZE size, but we receive a common error about memory in another instance.

The other instance - we think - refers to DOCT2, which points to the same database.

These are the errors we're getting, when running a script with expdp, and when trying to set the STREAMS_POOL_SIZE.

This is on an AIX server.

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_03 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-31635: unable to establish job resource synchronization

system@DOCT>SELECT owner_name, job_name, operation, state FROM DBA_DATAPUMP_JOBS;
SYSTEM, SYS_EXPORT_FULL_01, EXPORT, NOT RUNNING

system@DOCT>ALTER SYSTEM SET STREAMS_POOL_SIZE=100m ;
ALTER SYSTEM SET STREAMS_POOL_SIZE=100m
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Report message to a moderator

Re: Not able to run expdp [message #689875 is a reply to message #689874]

Sun, 16 June 2024 13:31

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As far as I remember, in this version, all data pump jobs must run on the same instance, so you have to force all your dp jobs to run in a specific instance using SERVICE_NAME option and possibly CLUSTER=NO to prevent from spreading the workers across the instances.

Report message to a moderator

Re: Not able to run expdp [message #689876 is a reply to message #689875]

Mon, 17 June 2024 10:26

orHooha
Messages: 19
Registered: April 2015

Junior Member

I'll try that, thanks Michael.

Funny, this response didn't show up in my email notifications.

Report message to a moderator

Re: Not able to run expdp [message #689877 is a reply to message #689876]

Mon, 17 June 2024 11:08

orHooha
Messages: 19
Registered: April 2015

Junior Member

Looks like we're not using RAC resource groups. So according to the person who asked for the export, it wouldn't apply to us.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-data-pump-export-utility.html#GUID-9BCE2098-A930-4547-8758 -E3A9C2A7A20B

"You can use the SERVICE_NAME parameter with the CLUSTER=YES parameter to specify an existing service associated with a resource group that defines a set of Oracle Real Application Clusters (Oracle RAC) instances belonging to that resource group. Typically, the resource group is a subset of all the Oracle RAC instances."

Report message to a moderator

Re: Not able to run expdp [message #689878 is a reply to message #689877]

Mon, 17 June 2024 11:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Looks like we're not using RAC resource groups.

This is my suggestion: use it (with CLUSTER=YES). Smile

Smile

Alternatively you can force an instance using a network service name (tnsnames one for instance) referring a specific host/listener (and CLUSTER=NO) and not a SCAN one.

Report message to a moderator

Re: Not able to run expdp [message #689879 is a reply to message #689878]

Mon, 17 June 2024 13:51

orHooha
Messages: 19
Registered: April 2015

Junior Member

We're trying the SERVICE_NAME now, with a parameter file.

Currently, these are the last lines in the script they wrote.

...
...
parfile=$ORACLE_SID\_export.par
cat >$parfile <<!
userid=system
directory=edb_dp_dir
dumpfile=Full_export_$ORACLE_SID.dmp
logfile=Full_export_$ORACLE_SID.log
SERVICE_NAME=DOCT
CLUSTER=YES
full=y
status=60
!
#
expdp parfile=$parfile

Report message to a moderator

Re: Not able to run expdp [message #689880 is a reply to message #689879]

Mon, 17 June 2024 14:17

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Let us know if it works.

Note: I'd use REUSE_DUMPFILES=Y if you are not sure the previous dump file was removed.

Report message to a moderator

Re: Not able to run expdp [message #689881 is a reply to message #689880]

Mon, 17 June 2024 14:26

orHooha
Messages: 19
Registered: April 2015

Junior Member

Same results. I'll try your REUSE_DUMPFILES=Y next.

Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_03 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-31635: unable to establish job resource synchronization

Report message to a moderator

Re: Not able to run expdp [message #689882 is a reply to message #689881]

Mon, 17 June 2024 14:45

orHooha
Messages: 19
Registered: April 2015

Junior Member

Exactly the same result. A mystery.

Report message to a moderator

Re: Not able to run expdp [message #689883 is a reply to message #689882]

Mon, 17 June 2024 14:54

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Does SERVICE_NAME=DOCT restrict the job to a single instance?

Report message to a moderator

Re: Not able to run expdp [message #689884 is a reply to message #689883]

Mon, 17 June 2024 15:46

orHooha
Messages: 19
Registered: April 2015

Junior Member

How do I tell if it does?

Report message to a moderator

Re: Not able to run expdp [message #689885 is a reply to message #689884]

Tue, 18 June 2024 01:03

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I'm not a RAC expert and can't help you more on this point.

I suggest you create a topic in our RAC (sub)forum something like "How to create and verify RAC resource groups in 11gR2 on <your OS>".

Report message to a moderator

Re: Not able to run expdp [message #689886 is a reply to message #689884]

Tue, 18 June 2024 14:23

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

orHooha wrote on Mon, 17 June 2024 21:46

How do I tell if it does?

My memory may not be accurate going that far back, but I think this will show you how the service is configured and running:

srvctl config service -d <your database's unique name, probably doct> -s doct
srvctl status  service -d <your database's unique name> -s doct

However, I think that you are using the default service for the database. You have instances doct1 and doct2, opening a database doct. You should not be using the default service, you should be creating your own service which you start on only one node.
Hope that helps.

Report message to a moderator

Re: Not able to run expdp [message #689889 is a reply to message #689886]

Thu, 20 June 2024 09:59

orHooha
Messages: 19
Registered: April 2015

Junior Member

Thanks, John. I don't understand how to create our own service. Anyway, I'll forward your comment, thanks.

John Watson wrote on Tue, 18 June 2024 15:23

orHooha wrote on Mon, 17 June 2024 21:46
How do I tell if it does?
My memory may not be accurate going that far back, but I think this will show you how the service is configured and running:
srvctl config service -d <your database's unique name, probably doct> -s doct
srvctl status  service -d <your database's unique name> -s doct
However, I think that you are using the default service for the database. You have instances doct1 and doct2, opening a database doct. You should not be using the default service, you should be creating your own service which you start on only one node.
Hope that helps.

Report message to a moderator

Re: Not able to run expdp [message #689890 is a reply to message #689885]

Thu, 20 June 2024 10:00

orHooha
Messages: 19
Registered: April 2015

Junior Member

Thanks, Michel.

Michel Cadot wrote on Tue, 18 June 2024 02:03

I'm not a RAC expert and can't help you more on this point.

I suggest you create a topic in our RAC (sub)forum something like "How to create and verify RAC resource groups in 11gR2 on <your OS>".

Report message to a moderator

Re: Not able to run expdp [message #689891 is a reply to message #689886]

Thu, 20 June 2024 11:55

orHooha
Messages: 19
Registered: April 2015

Junior Member

Hi John,

I didn't catch these suggestions, until now. But there's an error running the status line. I'll try looking up the syntax of srvctl.

lic030ta2db[oracle@DOCT1] /home/oracle>srvctl status service -d doct -s doct
PRCD-1027 : Failed to retrieve database doct
PRCR-1115 : Failed to find entities of type resource that match filters ((NAME

ora.doct.db) && (TYPE

ora.database.type)) and contain attributes VERSION,ORACLE_HOME,DATABASE_TYPE

Report message to a moderator

Re: Not able to run expdp [message #689892 is a reply to message #689874]

Wed, 26 June 2024 11:17

orHooha
Messages: 19
Registered: April 2015

Junior Member

CONCLUSION -

It's possible that our TEST database wasn't fully built. The apps that run in PROD weren't also built in TEST.

So we decided to skip TEST, and move to our STAGing database. The export script ran perfectly.

Everything's good, thanks to all for your help.

Report message to a moderator

Re: Not able to run expdp [message #689893 is a reply to message #689892]

Wed, 26 June 2024 12:10

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Report message to a moderator

Previous Topic:	impd data only on the same database from one table to another table
Next Topic:	How to import a higher TZ dump file into a lower TZ database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 19:25:54 CST 2025