on 09-09-2015 5:47 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
81 | |
9 | |
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.