Oracle诊断案例-Spfile案例一则

ITPUB 第 1 页科技技术新动力

本期内容导读 Oracle 诊断案例 -Spfile 案例一则 By Eygle 本文通过一个 UNDO 表空间丢失的案例, 详细的介绍了怎样通过使用 spfile 来解决问题, 而无需再手工修改 pfile 文件从而减少了犯错的可能 Oracle 诊断案例 -SGA 与 Swap 之一 By Eygle Oracle 数据库问题的解决从来就离不开操作系统, 很多时候我们必须通过 操作系统一级的手段来诊断并解决问题 通过本文的介绍会使您对这点有更 深刻的认识和理解 Oracle 诊断案例 -SGA 与 Swap 之二 By Eygle SGA 设置不当很容易引起的数据库异常, 问题本身并不复杂, 所以这一类 问题应该在数据库规划和建设阶段就避免掉 ORACLE 诊断事件及深入解析 10053 事件 By wanghai,liyongdong Oracle 为 RDBMS 提供了多种的诊断工具, 诊断事件 (Event) 是其中一 种常用 好用的方法, 它使 DBA 可以方便的转储数据库各种结构及跟踪特 定事件的发生 用 events 跟踪解决不能创建物化视图一例 By Fenng 本文作者通过一个统计系统的数据同步案例, 详尽分析了如何用 events 跟踪解决不能创建物化视图, 相信对读者受益匪浅 ITPUB 第 2 页科技技术新动力

ORA-1157 错误解决手册 By coolly ORA-1157, "cannot identify/lock data file %s - see DBWR trace file" 这个问题相信不少人都遇到过, 看看本文的作者是怎样分析和 解决的吧, 或许您能从中悟出不少东西 一条 sql 导致数据库整体性能下降的诊断和解决的全过程 By biti_rainy 一条编写不当的 SQl 语句足可以使数据库性能极具下降, 使 DBA 焦头烂额, 通过本文希望能引起大家的重视 修改 fet$ 基表, 结合碎片案例实战 By logzgh 本文介绍了怎样通过修改 fet$ 基表来解决表空间碎片过多的问题, 此方法 虽直接有效但存在一定风险, 建议对 Oracle 有深刻了解后, 方可尝试 ITPUB 第 3 页科技技术新动力

ITPUB 技术丛书第三册 深入浅出 Oracle-DBA 入门 进阶与诊断案例 即将由人民邮电出版社出版 作者 : 盖国强 (eygle) 内容简介 : 本书分为 9 章, 具体结构划分如下 第一章 : 数据库的启动和关闭, 从基础入手, 讲解 Oracle 数据库的启动和关闭, 并深入探讨数据库启动关闭的核心本质及内部处理 ITPUB 第 4 页科技技术新动力

第二章 : 参数及参数文件, 这一部分从 Oracle 启动必需的参数文件入手, 讲解 重要参数 参数文件对于 Oracle 的作用, 并结合 RAC 环境,Oracle10g 环境 介绍参数文件等的不断改进和变迁 第三章 : 数据字典, 深入到数据库的核心, 全面了解数据字典的机制和重要性 第四章 : 内存管理,Oracle 的内存管理非常重要, 本章就 SGA PGA 的管理进 行探讨, 并深入介绍 Oracle 内存管理技术在 Oracle8i/9i/10g 不同版本中的变 迁 第五章 :Buffer Cache 与 Shared Pool 原理, 本章深入介绍了 Buffer Cache 和 Shared Pool 的原理, 并涉及闩锁和热点块等深入话题 第六章 : 重做, 重做机制是 Oracle 恢复的保障, 本章针对 Oracle 的重做机制 进行探讨, 并涉及重做的内部原理及工作机制 第七章 : 回滚与撤销, 回滚和事务密切相关, 本章从基础出发, 介绍 Oracle 的 回滚机制, 进而深入研究和探讨回滚机制的内部操作及 ORA-01555 错误等相 关知识 第八章 : 等待事件, 等待事件在数据库性能诊断中起着极为重要的作用, 在不同 版本中,Oracle 一直在不断加强等待事件的功能, 本章从等待事件入手, 进一 步讲解数据库性能诊断和优化知识 第九章 : 性能诊断与 SQL 优化, 这一章是实践的总结, 通过一些实践的案例, 介绍一种思路和方法给大家, 解决问题是学习的最终目的 关于本书 根据 Gartner 公司的最新统计数据, 在 2005 年,Oracle 数据库以 48.6% 的 市场占有率继续稳居关系数据库市场的首位, 在过去这一年中, 国内的 Oracle ITPUB 第 5 页科技技术新动力

