| 導購 | 订阅 | 在线投稿
分享
 
 
 

Oracle診斷案例-Spfile案例一則

來源:互聯網網民  2004-11-29 22:18:41  評論

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\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/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 例程已經啓動。

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給我們提供了這個新特性,就值得我們學習使用它.

 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
Oracle診斷案例-Spfile案例一則 link: [url=http://www.eygle.com/case/spfile.htm]http://www.eygle.com/case/spfile.htm[/url] 情況說明: 系統: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/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 例程已經啓動。 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給我們提供了這個新特性,就值得我們學習使用它.
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 
 熱帖排行
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有