查看: 7305|回复: 9

ORA-27300错误 和metalink 已经折腾了一周

[复制链接]
招聘 : 产品经理/专员
论坛徽章:
1
生肖徽章2007版:鼠
日期:2009-04-30 11:13:50
发表于 2009-3-31 20:37 | 显示全部楼层 |阅读模式
Tue Mar 31 13:17:42 2009
Completed: ALTER DATABASE OPEN
Tue Mar 31 17:00:41 2009
Process startup failed, error stack:
Tue Mar 31 17:00:41 2009
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_8408.trc:
ORA-27300: OS 系统相关操作: spcdr:9261:4200 失败, 状态为: 997
ORA-27301: OS 故障消息: 重叠 I/O 操作在进行中。
ORA-27302: 错误发生在: skgpspawn



SQL> conn cdgd/cdgd@146
ERROR:
ORA-12518: TNS: 监听程序无法分发客户机连接



SQL> select substr(name,1,30) name, value, unit from v$pgastat;

NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter                      524288000 bytes
aggregate PGA auto target                           356520960 bytes
global memory bound                                 104857600 bytes
total PGA inuse                                     128259072 bytes
total PGA allocated                                 254377984 bytes
maximum PGA allocated                               255469568 bytes
total freeable PGA memory                                   0 bytes
process count                                             557
max processes count                                       557
PGA memory freed back to OS                                 0 bytes
total PGA used for auto workar                         106496 bytes
maximum PGA used for auto work                        3880960 bytes
total PGA used for manual work                              0 bytes
maximum PGA used for manual wo                              0 bytes
over allocation count                                       0
bytes processed                                     648549376 bytes
extra bytes read/written                                    0 bytes
cache hit percentage                                      100 percent
recompute count (total)                                  8560



[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, Standard" /noexecute=optout /fastdetect /3GB /PAE

[ 本帖最后由 lazycn 于 2009-3-31 20:55 编辑 ]
招聘 : 产品经理/专员
论坛徽章:
1
生肖徽章2007版:鼠
日期:2009-04-30 11:13:50
 楼主| 发表于 2009-3-31 20:37 | 显示全部楼层
Tue Mar 31 13:15:27 2009
Process m000 died, see its trace file
Tue Mar 31 13:15:27 2009
ksvcreate: Process(m000) creation failed
Tue Mar 31 13:15:41 2009
Shutting down instance (abort)
License high water mark = 559
Tue Mar 31 13:17:31 2009
Instance terminated by USER, pid = 5988
Tue Mar 31 13:17:32 2009
Starting ORACLE instance (normal)
Tue Mar 31 13:17:32 2009
Specified value of sga_max_size is too small, bumping to 947912704
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\oracle\product\10.2.0\db_2\RDBMS
Autotune of undo retention is turned on.
IMODE=BR
ILAT =121
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 1000
  sessions                 = 1105
  sga_max_size             = 947912704
  __shared_pool_size       = 415236096
  shared_pool_size         = 0
  __large_pool_size        = 83886080
  large_pool_size          = 0
  __java_pool_size         = 4194304
  java_pool_size           = 0
  __streams_pool_size      = 8388608
  streams_pool_size        = 8388608
  spfile                   = D:\ORACLE\PRODUCT\10.2.0\DB_2\DBS\SPFILEORCL.ORA
  nls_language             = SIMPLIFIED CHINESE
  nls_territory            = CHINA
  sga_target               = 947912704
  control_files            = D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
  db_block_size            = 8192
  __db_cache_size          = 427819008
  db_cache_size            = 0
  compatible               = 10.2.0.1.0
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  _kgl_large_heap_warning_threshold= 8388608
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=orclXDB)
  job_queue_processes      = 40
  audit_file_dest          = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
  background_dump_dest     = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
  user_dump_dest           = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
  core_dump_dest           = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
  db_name                  = orcl
  open_cursors             = 300
  pga_aggregate_target     = 524288000
