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 files are usually our first step in our diagnostic database problem 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_ar chive_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_a rchive_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_curs = 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))' ... 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 alert log in the Open state. 2. 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. Acted routine. 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 to disconnect the problem of the project report. 3. Check the data file Bash-2.03 $ CD / U01 / ORADATA / GZHSBASBASH-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 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 2004Copyright (C) 1982, 2002, Oracle Corporation. All Rights Reserved. Acted routine . SQL> SQL> SQL> Startup Mount; Oracle routines have been started. Total System Global Area 4364148184 Bytesfixed Size 736728 Bytesvariable size 1845493760 Bytesdatabase buffers 2516582400 bytes database loading.
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 has selected 23 rows.
SQL> SQL> Show Parameter undoname Type Value --------------------------- ------ ------------------------------ undo_management string AUTOundo_retention integer 10800undo_suppress_errors boolean FALSEundo_tablespace string UNDOTBS1SQL> show parameter spfileNAME TYPE Value ----------------------------------- ---------------------------- SPFile String Discovery System does not use SPFILE, and the UNDO table space set by the initialization parameter is undotbs1 5. Check the parameters file bash-2.03 $ cd $ ORACLE_HOME / dbsbash-2.03 $ lsinit.ora initgzhs.ora initgzhs.ora.old orapwgzhsinitdw.ora initgzhs.ora.hurray lkGZHS snapcf_gzhs.fbash-2.03 $ vi initgzhs.ora "initgzhs.ora" [Incomplete Last Line] 105 LINES, 3087 Characters ########################################################################################################################################################################################################################################################## ################################?991, 2001, 2002 by Oracle Corporation # ######################################################################################################################################################################################################################################################################################################## ######################################################################################################################################################################################################################################################################################################## ######################################################################################################################################################################################################################################## ############## log_archive_dest_1 = 'location = / u06 / o Radata / gzhs / arch'log_archive_format =% T_% s.dbflog_archive_start = true ###################################################################################################################################################################################################################################################### ########## Cache and I / o ############################################################################################################################################################################################################################################################## ######## DB_BLOCK_SIZE = 8192db_cache_size = 2516582400db_file_multiblock_read_count = 16 ##################################################################################################################################################################### ######### CURSORS AND library cache ######################################################################################################################################################################################################## ###### Open_cursors = 300 ...................... ########################### ################## stem managed undo and rollback segments ####################### ######################## Undo_management = autOUndO_Retention = 10800Undo_tablespace = undotbs1: Q! This setting is extremely suspicious. Doubtable parameter files and actual database settings do not match. 6. Check the Alert file again to find the first part of the operation of the undo table space,
Information when creating a database: Sat Feb 7 20:30:12 2004CREATE DATABASE gzhsMAXINSTANCES 1MAXLOGHISTORY 1MAXLOGFILES 5MAXLOGMEMBERS 3MAXDATAFILES 100DATAFILE '/u01/oradata/gzhs/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCALDEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE ' /u01/oradata/gzhs/temp01.dbf 'SIZE 1000M REUSE AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITEDUNDO TABLESPACE "UNDOTBS1" DATAFILE' /u01/oradata/gzhs/undotbs01.dbf 'SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITEDCHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16LOGFILE 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 mentioned in the OCP textbook. One of the second part of the way to create undo table space. Discover 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 Unlimitedwed Mar 24 20:22:37 2004Created undo segment _syssmu 11 $ 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 2004Undo Segment 11 OnlinedUndo Segment 12 OnlinedUndo Segment 13 OnlinedUndo Segment 14 OnlinedUndo Segment 15 OnlinedUndo Segment 16 OnlinedUndo Segment 17 OnlinedUndo Segment 18 OnlinedUndo Segment 19 OnlinedUndo Segment 20 OnlinedSuccessfully onlined Undo Tablespace 15.Undo Segment 1 OfflinedUndo Segment 2 OfflinedUndo Segment Segment 5 OfflinedUndo Segment 6 OfflinedUndo Segment 7 OfflinedUndo Segment 8 OfflinedUndo Segment 9 OfflinedUndo Segment 10 OfflinedUndo tablespace 1 successfully switched out. part 3 OfflinedUndo 4 OfflinedUndo Segment, new UNDO tablespace is applied 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 the Pfile file is used, the modified only take effect on the current instance, the operator has forgotten the modification of the Pfile file . Drop TableSpace "undotbs1" incruding contents and datafiles cascade constrainswed mar 24 20:25:03 2004Deleted file /u01/oradata/gzhs/undotbs01.dbfcomplet ED: / * ORACLEOEM * / DROP TABLESPACLE "undotbs1" Includ2 When the database 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, even unable to know Who is the operation.
