OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » Issue in export (Oracle 11g)

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

Issue in export [message #661907]

Wed, 05 April 2017 17:15

chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad

Senior Member

Hello Friends,

Today, i stuck while exporting schema export. I am taking export everyday but today it can not performing. please guide me to come out of it.

Quote:

[mydb@mydbhost ~]$ expdp post08/post08

Export: Release 11.2.0.1.0 - Production on Thu Apr 6 03:32:28 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_02 for user POST08
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_20170406033228 to queue "KUPC$C_1_
20170406033228"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 250
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Thanks,
Chintan

Report message to a moderator

Re: Issue in export [message #661911 is a reply to message #661907]

Thu, 06 April 2017 00:39

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

Senior Member
Account Moderator

1/ Are you in RAC?
2/ Are you running in parallel?
3/ What is the value of your DATA_PUMP_DIR directory?
4/ What is the result of the following queries:

Code: [Select all] [Show/ hide]

SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_N".CURRVAL val# FROM DUAL;
SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_N".CURRVAL val# FROM DUAL;

Report message to a moderator

Re: Issue in export [message #661912 is a reply to message #661911]

Thu, 06 April 2017 00:50

chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad

Senior Member

Dear Michel

1/ Are you in RAC?
Answer : No

2/ Are you running in parallel?
Answer : No

3/ What is the value of your DATA_PUMP_DIR directory?

Code: [Select all] [Show/ hide]

SQL> select * from dba_directories where DIRECTORY_NAME like '%DATA_PUMP%';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                            DATA_PUMP_DIR                  /mydb/odb/OH1/rdbms/log/

4/ What is the result of the following queries:

Code: [Select all] [Show/ hide]

SQL> SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_N".CURRVAL val# FROM DUAL;
SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_N".CURRVAL val# FROM DUAL
             *
ERROR at line 1:
ORA-08002: sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N.CURRVAL is not yet defined in this session


SQL> SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_N".CURRVAL val# FROM DUAL;
SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_N".CURRVAL val# FROM DUAL
             *
ERROR at line 1:
ORA-08002: sequence AQ$_KUPC$DATAPUMP_QUETAB_N.CURRVAL is not yet defined in this session

Regards

Chintan

Report message to a moderator

Re: Issue in export [message #661913 is a reply to message #661912]

Thu, 06 April 2017 00:51

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

Senior Member
Account Moderator

Use NEXTVAL instead of CURRVAL in the queries and post the result.

Report message to a moderator

Re: Issue in export [message #661914 is a reply to message #661911]

Thu, 06 April 2017 00:56

chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad

Senior Member

Dear Michel

Here is the result.

Code: [Select all] [Show/ hide]

SQL> SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_N".NEXTVAL val# FROM DUAL;

      VAL#
----------
   1000251

SQL> SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_N".NEXTVAL val# FROM DUAL;

      VAL#
----------
         1

Regards

Chintan

Report message to a moderator

Re: Issue in export [message #661916 is a reply to message #661914]

Thu, 06 April 2017 01:01

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

Senior Member
Account Moderator

You hit a known bug, if AQ$_KUPC$DATAPUMP_QUETAB_1_N reaches 1000000 you have this error.
No patch in your version, the workaround is to recreate the Data Pump environment:

Code: [Select all] [Show/ hide]

   @$ORACLE_HOME/rdbms/admin/catdph.sql
   @$ORACLE_HOME/rdbms/admin/prvtdtde.plb
   @$ORACLE_HOME/rdbms/admin/catdpb.sql
   @$ORACLE_HOME/rdbms/admin/dbmspump.sql
   @$ORACLE_HOME/rdbms/admin/utlrp.sql

Report message to a moderator

Re: Issue in export [message #661918 is a reply to message #661916]

Thu, 06 April 2017 01:06

chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad

Senior Member

Dear Michel,

As i understand, I've to reinstall the package related to EXPDP utility. For that I've to shutdown my running instance and startup in nomount stage to run the below mentioned scripts. Please suggest.

Regards

Chintan

Report message to a moderator

Re: Issue in export [message #661919 is a reply to message #661918]

Thu, 06 April 2017 01:12

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

Senior Member
Account Moderator

No, database must be open, you can't do anything inside the database if it is closed.

Report message to a moderator

Re: Issue in export [message #661920 is a reply to message #661919]

Thu, 06 April 2017 01:21

chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad

Senior Member

Dear Michel,

So I can strait away run this scripts in my database, as currently it is open and running good for the application users. Do I need to stop the applications users (live transactions) as a safety measure while performing the running of suggested scripts? Please let me know finally.

Regards

Chintan

Report message to a moderator

Re: Issue in export [message #661921 is a reply to message #661920]

Thu, 06 April 2017 01:28

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

Senior Member
Account Moderator

It is always a good behavior to change SYS objects in a safe environment that is during a maintenance window when the applications and end users are not there, even if theoretically you can do it at any time.

Report message to a moderator

Re: Issue in export [message #661922 is a reply to message #661921]

Thu, 06 April 2017 01:32

chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad

Senior Member

Thanks Michel,

Will update you once again, after the re-installation of the data pump utility completed.

Thanks again.

Report message to a moderator

Previous Topic:	sql loader not loading file
Next Topic:	SQLLDR Issue with NUMBER data type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 03 06:09:11 CDT 2025