PMON started with pid=2, OS id=5596
PSP0 started with pid=3, OS id=8408
MMAN started with pid=4, OS id=8872
DBW0 started with pid=5, OS id=8684
LGWR started with pid=6, OS id=9936
CKPT started with pid=7, OS id=7624
SMON started with pid=8, OS id=7776
RECO started with pid=9, OS id=7076
CJQ0 started with pid=10, OS id=7100
MMON started with pid=11, OS id=3788
Tue Mar 31 13:17:32 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=8068
Tue Mar 31 13:17:32 2009
starting up 1 shared server(s) ...
Tue Mar 31 13:17:33 2009
ALTER DATABASE   MOUNT
Tue Mar 31 13:17:37 2009
Setting recovery target incarnation to 2
Tue Mar 31 13:17:37 2009
Successful mount of redo thread 1, with mount id 1210732141
Tue Mar 31 13:17:37 2009
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Tue Mar 31 13:17:38 2009
ALTER DATABASE OPEN
Tue Mar 31 13:17:38 2009
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Tue Mar 31 13:17:38 2009
Started redo scan
Tue Mar 31 13:17:38 2009
Completed redo scan
11 redo blocks read, 5 data blocks need recovery
Tue Mar 31 13:17:38 2009
Started redo application at
Thread 1: logseq 114, block 100784
Tue Mar 31 13:17:38 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 114 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Tue Mar 31 13:17:38 2009
Completed redo application
Tue Mar 31 13:17:38 2009
Completed crash recovery at
Thread 1: logseq 114, block 100795, scn 5524772
5 data blocks read, 5 data blocks written, 11 redo blocks read
Tue Mar 31 13:17:39 2009
Thread 1 advanced to log sequence 115
Thread 1 opened at log sequence 115
  Current log# 3 seq# 115 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
Tue Mar 31 13:17:39 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Mar 31 13:17:39 2009
SMON: enabling cache recovery
Tue Mar 31 13:17:39 2009
Successfully onlined Undo Tablespace 1.
Tue Mar 31 13:17:39 2009
SMON: enabling tx recovery
Tue Mar 31 13:17:39 2009
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=24, OS id=7444
Tue Mar 31 13:17:42 2009
Completed: ALTER DATABASE OPEN
Tue Mar 31 17:00:41 2009
Process startup failed, error stack:
Tue Mar 31 17:00:41 2009
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_8408.trc:
ORA-27300: OS 系统相关操作: spcdr:9261:4200 失败, 状态为: 997
ORA-27301: OS 故障消息: 重叠 I/O 操作在进行中。
ORA-27302: 错误发生在: skgpspawn

使用道具 举报

回复
招聘 : 产品经理/专员
论坛徽章:
1
生肖徽章2007版:鼠
日期:2009-04-30 11:13:50
 楼主| 发表于 2009-3-31 20:47 | 显示全部楼层
路过的dba帮kankan
windows 2003 server standard edition sp2
mem:4G
oracle 10.2.0.1

使用道具 举报

回复
论坛徽章:
68
2012新春纪念徽章
日期:2012-01-04 11:51:22奥运会纪念徽章:举重
日期:2012-08-02 22:17:14ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:312013年新春福章
日期:2013-02-25 14:51:24慢羊羊
日期:2015-03-04 14:51:352015年新春福章
日期:2015-03-06 11:57:312015年新春福章
日期:2015-06-11 12:54:06
发表于 2009-3-31 21:30 | 显示全部楼层
AWE开关是否已经打开?

使用道具 举报

回复
论坛徽章:
183
生肖徽章:狗
日期:2006-11-23 04:26:03生肖徽章:羊
日期:2007-09-26 17:08:21生肖徽章:马
日期:2007-09-26 17:08:49授权会员
日期:2007-12-31 19:14:41生肖徽章2007版:牛
日期:2008-03-28 10:02:30奥运会纪念徽章:柔道
日期:2008-04-30 16:28:44奥运会纪念徽章:垒球
日期:2008-05-12 21:28:28奥运会纪念徽章:体操
日期:2008-06-26 10:00:41奥运会纪念徽章:沙滩排球
日期:2008-07-27 12:41:59奥运会纪念徽章:艺术体操
日期:2008-07-30 11:09:47
发表于 2009-3-31 21:32 | 显示全部楼层
在操作系统的boot.ini文件中增加'/3G',然后降低SGA。metalink上的解释如下:
ChangesThe '/3GB'  switch was added to the  'boot.ini'  file at the OS level but the errors are eventually encountered when the database is under heavy load. CauseOS resource issue.
The maximum amount of addressable memory by a process ('oracle.exe' for example) running in a
32-Bit Windows environment has been reached.

