分享
 
 
 

Oracle诊断案例-Spfile案例一则

王朝oracle·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

情况说明:

系统: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\310267

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/gzhsbash-2.03$ ls -ltotal 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 例程已经启动。

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

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

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

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 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

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的信息

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

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给我们提供了这个新特性,就值得我们学习使用它.

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有