Home » RDBMS Server » Server Utilities » Not able to run expdp (Enterprise 11.2.0.3.0)
Not able to run expdp [message #689874] Sun, 16 June 2024 12:41 Go to next message
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





Re: Not able to run expdp [message #689875 is a reply to message #689874] Sun, 16 June 2024 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Not able to run expdp [message #689876 is a reply to message #689875] Mon, 17 June 2024 10:26 Go to previous messageGo to next message
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.
Re: Not able to run expdp [message #689877 is a reply to message #689876] Mon, 17 June 2024 11:08 Go to previous messageGo to next message
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."
Re: Not able to run expdp [message #689878 is a reply to message #689877] Mon, 17 June 2024 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Looks like we're not using RAC resource groups.  

This is my suggestion: use it (with CLUSTER=YES). 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.

Re: Not able to run expdp [message #689879 is a reply to message #689878] Mon, 17 June 2024 13:51 Go to previous messageGo to next message
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
Re: Not able to run expdp [message #689880 is a reply to message #689879] Mon, 17 June 2024 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Not able to run expdp [message #689881 is a reply to message #689880] Mon, 17 June 2024 14:26 Go to previous messageGo to next message
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

Re: Not able to run expdp [message #689882 is a reply to message #689881] Mon, 17 June 2024 14:45 Go to previous messageGo to next message
orHooha
Messages: 19
Registered: April 2015
Junior Member
Exactly the same result.  A mystery.
Re: Not able to run expdp [message #689883 is a reply to message #689882] Mon, 17 June 2024 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Not able to run expdp [message #689884 is a reply to message #689883] Mon, 17 June 2024 15:46 Go to previous messageGo to next message
orHooha
Messages: 19
Registered: April 2015
Junior Member
How do I tell if it does?  
Re: Not able to run expdp [message #689885 is a reply to message #689884] Tue, 18 June 2024 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
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>".

Re: Not able to run expdp [message #689886 is a reply to message #689884] Tue, 18 June 2024 14:23 Go to previous messageGo to next message
John Watson
Messages: 8939
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.

Re: Not able to run expdp [message #689889 is a reply to message #689886] Thu, 20 June 2024 09:59 Go to previous messageGo to next message
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.

Re: Not able to run expdp [message #689890 is a reply to message #689885] Thu, 20 June 2024 10:00 Go to previous messageGo to next message
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>".

Re: Not able to run expdp [message #689891 is a reply to message #689886] Thu, 20 June 2024 11:55 Go to previous messageGo to next message
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
Re: Not able to run expdp [message #689892 is a reply to message #689874] Wed, 26 June 2024 11:17 Go to previous messageGo to next message
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.
Re: Not able to run expdp [message #689893 is a reply to message #689892] Wed, 26 June 2024 12:10 Go to previous message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.
Previous Topic: impd data only on the same database from one table to another table
Goto Forum:
  


Current Time: Mon Jul 22 20:53:44 CDT 2024