In a 32-Bit Windows environment, the total addressable memory by a single process is a 4GB. On Windows, the OS reserves half of this memory by default for kernel memory, leaving 2GB of addressable memory for a user process. By placing the '/3GB' switch in the 'boot.ini'  file, this changes the ratio of kernel memory and user memory. When he  '/3GB'  switch is in place, processes (such as 'oracle.exe’) can address 3GB of virtual memory out of the total 4GB of addressable memory. However, once the 3GB of virtual memory is depleted, the process will fail. SolutionTo implement the solution, please execute the following steps:

Tune the application running on the 32-bit environment so that it will not consume greater than 2.7GB of virtual memory when the  '/3GB'  switch is set in the  'boot.ini'  file. In the case of Oracle encountering the OS resource issue, the options are:

1) Reduce the amount of SGA needed to be allocated for the database.
2) Limit the number of dedicated connections to the database and the amount of memory each user process will consume.
3) Change from dedicated connections to multi-threaded server (MTS) connections as MTS only uses a fraction of the amount of memory allocated to each user process when initial connection to the database is established.

See Metalink Note 225349.1 for an in depth discussion regarding memory usage in a 32-bit Windows environment

===========================

ORA-27300 ORA-27301 ORA-27302 in alert log. Cannot connect to database.
  文档 ID:  注释:371074.1 类型:  PROBLEM
  上次修订日期:  30-JAN-2008 状态:  MODERATED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References



--------------------------------------------------------------------------------


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.3
Oracle Server - Personal Edition - Version: 8.1.7.4 to 10.2.0.3
Oracle Server - Standard Edition - Version: 8.1.7.4 to 10.2.0.3
Microsoft Windows 2000
Microsoft Windows XP
Microsoft Windows Server 2003


Microsoft Windows (32-bit)
SymptomsNo new connections are allowed to the database.

Database appears to be in a hang state.
Errors:

Mon May 22 14:00:46 2006
Errors in file d:\ecomdb\bdump\ecomdb_psp0_1192.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn

written to alert log.


.
Verified the issue by the RDA output provided, which displays errors:

Mon May 22 14:00:46 2006
Errors in file d:\ecomdb\bdump\ecomdb_psp0_1192.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn

in the alert log included in RDA output.

ChangesThe '/3GB'  switch was added to the  'boot.ini'  file at the OS level but the errors are eventually encountered when the database is under heavy load. CauseOS resource issue.
The maximum amount of addressable memory by a process ('oracle.exe' for example) running in a
32-Bit Windows environment has been reached.

In a 32-Bit Windows environment, the total addressable memory by a single process is a 4GB. On
Windows, the OS reserves half of this memory by default for kernel memory, leaving 2GB of
addressable memory for a user process. By placing the '/3GB' switch in the 'boot.ini'  file, this
changes the ratio of kernel memory and user memory. When the  '/3GB'  switch is in place, processes (such as 'oracle.exe’) can address 3GB of virtual memory out of the total 4GB of addressable memory. However, once the 3GB of virtual memory is depleted, the process will fail. SolutionTo implement the solution, please execute the following steps:

Tune the application running on the 32-bit environment so that it will not consume greater than
2.7GB of virtual memory when the  '/3GB'  switch is set in the  'boot.ini'  file.
In the case of Oracle encountering the OS resource issue, the options are:

1) Reduce the amount of SGA needed to be allocated for the database.
2) Limit the number of dedicated connections to the database and the amount of memory each user
process will consume.
3) Change from dedicated connections to multi-threaded server (MTS) connections as MTS only
uses a fraction of the amount of memory allocated to each user process when initial connection to
the database is established.


See Metalink Note 225349.1 for an in depth discussion regarding memory usage in a 32-bit Windows environment.

ReferencesNote 225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms

使用道具 举报

回复
招聘 : 产品经理/专员
论坛徽章:
1
生肖徽章2007版:鼠
日期:2009-04-30 11:13:50
 楼主| 发表于 2009-3-31 21:32 | 显示全部楼层
Dump file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_8408.trc
Tue Mar 31 17:00:41 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V5.2 Service Pack 1
CPU                 : 8 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:2500M/4092M, Ph+PgF:5866M/8018M, VA:102M/3071M
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 3

