Re: RE : datapump errors

From: Surachart Opun <surachart_at_gmail.com>
Date: Fri, 4 Dec 2009 22:52:01 +0700
Message-ID: <1dacf81e0912040752i65bbd21dhd373497c003403eb_at_mail.gmail.com>

The 4031 error can occur in the Large Pool, Java Pool, Streams Pool (new to 10g), or the Shared Pool. The error message will indicate which pool had the problem. If the error indicates a problem in a pool other than the Shared Pool, this usually indicates the problem pool is configured too small for the application environment. Increase the size of the problem pool in increments of 50MB or 100MB and monitor for continued problems. If using the 10g, Automatic Shared Memory Management (ASMM) functionality, the MMAN process will attempt to shrink and grow different components in the SGA as memory is needed over time. You may need to increase the setting for SGA_TARGET to allow MMAN more memory to manage behind the scenes if you experience ORA-04031 errors in the Large Pool, Streams Pool, or Java Pool.

*
* STREAMS_POOL_SIZE* This is new memory pool in 10g. It is intended to alleviate stress on memory structures in the Shared Pool related to Streams operations. Review the size set for the parameter in the RDA. *
If using SGA_TARGET on 10g Release 2, the parameter will be auto-tuned and will show up as 0. *
*You can hard-code a minimum size with 10g and Release 2 and MMAN will not attempt to shrink the Streams Pool below that setting.*

--Script to check ASSM
ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS'; SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL TARGET_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
COL OPER_TYPE FORMAT A10
select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
order by start_time, component;

Good Luck
Surachart Opun
http://surachartopun.com

On Fri, Dec 4, 2009 at 9:39 PM, <oracle-l-bounce_at_freelists.org> wrote:

> This does seem to have been the issue. I had to increase sga_target and
> then increase the stream pool from 0.
>
> Datapump then worked OK.
>
>
> Not sure why this was suddenly required. Have restarted database and am
> monitoring memory.
>
>
>
>
>
>
> John Dunn
>
> Sefas Innovation Limited.
>
> Tel: + 44 (0) 117 373 6122
>
>
>
> P Please consider the environment before printing this email
>
>
>
>
> -----Original Message-----
> From: Bertrand Guillaumin [mailto:bertrand.guillaumin_at_digora.com]
> Sent: 04 December 2009 14:28
> To: Richard.Goulet_at_parexel.com; John Dunn
> Subject: RE : datapump errors
>
> Hi,
> Also I have seen problems when the streams_pool_size is 0 and sga
> management is AUTO(use of sga_target).
> The SGA is full and cannot make space for the stream pool. Maybe increasing
> streams_pool_size will resolve your problem.
>
>
> B.Guillaumin
> DBA Consultant
> DIGORA
> ________________________________________
> De : oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] de la
> part de Goulet, Richard [Richard.Goulet_at_parexel.com] Date d'envoi :
> vendredi 4 décembre 2009 15:11 Ŕ : JDunn_at_sefas.com; oracle-l digest users
> Objet : RE: datapump errors
>
> Take a look in dba_datapump_jobs. The job may still be in there and even
> if it's not really functioning you can't start a new job by the same name
> till you clear this one. Sometimes connecting with impdp/expdp and
> attaching to the job will allow you to kill it, but sometimes it's already
> dead, just the master table, which has the same name as the job, hasn't been
> deleted yet. Simply drop that table and it should clear up nicely.
>
>
> Dick Goulet
> Senior Oracle DBA/NA Team Lead
> PAREXEL International
>
>
>
> ________________________________
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of John Dunn
> Sent: Friday, December 04, 2009 6:32 AM
> To: oracle-l digest users
> Subject: datapump errors
>
> I have run datapump on a regular basis on the same schema but now get the
> errors below:
>
> There have been no database upgrades or anything
>
>
> Any ideas?
>
>
>
> Connected to: Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
>
> ORA-31626: job does not exist
>
> ORA-31637: cannot create job SYS_SQL_FILE_FULL_01 for user PRODUCER
>
> 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_20091204112459" and
> "KUPC$S_1_20091
>
> 204112459" for Data Pump job
>
> ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
>
> ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
>
> ORA-04031: unable to allocate 376 bytes of shared memory ("streams
> pool","unknow
>
> n object","streams pool","kwqbsinfy:cco")
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 04 2009 - 09:52:01 CST