Description of the situation:
System: SUN SOLARIS8
Database version: 9203
Problem Description: The engineer reports that the database does not start properly when it restarts. The check found that the Undo table space is missing.
The problem diagnosis and resolution process are as follows:
1. Login system Check Alert.log file
Checking the Alert.log file is usually the first step to diagnosing our database problem.
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) to 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 the scan
Thu APR 1 11:11:28 2004
Completed the 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\310\
267
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
.............
The end of the alert log shows that the database terminated abnormally in the open state because of an error.
2. Try restarting 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.
You are connected to an idle routine.
Sql> Startup
The ORACLE routine has started.
Total System Global area 4364148184 bytes
Fixed Size 736728 bytes
Variable Size 1845493760 bytes
Database buffers 2516582400 bytes
Redo buffers 1335296 bytes
Database loading complete.
Ora-01092:oracle routine terminated. To forcibly disconnect a connection
.............
The engineering staff reported the recurrence of the problem.
bash-2.03$ sqlplus "/As SYSDBA" Sql*plus:release 9.2.0.3.0-production on Thu April 1 11:46:20 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. You are connected to an idle routine. Sql> sql> sql> startup Mount;
The ORACLE routine has started.
Total System Global area 4364148184 bytes Fixed Size 736728 bytes Variable Size 1845493760 bytes Database buffers 25165824 Bytes Redo buffers 1335296 bytes database loading completed.
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 rows 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 system is not using SPFile, and the undo table space set by the initialization parameter is UNDOTBS1
5. Check 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
"Initgzhs.ora" [incomplete last line] 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!
.............
This setting is extremely suspicious.
Suspect that the parameter file does not match the actual database settings.
6. Check the alert file again
Find actions for the undo table space
The first part, the information when you create the database:
Sat Feb 7 20:30:12 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 L ocal DEFAULT Temporary tablespace TEMP tempfile '/u01/oradata/gzhs/temp01.dbf ' SIZE 1000M reuse autoextend on NEXT 250M MA Xsize 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 one of the two ways to create the undo table space mentioned in the OCP textbook
The second section discovers the record information for creating UNDOTBS2:
Wed 20:20:58/* oracleoem * * CREATE UNDO tablespace "UNDOTBS2" datafile '/u01/oradata/gzhs/undotbs2.dbf ' SIZE 1024M autoextend on NEXT 100M MAXSIZE Unlimited Wed Mar 20:22:37-Created undo Segment _syssmu11$ Created undo Segm ENT _syssmu12$ Created Undo Segment _syssmu13$ Created undo Segment _syssmu14$ Created Undo Segment _syssmu15$ Created Und o Segment _syssmu16$ Created Undo Segment _syssmu17$ Created Undo Segment _syssmu18$ Created Undo Segment _syssmu19$ creat Ed undo Segment _syssmu20$ Completed:/* oracleoem * CREATE Undo tablespace "UNDOTBS2"
Wed Mar 24 20:24:25 2004
Undo Segment One onlined
Undo Segment onlined
Undo Segment onlined
Undo Segment onlined
Undo Segment onlined
Undo Segment onlined
Undo Segment onlined
Undo Segment onlined
Undo Segment onlined
Undo Segment 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 offlined
Undo Tablespace 1 successfully switched out.
.............
The third part, the new undo table space is applied
Wed Mar 24 20:24:25 2004
ALTER SYSTEM SET undo_tablespace= ' UNDOTBS2 ' scope=memory;
We found the problem right here, after creating the new undo table space, the operator forgot to modify the Pfile file because the Pfile file was used and the modification only took effect on the current instance.
If you use SPFile, the default scope of modification is both, the SPFile file can be modified at the same time to avoid the above problems.
Part IV, delete the information of the UNDOTBS1
Wed 20:25:01/* Oracleoem/DROP tablespace "UNDOTBS1" including CONTENTS and datafiles CASCADE CONSTRAINTS W Ed Mar 20:25:03 Deleted file/u01/oradata/gzhs/undotbs01.dbf Completed:/* oracleoem */DROP tablespace "UNDOTBS1" Includi
.............
This is the time to restart the database, the problem arises, pfile defined in the UNDOTBS1 can not be found, and the operation is very long ago, no one could recall, and even can not know what person's operation.
7. Change Pfile, start database
Modify the Undo table space
###########################################
# 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 Thursday April 1 11:55:11 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connect to:
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
From Oracle9i Enterprise Edition release 9.2.0.3.0-64bit Production
With the partitioning, OLAP and Oracle Data Mining options
Disconnect in Jserver release 9.2.0.3.0-production
bash-2.03$
Here we can see that using spfile eliminates the hassle of manually modifying Pfile files and reduces the likelihood of making mistakes.
Now that Oracle9i has provided us with this new feature, it is worth learning to use it.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
