Oracle EM 10g R2: Export to export file problem

[aan_1]

Hi oracle expert,
I got the problem when trying to export database to export file using Oracle Enterprise Manager Database Control.

The error indicate 'Export Submit Failed' with following detail:
Errors: ORA-31626: job does not exist ORA-31637: cannot create job EXPORT000046 for user SCOTT 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_20090106111009" and "KUPC$S_1_20090106111009" for Data Pump job ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPC$QUE_INT", line 1555 ORA-00832: no streams pool created and cannot automatically create one Exception : ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 911 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356 ORA-06512: at line 2

I user scott user, i have tried using sysman and system user also but still got this problem.
Please Help...

Asrul Muan

[PAVIC Thierry]

found on Oracle support ->
This is caused by Bug 4478139.

When running the scripts to create the queue tables for export, it fails because there are over 148 enabled
roles assigned to the SYS schema. This is the reason for the ORA-39080 encountered during the export.

In a typical 10.2.0.3 install, there are approximately 31 roles granted to the SYS schema:

EXAMPLE

SQL> select granted_role from dba_role_privs where grantee='SYS';

GRANTED_ROLE
------------------------------
OLAP_DBA
JAVAUSERPRIV
EXP_FULL_DATABASE
CONNECT
CTXAPP
SELECT_CATALOG_ROLE
MGMT_USER
JAVA_ADMIN
EJBCLIENT
JAVADEBUGPRIV
JAVAIDPRIV
SCHEDULER_ADMIN
AQ_USER_ROLE
GATHER_SYSTEM_STATISTICS
DELETE_CATALOG_ROLE
XDBADMIN
LOGSTDBY_ADMINISTRATOR
EXECUTE_CATALOG_ROLE
RESOURCE
XDBWEBSERVICES
AUTHENTICATEDUSER
JAVA_DEPLOY
OEM_ADVISOR
IMP_FULL_DATABASE
JAVASYSPRIV
RECOVERY_CATALOG_OWNER
OEM_MONITOR
AQ_ADMINISTRATOR_ROLE
DBA
OLAP_USER
HS_ADMIN_ROLE

31 rows selected.


The bug is encountered when there are over 148 roles assigned to the SYS schema.

Solution
There are several solutions to this issue. To implement the solution, please execute one of the following steps:


1. Upgrade to 10.2.0.4 or 11g

AND

Reload the Datapump types and views;
connect as SYS then run the following
SQL >@ $ORACLE_HOME\rdbms\admin\catdph.sql
SQL >@ $ORACLE_HOME\rdbms\admin\prvtdtde.plb
SQL >@ $ORACLE_HOME\rdbms\admin\catdpb.sql
SQL >@ $ORACLE_HOME\rdbms\admin\dbmspump.sql


OR

2. If available for your platform, download and install Patch 4478139

AND

Reload the Datapump types and views;
connect as SYS then run the following
SQL >@ $ORACLE_HOME\rdbms\admin\catdph.sql
SQL >@ $ORACLE_HOME\rdbms\admin\prvtdtde.plb
SQL >@ $ORACLE_HOME\rdbms\admin\catdpb.sql
SQL >@ $ORACLE_HOME\rdbms\admin\dbmspump.sql


OR


3. Use the workaround:

As the SYSDBA user
â ¢ Shutdown database
â ¢ Start the database in the UPGRADE mode
â ¢ Drop the excess SYS roles:
drop role ;

AND

Reload the Datapump types and views;
connect as SYS then run the following
SQL >@ $ORACLE_HOME\rdbms\admin\catdph.sql
SQL >@ $ORACLE_HOME\rdbms\admin\prvtdtde.plb
SQL >@ $ORACLE_HOME\rdbms\admin\catdpb.sql
SQL >@ $ORACLE_HOME\rdbms\admin\dbmspump.sql


4. Shutdown/startup

References
Bug 4478139 - ORA-28031 MAXIMUM OF 148 ENABLED ROLES WHEN CREATING MULTICONSUMER QUEUE TABLE
Note 345198.1 - IMPDP OR EXPDP FAILS WITH ORA-31626 AND ORA-31637
Note 369927.1 - ORA-28031 and ORA-600 [kcbgtcr_4] error reported when creating a multi-consumer queue table.

[patrik rybar_1]

check the Doc ID: 345198.1 at metalink

[aan_1]

Hi all,

Thank you for the reply.

The problem indicate on
ORA-00832: no streams pool created and cannot automatically create one.

I have found the solution by add value to
streams_pool_size parameter.

I'll close this case.

Best regards,

Asrul Muan

[aan_1]

Thank you