UDI-31623: operation generated ORACLE error 31623

                               UDI-31623: operation generated ORACLE error 31623
                               ORA-31623: a job is not attached to this session via the specified handle.

ISSUE:-

We encountered one issue while running the impdp job on one of our database with the below listed error:

oracle@bossWW:~$  impdp oracle/xxxx@Tnsname schemas=HPSEBL,MIS,CRM  directory=EXP_BKP  dumpfile=dbschema.dmp logfile=impdbschema.log

Import: Release 11.2.0.4.0 – Production on Mon Oct 30 15:07:28 2017

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining 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 3326
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4551
ORA-06512: at line 1

Solution:-

As a workaround we are modify the parameters and just restarting the database and the impdp job was running fine.

Cause:

The problem is the stream pool size. It was configured to value

SQL> show parameter streams_pool

NAME                                 TYPE             VALUE
———————————— ———– ——————————
streams_pool_size                    big integer      0
Solution:

Increase the value of stream_pool_size initialization parameter

oracle@bossWW:~$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 16 14:44:23 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set streams_pool_size=256M scope=both;
alter system set streams_pool_size=256M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

SQL> show parameter sga;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 596M
sga_target                           big integer 0
Here we increase the SGA size and modify the streams_pool_size size.
QL> alter system set sga_max_size=700m scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area  730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             591396928 bytes
Database Buffers           67108864 bytes
Redo Buffers               69951488 bytes
Database mounted.
Database opened.
SQL> alter system set streams_pool_size=20m scope=both;
System altered.
After increasing the stream_pool_size parameter all expdp and impdp jobs started working without issues.
Orscle datapump uses the stream_pool_size parameter, So we must ensure that this parameter is configured appropriately before running the data pump jobs.
  • November 6, 2017 | 18 views
  • Comments