Windows thread id: 8408, image: ORACLE.EXE (PSP0)


*** SERVICE NAMESYS$BACKGROUND) 2009-03-31 17:00:41.102
*** SESSION ID1104.1) 2009-03-31 17:00:41.102
*** 2009-03-31 17:00:41.102
Process startup failed, error stack:
ORA-27300: OS 系统相关操作: spcdr:9261:4200 失败, 状态为: 997
ORA-27301: OS 故障消息: 重叠 I/O 操作在进行中。
ORA-27302: 错误发生在: skgpspawn
*** 2009-03-31 17:01:42.102
Process startup failed, error stack:
ORA-27300: OS 系统相关操作: spcdr:9261:4200 失败, 状态为: 997
ORA-27301: OS 故障消息: 重叠 I/O 操作在进行中。
ORA-27302: 错误发生在: skgpspawn
*** 2009-03-31 17:02:43.118

使用道具 举报

回复
论坛徽章:
11
CTO参与奖
日期:2009-02-03 14:04:30鲜花蛋
日期:2012-04-28 09:12:592010广州亚运会纪念徽章:卡巴迪
日期:2011-01-28 16:58:54ITPUB9周年纪念徽章
日期:2010-10-08 09:34:022010年世界杯参赛球队:斯洛文尼亚
日期:2010-02-03 05:31:212010年世界杯参赛球队:斯洛伐克
日期:2010-01-01 15:02:272010年世界杯参赛球队:塞尔维亚
日期:2009-12-31 09:53:35祖国60周年纪念徽章
日期:2009-10-09 08:28:00数据库板块每日发贴之星
日期:2009-08-17 01:01:022009日食纪念
日期:2009-07-22 09:30:00
发表于 2009-3-31 21:33 | 显示全部楼层
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_8408.trc

传上来让大家看看

使用道具 举报

回复
招聘 : 产品经理/专员
论坛徽章:
1
生肖徽章2007版:鼠
日期:2009-04-30 11:13:50
 楼主| 发表于 2009-3-31 21:48 | 显示全部楼层
没有开AWE,因为内存只有4G。打开了/3GB /PAE
sga已经降低到900M了。

使用道具 举报

回复
论坛徽章:
11
CTO参与奖
日期:2009-02-03 14:04:30鲜花蛋
日期:2012-04-28 09:12:592010广州亚运会纪念徽章:卡巴迪
日期:2011-01-28 16:58:54ITPUB9周年纪念徽章
日期:2010-10-08 09:34:022010年世界杯参赛球队:斯洛文尼亚
日期:2010-02-03 05:31:212010年世界杯参赛球队:斯洛伐克
日期:2010-01-01 15:02:272010年世界杯参赛球队:塞尔维亚
日期:2009-12-31 09:53:35祖国60周年纪念徽章
日期:2009-10-09 08:28:00数据库板块每日发贴之星
日期:2009-08-17 01:01:022009日食纪念
日期:2009-07-22 09:30:00
发表于 2009-3-31 21:59 | 显示全部楼层
oracle承认这是一个bug(10.2.0.2.0之前10g版本),BUG 5607984 - ORACLE DOES NOT CLOSE TCP CONNECTIONS. REMAINS IN CLOSE_WAIT STATE. [On Windows 32-bit].
可打补丁patch number 5639232

使用道具 举报

回复
论坛徽章:
11
CTO参与奖
日期:2009-02-03 14:04:30鲜花蛋
日期:2012-04-28 09:12:592010广州亚运会纪念徽章:卡巴迪
日期:2011-01-28 16:58:54ITPUB9周年纪念徽章
日期:2010-10-08 09:34:022010年世界杯参赛球队:斯洛文尼亚
日期:2010-02-03 05:31:212010年世界杯参赛球队:斯洛伐克
日期:2010-01-01 15:02:272010年世界杯参赛球队:塞尔维亚
日期:2009-12-31 09:53:35祖国60周年纪念徽章
日期:2009-10-09 08:28:00数据库板块每日发贴之星
日期:2009-08-17 01:01:022009日食纪念
日期:2009-07-22 09:30:00
发表于 2009-3-31 21:59 | 显示全部楼层
网上找到的。
大家参考一下。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表