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

Oracle诊断案例-Spfile案例一则

作者：闵涛 文章来源：闵涛的学习笔记 点击数：1633 更新时间：2009/4/22 22:06:54

Oracle诊断案例-Spfile案例一则

 

link:

http://www.eygle.com/case/spfile.htm

情况说明:
系统：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
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
"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
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.
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'''' \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. 检查数据文件

 


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;

  ORACL
[1] [2]  下一页

[ORACLE]Oracle诊断案例-SGA与Swap之二  [ORACLE]Oracle诊断案例-SGA与Swap之一
[ORACLE]Oracle诊断案例-Sql_trace之二  [ORACLE]Oracle诊断案例-Sql_trace之一
[ORACLE]Oracle9i新特点-SPFILE的使用  [ORACLE]oracle9i和oracle10G里设置不使用spfile
[ORACLE]Oracle诊断案例-Job任务停止执行  
教程录入：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……
    		咸宁网络警察报警平台