从业市场和学习环境都有了很大的发展和进步, 市场进一步规范和成熟, 从事数 据库管理工作的朋友们也越来越多 为了让更多进入 Oracle 领域的朋友能够快速了解和掌握 Oracle 技术, 让具备一定经验和积累的 Oracle 从业人员继续深入学习, 作者倾力撰写了本书 本书作者活跃于国内著名 Oracle 技术论坛 ITPUB( www.itpub.net), 并全力打造国内最具影响力的个人 Oracle 技术站点 Eygle.com ( www.eygle.com), 本书从基础出发, 逐层深入, 并结合实际工作中的诊断案例进行全面讲解, 力图从点到面, 让读者对每个主题都有深入的了解和认识 本书是 ITPUB 技术丛书的第三本, 在 Oracle 数据库 DBA 专题技术精粹 和 Oracle 数据库性能优化 二书出版的 2 年多以来,ITPUB 和 Oracle 市场都有了长足的发展, 希望本书的出现能为读者带来更深入的技术知识和更多的实践经验 本书特点 目前市场上的 Oracle 书籍普遍存在的问题是模式单一, 要么只讲基础知识, 要么侧重代码编程实例, 要么针对具体的版本特性 ( Oracle8i/Oracle9i/Oracle10g 等 ), 要么缺少实践应用检验, 很少能对 Oracle 相关知识进行全面深入讲解 本书针对这些问题, 从基础知识入手, 再进行深入研究, 结合性能调整 诊断案例实践, 将 Oracle 知识全面 系统 深入的展现给读者 ; 本书内容更涉及 Oracle8i/Oracle9i/Oracle10g, 将 Oracle 的版本变化, 功能改进, 一以贯之的展现出来, 让大家看到这些变革的真正原因以及 Oracle 的不断技术创新, 通过真实案例的学习, 更可以加深大家对现实环境的了解, 从而提高实践能力 本书是作者多年实践工作的积累和总结, 各篇章更从 DBA 的成长历程入手, 引 导大家快速进入并深入 Oracle 知识的国度 本书继续贯彻了作者 由点到线再及面 的学习方法, 既可以让初学者参考学习, 又可以帮助具备一定基础的中级 DBA 进行进阶学习, 不同层次的学习者都能从 本书的不同内容中受益 ITPUB 第 6 页科技技术新动力

本书的读者对象 本书适用于打算进入 Oracle 领域的初学者, 也适用于具备一定数据库基础 打 算深入学习 Oracle 技术的数据库从业人员, 尤其适用于入门 进阶以及希望深 入研究 Oracle 技术的数据库管理人员 ; 本书也可以作为各大中专院校相关专业的教学辅导和参考用书, 或作为相关培训 机构的培训教材 ITPUB 第 7 页科技技术新动力

本期目录 Oracle 诊断案例 -Spfile 案例一则... 9 Oracle 诊断案例 -SGA 与 Swap 之一... 23 Oracle 诊断案例 -SGA 与 Swap 之二... 44 ORACLE 诊断事件及深入解析 10053 事件... 50 用 events 跟踪解决不能创建物化视图一例... 67 ORA-1157 错误解决手册... 73 一条 sql 导致数据库整体性能下降的诊断和解决全过程... 89 修改 fet$ 基表, 结合碎片案例实战... 97 ITPUB 第 8 页科技技术新动力

Oracle 诊断案例 -Spfile 案例一则 作者 :Eygle 情况说明 : 系统 :SUN Solaris8 数据库版本 :9203 问题描述 : 工程人员报告, 数据库在重新启动时无法正常启动. 检查发现 UNDO 表空间丢失. 问题诊断及解决过程如下 : 1. 登陆系统检查 alert.log 文件 检查 alert.log 文件是通常是我们诊断数据库问题的第一步 SunOS 5.8 login: root Password: Last login: Thu Apr 1 11:39:16 from 10.123.7.162 Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. # su - oracle bash-2.03$ cd $ORACLE_BASE/admin/*/bdump bash-2.03$ vi *.log "alert_gzhs.log" 7438 lines, 283262 characters Sat Feb 7 20:30:06 2004 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. System parameters with non-default values: processes = 150 ITPUB 第 9 页科技技术新动力

