ORA-4031 ERROR

[Former Member]

Hi all, I'm receiving this error when trying to install ECC 6 in Oracle:

DbSl Trace: Error 604 in exec_immediate() from oci_execute_stmt(), orpc=0

DbSl Trace: ORA-604 occurred when executing SQL stmt (parse error offset=38)

DB) ERROR: DDL statement failed

(CREATE UNIQUE INDEX "PIQLOIOT03~0" ON "PIQLOIOT03" ( "MANDT", "LANGU", "LOIO_ID" ) TABLESPACE PSAPSR3 STORAGE (INITIAL 16384 NEXT 0000000640K MINEXTENTS 0000000001 MAXEXTENTS 2147483645 PCTINCREASE 0 ) NOLOGGING COMPUTE STATISTICS )

DbSlExecute: rc = 99

  (SQL error 604)

  error message returned by DbSl:

ORA-00604: error occurred at recursive SQL level 2

ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")

(DB) INFO: disconnected from DB

I was told that this error is related to the value of the variable SHARE_POOL_. I have configured these values increasing them in the file initCSN.ora. Attached.

After setting the "correct" values I have restarted the virtual machine.

The same error ocurrs with 30 process in the same during the installation.

Could I receive an answer? I'm freaking out.

There are two more files in the directory initCSN.1.ora and iniCSN.2.ora

INITCSN.ORA FILE

# C:\oracle\CSN\saptrace\usertrace:  trace files of the user processes

# log_archive_dest is a destination, not a path.

# The archivefiles get the name

# C:\oracle\CSN\oraarch\CSNarch<thread#>_<log#>

background_dump_dest = C:\oracle\CSN\saptrace\background

user_dump_dest      = C:\oracle\CSN\saptrace\usertrace

core_dump_dest      = C:\oracle\CSN\saptrace\background

log_archive_dest    = C:\oracle\CSN\oraarch\CSNarch

#log_archive_format  = %t_%s

#### OTHER

# reduce_alarm only supported on HP

#reduce_alarm = TRUE

#### ORACLE OPS PARAMETER

remote_os_authent = true

# retention time for RMAN backup information in control file

control_file_record_keep_time = 30

#see SAP note 124361

#db_file_multiblock_read_count = 8

log_checkpoints_to_alert = true

#### OPTIMIZER MODE

#optimizer_index_cost_adj = 10

#### AUDITING AND STATISTICS

#timed_statistics = true

compatible = 10.2.0

parallel_execution_message_size = 16384

query_rewrite_enabled = false

replication_dependency_tracking = false

star_transformation_enabled = true

undo_retention = 43200

undo_tablespace = PSAPUNDO

undo_management = AUTO

pga_aggregate_target = 629145600

sga_max_size = 9585033210

db_cache_size = 479251660

job_queue_processes = 1

remote_login_passwordfile = exclusive

recyclebin = off

event = "10191 trace name context forever, level 1"

FILESYSTEMIO_OPTIONS = setall

_OPTIM_PEEK_USER_BINDS = FALSE

_B_TREE_BITMAP_PLANS = FALSE

_INDEX_JOIN_ENABLED = FALSE

_IN_MEMORY_UNDO = FALSE

_OPTIMIZER_MJC_ENABLED = FALSE

_SORT_ELIMINATION_COST_RATIO = 10

_TABLE_LOOKUP_PREFETCH_SIZE = 0

control_files = (C:\oracle\CSN\origlogA\cntrl\cntrlCSN.dbf, C:\oracle\CSN\origlogB\cntrl\cntrlCSN.dbf, C:\oracle\CSN\sapdata1\cntrl\cntrlCSN.dbf)

Thanks for your help in advance.

[Former Member]

Hi Juan,

Can you please check the  oracle parameters SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, and LARGE_POOL_SIZE parameters increase those do it in both pfile & Spfile.

After which restart the Oracle DB.

Also you may want to have look on two notes mentioned below:-

1635622 - ORA-04031 despite sufficiently sized shared pool

1873631 - Session fails due to memory overflow on the database level

Also What is the DB version & memory parameters mainly SGA, shared pool sizes you have which can help us to check further.

Regards,

Ram

[Former Member]

Hi Ramkrishna.

Thanks for the answer. I really need help.

Here the information you requested: (thanks for your HELP)

I couldn't find/locate the LARGE_POOL_SIZE parameter. Where can I find it?

DB Version 10.2.0.4

From initCSN.ora:

SHARED_POOL_SIZE=479251660

SHARED_POOL_RESERVED_SIZE=47925166

SGA_MAX_SIZE=9585033210

What do pfile and spfile stand for? I mean I'm not an expert neither SAP nor Oracle (sorry).

db_cache_size = 479251660

I have in the directory two more files:

initCSN.1.ora

###########################################################

#  (c)Copyright SAP AG, Walldorf                          #

# @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/INITSIDBASE.ORA#1 $

###########################################################

###########################################################

# PART I, TUNING PARAMETERS                               #

###########################################################

#### SYSTEM GLOBAL AREA BUFFERS

# Total System Global Area = sum(bytes) of all caches

