Oracle診斷案例-Spfile案例一則link:http://www.eygle.com/case/spfile.htm情況說明:系統：SUN Solaris8數據庫版本:9203 問題描述：工程人員報告，數據庫在重新啟動時無法正常啟動.檢查發現UNDO表空間丟失.問題診斷及解決過程如下:1. 登陸系統檢查alert.log文件檢查alert.log文件是通常是我們診斷數據庫問題的第一步SunOS 5.8login: rootPassWord: Last login: Thu Apr 1 11:39:16 from 10.123.7.162Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001You have new mail.# su - oraclebash-2.03$ cd $ORACLE_BASE/admin/*/bdumpbash-2.03$ vi *.log'alert_gzhs.log' 7438 lines, 283262 characters Sat Feb 7 20:30:06 2004Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 3Using log_archive_dest parameter default valueLICENSE_MAX_USERS = 0SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.3.0.System parameters with non-default values:processes = 150timed_statistics = TRUEshared_pool_size = 1157627904large_pool_size = 16777216Java_pool_size = 637534208control_files = /u01/oradata/gzhs/control01.ctl,/u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctldb_block_size = 8192db_cache_size = 2516582400compatible = 9.2.0.0.0log_archive_start = TRUElog_archive_dest_1 = LOCATION=/u06/oradata/gzhs/archlog_archive_format = %t_%s.dbfdb_file_multiblock_read_count= 16fast_start_mttr_target = 300undo_management = AUTOundo_tablespace = UNDOTBS1undo_retention = 10800remote_login_passwordfile= EXCLUSIVEdb_domain =instance_name = gzhsdispatchers = (PROTOCOL=TCP) (SERVICE=gzhsXDB)job_queue_processes = 10hash_join_enabled = TRUEbackground_dump_dest = /oracle/admin/gzhs/bdumpuser_dump_dest = /oracle/admin/gzhs/udumpcore_dump_dest = /oracle/admin/gzhs/cdumpsort_area_size = 524288db_name = gzhsopen_cursors = 300star_transformation_enabled= FALSEquery_rewrite_enabled = FALSEpga_aggregate_target = 838860800aq_tm_processes = 1PMON started with pid=2DBW0 started with pid=3LGWR started with pid=4CKPT started with pid=5SMON started with pid=6'alert_gzhs.log' 7438 lines, 283262 charactersUSER: terminating instance due to error 30012Instance terminated by USER, pid = 26433ORA-1092 signalled during: ALTER DATABASE OPEN...Thu Apr 1 11:11:08 2004Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 3Using log_archive_dest parameter default valueLICENSE_MAX_USERS = 0SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.3.0.System parameters with non-default values:processes = 150timed_statistics = TRUEshared_pool_size = 1157627904large_pool_size = 16777216java_pool_size = 637534208control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctldb_block_size = 8192db_cache_size = 2516582400compatible = 9.2.0.0.0log_archive_start = TRUElog_archive_dest_1 = LOCATION=/u06/oradata/gzhs/archlog_archive_format = %t_%s.dbfdb_file_multiblock_read_count= 16fast_start_mttr_target = 300undo_management = AUTOundo_tablespace = UNDOTBS1undo_retention = 10800remote_login_passwordfile= EXCLUSIVEdb_domain =instance_name = gzhsdispatchers = (PROTOCOL=TCP) (SERVICE=gzhsXDB)job_queue_processes = 10hash_join_enabled = TRUEbackground_dump_dest = /oracle/admin/gzhs/bdumpuser_dump_dest = /oracle/admin/gzhs/udumpcore_dump_dest = /oracle/admin/gzhs/cdumpsort_area_size = 524288db_name = gzhsopen_cursors = 300star_transformation_enabled= FALSEquery_rewrite_enabled = FALSEpga_aggregate_target = 838860800aq_tm_processes = 1PMON started with pid=2DBW0 started with pid=3LGWR started with pid=4CKPT started with pid=5SMON started with pid=6RECO started with pid=7CJQ0 started with pid=8Thu Apr 1 11:11:13 2004starting up 1 shared server(s) ...QMN0 started with pid=9Thu Apr 1 11:11:13 2004starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...ARCH: STARTING ARCH PROCESSESARC0 started with pid=12ARC0: Archival startedARC1 started with pid=13Thu Apr 1 11:11:13 2004ARCH: STARTING ARCH PROCESSES COMPLETEThu Apr 1 11:11:13 2004ARC0: Thread not mountedThu Apr 1 11:11:13 2004ARC1: Archival startedARC1: Thread not mountedThu Apr 1 11:11:14 2004ALTER DATABASE MOUNTThu Apr 1 11:11:18 2004SUCcessful mount of redo thread 1, with mount id 1088380178.Thu Apr 1 11:11:18 2004Database mounted in Exclusive Mode.Completed: ALTER DATABASE MOUNTThu Apr 1 11:11:27 2004alter database openThu Apr 1 11:11:27 2004Beginning crash recovery of 1 threadsThu Apr 1 11:11:27 2004Started first pass scanThu Apr 1 11:11:28 2004Completed first pass scan1 redo blocks read, 0 data blocks need recoveryThu Apr 1 11:11:28 2004Started recovery atThread 1: logseq 177, block 2, scn 0.33104793Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0Mem# 0 errs 0: /u01/oradata/gzhs/redo03.logThu Apr 1 11:11:28 2004Completed redo applicationThu Apr 1 11:11:28 2004Ended recovery atThread 1: logseq 177, block 3, scn 0.331247940 data blocks read, 0 data blocks written, 1 redo blocks readCrash recovery completed successfullyThu Apr 1 11:11:28 2004LGWR: Primary database is in CLUSTER CONSISTENT modeThread 1 advanced to log sequence 178Thread 1 opened at log sequence 178Current log# 1 seq# 178 mem# 0: /u01/oradata/gzhs/redo01.logSuccessful open of redo thread 1.Thu Apr 1 11:11:28 2004ARC0: Evaluating archive log 3 thread 1 sequence 177Thu Apr 1 11:11:28 2004ARC0: Beginning to archive log 3 thread 1 sequence 177Creating archive destination LOG_ARCHIVE_DEST_1: '/u06/oradata/gzhs/arch/1_177.dbf'Thu Apr 1 11:11:28 2004SMON: enabling cache recoveryARC0: Completed archiving log 3 thread 1 sequence 177Thu Apr 1 11:11:28 2004Errors in file /oracle/admin/gzhs/udump/gzhs_ora_27781.trc:ORA-30012: 263267317373261355277325274344 'UNDOTBS1' 262273264346324332273362300340320315262273325375310267Thu Apr 1 11:11:28 2004Error 30012 happened during db open, shutting down databaseUSER: terminating instance due to error 30012Instance terminated by USER, pid = 27781ORA-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 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.已連接到空閑例程。 SQL> startupORACLE 例程已經啟動。Total System Global Area 4364148184 bytesFixed Size 736728 bytesVariable Size 1845493760 bytesDatabase Buffers 2516582400 bytesRedo 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.dbf4. 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表空間為UNDOTBS15. 檢查參數文件 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 2004ALTER 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=AUTOundo_retention=10800undo_tablespace=UNDOTBS2....bash-2.03$ sqlplus '/ as sysdba'SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:55:11 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.連接到: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0 - ProductionSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit ProductionPL/SQL Release 9.2.0.3.0 - ProductionCORE 9.2.0.3.0 ProductionTNS for Solaris: Version 9.2.0.3.0 - ProductionNLSRTL Version 9.2.0.3.0 - ProductionSQL> exit從Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0 - Production中斷開bash-2.03$在這里我們可以看到，使用spfile可以免去手工修改pfile文件的麻煩，減少了犯錯的可能。既然Oracle9i給我們提供了這個新特性，就值得我們學習使用它.