timed_statistics = TRUE shared_pool_size = 1157627904 large_pool_size = 16777216 java_pool_size = 637534208 control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl db_block_size = 8192 db_cache_size = 2516582400 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u06/oradata/gzhs/arch log_archive_format = %t_%s.dbf 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 = gzhs dispatchers = (PROTOCOL=TCP) (SERVICE=gzhsXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /oracle/admin/gzhs/bdump user_dump_dest = /oracle/admin/gzhs/udump core_dump_dest = /oracle/admin/gzhs/cdump sort_area_size = 524288 db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 838860800 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 ITPUB 第 10 页科技技术新动力

"alert_gzhs.log" 7438 lines, 283262 characters USER: terminating instance due to error 30012 Instance terminated by USER, pid = 26433 ORA-1092 signalled during: ALTER DATABASE OPEN... Thu Apr 1 11:11:08 2004 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 1157627904 large_pool_size = 16777216 java_pool_size = 637534208 control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl db_block_size = 8192 db_cache_size = 2516582400 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u06/oradata/gzhs/arch log_archive_format = %t_%s.dbf 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 = gzhs dispatchers = (PROTOCOL=TCP) (SERVICE=gzhsXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /oracle/admin/gzhs/bdump ITPUB 第 11 页科技技术新动力

user_dump_dest = /oracle/admin/gzhs/udump core_dump_dest = /oracle/admin/gzhs/cdump sort_area_size = 524288 db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 838860800 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 Thu Apr 1 11:11:13 2004 starting up 1 shared server(s)... QMN0 started with pid=9 Thu Apr 1 11:11:13 2004 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... ARCH: STARTING ARCH PROCESSES ARC0 started with pid=12 ARC0: Archival started ARC1 started with pid=13 Thu Apr 1 11:11:13 2004 ARCH: STARTING ARCH PROCESSES COMPLETE Thu Apr 1 11:11:13 2004 ARC0: Thread not mounted Thu Apr 1 11:11:13 2004 ARC1: Archival started ARC1: Thread not mounted Thu Apr 1 11:11:14 2004 ALTER DATABASE MOUNT Thu Apr 1 11:11:18 2004 Successful mount of redo thread 1, with mount id 1088380178. Thu Apr 1 11:11:18 2004 Database mounted in Exclusive Mode. ITPUB 第 12 页科技技术新动力

Completed: ALTER DATABASE MOUNT Thu Apr 1 11:11:27 2004 alter database open Thu Apr 1 11:11:27 2004 Beginning crash recovery of 1 threads Thu Apr 1 11:11:27 2004 Started first pass scan Thu Apr 1 11:11:28 2004 Completed first pass scan 1 redo blocks read, 0 data blocks need recovery Thu Apr 1 11:11:28 2004 Started recovery at Thread 1: logseq 177, block 2, scn 0.33104793 Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0 Mem# 0 errs 0: /u01/oradata/gzhs/redo03.log Thu Apr 1 11:11:28 2004 Completed redo application Thu Apr 1 11:11:28 2004 Ended recovery at Thread 1: logseq 177, block 3, scn 0.33124794 0 data blocks read, 0 data blocks written, 1 redo blocks read Crash recovery completed successfully Thu Apr 1 11:11:28 2004 LGWR: Primary database is in CLUSTER CONSISTENT mode Thread 1 advanced to log sequence 178 Thread 1 opened at log sequence 178 Current log# 1 seq# 178 mem# 0: /u01/oradata/gzhs/redo01.log Successful open of redo thread 1. Thu Apr 1 11:11:28 2004 ARC0: Evaluating archive log 3 thread 1 sequence 177 Thu Apr 1 11:11:28 2004 ARC0: Beginning to archive log 3 thread 1 sequence 177 Creating archive destination LOG_ARCHIVE_DEST_1: '/u06/oradata/gzhs/arch/1_177.dbf' Thu Apr 1 11:11:28 2004 SMON: enabling cache recovery ARC0: Completed archiving log 3 thread 1 sequence 177 Thu Apr 1 11:11:28 2004 Errors in file /oracle/admin/gzhs/udump/gzhs_ora_27781.trc: ORA-30012: \263\267\317\373\261\355\277\325\274\344 'UNDOTBS1' ITPUB 第 13 页科技技术新动力

\262\273\264\346\324\332\273\362\300\340\320\315\262\273\325\375\310\ 267 Thu Apr 1 11:11:28 2004 Error 30012 happened during db open, shutting down database USER: terminating instance due to error 30012 Instance terminated by USER, pid = 27781 ORA-1092 signalled during: alter database open... :q... 在警报日志末尾显示了数据库在 Open 状态因为错误而异常终止. 2. 尝试重新启动数据库 bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4 月 1 11:43:52 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 已连接到空闲例程 SQL> startup ORACLE 例程已经启动 Total System Global Area 4364148184 bytes Fixed Size 736728 bytes Variable Size 1845493760 bytes Database Buffers 2516582400 bytes Redo Buffers 1335296 bytes 数据库装载完毕 ORA-01092: ORACLE 例程终止 强行断开连接... 工程人员报告的问题重现. 3. 检查数据文件 ITPUB 第 14 页科技技术新动力

bash-2.03$ cd /u01/ oradata/gzhs bash-2.03$ ls -l total 55702458 -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 UNDOTBS2.dbf -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_BILLINGDETAIL.dbf -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_MAIN.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN10.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN11.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN2.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN3.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN4.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN5.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN6.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN7.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN8.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN9.dbf -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_MVIEW.dbf -rw-r----- 1 oracle dba 1073750016 Mar 24 17:15 WAP12_TEMP1.dbf... 发现存在文件 UNDOTBS2.dbf 4. mount 数据库, 检查系统参数 bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4 月 1 11:46:20 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 已连接到空闲例程 SQL> SQL> SQL> startup mount; ORACLE 例程已经启动 ITPUB 第 15 页科技技术新动力

Total System Global Area 4364148184 bytes Fixed Size 736728 bytes Variable Size 1845493760 bytes Database Buffers 2516582400 bytes Redo Buffers 1335296 bytes 数据库装载完毕 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/oradata/gzhs/system01.dbf /u01/oradata/gzhs/cwmlite01.dbf /u01/oradata/gzhs/drsys01.dbf /u01/oradata/gzhs/example01.dbf /u01/oradata/gzhs/indx01.dbf /u01/oradata/gzhs/odm01.dbf /u01/oradata/gzhs/tools01.dbf /u01/oradata/gzhs/users01.dbf /u01/oradata/gzhs/xdb01.dbf... /u01/oradata/gzhs/undotbs2.dbf 已选择 23 行 SQL> SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1 SQL> show parameter spfile NAME TYPE VALUE ITPUB 第 16 页科技技术新动力

------------------------------------ ----------- ------------------------------ spfile string...... 发现系统没有使用 spfile, 而初始化参数设置的 undo 表空间为 UNDOTBS1 5. 检查参数文件 bash-2.03$ cd $ORACLE_HOME/dbs bash-2.03$ ls init.ora initgzhs.ora initgzhs.ora.old orapwgzhs initdw.ora initgzhs.ora.hurray lkgzhs snapcf_gzhs.f bash-2.03$ vi initgzhs.ora "initgzhs.ora" [Incomplete last line] 105 lines, 3087 characters #################################################### # Copyright (c) 1991, 2001, 2002 by Oracle Corporation #################################################### ########################################### # Archive ########################################### log_archive_dest_1='location=/u06/oradata/gzhs/arch' log_archive_format=%t_%s.dbf log_archive_start=true ########################################### # Cache and I/O ########################################### db_block_size=8192 db_cache_size=2516582400 ITPUB 第 17 页科技技术新动力

db_file_multiblock_read_count=16 ########################################### # Cursors and Library Cache ########################################### open_cursors=300... ########################################### # System Managed Undo and Rollback Segments ########################################### undo_management=auto undo_retention=10800 undo_tablespace=undotbs1 :q!... 这个设置是极其可疑的. 怀疑参数文件和实际数据库设置不符. 6. 再次检查 alert 文件 查找对于 UNDO 表空间的操作 第一部分, 创建数据库时的信息 : Sat Feb 7 20:30:12 2004 CREATE DATABASE gzhs MAXINSTANCES 1 MAXLOGHISTORY 1 MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE '/u01/oradata/gzhs/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT ITPUB 第 18 页科技技术新动力

10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/gzhs/temp01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/oradata/gzhs/undotbs01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED CHARACTER SET ZHS16GBK NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 ('/u01/oradata/gzhs/redo01.log') SIZE 256M, GROUP 2 ('/u01/oradata/gzhs/redo02.log') SIZE 256M, GROUP 3 ('/u01/oradata/gzhs/redo03.log') SIZE 256M... 注意, 这也是 OCP 教材上提到的两种创建 UNDO 表空间的方式之一 第二部分, 发现创建 UNDOTBS2 的记录信息 : Wed Mar 24 20:20:58 2004 /* OracleOEM */ CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/oradata/gzhs/undotbs2.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED Wed Mar 24 20:22:37 2004 Created Undo Segment _SYSSMU11$ Created Undo Segment _SYSSMU12$ Created Undo Segment _SYSSMU13$ Created Undo Segment _SYSSMU14$ Created Undo Segment _SYSSMU15$ Created Undo Segment _SYSSMU16$ Created Undo Segment _SYSSMU17$ Created Undo Segment _SYSSMU18$ Created Undo Segment _SYSSMU19$ Created Undo Segment _SYSSMU20$ Completed: /* OracleOEM */ CREATE UNDO TABLESPACE "UNDOTBS2" Wed Mar 24 20:24:25 2004 Undo Segment 11 Onlined ITPUB 第 19 页科技技术新动力

Undo Segment 12 Onlined Undo Segment 13 Onlined Undo Segment 14 Onlined Undo Segment 15 Onlined Undo Segment 16 Onlined Undo Segment 17 Onlined Undo Segment 18 Onlined Undo Segment 19 Onlined Undo Segment 20 Onlined Successfully onlined Undo Tablespace 15. Undo Segment 1 Offlined Undo Segment 2 Offlined Undo Segment 3 Offlined Undo Segment 4 Offlined Undo Segment 5 Offlined Undo Segment 6 Offlined Undo Segment 7 Offlined Undo Segment 8 Offlined Undo Segment 9 Offlined Undo Segment 10 Offlined Undo Tablespace 1 successfully switched out.... 第三部分, 新的 UNDO 表空间被应用 Wed Mar 24 20:24:25 2004 ALTER SYSTEM SET undo_tablespace='undotbs2' SCOPE=MEMORY; 我们发现问题就在这里, 创建了新的 UNDO 表空间以后, 因为使用的是 pfile 文件, 修改的只对当前 实例生效, 操作人员忘记了修改 pfile 文件. 如果使用 spfile, 缺省的修改范围是 both, 会同时修改 spfile 文件, 就可以避免以上问题的出现. 第四部分, 删除了 UNDOTBS1 的信息 ITPUB 第 20 页科技技术新动力

Wed Mar 24 20:25:01 2004 /* OracleOEM */ DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS Wed Mar 24 20:25:03 2004 Deleted file /u01/oradata/gzhs/undotbs01.dbf Completed: /* OracleOEM */ DROP TABLESPACE "UNDOTBS1" INCLUDI... 这样再次重新启动数据库的时候, 问题出现了,pfile 中定义的 UNDOTBS1 找不到了, 而且操作实 在很久以前, 没人能回忆起来, 甚至无法得知是什么人的操作 7. 更改 pfile, 启动数据库 修改 undo 表空间 ########################################### # System Managed Undo and Rollback Segments ########################################### undo_management=auto undo_retention=10800 undo_tablespace=undotbs2... bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4 月 1 11:55:11 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 连接到 : Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production With the Partitioning, OLAP and Oracle Data Mining options ITPUB 第 21 页科技技术新动力

JServer Release 9.2.0.3.0 - Production SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production PL/SQL Release 9.2.0.3.0 - Production CORE 9.2.0.3.0 Production TNS for Solaris: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production SQL> exit 从 Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production 中断开 bash-2.03$ 在这里我们可以看到, 使用 spfile 可以免去手工修改 pfile 文件的麻烦, 减少了犯错的可能 既然 Oracle9i 给我们提供了这个新特性, 就值得我们学习使用它. 编者注 : 您可以通过以下连接参与关于本文的讨论, 直接和作者对话 : http://www.itpub.net/showthread.php?s=&threadid=211984 或 http://www.eygle.com/case/spfile.htm ITPUB 第 22 页科技技术新动力

Oracle 诊断案例 -SGA 与 Swap 之一 作者 :Eygle 案例描述 : 用户报告, 服务器启动一段时间以后, 无法建立数据库连接 重新启动几分钟以后, 再次无法连接 系统无法正常使用. 1. 登陆系统 SunOS 5.8 login: root Password: Last login: Tue Mar 23 13:56:59 from 172.16.31.41 Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. 2.su 为 Oracle 用户 检查启动的 Oracle 进程 发现后台进程正常, 有一定量的用户连接 wapplatform:/>su - oracle Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. /export/home1/oracle>ls admin codesyndealt31 exp.sh local.cshrc local.profile oraclebak oui v6_database app exp.log jre local.login nsmail oradata swan export/home1/oracle>cd admin /export/home1/oracle/admin>ps -ef grep ora oracle 25269 25258 0 13:58:36 pts/3 0:00 grep ora oracle 25257 24906 0 13:58:31 pts/4 0:00 vi alert_hswapdb.log oracle 25267 1 1 13:58:34? 0:00 oraclehswapdb (LOCAL=NO) oracle 25184 1 0 13:56:57? 0:00 ora_p007_hswapdb ITPUB 第 23 页科技技术新动力

oracle 25182 1 0 13:56:57? 0:00 ora_p006_hswapdb oracle 25193 1 0 13:57:03? 0:01 oraclehswapdb (LOCAL=NO) oracle 25209 1 0 13:57:09? 0:00 oraclehswapdb (LOCAL=NO) oracle 25176 1 0 13:56:57? 0:00 ora_p003_hswapdb oracle 25180 1 0 13:56:57? 0:00 ora_p005_hswapdb oracle 25172 1 0 13:56:56? 0:00 ora_p001_hswapdb oracle 25178 1 0 13:56:57? 0:00 ora_p004_hswapdb oracle 25170 1 0 13:56:56? 0:00 ora_p000_hswapdb oracle 24254 24240 0 12:08:25 pts/2 0:00 -ksh oracle 25174 1 0 13:56:56? 0:00 ora_p002_hswapdb oracle 25244 1 1 13:58:23? 0:00 oraclehswapdb (LOCAL=NO) oracle 25218 1 0 13:57:23? 0:00 oraclehswapdb (LOCAL=NO) oracle 25159 1 0 13:56:42? 0:02 ora_qmn0_hswapdb oracle 25230 1 0 13:57:40? 0:01 oraclehswapdb (LOCAL=NO) oracle 25161 1 0 13:56:42? 0:00 ora_s000_hswapdb oracle 25149 1 0 13:56:41? 0:01 ora_lgwr_hswapdb oracle 25157 1 0 13:56:42? 0:00 ora_cjq0_hswapdb oracle 24906 3698 0 13:47:47 pts/4 0:00 -ksh oracle 25153 1 0 13:56:42? 0:01 ora_smon_hswapdb oracle 25058 7464 0 13:55:14 pts/1 0:00 -ksh oracle 25163 1 0 13:56:42? 0:00 ora_d000_hswapdb oracle 25155 1 0 13:56:42? 0:00 ora_reco_hswapdb oracle 25151 1 0 13:56:41? 0:00 ora_ckpt_hswapdb oracle 25145 1 0 13:56:41? 0:00 ora_dbw0_hswapdb oracle 25199 1 15 13:57:04? 0:49 ora_j000_hswapdb oracle 4149 4146 0 12:05:11 pts/5 0:00 -ksh oracle 25232 1 0 13:57:41? 0:00 oraclehswapdb (LOCAL=NO) oracle 25119 1 0 13:56:29? 0:00 oraclehswapdb (LOCAL=NO) oracle 25075 1 0 13:55:34? 0:00 /export/home1/oracle/app/bin/tnslsnr LISTENER -inherit oracle 24374 4149 0 12:21:56 pts/5 0:00 sqlplus /nolog oracle 25143 1 0 13:56:41? 0:00 ora_pmon_hswapdb oracle 25258 25242 0 13:58:31 pts/3 0:00 -ksh /export/home1/oracle/admin>ps -ef grep ora_ oracle 25275 25258 0 13:58:42 pts/3 0:00 grep ora_ oracle 25184 1 0 13:56:57? 0:00 ora_p007_hswapdb oracle 25182 1 0 13:56:57? 0:00 ora_p006_hswapdb oracle 25176 1 0 13:56:57? 0:00 ora_p003_hswapdb oracle 25180 1 0 13:56:57? 0:00 ora_p005_hswapdb oracle 25172 1 0 13:56:56? 0:00 ora_p001_hswapdb ITPUB 第 24 页科技技术新动力

oracle 25178 1 0 13:56:57? 0:00 ora_p004_hswapdb oracle 25170 1 0 13:56:56? 0:00 ora_p000_hswapdb oracle 25174 1 0 13:56:56? 0:00 ora_p002_hswapdb oracle 25159 1 0 13:56:42? 0:02 ora_qmn0_hswapdb oracle 25161 1 0 13:56:42? 0:00 ora_s000_hswapdb oracle 25149 1 0 13:56:41? 0:01 ora_lgwr_hswapdb oracle 25157 1 0 13:56:42? 0:00 ora_cjq0_hswapdb oracle 25153 1 0 13:56:42? 0:01 ora_smon_hswapdb oracle 25163 1 0 13:56:42? 0:00 ora_d000_hswapdb oracle 25155 1 0 13:56:42? 0:00 ora_reco_hswapdb oracle 25151 1 0 13:56:41? 0:00 ora_ckpt_hswapdb oracle 25145 1 0 13:56:41? 0:00 ora_dbw0_hswapdb oracle 25199 1 13 13:57:04? 0:51 ora_j000_hswapdb oracle 25143 1 0 13:56:41? 0:00 ora_pmon_hswapdb 3. 检查 Alert.log 警报日志文件 /export/home1/oracle/admin>ls hswapdb /export/home1/oracle/admin>cd * /export/home1/oracle/admin/hswapdb>ls bdump cdump create pfile udump /export/home1/oracle/admin/hswapdb>cd bdump /export/home1/oracle/admin/hswapdb/bdump> /export/home1/oracle/admin/hswapdb/bdump>ls -l *.log -rw-r--r-- 1 oracle dba 813396 Mar 23 13:57 alert_hswapdb.log /export/home1/oracle/admin/hswapdb/bdump>vi *.log "alert_hswapdb.log" 18888 lines, 813396 characters (115 null) Tue Jun 24 21:17:14 2003 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. ITPUB 第 25 页科技技术新动力

System parameters with non-default values: processes = 400 timed_statistics = TRUE shared_pool_size = 117440512 large_pool_size = 83886080 java_pool_size = 33554432 control_files = /export/home1/oracle/oradata/hswapdb/control01.ctl, /export/home1/oracle/oradata/hswapdb/control02.ctl, /export/home1/oracle/oradata/hswapdb/control03.ctl db_block_size = 8192 db_cache_size = 352321536 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 = eygle.com instance_name = hswapdb dispatchers = (PROTOCOL=TCP) (SERVICE=hswapdbXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /export/home1/oracle/admin/hswapdb/bdump user_dump_dest = /export/home1/oracle/admin/hswapdb/udump core_dump_dest = /export/home1/oracle/admin/hswapdb/cdump sort_area_size = 524288 db_name = hswapdb open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 154140672 aq_tm_processes = 1... 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 ITPUB 第 26 页科技技术新动力

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 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 Tue Mar 23 13:55:38 2004 Starting ORACLE instance (normal) Shutting down instance: further logons disabled Tue Mar 23 13:56:20 2004 Shutting down instance (abort) License high water mark = 26 Instance terminated by USER, pid = 25112 Tue Mar 23 13:56:37 2004 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. System parameters with non-default values: processes = 400 timed_statistics = TRUE shared_pool_size = 117440512 large_pool_size = 83886080 java_pool_size = 33554432 control_files = /export/home1/oracle/oradata/hswapdb/control01.ctl, /export/home1/oracle/oradata/hswapdb/control02.ctl, /export/home1/oracle/oradata/hswapdb/control03.ctl db_block_size = 8192 db_cache_size = 352321536 compatible = 9.2.0.0.0 db_file_multiblock_read_count= 16 ITPUB 第 27 页科技技术新动力

fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = eygle.com instance_name = hswapdb dispatchers = (PROTOCOL=TCP) (SERVICE=hswapdbXDB) remote_dependencies_mode = SIGNATURE job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /export/home1/oracle/admin/hswapdb/bdump user_dump_dest = /export/home1/oracle/admin/hswapdb/udump core_dump_dest = /export/home1/oracle/admin/hswapdb/cdump sort_area_size = 524288 db_name = hswapdb open_cursors = 300 star_transformation_enabled= FALSE parallel_automatic_tuning= TRUE query_rewrite_enabled = FALSE pga_aggregate_target = 154140672 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 Tue Mar 23 13:56:42 2004 starting up 1 shared server(s)... Tue Mar 23 13:56:42 2004 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Tue Mar 23 13:56:43 2004 ALTER DATABASE MOUNT Tue Mar 23 13:56:47 2004 Successful mount of redo thread 1, with mount id 3253076635. ITPUB 第 28 页科技技术新动力

Tue Mar 23 13:56:47 2004 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Tue Mar 23 13:56:47 2004 Current log# 2 seq# 2136 mem# 0: /export/home1/oracle/oradata/hswapdb/redo02.log Successful open of redo thread 1. Tue Mar 23 12:24:54 2004 SMON: enabling cache recovery Tue Mar 23 12:24:56 2004 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. 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 ITPUB 第 29 页科技技术新动力

/* 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. 尝试连接数据库 收到错误信息, 无法连接数据库 ITPUB 第 30 页科技技术新动力

$ 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 个处理程序... 处理程序 : ITPUB 第 31 页科技技术新动力

"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\3 03\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\3 03\265\304\267\376\316\361\306\36 ITPUB 第 32 页科技技术新动力

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 ITPUB 第 33 页科技技术新动力

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... ITPUB 第 34 页科技技术新动力

Mar 23 10:18:15 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full Mar 23 10:20:41 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg /export/home1: file system full Mar 23 10:20:47 wapplatform last message repeated 1 time Mar 23 10:23:15 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full Mar 23 10:24:38 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg /export/home1: file system full Mar 23 10:24:43 wapplatform last message repeated 1 time Mar 23 10:24:55 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg /export/home1: file system full Mar 23 10:25:06 wapplatform last message repeated 2 times Mar 23 11:09:31 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3118 (su) Mar 23 11:09:39 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3121 (su) Mar 23 11:10:48 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3137 (su) Mar 23 11:18:02 wapplatform sshd[3620]: [ID 800047 auth.error] error: grantpt: Not enough space Mar 23 11:18:02 wapplatform sshd[3620]: [ID 800047 auth.error] error: session_pty_req: session 0 alloc failed Mar 23 11:18:43 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3636 (su) Mar 23 11:19:47 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3672 (su) Mar 23 11:20:20 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3694 (su) Mar 23 11:22:23 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3736 (sshd) Mar 23 11:23:17 wapplatform tmpfs: [ID 518458 kern.warning] WARNING: /tmp: File system full, swap space limit exceeded Mar 23 11:23:40 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3804 (su) Mar 23 11:23:40 wapplatform last message repeated 8 times Mar 23 11:23:56 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3806 (ps) Mar 23 11:23:56 wapplatform last message repeated 12 times ITPUB 第 35 页科技技术新动力

Mar 23 11:24:01 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3808 (w) Mar 23 11:24:01 wapplatform last message repeated 8 times Mar 23 13:40:56 wapplatform su: [ID 810491 auth.crit] 'su root' failed for root on /dev/pts/2 Mar 23 13:46:26 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 24888 (sqlplus) Mar 23 13:49:18 wapplatform su: [ID 810491 auth.crit] 'su oracle' failed for root on /dev/pts/6 Mar 23 13:54:03 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 25035 (su) Mar 23 13:54:08 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 25036 (su) 现在基本可以判断是交换区的问题, 当然和 Oracle SGA 设置有关. 7. 检查系统内存及交换区使用 /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 ITPUB 第 36 页科技技术新动力

^?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: alloc: /export/home1: file system full Mar 23 10:20:41 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg /export/home1: file system full Mar 23 10:20:47 wapplatform last message repeated 1 time ITPUB 第 37 页科技技术新动力

Mar 23 10:23:15 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: /export/home1: file system full Mar 23 10:24:38 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg /export/home1: file system full Mar 23 10:24:43 wapplatform last message repeated 1 time Mar 23 10:24:55 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg /export/home1: file system full Mar 23 10:25:06 wapplatform last message repeated 2 times Mar 23 11:09:31 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3118 (su) Mar 23 11:09:39 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3121 (su) Mar 23 11:10:48 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3137 (su) Mar 23 11:18:02 wapplatform sshd[3620]: [ID 800047 auth.error] error: grantpt: Not enough space Mar 23 11:18:02 wapplatform sshd[3620]: [ID 800047 auth.error] error: session_pty_req: session 0 alloc failed Mar 23 11:18:43 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3636 (su) Mar 23 11:19:47 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3672 (su) Mar 23 11:20:20 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3694 (su) Mar 23 11:22:23 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3736 (sshd) Mar 23 11:23:17 wapplatform tmpfs: [ID 518458 kern.warning] WARNING: /tmp: File system full, swap space limit exceeded Mar 23 11:23:40 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3804 (su) Mar 23 11:23:40 wapplatform last message repeated 8 times Mar 23 11:23:56 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3806 (ps) Mar 23 11:23:56 wapplatform last message repeated 12 times Mar 23 11:24:01 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3808 (w) Mar 23 11:24:01 wapplatform last message repeated 8 times Mar 23 13:40:56 wapplatform su: [ID 810491 auth.crit] 'su root' failed for root on /dev/pts/2 Mar 23 13:46:26 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap ITPUB 第 38 页科技技术新动力

space to grow stack for pid 24888 (sqlplus) Mar 23 13:49:18 wapplatform su: [ID 810491 auth.crit] 'su oracle' failed for root on /dev/pts/6 Mar 23 13:54:03 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 25035 (su) Mar 23 13:54:08 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 25036 (su) 现在基本可以判断是交换区的问题, 当然和 Oracle SGA 设置有关. 7. 检查系统内存及交换区使用 $ top last pid: 25456; load averages: 0.67, 0.70, 0.69 14:10:03 93 processes: 91 sleeping, 2 on cpu CPU states: 72.7% idle, 14.9% user, 2.7% kernel, 9.7% iowait, 0.0% swap Memory: 1024M real, 34M free, 752M swap in use, 10M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND 25199 oracle 1 40 0 674M 631M cpu/2 8:03 16.32% oracle 25209 oracle 1 30 0 675M 630M sleep 0:03 0.13% oracle 25159 oracle 1 48 0 674M 628M sleep 0:03 0.06% oracle 25384 oracle 1 58 0 2632K 1736K cpu/0 0:01 0.05% top 25145 oracle 143 58 0 682M 630M sleep 0:01 0.03% oracle 25446 oracle 1 58 0 674M 628M sleep 0:00 0.03% oracle 25149 oracle 15 58 0 682M 626M sleep 0:00 0.02% oracle 25075 oracle 1 48 0 17M 7208K sleep 0:00 0.01% tnslsnr 25151 oracle 11 58 0 676M 624M sleep 0:00 0.01% oracle 25366 oracle 1 10 0 674M 628M sleep 0:00 0.00% oracle 25356 oracle 1 18 0 674M 628M sleep 0:00 0.00% oracle 25360 oracle 1 20 0 674M 628M sleep 0:00 0.00% oracle 25364 oracle 1 20 0 674M 628M sleep 0:00 0.00% oracle 25362 oracle 1 20 0 674M 628M sleep 0:00 0.00% oracle 25330 oracle 1 28 0 674M 628M sleep 0:00 0.00% oracle ITPUB 第 39 页科技技术新动力

发现物理内存仅为 1G,free 部分为 34M, 交换区使用了 752M, 仅 10M free 系统内存严重不足,Swap 区不足 8. 检查数据库的 SGA 设置 发现 SGA 设置为 : 622299344 bytes 接近 600M wapplatform:/>su - oracle Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. /export/home1/oracle>sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 星期二 3 月 23 14:02:30 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 连接到 : Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> show sga Total System Global Area 622299344 bytes Fixed Size 731344 bytes Variable Size 268435456 bytes Database Buffers 352321536 bytes Redo Buffers 811008 bytes SQL> 对于 RAM 小于 1G 的系统,Dedicated 模式下,Oracle 的 SGA 一般不应超过 1/2 物理内存. 9. 第一步调整 减小 SGA, 为系统保留足够的内存. 10. 增加 swap 区 wapplatform:/>df -k 文件系统千字节用了可用容量挂接在 /dev/dsk/c0t1d0s0 3099093 105421 2931691 4% / /dev/dsk/c0t2d0s0 10325760 8359637 1862866 82% /usr ITPUB 第 40 页科技技术新动力

/proc 0 0 0 0% /proc fd 0 0 0 0% /dev/fd mnttab 0 0 0 0% /etc/mnttab /dev/dsk/c0t1d0s3 1018382 285914 671366 30% /var swap 3904 24 3880 1% /var/run swap 3936 56 3880 2% /tmp /dev/dsk/c0t1d0s5 1671823 459202 1162467 29% /opt /dev/dsk/c0t2d0s7 7087473 6068462 948137 87% /export/home /dev/dsk/c2t1d0s7 17413250 15900222 1338896 93% /export/home2 /dev/dsk/c0t3d0s7 17413250 13749782 3489336 80% /export/home1 /dev/dsk/c0t1d0s1 771110 382410 334723 54% /usr/openwin /export/home/wapgw/luke 7087473 6068462 948137 87% /home/wap wapplatform:/var/swap>cd /export/home1 wapplatform:/export/home1>ls TT_DB lost+found oracle oracli9 wapplatform:/export/home1>mkdir swap wapplatform:/export/home1>cd swap wapplatform:/export/home1/swap>mkfile -v 1g swapfile1 swapfile1 1073741824 bytes wapplatform:/export/home1/swap>id uid=0(root) gid=1(other) wapplatform:/export/home1/swap>swap -a /export/home1/swap/swapfile1 wapplatform:/export/home1/swap>swap -s 总数 : 分配了 623160k 字节 + 保留 162704k = 已使用 785864k,1010936k 可用 11. 连接测试 系统恢复正常, 问题解决 wapplatform:/export/home1/swap>su - oracle Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. /export/home1/oracle>sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 3 月 25 11:56:28 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 连接到 : ITPUB 第 41 页科技技术新动力

Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> exit 从 Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production 中断开 /export/home1/oracle>top last pid: 5372; load averages: 0.25, 0.22, 0.29 11:57:58 148 processes: 137 sleeping, 9 zombie, 2 on cpu CPU states: 98.8% idle, 0.2% user, 0.7% kernel, 0.2% iowait, 0.0% swap Memory: 1024M real, 17M free, 824M swap in use, 934M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND 5363 root 1 58 0 2680K 1736K sleep 0:00 0.24% top 5370 oracle 1 58 0 514M 469M sleep 0:00 0.18% oracle 5366 oracle 1 28 0 514M 469M sleep 0:00 0.11% oracle 5341 oracle 1 58 0 2680K 1736K cpu/2 0:00 0.10% top 5372 oracle 1 48 0 61M 3288K cpu/3 0:00 0.06% oracle 1288 oracle 1 48 0 514M 468M sleep 5:33 0.05% oracle 607 root 12 48 0 2768K 2312K sleep 1:48 0.03% mibiisa 25075 oracle 1 48 0 17M 7208K sleep 0:16 0.02% tnslsnr 1278 oracle 15 58 0 522M 466M sleep 0:49 0.02% oracle 374 root 11 53 0 3504K 2888K sleep 0:16 0.01% nscd 1280 oracle 19 58 0 518M 466M sleep 0:28 0.00% oracle 5361 root 1 46 0 1024K 680K sleep 0:00 0.00% sleep 5362 root 1 46 0 1024K 680K sleep 0:00 0.00% sleep 5469 root 1 36 0 1952K 1176K sleep 30:09 0.00% monithttp 4167 oracle 1 40 0 515M 471M sleep 29:38 0.00% oracle 问题总结 : Oracle 数据库问题的解决从来就离不开操作系统 很多时候我们必须通过操作系统一级的手段来诊断并解决问题. 关于操作系统 ITPUB 第 42 页科技技术新动力

一般 Swap 区的推荐值为 2XRAM 如果 Ram 很大, 不一定非要把 Swap 设置为 2xSwap 但是通常至少设置 Swap = Ram 如果 Swap 区过小, 在系统繁忙期间 产生大量交换无法换到磁盘, 就会出现问题. 如本案例就是这样 另外, 如果系统 Ram 较小 通常设置 SGA < 1/2 Ram 要为 Server process 及 OS 保留足够的内存空间. 编者注 : 您可以通过以下连接参与关于本文的讨论, 直接和作者对话 : http://www.itpub.net/showthread.php?s=&threadid=213582 或 http://www.eygle.com/case/sga1.htm ITPUB 第 43 页科技技术新动力

Oracle 诊断案例 -SGA 与 Swap 之二 作者 :Eygle 案例描述 : 这是一个大型生产系统问题出现时系统累计大量用户进程用户请求得不到及时响应, 新的进程不断尝试建立连接连接数很快被用完 数据库版本 :9.2.0.3 操作系统 :Solaris8 1. 检查 alert 文件 日志中记录如下错误信息, 说明磁盘异步 IO 出现问题 : WARNING: aiowait timed out 2 times Tue Aug 26 15:33:32 2003 WARNING: aiowait timed out 2 times Tue Aug 26 15:33:34 2003 WARNING: aiowait timed out 2 times Tue Aug 26 15:33:36 2003 WARNING: aiowait timed out 2 times Tue Aug 26 15:33:38 2003 WARNING: aiowait timed out 2 times Tue Aug 26 15:33:43 2003 WARNING: aiowait timed out 1 times Tue Aug 26 15:33:46 2003 ITPUB 第 44 页科技技术新动力

WARNING: aiowait timed out 1 times Tue Aug 26 15:33:49 2003 WARNING: aiowait timed out 1 times Tue Aug 26 15:33:51 2003 WARNING: aiowait timed out 1 times Tue Aug 26 15:33:52 2003 WARNING: aiowait timed out 1 times Tue Aug 26 15:33:53 2003 WARNING: aiowait timed out 1 times... 我们知道在 SUN 的某些版本上异步 IO 存在问题 而异步 IO 缺省是打开的 SQL> show parameter disk_a NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ disk_asynch_io boolean TRUE 针对此问题, 我们停用了数据库的异步 IO 写入 2. 共享内存问题 3. alert 文件中还记录了以下错误信息 : Tue Aug 26 21:37:40 2003 WARNING: EINVAL creating segment of size 0x0000000190400000 fix shm parameters in /etc/system or equivalent ITPUB 第 45 页科技技术新动力