No startup acknowledgement from forked process after 30 seconds

[size=medium]摘抄2篇文章。都是这个问题的描述，结果都是没有办法解决，直接重启搞定的。我的环境是WINDOWS2003-32+ORACLE9201,最后附上我的alert.log部分内容以供参考 [/size]
http://space.itpub.net/471666/viewspace-118586
ORA-04030: 在尝试分配 1049100 字节 (pga heap,KSFQ Buffers) 时进程内存不足
上一篇 / 下一篇 2007-08-31 00:00:00 / 个人分类：Oracle DBA

查看( 1845 ) / 评论( 1 ) / 评分( 0 / 0 )
ORA-04030: 在尝试分配 1049100 字节 (pga heap,KSFQ Buffers) 时进程内存不足

OS :WINDOWS 2000 SP4 内存：2G

DB:ORACLE 9201

altert.log 错误

Control autobackup failed with following error :
ORA-19583: 交谈因错误而终止
ORA-04030: 在尝试分配 1049100 字节 (pga heap,KSFQ Buffers) 时进程内存不足
Wed Aug 29 09:20:28 2007

LISTENR.LOG 错误

TNS-12500: TNS：监听器未能启动专用的服务器进程
TNS-12540: TNS：超出内部极限限制
TNS-12560: TNS: 协议适配器错误
TNS-00510: 超出内部极限限制
32-bit Windows Error: 8: Exec format error


Wed Aug 29 15:57:24 2007
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn

在网上查找类似错误

大概如下：
skgpspawn failed: skgpspawn 这个创建 process处理有问题
category = 27143, 错误分类
depinfo = 11, is the o/s errno [EACCES]
error may indicate the requested file is not available
which may be an effect that the process did not start and hence
its proc entries were not created.

op = spdcr, 系统调用spdcr错
loc = skgpspawn 位置是skgpspawn


造成这种错误有两种可能：

1，系统资源不足。

2。oracle bug，需要升级到9208

检查系统：

1.用windows企业管理器查看系统进程，发现oracle占用1.2g物理内存，1.8G虚拟内存。

2. 系统中内存使用2.2G.

由此判断可能是系统资源不足，内存不够，检查数据库初始化参数。

SGA:1.1g

SHARED POOL :230m

DATA BUFFER : 680m

LAREGE POOL :140m

PGA : 250m

3.出现此问题前一天，cpu 达到100％，是由于报表导致。kill session 后 cpu正常。

分析可能由于报表原因导致大量使用内存。os不能释放。导致系统资源不足。

解决办法：

1。降低了SGA和PGA

SHARED POOL :160m

DATA BUFFER : 480m

LAREGE POOL : 64m (使用rman备份)

PGA : 200m

减少了数据库内存，并使用了内存清理工具。但是虚拟内存降不下来，alter中还继续出现skgpspawn failed:category = 27143,

depinfo = 9261, op = spdcr, loc = skgpspawn ，

客户端连接连接数据库提示：TNS-12500: TNS：监听器未能启动专用的服务器进程。

没有办法，只能重启数据库后在观察了。


2。数据库服务器重启后，至今为发现异常。


奇怪的问题:ksbsrv: No startup acknowledgement from forked process after 30 secon上一篇 / 下一篇 2007-03-24 00:00:00 / 个人分类：oracle 数据库

查看( 223 ) / 评论( 0 ) / 评分( 0 / 0 )
HPUX11i + ORACLE9.2.0.6

昨天早晨，正在家里，突然接到同事的电话，说ODS数据库不能进行连接了，赶紧跑到单位，自己利用sqlplus 普通用户果然连接不上，报告shared memory inavaliable!但是因为昨天我用pl/sql developer进行操作完后，并没有关掉连接，利用它直接执行一个sql ,没想到还能正常执行，奇怪至极。


察看alert.log 文件，竟然大部分都是以下错误:

DELETE FROM T_CS_CXPXDY WHERE SQL_XH = :1
Thu Mar 22 19:19:28 hu Mar 22 19:22:01
ksbsrv: No startup acknowledgement from forked process after 30 seconds
g0Thu Mar 22 19:24:33 ksbsrv: No startup acknowledgement from forked process after 30 seconds

