Search This Blog

2009-08-05

RMAN shared_pool_size requirement

Following shows that RMAN needs 13.49981689453125 MB of shared_pool_size in order to run

The demonstration shown initial with about 10 MB shared pool free, RMAN failed to allocate memory from shared pool. Then 8 MB is added, and it is able to run. By subtracting the free size before and after ran, it is found RMAN needs about 13.5 MB of shared_pool

select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
POOL         NAME           BYTES
------------ ----------- --------
shared pool free memory 10552632
RMAN failed immediately with memory allocation error

$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Aug 5 15:10:09 2009

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

ORACLE error from target database:
ORA-04031: unable to allocate 41152 bytes of shared memory ("shared pool","DBMS_RCVMAN","PL/SQL MPCODE","BAMIMA: Bam Buffer")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_RCVMAN"

error executing package DBMS_RCVMAN in TARGET database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: TARGET database is not compatible with this version of RMAN

select component, current_size/1024/1024 mb, max_size from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT MB MAX_SIZE
------------------------------ ---------- ----------
shared pool 84 0
large pool 0 0
java pool 24 0
streams pool 0 0
DEFAULT buffer cache 48 0
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
DEFAULT 32K buffer cache 0 0
ASM Buffer Cache 0 0
Shrink java_pool_size to free up 8 MB, and add it into shared_pool_size

select * from v$SGA_DYNAMIC_FREE_MEMORY;

CURRENT_SIZE
------------
0

select component, current_size/1024/1024 mb, min_size/1024/1024 min_size, max_size from V$SGA_DYNAMIC_COMPONENTS where component in ( 'java pool' , 'shared pool' );

COMPONENT MB MIN_SIZE MAX_SIZE
----------- -- ---------- ----------
shared pool 84 84 0
java pool 24 24 0

alter system set java_pool_size = 16m;

select * from v$SGA_DYNAMIC_FREE_MEMORY;

CURRENT_SIZE
------------
8388608

alter system set shared_pool_size = 92m;

select component, current_size/1024/1024 mb, min_size/1024/1024 min_size, max_size from V$SGA_DYNAMIC_COMPONENTS where component in ( 'java pool' , 'shared pool' );
COMPONENT    MB   MIN_SIZE   MAX_SIZE
----------- --- ---------- ----------
shared pool 92 84 0
java pool 16 16 0


Now RMAN is able to run. After RMAN executed, check free shared pool memory again

select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
POOL NAME BYTES
------------ ----------- ----------
shared pool free memory 4785656

So RMAN shared pool memory usage is
10552632 + 8192*1024 - 4785656
= 14155584 bytes
= 13.49981689453125 MB

Please note that shared pool memory won't free immediately after RMAN completion. This is normal behavior. If RMAN failed to run with similar error again, either adjust the shared_pool_size to larger, or pin its package to shared pool. Alternatively, set shared_pool_reserved_size to 13.5 MB, or some value which will helps depending on database usage

Please use following PayPal donate if my post helped

No comments:

Blog Archive