转至繁体中文版　　　  | 网站首页 | 图文教程 | 资源下载 | 站长博客 | 图片素材 | 武汉seo | 武汉网站优化 | 
最新公告：     敏韬网|教学资源学习资料永久免费分享站！  [mintao  2008年9月2日]        
您现在的位置： 学习笔记 >> 图文教程 >> 数据库 >> ORACLE >> 正文
Oracle诊断案例-SGA与Swap之一    热     ★★★★

Oracle诊断案例-SGA与Swap之一

作者：闵涛 文章来源：闵涛的学习笔记 点击数：3057 更新时间：2009/4/22 22:06:53
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.
Tue Mar 23 12:24:56 2004
SMON: enabling tx recovery
Tue Mar 23 12:24:56 2004
Database Characterset is ZHS16GBK
Tue Mar 23 12:25:01 2004
SMON: Parallel transaction recovery tried
Tue Mar 23 12:25:01 2004
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Tue Mar 23 12:28:26 2004
/* OracleOEM */ ALTER DATABASE DATAFILE ''''/export/home1/oracle/oradata/hswapdb/users01.dbf'''' RESIZE 2501760K
Tue Mar 23 12:28:26 2004
ORA-3297 signalled during: /* OracleOEM */ ALTER DATABASE DATAFILE ''''/export/h...
Tue Mar 23 12:28:32 2004
/* OracleOEM */ ALTER DATABASE DATAFILE ''''/export/home1/oracle/oradata/hswapdb/users01.dbf'''' RESIZE 2501760K
ORA-3297 signalled during: /* OracleOEM */ ALTER DATABASE DATAFILE ''''/export/h...
Tue Mar 23 12:28:53 2004
/* OracleOEM */ ALTER DATABASE DATAFILE ''''/export/home1/oracle/oradata/hswapdb/users01.dbf'''' RESIZE 3501760K
Tue Mar 23 12:28:53 2004
ORA-3297 signalled during: /* OracleOEM */ ALTER DATABASE DATAFILE ''''/export/h...
Tue Mar 23 13:40:45 2004
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 11, op = fork, loc = skgpspawn5
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
Tue Mar 23 13:42:02 2004
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
:q


发现数据库多次重起，并记录了部分错误信息

该提示说明数据库无法spawn a new session.

quote Yong Huang''''s comment:

The number in "skgpspawn failed:category = 27142" is probably ORA error:

$ oerr ora 27142
27142, 0000, "could not create new process"
// *Cause: OS system call
// *Action: check errno and if possible increase the number of processes

OSD (OS-dependent) errors are almost always shown as an skg... error (probably means "system, kernel generic").

I don''''t know what "depinfo = 12" means.

 

4.尝试连接数据库

收到错误信息，无法连接数据库

 


$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on 星期二 3月 23 14:14:06 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-12540: TNS: 超出内部限制

请输入用户名:
ERROR:
ORA-12540: TNS: 超出内部限制

请输入用户名:
ERROR:
ORA-12540: TNS: 超出内部限制

SP2-0157: 在3次尝试之后无法 CONNECT 到 ORACLE, 退出 SQL*Plus

 


内部限制超过，通常说明某些系统资源不足.

 

5.检查监听器

发现部分连接被拒绝

 

/export/home1/oracle>lsnrctl services

LSNRCTL for Solaris: Version 9.2.0.3.0 - Production on 23-3月 -2004 14:37:23

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
处理程序:
"DEDICATED" 已建立:0 已被拒绝:0
LOCAL SERVER
服务 "hswapdb.eygle.com" 包含 2 个例程。
例程 "hswapdb", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
处理程序:
"DEDICATED" 已建立:6 已被拒绝:0
LOCAL SERVER
例程 "hswapdb", 状态 READY, 包含此服务的 1 个处理程序...
处理程序:
"DEDICATED" 已建立:21 已拒绝:6 状态:ready
LOCAL SERVER
服务 "hswapdbXDB.eygle.com" 包含 1 个例程。
例程 "hswapdb", 状态 READY, 包含此服务的 1 个处理程序...
处理程序:
"D000" 已建立:0 已被拒绝:0 当前: 0 最大: 972 状态: ready
DISPATCHER <machine: wapplatform, pid: 25839>
(ADDRESS=(PROTOCOL=tcp)(HOST=wapplatform)(PORT=32869))
命令执行成功

 

在listener.log中找到了相关错误信息

 

23-3\324\302 -2004 12:19:40 * (CONNECT_DATA=(SID=hswapdb)(CID=(PROGRAM=C:\WINNT\Microsoft.NET\Framework\v1.1.4322\aspnet_wp.e
xe)(HOST=SWAN)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.30.125)(PORT=1291)) * establish * hswapdb * 12500
TNS-12500: TNS\243\272\274\340\314\375\306\367\316\264\304\334\306\364\266\257\327\250\323\303\265\304\267\376\316\361\306\36
7\275\370\263\314
TNS-12540: TNS\243\272\263\254\263\366\304\332\262\277\274\253\317\336\317\336\326\306
TNS-12560: TNS: \320\255\322\351\312\312\305\344\306\367\264\355\316\363
TNS-00510: \263\254\263\366\304\332\262\277\274\253\317\336\317\336\326\306
Solaris Error: 12: Not enough space
23-3\324\302 -2004 12:19:50 * (CONNECT_DATA=(SID=hswapdb)(CID=(PROGRAM=C:\Program Files\PLSQL Developer\PLSQLDev.exe)(HOST=SW
AN)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.30.125)(PORT=1292)) * establish * hswapdb * 12500
TNS-12500: TNS\243\272\274\340\314\375\306\367\316\264\304\334\306\364\266\257\327\250\323\303\265\304\267\376\316\361\306\36
7\275\370\263\314
TNS-12540: TNS\243\272\263\254\263\366\304\332\262\277\274\253\317\336\317\336\326\306
TNS-12560: TNS: \320\255\322\351\312\312\305\344\306\367\264\355\316\363
TNS-00510: \263\254\263\366\304\332\262\277\274\253\317\336\317\336\326\306
Solaris Error: 12: Not enough space

/export/home1/oracle/app/network/log>grep -w 12 /usr/include/sys/errno.h
#define ENOMEM 12 /* Not enough core 

 

quote Yong Huang''''s comment:

