General
=====================
When performing export via expdp, following error is coming:
ORA-31623: a job is not attached to this session via the specified handle
=====================
Evidences
=====================
Full error stack:
Export: Release 11.2.0.4.0 - Production on Wed May 23 07:36:34 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
UDE-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
***************************************************
FINISHED Export of DataBase
***************************************************
iu@my_server:/starhome/dbinstall/app/db/Export-Import>%
=====================
Checking Current settings
=====================
SELECT name, ROUND(bytes/1024/1024) as MB, resizeable
FROM V$SGAINFO
ORDER BY bytes DESC;
NAME MB RESIZEABLE
-------------------------------- ---------- ----------
Maximum SGA Size 65243 No
Buffer Cache Size 33024 Yes
Free SGA Memory Available 22912
Streams Pool Size 5120 Yes
Shared Pool Size 3456 Yes
Startup overhead in Shared Pool 696 No
Large Pool Size 384 Yes
Java Pool Size 256 Yes
Granule Size 128 No
Redo Buffers 89 No
Fixed SGA Size 2 No
Shared IO Pool Size 0 Yes
SELECT name, ROUND(value/1024/1024) AS MB, description
FROM V$PARAMETER
WHERE name like '%pool%';
NAME MB DESCRIPTION
------------------------------ ---------- -----------------------------------------
shared_pool_size 0 size of shared pool
large_pool_size 0 size of large pool
java_pool_size 0 size of java pool
streams_pool_size 0 size of the streams pool
shared_pool_reserved_size 173 size 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
olap_page_pool_size 0 size of the olap page pool
9 rows selected.
=====================
Solution
=====================
The solution was to increase the Streams Pool Size explicitly to 8Gb.
This specific system was running Several Golden Gate Replication Processes.
Although Stream Pool Size was set to 0, and Oracle was supposed to allocate optimal memory for Stream Pool, the 5Gb allocated by Oracle, was obviously not enough.
After increasing the Stream Pool Size to 8Gb, the export finished after 4 minutes, without error.
ALTER SYSTEM SET STREAMS_POOL_SIZE=8192M;
SELECT name, ROUND(bytes/1024/1024) as MB, resizeable
FROM V$SGAINFO
ORDER BY bytes DESC;
------------------------------ ---------- ------------
Maximum SGA Size 65243 No
Buffer Cache Size 29952 Yes
Free SGA Memory Available 22912
Streams Pool Size 8192 Yes
Shared Pool Size 3456 Yes
Startup overhead in Shared Pool 696 No
Large Pool Size 384 Yes
Java Pool Size 256 Yes
Granule Size 128 No
Redo Buffers 89 No
Fixed SGA Size 2 No
Shared IO Pool Size 0 Yes
SELECT name, ROUND(value/1024/1024) AS MB, description
FROM V$PARAMETER
WHERE name like '%pool%';
NAME MB DESCRIPTION
------------------------------ ---------- -----------------------------------------
shared_pool_size 0 size of shared pool
large_pool_size 0 size of large pool
java_pool_size 0 size of java pool
streams_pool_size 8192 size of the streams pool
shared_pool_reserved_size 173 size 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
olap_page_pool_size 0 size of the olap page pool
9 rows selected.
No comments:
Post a Comment