how to resolve ORA-31623: a job is not attached to this session via the specified handle

ORA-31623: a job is not attached to this session via the specified handle

=====================

-Issue-

When performing Import via impdp, following error is coming:
ORA-31623: a job is not attached to this session via the specified handle

–Full error stack:

Import: Release 12.1.0.2.0 – Production on Wed Feb 2 11:08:25 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3905
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5203
ORA-06512: at line 1
--Current Memory settings 

SQL> show parameter streams

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 256M


SQL> SELECT name, ROUND(bytes/1024/1024) as MB, resizeable
  FROM V$SGAINFO
ORDER BY bytes DESC;  2    3

NAME                                     MB RESIZEABLE
-------------------------------- ---------- --------------------
Maximum SGA Size                      10368 No
Shared Pool Size                       6080 Yes
Buffer Cache Size                      3168 Yes
Streams Pool Size                       864 Yes
Startup NUMA Shared Pool memory         448 No
Shared IO Pool Size                     416 Yes
Startup overhead in Shared Pool         378 No
Redo Buffers                            132 No
Free SGA Memory Available                32
Java Pool Size                           32 Yes
Large Pool Size                          32 Yes
Granule Size                             32 No
Fixed SGA Size                            3 No
In-Memory Area Size                       0 No
Data Transfer Cache Size                  0 Yes



SQL> SELECT name, ROUND(value/1024/1024) AS MB, description FROM  V$PARAMETER WHERE name like '%pool%';

NAME                                             MB DESCRIPTION
---------------------------------------- ---------- ----------------------------------------------
shared_pool_size                               1568 size in bytes of shared pool
large_pool_size                                   0 size in bytes of large pool
java_pool_size                                    0 size in bytes of java pool
streams_pool_size                               256 size in bytes of the streams pool
shared_pool_reserved_size                       304 size in bytes of reserved area of shared pool
buffer_pool_keep                                    Number of database blocks/latches in keep buffer pool
buffer_pool_recycle                                 Number of database blocks/latches in recycle buffer pool
global_context_pool_size                            Global Application Context Pool Size in Bytes
olap_page_pool_size                               0 size of the olap page pool in bytes

9 rows selected.

SQL> show parameter streams

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 256M

=====================

Solution–

The solution was to increase the Streams Pool Size explicitly to 1Gb.

This specific system was running Several Golden Gate Replication Processes.

Although Stream Pool Size was set to 256M, and Oracle was supposed to allocate optimal memory for Stream Pool, the allocated memory by Oracle was obviously not enough.

After increasing the Stream Pool Size to 1Gb, still the error was not resolved as its required more streams_pool, the export finished after releasing the used streams from stopping OGG process in 4 minutes, without error.
if its small export/import then only you can pause the OGG process else increase the sufficient Streams_pool_size and start the export/import.

–Increase the streams_pool_size, In my case I am increasing it 1gb

SQL> alter system set streams_pool_size=1g scope=both sid='*';
alter system set streams_pool_size=1g scope=both sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

-- do it on instance level from seperet for all instance.

SQL> alter system set streams_pool_size=1g scope=both sid='instance1';

System altered.

SQL> alter system set streams_pool_size=1g scope=both sid='instance2';

System altered.

SELECT name, ROUND(bytes/1024/1024) as MB, resizeable 
 FROM V$SGAINFO 
ORDER BY bytes DESC;

NAME                                   MB DESCRIPTION
------------------------------ ---------- --------------------------------------
shared_pool_size                     1568 size in bytes of shared pool
large_pool_size                         0 size in bytes of large pool
java_pool_size                          0 size in bytes of java pool
streams_pool_size                    1024 size in bytes of the streams pool
shared_pool_reserved_size             304 size in bytes of reserved area of shared pool
buffer_pool_keep                          Number of database blocks/latches in keep buffer pool
buffer_pool_recycle                       Number of database blocks/latches in recycle buffer pool
global_context_pool_size                  Global Application Context Pool Size in Bytes
olap_page_pool_size                     0 size of the olap page pool in bytes

9 rows selected.

set line 190
col NAME for a40
col DESCRIPTION for a80
SELECT name, ROUND(value/1024/1024) AS MB, description
FROM  V$PARAMETER
WHERE name like '%pool%'; 

NAME                                             MB DESCRIPTION
---------------------------------------- ---------- -----------------------------------
shared_pool_size                               1568 size in bytes of shared pool
large_pool_size                                   0 size in bytes of large pool
java_pool_size                                    0 size in bytes of java pool
streams_pool_size                              1024 size in bytes of the streams pool
shared_pool_reserved_size                       304 size in bytes of reserved area of shared pool
buffer_pool_keep                                    Number of database blocks/latches in keep buffer pool
buffer_pool_recycle                                 Number of database blocks/latches in recycle buffer pool
global_context_pool_size                            Global Application Context Pool Size in Bytes
olap_page_pool_size                               0 size of the olap page pool in bytes

9 rows selected.



start the import and it will resolve the issue, if still issue is there, please stop the golden gate process for temporary resolve as integrated extract utilized streams_pool_size.

Import: Release 12.1.0.2.0 - Production on Wed Feb 2 11:18:58 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_08" successfully loaded/unloaded
************
************
************
************
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "SYSTEM"."SYS_IMPORT_FULL_08" successfully completed at Wed Feb 2 09:22:36 2022 elapsed 0 00:03:34

Leave a Reply

Your email address will not be published. Required fields are marked *