- 浏览: 138309 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (52)
- oracle linux R6-U2-server-i386 (8)
- oracleDBA (25)
- 虚拟机-vmware-8.0.2 (2)
- linux-ubuntu (0)
- oracle背景资料 (28)
- DOS (3)
- sql常用操作 (2)
- win7/XP/03/08/2K/ (1)
- NT系统优化 (2)
- 网络 (1)
- 安全 (0)
- 乌七杂八资料库 (23)
- 个人心情 (2)
- ESXI 4.1 (1)
- vsphere5.0 (4)
- rac (3)
- oracle linux R6-U2-server-i386,oracle9204 (2)
- 条件处理符号 (1)
- 批处理 (2)
最新评论
-
yexiaochong:
我从头到尾看完了。写得也挺有道理的。我也类似你一样。花了不止两 ...
工作意向及规划个人建议 -
netkiller.github.com:
写的真多,实在看不下去了,支持一下
工作意向及规划个人建议
摘抄2篇文章。都是这个问题的描述,结果都是没有办法解决,直接重启搞定的。我的环境是WINDOWS2003-32+ORACLE9201,最后附上我的alert.log部分内容以供参考
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
因为昨天我对物化试图进行了调整,每个物化试图的并行度由3 增加到了16,我以为是这个原因造成的,但是一看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
没办法,实在是没办法,只好重新启动小型机了
重新启动小型机后,重新启动数据库,一切恢复了正常!!
alert.log的内容。
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
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
因为昨天我对物化试图进行了调整,每个物化试图的并行度由3 增加到了16,我以为是这个原因造成的,但是一看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
没办法,实在是没办法,只好重新启动小型机了
重新启动小型机后,重新启动数据库,一切恢复了正常!!
alert.log的内容。
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
发表评论
-
oracle 10g 使用expdp network_link导出远程数据库到本地文件 【转载】
2012-12-11 13:45 5426文章不错,解除疑惑,oracle10G0204可以实现远程导入 ... -
oracle中ddl不能回滚的问题【转载】
2012-11-27 10:25 1101原文地址: http://www.2cto.com/datab ... -
oracle在linux下安装遇到的所有的问题总结说明(3)
2012-10-30 15:48 1314大部分内容为已经遇到过的问题解决方案,基本都是谷歌出来的,留下 ... -
oracle on linux非官方做法遇到的问题集锦(1)
2012-10-30 15:44 17491、安装oracle linux 6.2创 ... -
oracle在linux下安装遇到的所有的问题总结说明(2)(20121122修改)
2012-10-30 15:35 2100安装的时候遇到的问题说明(部分内容 ... -
oracle在linux下安装遇到的所有的问题总结说明(1)
2012-10-30 15:31 1339安装oracle 官方文档,翻译说明( ... -
oracle在ORACLElinux下安装完整步骤【虚拟机】【含视频】(20121220修改)
2012-10-30 15:24 3161经过N久的不断尝试,终于彻底摸清了,oracle 10G在 ... -
过大的Oracle监听日志文件处理(转载)
2012-10-15 12:53 2029过大的Oracle监听日志文件处理 2011-03-11 10 ... -
.【转载】oracle物化视图的一般用法
2012-08-29 10:45 911本文转载自乔文《oracle ... -
ora-12542 address in used(转载)
2012-06-16 16:51 1609转载的,原作者网址:http://space.itpub.ne ... -
EXP-00091 Exporting questionable statistics
2012-06-15 20:02 1202http://space.itpub.net/trackbac ... -
虚拟机中vsphere克隆最简单方法
2012-06-05 23:37 16373vsphere不如workstation好用 ... -
《转载》oracle绑定变量详解
2012-05-11 10:18 859原文转载自linuxg公社 http://www.linuxi ... -
oracle9I坏块错误
2012-05-10 18:41 863在一个客户的9201 for Windows数据库中发现了 ... -
《转载》ora-00020超出最大进程数
2012-05-10 18:42 1002纯属学习查询资料,没什么说的,当资料了,感谢作者,转载无罪 ... -
《转载》oracle1000问
2012-05-10 18:42 784没什么说的,比较适合初学者的,呵呵,一块学习吧。 -
《转载》韩顺平-玩转oracle视频教材笔记(文档)
2012-05-10 18:42 1009转载的,那下载的,忘了,全名叫oracle10G从入门到精 ... -
ora-27100shared memory realm already exists(20120529修改)
2012-05-10 18:42 2292这个问题一般情况下,是因为调整内存参数的时候出现问题了,而且数 ... -
FATAL ERROR IN TWO-TASK SERVER: error = 12571
2012-05-11 10:18 1208这个问题貌似是因为应用程序和数据库在连接的时候出现各种意外 ... -
ora-12516监听程序找不到符合协议堆栈要求的可用处理程
2012-05-11 10:19 10043oracle服务器上某个数据库出现' ORA-12516: T ...
相关推荐
No Money, No Excuses Chapter 13. Startup Marketing Alchemy Chapter 14. ROI of Happy Customers Chapter 15. Scaling to New Heights Chapter 16. Always Be Innovating Chapter 17. The Exponential Power of ...
"startup.bat 闪退问题解决办法" startup.bat闪退问题是Windows操作系统中一种常见的故障现象,特别是在使用Tomcat服务器时。这种问题的出现可能是由于多方面的原因,包括系统配置、环境变量、文件权限、命令语法等...
`STM32F10x_startup`指的是针对该系列芯片的启动文件,通常以`.s`格式存在,它是微控制器在上电或复位后执行的第一段代码,对整个系统的初始化起着关键作用。 1. **启动文件的作用**: - 初始化堆栈:设置初始堆栈...
【佳天下 Startup Delayer V2.5.0.138绿色汉化单文件版】是一款专门用于优化系统启动过程的工具,它允许用户自定义程序的启动顺序,以提高计算机启动时的性能和响应速度。这个版本是经过汉化的,更适合中文用户使用...
startup.sh linux 启动jar包命令startup.sh linux 启动jar包命令startup.sh linux 启动jar包命令startup.sh linux 启动jar包命令startup.sh linux 启动jar包命令
标题“head-nommu.rar_startup”暗示我们正在讨论Linux内核启动过程中与非分页内存管理(Non-MMU,Non-Memory Management Unit)相关的代码。描述提到“Common kernel startup code (non-paged MM)”,这进一步确认...
arm的startup.s解析,有详细的注释,适用用于lpc类的 arm
默认情况下,如果不指定任何选项,`STARTUP`命令将执行`STARTUP MOUNT`之后再自动执行`ALTER DATABASE OPEN`,即打开所有数据文件和日志文件,使数据库进入正常工作状态。 命令示例:`SQL> startup` **4. 特殊启动...
startup.cmd
Java Spring Startup Analyzer是一款强大的工具,专门用于分析Spring应用程序的启动过程,帮助开发者深入理解应用程序启动时间的消耗,找出性能瓶颈,从而优化应用的启动速度。这个工具为开发者提供了一个交互式的...
SQL> select count(*) from v$process; SQL> select count(*) from v$session; ``` #### 二、修改Process数量 接下来,我们学习如何调整`process`的数量。`processes`参数定义了Oracle实例可以创建的最大并发...
startup_stm32f103xb.s
Wince 6 startup processes, PROC: Name hProcess: CurAKY :dwVMBase:CurZone P00: NK.EXE 00400002 00000000 80070000 0000000b P01: shell.exe 00eb0002 00000000 00010000 00000000 P02: udevice.exe 01a...
标题 "startup_n.rar_S3C4510 STARTUP_startup" 暗示了这是一个与S3C4510处理器相关的启动代码压缩包。S3C4510是一款由Samsung制造的ARM7TDMI架构的微处理器,常用于嵌入式系统设计。这个压缩包中的"startup"部分...
启动项目管理器startup启动项目管理器startup
startup_stm32f40_41xxx.s
### 清除宏病毒(StartUp.xls):详解与步骤 #### 一、宏病毒简介 宏病毒是一种利用Microsoft Office等应用程序中的宏功能来传播的恶意软件。它们通常隐藏在文档或工作簿中,当用户打开这些文件时,宏会自动执行,...
STARTUP.A51
startup_stm32f4xx.s文件,配合我的学习笔记STM32F4启动文件http://blog.csdn.net/a5130599/article/details/8227591