Thu Mar 22 21:52:01 2007
6`&xgF(T,wG'F'h%[0SELECT /*+ Q160113000 NO_EXPAND ROWID(A1) */ A1.ROWID,A1."SQL_XH",A1."XH" FROM "DB_ODS"."T_CS_CXPXDY" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1 WHERE A1."SQL_XH"=:B1
Thu Mar 22 21:52:01 2007
0ORA-01555 caused by SQL statement below (Query Duration=1174571518 sec, SCN: 0x0000.b1d48904):
H0Thu Mar 22 21:52:01 2007
TD0SELECT /*+ Q160113000 NO_EXPAND ROWID(A1) */ A1.ROWID,A1."SQL_XH",A1."XH" FROM "DB_ODS"."T_CS_CXPXDY" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1 WHERE A1."SQL_XH"=:B1

在google 上搜到一篇文章：

CPU LOAD

(Excerpt from a TAR - still open)

>From time to time, our Oracle test server (9.2.0.4 on Intel/Linux, 2

CPUs) got unusuable at CPU load of 99% as shown by top; in this state, nothing else could be done with Oracle, even trying to connect via sqlplus took about 1 hour (assuming one would wait that long). Processes running were Oracle processes and kswap (meaning that swapping was heavily taking place).

Users complain in such a situation and my only remedy has been to reboot the server. pstack and oradebug could not be used. After analyzing lots of things we found out that nothing seems to be wrong with the database - it is just that a very inefficient query is running which blocks the Oracle server and avoids any other activity. Well, one message was found in the alert log, saying ksbsrv: No startup acknowledgement from forked process after 30 seconds
G0but no ORA- error appears.
0Statspack Reports revealed a unusuable high "process startup" wait time.

According to my experience under the Sun/Solaris platform, even if the 4 CPUs of our E3500 are at maximum load (showing an average idle of 0%), the Oracle (8.1.7) server is still available for new sessions (which run of course slower than usual). This happens quite often by the way, so it is a reliable experience.

Assuming that the situation is caused by a bad query, I am concerned about the limited responsiveness of the server, since most of our queries are of batch type and run hours in the production platform, which is Sun/Solaris 7. If we transfer the production DB to the new, much faster Intel/Linux platform, we could have heavy trouble when such batch job run. They would be served in a first-in first-out base serialized one after one (limited by the number of CPUs available).

Is there a way to adjust priorities or something to guarantee an even distribution of computing power of the Oracle server? Is this more a operating system problem than it is an oracle one? (Note: at the OS level, reactivity is much better). We use RedHat Linux AS 2.1 with asynch_io=true. This is supposed to be a certified environment (Dell Power Edge 2650) for enterprise use of Oracle.

Oracle Corp. is quite clueless until now, so my question to the forum.

Thanks in advance

因为昨天我对物化试图进行了调整，每个物化试图的并行度由３　增加到了１６，我以为是这个原因造成的，但是一看alert.log 的报警时间是晚上7:00多，而物化试图的刷新是在凌晨，于是排除了这个原因！但是因为有ora-01555的错误，并且因为t_cs_cxpxdy 只有5296条数据，不可能Duration=1174571518 sec，所以我想可能是server process 创建问题或者是oracle bug 之类的！

SQL> connect / as sysdba;

connect an idle instance

SQL>shutdown immediate;

竟然报错

SQL> startup ;

ORA-01031: insufficient privileges

莫名其妙！

但是无意中发现oracle 下的文件的属主变成了root,继续查看，竟然不少文件属主都是变成了root，不解！！
于是执行
chown -R oracle:dba /opt/oracle
su - oracle
sqlplus /nolog
SQL>connect / as sysdba;
SQL>startup ;ITPUB个人空间!
报错
然后报权限不足。
ps -ef | grep oracle
发现竟然仍然有很多的oracle process
没办法，实在是没办法，只好重新启动小型机了
重新启动小型机后，重新启动数据库，一切恢复了正常！！


[color=red]alert.log的内容。[/color]
Wed Nov 21 13:34:53 2012
Thread 1 advanced to log sequence 20868
Current log# 1 seq# 20868 mem# 0: D:\ORACLE\ORADATA\HAPH\REDO01.LOG
Wed Nov 21 14:04:54 2012
Thread 1 advanced to log sequence 20869
Current log# 2 seq# 20869 mem# 0: D:\ORACLE\ORADATA\HAPH\REDO02.LOG
Wed Nov 21 14:34:25 2012
Thread 1 advanced to log sequence 20870
Current log# 3 seq# 20870 mem# 0: D:\ORACLE\ORADATA\HAPH\REDO03.LOG
Wed Nov 21 15:04:00 2012
Thread 1 advanced to log sequence 20871
Current log# 4 seq# 20871 mem# 0: F:\ORACLE_LOG\LOG4\LOG4.LOG
Wed Nov 21 15:34:06 2012
Thread 1 advanced to log sequence 20872
Current log# 5 seq# 20872 mem# 0: F:\ORACLE_LOG\LOG5\LOG5.LOG
Wed Nov 21 16:05:19 2012
Thread 1 advanced to log sequence 20873
Current log# 1 seq# 20873 mem# 0: D:\ORACLE\ORADATA\HAPH\REDO01.LOG
Wed Nov 21 16:36:25 2012
Thread 1 advanced to log sequence 20874
Current log# 2 seq# 20874 mem# 0: D:\ORACLE\ORADATA\HAPH\REDO02.LOG
Wed Nov 21 17:08:33 2012
Thread 1 advanced to log sequence 20875
Current log# 3 seq# 20875 mem# 0: D:\ORACLE\ORADATA\HAPH\REDO03.LOG
Wed Nov 21 17:40:42 2012
Thread 1 advanced to log sequence 20876
Current log# 4 seq# 20876 mem# 0: F:\ORACLE_LOG\LOG4\LOG4.LOG
Wed Nov 21 20:00:05 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:02:59 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:52:16 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:52:27 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:52:37 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:52:57 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:53:08 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:56:07 2012
ksbsrv: No startup acknowledgement from forked process after 30 seconds
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Wed Nov 21 20:56:53 2012
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Dump file d:\oracle\admin\haph\bdump\alert_haph.log
Wed Nov 21 21:25:55 2012
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Wed Nov 21 21:25:55 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 109051904
sga_max_size = 1343825276
large_pool_size = 209715200
java_pool_size = 33554432
control_files = D:\oracle\oradata\haph\CONTROL01.CTL, D:\oracle\oradata\haph\CONTROL02.CTL, D:\oracle\oradata\haph\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 838860800
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = haph
dispatchers = (PROTOCOL=TCP) (SERVICE=haphXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = D:\oracle\admin\haph\bdump
user_dump_dest = D:\oracle\admin\haph\udump
core_dump_dest = D:\oracle\admin\haph\cdump
sort_area_size = 524288
db_name = haph
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Wed Nov 21 21:25:58 2012
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Nov 21 21:26:00 2012
alter database mount exclusive
Wed Nov 21 21:26:05 2012
Successful mount of redo thread 1, with mount id 3661874792.
Wed Nov 21 21:26:05 2012
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Wed Nov 21 21:26:05 2012
alter database open
Wed Nov 21 21:26:05 2012
Beginning crash recovery of 1 threads
Wed Nov 21 21:26:05 2012
Started first pass scan
Wed Nov 21 21:26:06 2012
Completed first pass scan
34992 redo blocks read, 3809 data blocks need recovery
Wed Nov 21 21:26:06 2012
Started recovery at
Thread 1: logseq 20876, block 129631, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 4 Seq 20876 Reading mem 0
Mem# 0 errs 0: F:\ORACLE_LOG\LOG4\LOG4.LOG
Wed Nov 21 21:26:14 2012
Ended recovery at
Thread 1: logseq 20876, block 164623, scn 0.2481648691
3809 data blocks read, 3809 data blocks written, 34992 redo blocks read
Crash recovery completed successfully
Wed Nov 21 21:26:16 2012
Thread 1 advanced to log sequence 20877
Thread 1 opened at log sequence 20877
Current log# 5 seq# 20877 mem# 0: F:\ORACLE_LOG\LOG5\LOG5.LOG
Successful open of redo thread 1.
Wed Nov 21 21:26:16 2012
SMON: enabling cache recovery
Wed Nov 21 21:26:17 2012
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Wed Nov 21 21:26:17 2012
SMON: enabling tx recovery
Wed Nov 21 21:26:17 2012
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open