Oracle Diagnostic Case-SpFile Case
LINK:
http://www.eygle.com/case/spfile.htm
Situation Description: System: Sun Solaris8 Database Version: 9203 Problem Description: Engineering reports, databases cannot be started normally when restarting. Check discovers that UNDO tablespace is lost. The problem diagnosis and resolution process are as follows:
1. Login system Checking alert.log file Checking alert.log file is usually the first step in our diagnostic database problem
Sunos 5.8
login: 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_QUEU e_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 = 10800Remo te_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 '(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 2004 Completed Redo Applicationthu Apr 1 11:11:28 2004nded Recovery Atthread 1: LogSeq 177, Block 3, SCN 0.331247940 Data Blocks Read, 0 Data Block s 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/rch/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: / 263/267/317/373/261/355/277/325/274/344 'undotbs1' / 262/273/264 / 346/344/332/273/362/300/340/320/315/262/273 / 325/375/310 / 267Thu 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 ...........
The database is displayed at the end of the alarm log in the OPEN state to terminate because of the error.
2. Try to restart the database
BASH-2.03 $ SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.3.0 - Production on Thursday April 1 11:43:52 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
The idle routine is connected.
SQL> StartUporacle routines have been started.
Total System Global Area 4364148184 Bytesfixed Size 736728 Bytesvariable size 1845493760 Bytesdatabase buffers 2516582400 bytes database loading. ORA-01092: Oracle routine termination. Forced disconnection
...........
The problem of engineers reported.
3. Check the data file
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. ......................................... Discovered the existence file undotbs2.dbf
4. MOUNT database, check system parameters
BASH-2.03 $ SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.3.0 - Production on Thursday April 1 11:46:20 2004 CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
The idle routine is connected.
SQL>
SQL>
SQL> Startup Mount;
Oracle routines have been started.
Total System Global Area 4364148184 BYtes
Fixed size 736728 bytes
Variable size 1845493760 bytes
Database buffers 2516582400 Bytes
Redo buffers 1335296 bytes
The database is loaded.
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
/ 0 0 / dad g/ / /
/u01/oradata/gzhs/tools01.dbf
/u01/oradata/gzhs/Users01.dbf
/u01/oradata/gzhs/xdb01.dbf
.......................
/u01/oradata/gzhs/undotbs2.dbf
23 lines have been selected.
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
....................................
The discovery system does not use SPFILE, and the UNDO table space set by the initialization parameter is undotbs1
5. Check the parameter file
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
"InIntgzhs.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 # #################################### en_cursors = 300 ..... ................. ######################################################## ########## # system managements ################################################################################################################################################################################################################ ########## Undo_management = auto undo_retention = 10800 undo_tablespace = undotbs1
: Q! ...........
This setting is extremely suspicious. Doubting parameter files and actual database settings do not match.
6. Check the Alert file again to find the information of the operation of the undo table space, create the information when creating a database:
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 ...........
Note that this is also one of the two ways to create undo table space mentioned on the OCP textbook.
The second part, found record information for creating 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 ............
Part III, new undo table space applied to WED Mar 24 20:24:25 2004alter system set undo_tablespace = 'undotbs2' scope = memory; we found that the problem is here, after the new Undo table space, because of the use It is a PFile file that is modified only for the current instance. The operator has forgot to modify the PFile file. If you use spfile, the default modification range is Both, which will modify the spfile file at the same time, you can avoid the appearance of the above problems. Part 4, Delete the information of undotbs1
Wed Mar 24 20:25:01 2004
/ * Oracleoem * / Drop tableSpace "undotbs1" include incruding contents and datafiles cascade constraints
Wed Mar 24 20:25:03 2004
Deleted file /u01/oradata/gzhs/undotbs01.dbf
Completed: / * Oracleoem * / Drop TableSpace "undotbs1" incruuddi ...........
When this is restarted again, the problem has appeared, the undotbs1 defined in the Pfile can't find it, and the operation is really long ago, no one can recall, and even unable to know what person. 7. Change Pfile, start the database to modify the undo table space
##################################### stem management ############################################################################################################################################ .... BASH-2.03 $ SQLPLUS "/ as sysdba" SQL * Plus: Release 9.2.0.3.0 - Production on Thursday April 1 11:55:11 2004CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved Connected to: 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 - Productions QL> 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 - Productions QL> EXIT from 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 - Product CTION interrupt Bash-2.03 $
Here we can see that using spfile can be sent to manually modify the PFILE file, reducing the possibility of making mistakes. Since Oracle9i provides us with this new feature, it is worth learning to use it.