# Variable Size = shared_pool_size (approx.)

# Database Buffers = db_block_buffers * db_block_size

# Redo Buffers = log_buffer

#

# unit of shared_pool_size: bytes

# unit of db_block_buffers: number of cached blocks

# unit of log_buffer: bytes

shared_pool_size = 479251660

# 10% of shared_pool_size

shared_pool_reserved_size = 47925166

log_buffer = 1048576

#### MANAGING SORTS

# A small sort requires sort_area_retained_size in

# memory. Larger sorts are allocating segments in

# PSAPTEMP. They are using sort_area_size in memory.

sort_area_retained_size = 0

sort_area_size = 2097152

#sort_spacemap_size = 512

#### PGA-PARAMETERS

open_cursors = 800

#cursor_space_for_time =  true

#### LOCKS

# dml_locks = processes * 50

dml_locks = 4000

#enqueue_resources = 8000

#### CPU-PARAMETERS

#spin_count = 200

#_cpu_count = 4

#### TUNING ARCHIVING

#log_archive_buffer_size      127

#log_archive_buffers      4

#### USE VECTOR READ

#use_readv = true

#### OTHER TUNING PARAMETERS

#sequence_cache_entries = 100

#sequence_cache_hash_buckets = 89

###########################################################

# PART II, CHANGEABLE PARAMETERS                          #

###########################################################

#### CONTROL-FILES

# the controlfiles should be mirrored to every new

# database mount point @SAPDATA_HOME@/sapdata<#>

#control_files = @SAPDATA1@/cntrl/cntrlCSN.dbf @SAPDATA2@/cntrl/cntrlCSN.dbf @SAPDATA3@/cntrl/cntrlCSN.dbf

#### AUTOMATICLY STARTED BACKGROUND PROCESSES

#### MANAGING LOG SWITCHES

# checkpoints occur only when switching logs, if

# log_checkpoint_interval * size of OS blocks > size of

# the actual redo log

log_checkpoint_interval = 0

#log_checkpoint_timeout = 0

#### PROCESS-PARAMETERS

# The number of allocated semaphores is equal to the

# number of processes

# sessions = 1.2 * processes

processes = 80

sessions  = 160

#### AUDITING AND STATISTICS

# sql_trace=TRUE

# audit_trail = true

# db_block_lru_extended_statistics = 1000

# db_block_lru_statistics = true

###########################################################

# PART III, STATIC PARAMETERS                             #

###########################################################

#### DB-NAME

db_name = CSN

#### DB-BLOCKSIZE

db_block_size = 8192

#### DB-FILES

db_files = 254

#### OPTIMIZER MODE

#optimizer_mode = choose

#optimizer_search_limit = 3

#### PATHS / DESTINATIONS / TRACES

# C:\oracle\CSN\saptrace\background: trace files of the background

# processes

# C:\oracle\CSN\saptrace\usertrace:  trace files of the user processes

# log_archive_dest is a destination, not a path.

# The archivefiles get the name

# C:\oracle\CSN\oraarch\CSNarch<thread#>_<log#>

background_dump_dest = C:\oracle\CSN\saptrace\background

user_dump_dest       = C:\oracle\CSN\saptrace\usertrace

core_dump_dest       = C:\oracle\CSN\saptrace\background

log_archive_dest     = C:\oracle\CSN\oraarch\CSNarch

#log_archive_format  = %t_%s

#### OTHER

# reduce_alarm only supported on HP

#reduce_alarm = TRUE

#### ORACLE OPS PARAMETER

remote_os_authent = true

# retention time for RMAN backup information in control file

control_file_record_keep_time = 30

#see SAP note 124361

#db_file_multiblock_read_count = 8

log_checkpoints_to_alert = true

#### OPTIMIZER MODE

#optimizer_index_cost_adj = 10

#### AUDITING AND STATISTICS

#timed_statistics = true

InitCSN.2.ora:

###########################################################

#  (c)Copyright SAP AG, Walldorf                          #

# @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/INITSIDBASE.ORA#1 $

###########################################################

###########################################################

# PART I, TUNING PARAMETERS                               #

###########################################################

#### SYSTEM GLOBAL AREA BUFFERS

# Total System Global Area = sum(bytes) of all caches

# Variable Size = shared_pool_size (approx.)

# Database Buffers = db_block_buffers * db_block_size

# Redo Buffers = log_buffer

#

# unit of shared_pool_size: bytes

# unit of db_block_buffers: number of cached blocks

# unit of log_buffer: bytes

shared_pool_size = 479251660

# 10% of shared_pool_size

shared_pool_reserved_size = 47925166

log_buffer = 1048576

#### MANAGING SORTS

# A small sort requires sort_area_retained_size in

# memory. Larger sorts are allocating segments in

# PSAPTEMP. They are using sort_area_size in memory.

sort_area_retained_size = 0

sort_area_size = 2097152

#sort_spacemap_size = 512

#### PGA-PARAMETERS

open_cursors = 800

#cursor_space_for_time =  true

#### LOCKS

# dml_locks = processes * 50

dml_locks = 4000

#enqueue_resources = 8000

