Hi
Oracle 9.2
Windows Advanced OS
6G Ram

We switched our server from Shared Server to Dedicated. All was going well, but suddenly no one could log in. Currently logged in users were okay.

Alert Log showed several of the following errors:
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn

No errors in the listener.log

Attempted login with sqlplus reported ORA-12500: TNS:listener failed to start a dedicated server process

At the time less than 2G of the 6G was being utilized.

Also, during the time of this event, the event log showed several of the following:
Event Type: Error
Event Source: Oracle.ACP
Event Category: None
Event ID: 31
Date: 5/16/2008
Time: 9:48:59 AM
User: N/A
Computer: ACP
Description:
Unable to begin another thread.

Any help would be appreciated.
kd

Oracle has reported the "skgpspawn failed:category = 27143" error message as related to bugs that depending on the platform and version may show up starting 9iR2 and up to 10gR2, Verify if you currently are at the 9.2.0.8.0 release with the latest patchset applied.

On the other hand, it could also be due to an excessive amount of connections trying to access the database, verify if the "TNS-12540: TNS:internal limit restriction exceeded" shows also. I have read you monitored the memory consumption and you stated it is below 2G, but you should notice database memory is not the same as process memory, which impact with additional requirements.

You could be facing an issue where the oracle instance is unable to allocate more memory.

~ Madrid
http://hrivera99.blogspot.com

Yes, I am suspecting either a OS ram situation, i.e., OS not being able to allocate above 2G even thought the PAE switch is set. Or some oracle structure running out of memory. However, I'm unsure of which oracle structure may be responsible for this.

Also, the threads associated to the Oracle.exe were less than 200 - much less than we see with some other executables.
kd

Okay, you are on to something there. At the time of our failure, the oracle.exe process was consuming roughtly 1.6+G ram.
Thanks.
kd

I am sure you must had checked process parameter.
/3GB switch not required if using /PAE. (/3G lets application take 3G where 1g for kernal on 32 bit machines)

If you had set /PAE, you also need to set USE_INDIRECT_DATA_BUFFER=TRUE
on db.

Hi Don
Thanks for the info. I now have the 3GB switch set. I have been leery of setting the indirect buffers due to some posts I have read like these:
****
ORA-00385: cannot enable Very Large Memory with new buffer cache parameters

Cause: User specified one or more of { db_cache_size , db_recycle_cache_size, db_keep_cache_size, db_nk_cache_size (where n is one of 2,4,8,16,32) } AND use_indirect_data_buffers is set to TRUE. This is illegal.

Action: Very Large Memory can only be enabled with the old (pre-Oracle_8.2) parameters.

ORA-07305: ksmcsg: illegal database buffer size.

Cause: The database buffer size must be a multiple of the extended cache mapping size for indirect data buffers to be used.

Action: Verify that the db_block_size parameter is correct in INIT.ORA, or disable the use_indirect_data_buffers parameter.
*****

I'm assuming these are 10G and above errors, but I just don't know.

Our db cache is already at a fairly large size.(see non standard configs below).

Also, is this an either/or thing. In other words, /3GT OR /PAE and indirect buffers, but not both?

Thanks again.

Starting up ORACLE RDBMS Version: 9.2.0.7.0.
System parameters with non-default values:
processes = 1350
sessions = 1490
resource_limit = FALSE
license_max_sessions = 0
shared_pool_size = 268435456
shared_pool_reserved_size= 26214400
large_pool_size = 369098752
pre_page_sga = TRUE
enqueue_resources = 124
dbwr_io_slaves = 2
control_files = E:\orant\admin\OR8i\control1\Contrl1.ctl, E:\orant\admin\OR8i\control1\Contrl3.ctl, E:\orant\admin\OR8i\control2\Contrl2.ctl, E:\orant\admin\OR8i\control2\Contrl4.ctl
db_block_size = 8192
db_keep_cache_size = 16777216
db_cache_size = 503316480
compatible = 9.2.0
log_archive_start = TRUE
log_archive_dest = E:\orant\admin\OR8i\arch
log_archive_format = ARC%S.%T
log_buffer = 65536
log_checkpoint_interval = 0
log_checkpoint_timeout = 1800
db_files = 254
db_file_multiblock_read_count= 8
dml_locks = 2000
transactions_per_rollback_segment= 3
max_rollback_segments = 700
rollback_segments = roll_1, roll_2, roll_3, roll_4, roll_5, roll_6, roll_7, roll_8, roll_9,
remote_login_passwordfile= EXCLUSIVE
license_max_users = 0
global_names = TRUE
shared_servers = 0
max_shared_servers = 0
session_cached_cursors = 25
utl_file_dir = *
job_queue_processes = 2
background_dump_dest = E:\orant\admin\OR8i\bdump
user_dump_dest = E:\orant\admin\OR8i\udump
max_dump_file_size = 50240
sort_area_size = 1048576
sort_area_retained_size = 65536
db_name = EMR
open_cursors = 450
optimizer_mode = CHOOSE

Hi,
Slightly different question; using Oracle 10G (10.2.0.4) on Windows Server 2003 32-bit, is it possible to use PRE_PAGE_SGA=TRUE (with ORA_LPENABLE set in the registry) when using the Oracle parameter use_indirect__data_buffers=true ?

I have left the AWE WINDOW size as default 1GB. I have the following in my boot.ini and the host has two CPU's and 12GB RAM.

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE /3GB

Here is the pfile contents. I expect to have up to 200 concurrent connections to the database;

*.audit_file_dest='C:\admin\BUILD01\adump'
*.background_dump_dest='C:\admin\BUILD01\bdump'
*.compatible='10.2.0.4.0'
*.control_files='D:\ORADATA\BUILD01\CONTROLFILE\O1_MF_3WFGNROL_.CTL'
*.core_dump_dest='C:\admin\BUILD01\cdump'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_block_buffers=1179648
*.db_create_file_dest='D:\ORADATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='BUILD01'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BUILD01XDB)'
*.java_pool_size=16M
*.job_queue_processes=5
*.large_pool_size=100M
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=600M
*.plsql_code_type='INTERPRETED'
*.pre_page_sga=true
*.processes=350
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=0
*.sga_max_size=0
*.shared_pool_size=600M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.use_indirect_data_buffers=true
*.user_dump_dest='C:\admin\BUILD01\udump'
*.workarea_size_policy='AUTO'

Thanks, Richard.