$ grep -w 12 /usr/include/sys/errno.h
#define ENOMEM 12 /* Not enough core */

Here "core" means memory, including real RAM memory and swap space.

6.退出Oracle用户检查

检查系统日志信息，发现大量失败的su操作
有swap区不足的报告

 

/export/home1/oracle/admin/hswapdb/bdump>exit

            wapplatform:/>dmesg

2004年03月23日 星期二 14时00分32秒 CST
Mar 22 22:52:36 wapplatform elfexec: [ID 700856 kern.notice] ps: Cannot find ^?ELF^A^B^A
Mar 22 22:53:00 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 22:53:09 wapplatform elfexec: [ID 700856 kern.notice] w: Cannot find ^?ELF^A^B^A
Mar 22 22:53:53 wapplatform last message repeated 4 times
Mar 22 22:56:28 wapplatform elfexec: [ID 700856 kern.notice] ipnat: Cannot find ^?ELF^B^B^A
Mar 22 22:58:00 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 22:59:54 wapplatform elfexec: [ID 700856 kern.notice] ipnat: Cannot find ^?ELF^B^B^A
Mar 22 23:02:26 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 23:03:00 wapplatform last message repeated 1 time
Mar 22 23:08:00 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 23:08:34 wapplatform elfexec: [ID 700856 kern.notice] ipnat: Cannot find ^?ELF^B^B^A
Mar 22 23:10:27 wapplatform last message repeated 3 times
Mar 22 23:11:49 wapplatform elfexec: [ID 700856 kern.notice] ipnat: Cannot find ^?ELF^B^B^A
Mar 22 23:11:52 wapplatform last message repeated 1 time
Mar 22 23:13:01 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 23:18:01 wapplatform last message repeated 1 time
Mar 22 23:23:01 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 23:28:01 wapplatform last message repeated 1 time
Mar 22 23:33:01 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 23:38:01 wapplatform last message repeated 1 time
Mar 22 23:43:01 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 23:48:01 wapplatform last message repeated 1 time
Mar 22 23:53:01 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 22 23:58:01 wapplatform last message repeated 1 time
Mar 23 00:00:00 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg /export/home1: file system full
Mar 23 00:00:00 wapplatform sendmail[3075]: [ID 702911 mail.crit] My unqualified host name (wapplatform) unknown; sleeping

for retry
Mar 23 00:01:00 wapplatform sendmail[3075]: [ID 702911 mail.alert] unable to qualify my own domain name (wapplatform) --

using short name
Mar 23 00:02:36 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
Mar 23 00:03:02 wapplatform last message repeated 1 time
Mar 23 00:08:02 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full
....

Mar 23 10:18:15 wapplatform ufs: [ID 845546 kern.notice] NOTICE: a

上一页  [1] [2] [3] [4]  下一页


[ORACLE]Oracle诊断案例-Spfile案例一则  [ORACLE]Oracle诊断案例-SGA与Swap之二
[ORACLE]Oracle诊断案例-Sql_trace之二  [ORACLE]Oracle诊断案例-Sql_trace之一
[ORACLE]ORACLE  SGA  的分配  [ORACLE]Oracle诊断案例-Job任务停止执行
[ORACLE]Oracle内存结构:SGA的区域信息  [MySql]如何在Linux下增加Swap
教程录入：mintao    责任编辑：mintao 
  • 上一篇教程：

  • 下一篇教程：
    • 【字体：小 大】【发表评论】【加入收藏】【告诉好友】【打印此文】【关闭窗口】
    　 注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网]
    　　网友评论：（只显示最新10条。评论内容只代表网友观点，与本站立场无关！）
    同类栏目
    · Sql Server  · MySql
    · Access  · ORACLE
    · SyBase  · 其他
    更多内容
    热门推荐 更多内容
  • 没有教程
    		•
    赞助链接
    更多内容
    闵涛博文 更多关于武汉SEO的内容
    500 - 内部服务器错误。

    500 - 内部服务器错误。

    您查找的资源存在问题，因而无法显示。
    | 设为首页 |加入收藏 | 联系站长 | 友情链接 | 版权申明 | 广告服务
    MinTao学以致用网

    Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
    闵涛 投放广告、内容合作请Q我! E_mail:admin@mintao.net(欢迎提供学习资源)

    站长：MinTao ICP备案号：鄂ICP备11006601号-18

    闵涛站盟:医药大全-武穴网。A打造B、C、D……
    		咸宁网络警察报警平台