Home » RDBMS Server » Server Utilities » Issue in export (Oracle 11g)
Issue in export [message #661907] Wed, 05 April 2017 17:15 Go to next message
chintan.patel
Messages: 147
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
Re: Issue in export [message #661911 is a reply to message #661907] Thu, 06 April 2017 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
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:
SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_N".CURRVAL val# FROM DUAL;
SELECT "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_N".CURRVAL val# FROM DUAL;

Re: Issue in export [message #661912 is a reply to message #661911] Thu, 06 April 2017 00:50 Go to previous messageGo to next message
chintan.patel
Messages: 147
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?
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:
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
Re: Issue in export [message #661913 is a reply to message #661912] Thu, 06 April 2017 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Issue in export [message #661914 is a reply to message #661911] Thu, 06 April 2017 00:56 Go to previous messageGo to next message
chintan.patel
Messages: 147
Registered: July 2008
Location: Ahmedabad
Senior Member
Dear Michel

Here is the result.
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
Re: Issue in export [message #661916 is a reply to message #661914] Thu, 06 April 2017 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
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:
   @$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 

Re: Issue in export [message #661918 is a reply to message #661916] Thu, 06 April 2017 01:06 Go to previous messageGo to next message
chintan.patel
Messages: 147
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
Re: Issue in export [message #661919 is a reply to message #661918] Thu, 06 April 2017 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Issue in export [message #661920 is a reply to message #661919] Thu, 06 April 2017 01:21 Go to previous messageGo to next message
chintan.patel
Messages: 147
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
Re: Issue in export [message #661921 is a reply to message #661920] Thu, 06 April 2017 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Issue in export [message #661922 is a reply to message #661921] Thu, 06 April 2017 01:32 Go to previous message
chintan.patel
Messages: 147
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.
Previous Topic: sql loader not loading file
Next Topic: SQLLDR Issue with NUMBER data type
Goto Forum:
  


Current Time: Thu Dec 14 09:25:58 CST 2017

Total time taken to generate the page: 0.01594 seconds