#### CPU-PARAMETERS

#spin_count = 200

#_cpu_count = 4

#### TUNING ARCHIVING

#log_archive_buffer_size      127

#log_archive_buffers      4

#### USE VECTOR READ

#use_readv = true

#### OTHER TUNING PARAMETERS

#sequence_cache_entries = 100

#sequence_cache_hash_buckets = 89

###########################################################

# PART II, CHANGEABLE PARAMETERS                          #

###########################################################

#### CONTROL-FILES

# the controlfiles should be mirrored to every new

# database mount point @SAPDATA_HOME@/sapdata<#>

#control_files = @SAPDATA1@/cntrl/cntrlCSN.dbf @SAPDATA2@/cntrl/cntrlCSN.dbf @SAPDATA3@/cntrl/cntrlCSN.dbf

#### AUTOMATICLY STARTED BACKGROUND PROCESSES

#### MANAGING LOG SWITCHES

# checkpoints occur only when switching logs, if

# log_checkpoint_interval * size of OS blocks > size of

# the actual redo log

log_checkpoint_interval = 0

#log_checkpoint_timeout = 0

#### PROCESS-PARAMETERS

# The number of allocated semaphores is equal to the

# number of processes

# sessions = 1.2 * processes

processes = 80

sessions  = 160

#### AUDITING AND STATISTICS

# sql_trace=TRUE

# audit_trail = true

# db_block_lru_extended_statistics = 1000

# db_block_lru_statistics = true

###########################################################

# PART III, STATIC PARAMETERS                             #

###########################################################

#### DB-NAME

db_name = CSN

#### DB-BLOCKSIZE

db_block_size = 8192

#### DB-FILES

db_files = 254

#### OPTIMIZER MODE

#optimizer_mode = choose

#optimizer_search_limit = 3

#### PATHS / DESTINATIONS / TRACES

# C:\oracle\CSN\saptrace\background: trace files of the background

# processes

# C:\oracle\CSN\saptrace\usertrace:  trace files of the user processes

# log_archive_dest is a destination, not a path.

# The archivefiles get the name

# C:\oracle\CSN\oraarch\CSNarch<thread#>_<log#>

background_dump_dest = C:\oracle\CSN\saptrace\background

user_dump_dest       = C:\oracle\CSN\saptrace\usertrace

core_dump_dest       = C:\oracle\CSN\saptrace\background

log_archive_dest     = C:\oracle\CSN\oraarch\CSNarch

#log_archive_format  = %t_%s

#### OTHER

# reduce_alarm only supported on HP

#reduce_alarm = TRUE

#### ORACLE OPS PARAMETER

remote_os_authent = true

# retention time for RMAN backup information in control file

control_file_record_keep_time = 30

#see SAP note 124361

#db_file_multiblock_read_count = 8

log_checkpoints_to_alert = true

#### OPTIMIZER MODE

#optimizer_index_cost_adj = 10

#### AUDITING AND STATISTICS

#timed_statistics = true

compatible = 10.2.0

parallel_execution_message_size = 16384

query_rewrite_enabled = false

replication_dependency_tracking = false

star_transformation_enabled = true

undo_retention = 43200

undo_tablespace = PSAPUNDO

undo_management = AUTO

pga_aggregate_target = 629145600

sga_max_size = 958503321

db_cache_size = 479251660

job_queue_processes = 1

remote_login_passwordfile = exclusive

recyclebin = off

event = "10191 trace name context forever, level 1"

FILESYSTEMIO_OPTIONS = setall

_OPTIM_PEEK_USER_BINDS = FALSE

_B_TREE_BITMAP_PLANS = FALSE

_INDEX_JOIN_ENABLED = FALSE

_IN_MEMORY_UNDO = FALSE

_OPTIMIZER_MJC_ENABLED = FALSE

_SORT_ELIMINATION_COST_RATIO = 10

_TABLE_LOOKUP_PREFETCH_SIZE = 0

[Former Member]

Hi Juan,

pfile is a parameter file in oracle which stores all data which is in your case the initCSN.ora.

Spfile is a binary form of pfile.

Also you can check the parameters bu running below command:-

sqlplus "/as sysdba"

show parameter LARGE_POOL_SIZE ;

show parameter <Parameter Name>;

The parameter can be changed by giving below command:-

alter system set shared_pool_size=500M scope=both;-->Example

So try to increase the value of the parameters also as your DB is on version 10.2.0.4. as the earlier notes mentioned were for 10.2.0.5 & 11g.

also from current setup i can only around 5MB of shared pool.

so try to increase it to 20MB+ refer below link for more inputs.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams197.htm#REFRN10202

869006 - Composite SAP note: ORA-04031

And refer the 830576 - Parameter recommendations for Oracle 10g note for the parameter recommendation for 10g

Also once you change the parameter use the above command to make the parameter effective in pfile and spfile.

Do not modify the pfile directly.

also The Above error can be resolved some of the occasation by clean re-start of the DB, try to restart DB after stopping the sapinst.

let us know if you need further help on same.

Regards,

Ram

[Former Member]

Many thanks Ram for your answer. I'll let you know.